Cygwin PostgreSQL Tips

Overview

This document contains tips for installing and running PostgreSQL 8.2.9 on Windows XP under Cygwin as a Cygwin package. Note: There is also a native Windows installer on the PostgreSQL website for version from 8.2 upwards, which will be an easier route to follow, if you simply want PostgreSQL running under Windows.

Installation

Install the PostgreSQL packages using the Cygwin setup program.

Instructions for installing and setting up PostgreSQL under Cygwin are located under /usr/share/doc/Cygwin/postgresql-8.2.9.README.

You must set the CYGWIN environment variable to include 'server'. This can be done in the shell with:

    $ export CYGWIN=server

But it is best to add it as a global environment variable under Windows. Goto Control Panel->System->Advanced Tab then select the 'Environment Variables' button. Add 'CYGWIN' with a value of 'server' to the 'System variables' list. New shells will see the change, but Windows services don't see the change until you have rebooted. Reboot now.

It is useful to create a database and role with your windows user name as the PostgreSQL client, pgsql, connects to these by default. Your Windows username is that shown by:

    $ echo $USERNAME

Install as a Windows Service

The README suggests this procedure is harder to setup than the manual startup and shutdown method, but it isn't that much harder and is well worth the effort.

Run the /usr/bin/cygserver-config script to install the cygserver as a Windows service, if you haven't previously had need to setup cygserver.

The default location for the database is /usr/share/posgresql/data but it really should be somewhere under /var. Edit /etc/rc.d/init.d/postgresql and change the line containing PGDATA=/usr/share/posgresql/data to PGDATA=/var/lib/postgresql/data. It's also a good idea to use UNICODE as the default character set, rather than SQLASCII to create databases. Find the line that contains SQLASCII as the database encoding type and change it to UNICODE.

Create the empty data directory.

    $ mkdir -p /var/lib/postgresql/data

Make sure /var/lib/postgresql/data can be written to by SYSTEM

    $ chgrp SYSTEM /var/lib/postgresql/data
    $ chmod g+rwx /var/lib/postgresql/data

Make sure /usr/sbin/postgres /usr/sbin/pg_ctl /usr/sbin/initdb have all got exec privs for SYSTEM user.

    $ chmod o+rx /usr/sbin/postgres /usr/sbin/pg_ctl /usr/sbin/initdb

Initialise the database with:

    $ /etc/rc.d/init.d/posgresql initdb

Install PostgreSQL as a service:

    $ /etc/rc.d/init.d/posgresql install

Then start the service. This should be running as the user SYSTEM.

    $ /etc/rc.d/init.d/posgresql start

If it fails to start, the error message should give some idea of why. It'll most likely be a file permissions problem. Also check /var/log/postgresql.log.

Create a user and database for yourself, using your Windows user name:

    $ /usr/sbin/createuser -U SYSTEM YOUR_WINDOWS_NAME
    $ /usr/sbin/createdb -U SYSTEM YOUR_WINDOWS_NAME

If the createuser or createdb commands don't exist, connect to the database as the SYSTEM user and use SQL to create the database and user/role.

    $ psql -U SYSTEM postgres
    postgres=# CREATE USER your_windows_name;
    postgres=# CREATE DATABASE your_windows_name;

You should now be able to access the database:

    $ psql -l

Manual Startup and Shutdown

The default location for the database is /usr/share/posgresql/data but it really should be somewhere under /var.

Initialise the PostgreSQL database with:

    $ mkdir -p /var/lib/postgresql/data
    $ /usr/sbin/initdb -D /var/lib/postgresql/data -E UNICODE

Start the database with:

/usr/sbin/pg_ctl -D /var/lib/postgresql/data/ -l /var/log/postgresql.log start

You should now be able to access the database:

    $ psql -l

Resources

-- Frank Dean - 18 Aug 2008

Related Topics: PostgreSQL