Published 7/5/2007 11:05:48 PM  in  Postgresql

I have decided to give Postgresql a try. It is said to be a very competent database engine - it does support all advanced features needed such as triggers, stored procedures etc - while still being free to use .

This will be step 1 in this tutorial on getting Postgresql to work from wothin your ASP.NET application.

Step 1 is simply to install Postgresql.

Installing database engine

To make it more realistic I have decided to have it installed on my a VMWare session running Windows 2003 - this will take into consideration on how "hard" it is to get it to work from remote boxes ( please note that from one perspective is IS desirable for it to be hard - I mean allowing remote connection by default is not very good from a security  perspective )

Here's the link for the engine download. 

http://www.postgresql.org/ftp/binary/v8.2.1/win32/

The 8.2.1 is the current stable version as of writing this - however  

http://www.postgresql.org/download/

is the "official" way to go.

For Windows XP/2003 etc choose the postgresql-8.2.1-1.zip file. Around 24 MB - not bad at all.

I just unzipped it clicked one of the MSI files:

 Next:

I just clicked next here. Being a total novice going for standard is probably a good choice.

Running it as a service seems ok - never thought much about the account name etc but got some warnings:

So the account name is of course the Windows account name the service will run under - and while replacing it with a random password might seem good I decided not to - running as a virtual machine I couldn't care less for a weak password.

Next came this dialog:

I entered a simple password - this is the equivalent of sa in SQL Server or root in MySQL - again a simple to remember password suits me in this scenario. Also PLEASE NOTE THE "Accept connections" checkbox - I toggled it to be checked since I want to use it from other boxes.

Once again a new warning:

I would need to edit a file - pg_hba.conf - even though I toggled it. The install routine should be able to fix this if you ask me, however , it is free so I guess I shouldn't complain, should I?

Next I got to choose some extra modules:

I though the BLOB module might be useful so I threw that in.

And thats it. Installation is done! Now lets look at that file editing.

Allowing remote connections Postgresql

There is no GUI for editing the file - it's pretty much a Linux/Unix styled file we are about to do the changes in to allow for remote connections, however in the start menu there are some shortcuts (starting Notepad with that file loaded) - so we don't have to search for the file.

pg_hba.conf is used to specify client connection and authentication rules. Here are some examples:


# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
#host    all         all         ::1/128               md5

I changed it to
# IPv4 local connections:
host    all         all         0.0.0.0/0          md5
# IPv6 local connections:
#host    all         all         ::1/128               md5

Then you can (from the same menu) stop and start the service and we are done!

All in all - it took me about 30 minutes - that's pretty nice I think! Cause it did work at my first try, I will in next articles show you hopw to create a database, create a table in it, fill it with some data, and of course to use that database from an ASP.NET application.