Postgres Cygwin Install-as-Service on XP Pro/Home Mini HOWTO

Windows XP Home and Professional behave slightly different, compared to
Windows NT/2000, especially on issues of services and security models.
Therefore, the original instructions for installing postgres as a
service don't quite work with XP Home or Pro.

Here's what I've learned so far, which may be of great help to those
struggling with the same problem.

Windows XP Professional:

I haven't tried Postgres on Cygwin for XP Pro myself yet, but I believe
that you just need to follow all of the instructions in the
/usr/doc/Cygwin/postgresql-X.X.X-Readme file, as you would for Windows
NT or 2000.

BUT!!!! Swap Excerpted Steps 7 and 8. "initdb" won't actually run
successfully without ipc-daemon running in the background first, in my
experience.

Windows XP Home:

Because Windows XP Home has a number of differences from XP Pro,
primarily in user accounts/management, you can't simply follow the
standard NT/2000 install-as-service instructions. You have to do a few
things differently.

In case you're wondering or are confused, the following is an excerpt
from the /usr/doc/Cygwin/postgresql-7.2.1.README file, which is the
additional readme for Cygwin-specific issues for postgres. Note all of
the standard, non-Cygwin-specific docs are found in
/usr/doc/postgresql-7.2.1.

***************
BEGIN EXCERPT
***************

The following is the NT services Cygwin PostgreSQL installation procedure:

1. Install the cygipc ipc-daemon as a NT service:

# ipc-daemon --install-as-service

2. Create the "postgres" user account:

# cmd /c lusrmgr.msc # [3]
# mkpasswd -l -u postgres >>/etc/passwd

3. Grant the "postgres" user the "Log on as a service" user right:

# cmd /c secpol.msc [4]

4. Install postmaster as a NT service:

# cygrunsrv --install postmaster --path /usr/bin/postmaster --args
"-D /usr/share/postgresql/data -i" --dep ipc-daemon --termsig INT --user
postgres --shutdown

5. Create the PostgreSQL data directory:

# mkdir /usr/share/postgresql/data

6. Change ownership of the PostgreSQL data directory:

# chown postgres /usr/share/postgresql/data

7. Initialize PostgreSQL (*when running under the "postgres" account*):

$ initdb -D /usr/share/postgresql/data

8. Start the cygipc ipc-daemon:

# net start ipc-daemon

9. Start postmaster:

# net start postmaster

10. Connect to PostgreSQL:

# psql -U postgres template1

*************
END EXCERPT
*************

Compared to the excerpt, XP Home has a few differences, again, because
of its limited security model.

You must do all of the following from within a a Cygwin shell, logging
into Windows XP Home as an administrative user (any non-guest user),
preferably your normal user account. The account that was used to
install Cygwin is ideal due to some default filename/permissions that
Cygwin used during installation of itself, and of the postgres package.

I do everything on my machine as "kwong", so it was a natural choice to
log-in as kwong and launch a Cygwin shell under that user id. That's
the user account I use in the examples below. Substitute the
appropriate user account for your own installation.

Exerpted Step 1 works just fine.

Excerpted Step 2 and Step 3 don't work at all under XP Home, again due
to limitations. Skip them completely.

Excerpted Step 4 is very close, but doesn't quite work due to some funky
file/directory/login permissions. That's why the discussion above about
using the same user that had installed Cygwin and postgres to begin with...

So, instead of the command used in Excerpted Step 4, use this:

$ cygrunsrv --install "Cygwin postgres" --path /usr/bin/postmaster
--args "-D /usr/share/postgresql/data -i" --dep ipc-daemon --termsig INT
--user kwong --shutdown

Note, I'm assuming (as does the original readme) that you're going to be
using "/usr/share/postgres/data" as the data directory for postgres.
Again, note the username used here. It's best if the user that runs
the postgres service is the same one that installed postgres/Cygwin to
begin with. Otherwise, you need to get into lengthy manual permission
juggling in the postgres data directories.

Excerpted Steps 5 and 6 are fine.

Swap Excerpted Steps 7 and 8. "initdb" won't actually run successfully
without ipc-daemon running in the background first.

Excerpted Step 9 should be replaced with:

$ net start "Cygwin postgres"

I named my service a bit differently than in the original instructions,
to better match the naming conventions of other Cygwin services.

Excerpted Step 10 should be replaced with:

$ psql template1

You don't have to provide the username in launching psql, as we're
already logged-in as the user that created the databases. Again, this
is a limitation of XP Home security.

--
**********

This message is directed in confidence solely to the
person(s) identified. This message may also be subject to
privilege and all rights to that privilege are expressly
claimed. If you have received this message in error, please
notify the sender(s) and destroy the message immediately.

**********

Kevin Wong, LL.B
E-Mail: kevin-wong(at)usa(dot)net