My Note on Solutions.: postgresql

Monday, March 7, 2011

How To : using Postgresql C# Client Npgsql to access postgresql database

After How to : install and test PostgreSql on Centos, we need to change several things to enable C# Client Access to the remote DB.

Create a User (login) with a password
Change the pg_hba.conf to turn on the user access from the giving IP range. change postgres.conf to change the listening ip address to * instead of just localhost by default.
install Npgsql .net assembly on the client machine and write C# code.

To Create a user demo with password demo

\h Create User # \h will list the help tips for every command
Create User demo superuser login password ’demo’;
after that, you can query pg_shdow system table to list all users and their hashed password

PG_HBA.conf is located in the pgdata directory, here it will /usr/local/pgsql/data
add one line in the top , to enable demo user from host 192.168.209.1 to access all db with the md5 credential.

host all demo 192.168.209.1/32 md5

for postgresql.conf, change the listen address to * include all ips

#listen_addresses = 'localhost'
listen_addresses = '*'

the restart the service. “ pg_ctl -D /usr/local/pgsql/data/ restart”

Download the NPGsql .net library, create a C# Console application and reference the download Assembly Npgsql.dll

all the NPGXXX class are simply the implantation of ado.net standard interface.

Here is 10 lines of code to connect to remote postgresql db hellodb and do a basic query.

string connString = "Server=192.168.209.130;port=5432;user id=demo;password=demo;database=hellodb";

using (NpgsqlConnection conn = new NpgsqlConnection(connString))
{
conn.Open();

NpgsqlCommand cmd = new NpgsqlCommand("SELECT name, countrycode from City limit 10", conn);
NpgsqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("City " + reader.GetString(0) + "Code " + reader.GetString(1));
}
}

hit f5 to run

to my suprice, it use its own data protocol.

How to : install and test PostgreSql on Centos

basically, just download and config , then make && make install.

Download the latest and stable source from Postgresql Site

wget http://wwwmaster.postgresql.org/redir/407/h/source/v9.0.3/postgresql-9.0.3.tar.gz

Unzip the tarball file.

tar -zxf postgresql-9.0.3.tar.gz

run the config, make sure prerequisites are there

yum install openssl-devel
yum install zlib-devel
yum install gnu-crypto
yum install readline-devel
yum install gcc-objc++

./configure –help # list all options to run the compilation, you can override the installation folder using the –-pre-fix options.

Fine tuning of the installation directories:
--bindir=DIR            user executables [EPREFIX/bin]
--sbindir=DIR           system admin executables [EPREFIX/sbin]
--libexecdir=DIR        program executables [EPREFIX/libexec]
--sysconfdir=DIR        read-only single-machine data [PREFIX/etc]
--sharedstatedir=DIR    modifiable architecture-independent data [PREFIX/com]
--localstatedir=DIR     modifiable single-machine data [PREFIX/var]
--libdir=DIR            object code libraries [EPREFIX/lib]
--includedir=DIR        C header files [PREFIX/include]
--oldincludedir=DIR     C header files for non-gcc [/usr/include]
--datarootdir=DIR       read-only arch.-independent data root [PREFIX/share]
--datadir=DIR           read-only architecture-independent data [DATAROOTDIR]
--infodir=DIR           info documentation [DATAROOTDIR/info]
--localedir=DIR         locale-dependent data [DATAROOTDIR/locale]
--mandir=DIR            man documentation [DATAROOTDIR/man]
--docdir=DIR            documentation root [DATAROOTDIR/doc/postgresql]
--htmldir=DIR           html documentation [DOCDIR]
--dvidir=DIR            dvi documentation [DOCDIR]
--pdfdir=DIR            pdf documentation [DOCDIR]
--psdir=DIR             ps documentation [DOCDIR]