Tuesday, December 4, 2007

Postgres on Cygwin

I'm working on a project that requires database support and I decided to use Postgres on Cygwin. I encountered mild difficulty (and lots of confusion) in setting it up so I decided to document what I have done. Here goes..

Install and run Cygserver as background service

The Cygwin User's Manual states that Cygserver provides Cygwin applications with services which require security arbitration or which need to persist while no other cygwin application is running.

Once installed as a service, you can start cygserver by running the net start cygserver command. Consequently, the service can be stopped by issuing a net stop cygserver command.

Create the postgres Windows user account

The account shall be used to run postgres as a service. Execute the command mkpasswd -l > /etc/passwd to update the local password file.

Additionally, grant the postgres account the rights to Logon as a service. This can be done by using the local security policy editor found under the Administrative Tools of the Windows Control Panel.

Initialize the database

Initialize the database by executing the command initdb -D {dir}. Popular choices for data directory are /var/postgresql/data, /usr/local/pgsql/data, or variations thereof. Change the ownership of the data directory and its sub-directories to postgres.

Install the postmaster service

Firstly, change the ownership of /usr/sbin/postgres.exe to postgres. Install postmaster as a service by running the command

cygrunsrv --install postmaster --disp "CYGWIN postmaster" --path /usr/sbin/postmaster --args "-D /usr/local/pgsql/data -i" --dep cygserver --termsig INT --shutdown --env CYGWIN=server --user postgres

The dependency (--dep) option automatically launches cygserver, if not yet started, when the postmaster service is started.

My Configuration:

- Windows XP Prof Service Pack 2
- Cygwin DLL release version 1.5.24(0.156/4/2)
- PostgreSQL version 8.0.7