Use PostgreSQL and PHP on Windows

Installing Services

Installation complete? No, not yet. The files are there, somewhere in c:\cygwin or wherever you chose to put them. Now you must install services, create a postgres user and initialize PostgreSQL.

These instructions are based on the postgresql-7.3.4.README file, written by Jason Tishler, which is located in /usr/doc/Cygwin. In that file, you can find instructions for the Basic Cygwin PostgreSQL Installation, which is your only option if you're running Windows 95/98/ME (but if you ask me, the only option for someone running Windows 9x is: upgrade!).

Start your newly-installed Cygwin Bash Shell and complete these steps:

Install ipc-daemon2 as an NT service

As you remember from the Cygwin installation, Cygipc is the interprocess communication daemon that PostgreSQL depends on.

ipc-daemon2 --install-as-service

If you are a curious person (like me), launch the Services MMC Console Snap-in in Windows (services.msc) and verify that you have a new service called Cygwin IPC Daemon 2 in the list.

Create a new user account

A quote from the PostgreSQL documentation states: "It is advisable to run PostgreSQL under a separate user account".

So, let's add a new user, and due to lack of imagination, call the new user "postgres". Note that you should replace ******** below with the password you want to use for postgres.

You can issue one single net user command with all the options (fullname, comment, homedir) on one line, but I've written them as separate commands here to make the text more readable. To see the net user syntax, type "net user /?".

net user postgres ******** /add  
net user postgres /fullname:postgres  
net user postgres /comment:'PostgreSQL User Account'  
net user postgres /homedir:"$(cygpath -w /home/postgres)"

If you feel like it, check in the Local Users and Groups MMC Snap-in (compmgmt.msc) that postgres has joined the team. There should also be a newly created folder called C:\cygwin\home\postgres.

Update the file /etc/passwd

mkpasswd -l -u postgres >> /etc/passwd

The command above, translated into English, means: append information for the specified user postgres to /etc/passwd. Please note that there should be two greater-than characters!

Grant postgres the right to log on as a service

Why? This is rather obvious; postgres is the user that should run the postmaster service later. Launch the Local Security Settings MMC Snap-in (secpol.msc) and add postgres to the list of users that can log on as a service, under Local Policies \ User Rights Assignment.

Create a data directory

This is where PostgreSQL will store the data, of course.

mkdir /usr/share/postgresql/data

Make postgres the owner of the data area

Another natural thing; no other than postgres should own the data directory, of course.

chown postgres /usr/share/postgresql/data

Later, the initialization process will revoke permissions for everyone except postgres.

Install postmaster as an NT service

Postmaster is the name of the PostgreSQL server, located in /usr/bin. Postmaster will also be the name of the service you create (who said "lack of imagination"?!).

cygrunsrv -I postmaster -p /usr/bin/postmaster  
   -a "-D /usr/share/postgresql/data -i"  
   -y ipc-daemon2 -s INT -u postgres –o

Note that the command has been divided into three lines here to make it more readable, but you should write everything on one line.

Whoa! What do all these options mean? Translation: Install (-I) a new service called postmaster, with application path (-p) /usr/bin/postmaster, arguments (-a) "-D /usr/share/postgresql/data -i", which in turn means that postmaster should use /usr/share/postgresql/data as data directory (-D) and TCP/IP connections (-i) should be enabled.

Further on, the postmaster service depends on (-y) another service called ipc-daemon2, sends the signal INT when terminated (-s), is run by the user (-u) postgres and stops during system shutdown (-o).

Cygrunsrv will ask you (twice) for the password interactively.

To see the cygrunsrv syntax, type "cygrunsrv -h". If you want to learn more about options for the PostgreSQL server, type "postmaster --help".

Now, if you look in the Services MMC Console Snap-in (services.msc), you will see yet another service in the list. Double-click on postmaster to review the different properties you just assigned (Log On, Dependencies etc.).

Create a Database Cluster

Before you can start postmaster (the service), the database storage must be initialized. You must run the command initdb logged in as the postgres user.

Note: log in as the postgres user! To avoid all possible problems, log off from Windows, and log in again, but this time as postgres (i.e. don't use the su command in cygwin to switch user).

Start the Cygwin Bash Shell and enter:

initdb -D /usr/share/postgresql/data

The switch -D is used to indicate where the data area is located (quite easy to guess, wasn't it?). Now you can log out, and log in as yourself again.

Test to see whether PostgreSQL is working

Finally, you should now be able to connect to PostgreSQL. Launch Cygwin, connect as the user postgres and use the database template1, created by initdb.

psql -U postgres template1

The database template1 is, as you understand, the default template that's copied when you create a new database using the createdb command or the CREATE DATABASE statement.

If, for some reason, you receive a message like "psql: could not connect to server", start the postmaster service with this command:

net start postmaster

To learn more about how to use psql, the PostgreSQL interactive terminal, type "psql --help". I think we can define a general rule here: any command plus "-- help" will show you some helpful text!