Saturday, 28 September 2013

How to: Setup PostgreSQL on PC

After few years working on MSSQL, I almost forgot there is still a whole world of databases out there to be explored, and a lot of which are free and very lightweight, such as SQLite, PostgreSQL.

Long story short, I cracked on setting up a PostgreSQL as it is work-related, and I probably should have done it on Linux. But the web portal is .NET based, so here we are.

In order to set up your own PostgreSQL server on PC, it is pretty straight forward, but with some little tricks.

1. Install PostgreSQL

Go to this official download page to get a copy of EnterpriseDB for free, and install it. 

Then you will find most of interesting bits are installed in "C:\Program Files\PostgreSQL\<version>\bin".

2.Install pgAdmin

After that, you can also find a copy of admin tool from download page, and install it.

Open the pgAdmin, and the fun begins. Go 'File -> Add Server' to create a new database server, and drop in the settings as below:

You should be able to see this:

4. Restore a Database

Now you can choose to create a new database or restore from backup. Since I was buggered by restoring, I am going to show you how I dealt with it.

The backup file I hold is in plain text .backup file (bad I know, but because there is no confidential information, so I did). It stops me from running direct restore, and prompted me this message "input file appears to be a text format dump. Please use psql.".

I've mentioned most fun things are under bin folder, therefore, psql executable is kinda easy to find. And by running command "cat <backupfile>.backup | <unc>psql <target dbname>", the database was restored! What the heck? Yes, even I was surprised.

If you are interested, there is lot more fun in the bin folder than just this.

5. ORM

Setting up a PostgreSQL seems to be a nice and painless experience, but how do I consume it?

One of the preferable way is to use ORM, and with the help from tools like Devart, it really is easier done than said. Devart is a .NET library to access data using database-first or model-first, similar to Entity Framework, but without the support for code-first as far as I notice. The only downside is Devart is not free.

By installing dotConnect for PostgreSQL plugin, you will get more options when creating data access models in Visual Studio.

Choose to use database first, to start configuring.

The problem I came across is a network setting issue which suggests me I am not using proper SSL.

Since we are not communicating with any public network, I find the 'pg_hba.conf' file under 'C:\Program Files\PostgreSQL\<version>\data', and hack through it by adding 'host all all md5' to the config.

Then everyone's happy!

After this, you can very much keep clicking next until finish if you don't have any specific requirements. And you will find yourself in a place as if you are using LinqToSQL.


PostgreSQL is probably not as powerful as MSSQL, but you get most of day to day functions if you are not hardcore DBA. It can be hosted on different platforms, and most importantly it is free.

Since I only started using PostgreSQL, I find its UI is somehow alien, and could be a bit clunky to use sometimes. The syntax has its own vein of blood when it comes to write functions.

ORM is definitely the best way to access data, yet Devart does not give you everything for free. Perhaps this will not be a problem when community sorts out code first. Then we can truly enjoy the convenience and freedom of using PostgreSQL.