Sunday, April 19, 2009

Installing PostgreSQL (soft version)

Note: below description uses PostgreSQL 8.3.7 version installed on 32 bit Windows XP.
Installing PostgreSQL is theoretically piece of cake, but in my opinion there are some aspects worth saying a little more.
Those who installed MySQL database using the graphic installer on Windows, know that the installation process goes something like this: "Next, next, next, yes I want to have root access from remote machines, quit." This is almost the same as installing any program on Windows. PostgreSQL installer is not much more different in this case.

So what's the point? In a slightly different philosophy of security model - different than in MySQL. Okay, enough of this pseudo-technological gibberish - let's install database.

Step 1: Download the PostgreSQL from here. Download a file named

Step 2: After unpacking the zip file, run the installer and choose English

Step 3: after the splash screen and license information installer will ask us about the target installation directory. I suggest using directory C:\Development\PostgreSQL.

Step 4: Configure account for the PostgreSQL service.

It is convenient to install PostgreSQL as a service in Windows - the database server will always run at startup. We have something new in this point: PostgreSQL requires a special account in the system that does not have administrator rights in order to start the database service and initialize the database cluster. It is important to set a well-known password for this account, because in case of problems with PostgreSQL we will be able to use this account to peform some fixes on database files.

Step 5: configure the settings of the database server, super user account (root) and access from remote machines.

This is a step similar to step in the installation of MySQL, I recommend setting the server and client encoding to UTF-8 - this is the standard that should be use instead of some bizarre national encoding. Why? Because it gives us the flexibility - perhaps we want to store in our database accented Polish and German umlauts. In addition we will avoid potential problems with conversions etc.

Step 6: Installation.

Warning: before installation make sure that You have enabled and running Windows service named "Secondary Logon". Our system account "postgres" from step 4 needs that service. When it is not enabled, the installer will report an error:

Just turn on the service and the trouble is gone. Then on next screens just click "Next" without changing anything until You get to the last screen - the final step in our installation process.

Step 7: The end of the installation.

The selected checkbox allows You to install additional software and extensions for PostgreSQL. At the moment we do not need them so we can safely uncheck it and complete the installation - voila, we have PostgresSQL :-)

Okay, once we have database installed, let's take a look inside - just to write simple SELECT and be proud of Yourself that You still remember some SQL syntax ;-) Nothing could be more simple. Along with PostgreSQL was installed pgAdminIII - an advanced graphical client. Just find it in the windows start menu and run:

When You double-click the server, You will be asked for Your root password from step 5. After typing the correct password, You will be logged to the server as root (superuser):

PostgreSQL creates a default database named "postgres". We can log in to this default database using command line. Of course, we can also delete this database. Then we have available two more so-called "service" databases that are hidden (they are not visible by default in pgAdmin tree view). The names of those databases are "template0" and "template1". PgAdmin allows us to configure the server, manage users, roles, databases, etc. For details, refer to the documentation.

No comments: