This post originated from an RSS feed registered with Java Buzz by Weiqi Gao. Original Post: PostgreSQL 8.1.0 Comes To Cygwin Feed Title: Weiqi Gao's Weblog Feed URL: http://www.weiqigao.com/blog/rss.xml Feed Description: Sharing My Experience... Latest Java Buzz Posts Latest Java Buzz Posts by Weiqi Gao Latest Posts From Weiqi Gao's Weblog

For the longest time, my development relational database of choice has been PostgreSQL. It offers the features that I care about (the simple stuff: create table, select from where order by group by, transactions, JDBC drivers) on the platforms that I use the most often (GNU/Linux, Windows/Cygwin) under a BSD license.

What I like the most is the fact that it comes with the Red Hat distribution since the early days. For the last few years, a Cygwin compiled version also showed up as part of the Cygwin net distribution.

The Cygwin version, however, was broken earlier this summer. And I was without a database on Windows for a while. Finally last week, Reini Urban announced the availability of PostgreSQL 8.0.4 and 8.1.0 for Cygwin.

Setting up PostgreSQL as a Windows service has always been a tricky endeavor. The new release offers some relief by providing an init script that can simplify the process. According to the release notes, it could be as simple as:

/etc/rc.d/init.d/postgresql initdb
/etc/rc.d/init.d/postgresql install
/etc/rc.d/init.d/postgresql start

While this is essentially true, there are some hurdles that I have to jump over to have everything up and running. Let me just say that the script can be improved upon. Here's my Cygwin PostgreSQL 8.1.0 setup first impression:

• First of all, the CYGWIN environment variable needs to contain the word server. (I have CYGWIN=ntsec server.)
• The cygserver service needs to be installed already. (Running cygserver-config once will do it.)
• The initdb task will create the database template in /usr/share/postgresql/data and create a directory /tmp/postgresql for runtime use.
• After a fresh installation, the directory /usr/share/postgresql already contains some files and subdirectories. initdb won't like it if /usr/share/postgresql/data already exists and is non-empty or if /tmp/postgresql exists. (A failed run of initdb will create such a situation.)
• There are plenty of file/directory permission problems that would cause the install or the start steps to fail. All because the service is run as Local System, not the logged in user. Files and directories that need to open up permission include
  • /usr/sbin/postgres.exe (needs chmod ugo+rx),
  • /usr/share/postgresql/data (needs chmod ugo+rx),
  • /tmp/postgresql (needs chmod ugo+rwx),
  • /usr/share/postgresql/data/global (needs chmod ug+rwx.)
• The /etc/rc.d/init.d/postgresql script contains a chunk of code to ensure that the CYGWIN environment variable contains the word server. But it went too far. This caused the install task to fail with an error message saying "CYGWIN must contain server" even though my CYGWIN already does.
• The log file at /var/log/postgresql.log provided valuable information while I'm trouble shooting my setup.
• The psql (PostgreSQL's interactive SQL command line tool) seems to look for a UNIX domain socket to connect to at the wrong place (/tmp/.s.PGSQL.5432 instead of /tmp/postgresql/.s.PGSQL.5432.) I have to connect to the TCP socket by running psql -h localhost.
• The bootstrap user Id for PostgreSQL is SYSTEM. So to add my own login to PostgreSQL I need to run /usr/sbin/createuser -U SYSTEM

Here's the portion of /etc/rc.d/init.d/postgresql I modified

    # check for CYGWIN containing server
    if ! echo $CYGWIN | grep -q server
    then
        echo "ERROR CYGWIN must contain server for cygserver to work properly"
        exit 1
    fi

The if line used to say

    if [ -n "${CYGWIN%server}" ]

It sounds like a lot written down, but the actual trial and error of the setup is pretty easy and It took me less then 30 minutes to figure all of these out. What is important is that at the end, I have a functional PostgreSQL server running as a Windows service again:

[weiqi@gao] $ psql -h localhost
Welcome to psql 8.1.0, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

weiqi=# create table people (first_name varchar, last_name varchar);
CREATE TABLE
weiqi=# insert into people values ('Weiqi', 'Gao');
INSERT 0 1
weiqi=# select * from people;
 first_name | last_name
------------+-----------
 Weiqi      | Gao
(1 row)

weiqi=# \q
[weiqi@gao] $