Today I was helping a colleague of mine to install and configure PostgreSQL 9.1 to his laptop which was running on Windows 7. We had several problems with the one click installer and after the installation process was finished, we could not start the PostgreSQL database.
Since we could not solve this problem in a reasonable amount of time, we had to use the binary zip distribution. The installation process was quite straightforward but it took some time because I could not find a decent tutorial about it. This blog entry is written to fill that void. Also, even though this blog entry describes the installation process of PostgreSQL 9.1, these instructions should be valid for older versions of PostgreSQL as well.
The required steps are described with more details in later Sections of this blog entry.
Preparing the PostgreSQL 9.1 Installation
This process has two steps:
- Download and install the Microsoft Visual C++ 2008 redistributable package.
- Download and unpack the PostgreSQL binary distribution.
These steps are described with more details in the following.
I am not sure which PostgreSQL versions require the Microsoft Visual C++ 2008 distributable package but if I remember correctly, I had to install it when I installed PostgreSQL 9.1.
Downloading the Microsoft Visual C++ 2008 Redistributable Package
The first step is to download and install the Microsoft Visual C++ 2008 redistributable package.
Downloading and Unpacking the PostgreSQL binary distribution
The second step is to download the binary zip distribution.
After you have downloaded the binary zip distribution, you can unzip the binary distribution to the preferred directory. When this is done, the target directory should contain a directory called ‘pgsql’. This directory contains the binaries of PostgreSQL 9.1. I will refer this directory simply as POSTGRESQL_ROOT (Remember to replace the string POSTGRESQL_ROOT with the actual directory path of you installation when following these instructions).
The next step is to create the data and log directories for your PostgreSQL installation. This is done by creating the following directories to the POSTGRESQL_ROOT directory:
- The ‘data’ directory contains the data files of your PostgreSQL installation.
- The ‘log’ directory contains the logs of your PostgreSQL installation.
You have now finished the needed preparations and can move on to the next phase of the installation process.
Configuring the PostgreSQL 9.1 Installation
The next step is to create a new PostgreSQL database cluster. You can do this by using the initdb command which is found from the POSTGRESQL_ROOT\bin directory. You can create the database cluster by running the following command from the bin directory of your PostgreSQL installation:
initdb -U postgres -A password -E utf8 -W -D POSTGRESQL_ROOT\data
The command line parameters of the initdb command are described in following:
- -U postgres means that the superuser account of your database is called ‘postgres’.
- -A password means that password authentication is used.
- -E utf8 means that the default encoding will be UTF-8.
- -W means that you will enter the superuser password manually.
- -D POSTGRESQL_ROOT\data specifies the data directory of your PostgreSQL installation.
After you have successfully created the database cluster, your PostgreSQL installation is ready to be used. You can start and stop your database instance by using the following commands:
The database can be started by running the following command:
"POSTGRESQL_ROOT/bin/pg_ctl" -D "POSTGRESQL_ROOT/data" -l "POSTGRESQL_ROOT/log/pgsql.log" start
The database can be stopped by running the following command:
"POSTGRESQL_ROOT/bin/pg_ctl" -D "POSTGRESQL_ROOT/data" -l "POSTGRESQL_ROOT/log/pgsql.log" stop
To make things as easy as possible, you should create new shortcuts to your desktop and use them for starting and stopping the database server.
Note: If you want to run PostgreSQL as a service, you should run the following command:
POSTGRESQL_ROOT/bin/pg_ctl.exe register -N "postgresql" -U "NT AUTHORITY\NetworkService" -D "POSTGRESQL_ROOT/data" -w
After you have done this, you can start the service by using the Services panel.
Your PostgreSQL 9.1 installation should now be functional. The next step is to start the database server by using the created shortcut and verify that you can connect to your database instance. You can use the PgAdmin database management tool for this task and congratulate yourself for a job well done.