Windows XP, Cygwin 1.3.22-1, PostgreSQL 7.3.2, CygIPC 1.13.2-1 installation steps (14May2003)

This posting and its updates is an attempt to reduce the number of
questions asked regarding installing PostgreSQL under Cygwin and Windows
XP. It is NOT meant as a "Quick Start" guide, but more like a
"Long-winded, Detailed Approach for Those for Whom the Quick Start
Approach has Failed." New/changed information is marked with '&&&'.

Date: 14 May 2003

For anyone newcomers just getting started with PostgreSQL running
under Cygwin and Windows, here is a general set of instructions that
should help you avoid some 'gotchas' during your install.

______________________________________________________________________
SOFTWARE VERSIONS

These instructions were written when the software was at the
following versions:

Cygwin setup.exe v2.340.2.5
Cygwin v1.3.22-1
CygIPC v1.13.2-1
PostgreSQL v7.3.2 (as packaged in Cygwin distribution)

Items marked with '***' indicate a workaround until bugs can be fixed in
Windows, in Cygwin, or whereever the bug is hiding.
______________________________________________________________________
CAVEATS/WARNINGS/NOTICES/BASIC INFO

A. WARNING!!!! If you are running Windows XP, DO NOT USE the
'Switch User' feature to jump between accounts. This is KEY! ***
Instead, completely log off as one user before logging on as
another.
[This is due to a bug in the 'Terminal Services' NT service. For
details, look through postings on this list.]

&&& Another option is to set up sshd and use ssh to simulate Unix's su:

$ ssh postgres(at)localhost # equivalent to "su postgres" on Unix

Note that the above will *not* trigger the XP Fast User Switching
problem. However, it does require setting up the sshd service,
which is outside the scope of this posting. So depending on your
comfort level/experience with Cygwin/PostgreSQL, use the method
which works best for you.

B. Cygwin does not 'hook' itself into Windows in any serious ways.
It basically does 3 things:

* creates a folder on your HD (typically C:\cygwin)
* Creates shortcuts on your desktop and/or Start menu
(see [Start] | All Programs | Cygwin)
* Adds a few keys to the Windows Registry
* HKCU\Software\Cygnus Solutions
* HKLM\Software\Cygnus Solutions

This means that at any time, if you are truly unhappy with the
Cygwin install, to "start fresh", simply shut down any Cygwin
related processes (e.g., the BASH shell and anything like PostgreSQL
or CygIPC) so that no files are locked, and then delete the items
&&& above (C:\cygwin folder, shortcuts on Desktop and/or Start menu
including Program group 'Cygwin', and the registry keys mentioned).
Voila! Your system is like new.

One warning, though: You may find when you go to delete the
Cygwin files in C:\cygwin that Windows tells you that files are
in use. This can be due to NOT shutting down some Cygwin app you
had running, OR it is possible that it is due to the way file
permissions are set on some folder within the C:\cygwin tree.
In the latter case, first try logging off the account completely
to see if some app you were using didn't "let go" of a file.
Second, be sure to be sitting at the console, as I have found
programs like pcAnywhere seem to goof a bit and (in the latest
case) sometimes keep files open in the weirdest places (like
an X11 font file!). Finally, if all else fails, override the
permissions on the entire C:\cygwin tree, being sure to give
yourself full rights to that folder and have it propogate those
rights down the entire tree. One of the above should free things
up so you can delete.

C. In its default configuration, you can think of Cygwin as Unix
running in a 'sandbox' as it were on your Windows PC. That is,
Cygwin stays within it's C:\cygwin directory tree and does not
stray. Any time you are asked to download a .tar/.gz/.bz file
and install it somehow in Cygwin, use whatever you normally would
use to download the file(s) in question, and just be sure to drop
them somewhere within C:\cygwin so that Cygwin can "see" the
file(s). For example, you might save the files to C:\cygwin\tmp,
then run the BASH shell and do

$ cd /tmp

to get to your new found file(s). Also note that any time you are
working with .tar/.gz/.bz files (any compressed file) that are meant
for use in Cygwin, it is best to use the tools that are within
Cygwin itself. This helps avoid the various issues of people using
Windows tools like WinZip and so forth to decompress files.

Think "Cygwin files are touched only by Cygwin tools."

D. CygIPC is such a .tar.bz2 file, so only work with it within Cywgin.

E. In MS Windows, you get used to files being in certain locations.
Programs tend to install their files in 'C:\Program Files'. The
Windows OS files themselves tend to be in 'C:\Windows' (or C:\WinNT
for those running Windows NT4 or 2000).

Just like Windows, Unix systems have places where you typically find
files. Cygwin, being a form of Unix if you will, follows this
model. For simplicity's sake, just note the following comparison:

MS Windows Unix/Cygwin
----------------------- -----------------------
Root of files C:\ /
Program files C:\Program Files /bin
/usr/bin
/usr/local/bin
Temp files C:\Windows\Temp /tmp
Program data C:\Documents & Settings /usr

This is NOT a complete picture, but will give you enough to start
playing.

F. PostgreSQL is a robust piece of software, and it was originally
written for Unix. Like any software, the more you understand it,
the better off you'll be. For now, just note the following:

* PostgreSQL's executable programs (e.g., postmaster, psql,
etc.) can be found in
/bin
/usr/bin
* PostgreSQL's database files and configuration files are stored
by default in
/usr/share/postgresql/data
* PostgreSQL's socket files (which provide a way for you to hook
into the database engine 'postmaster' from 'psql' etc.) are
found in
/tmp

G. For CygIPC, upon which PostgreSQL currently depends, note the
fowllowing:
* CygIPC's executable programs (e.g., ipc-daemon) can be found
in
/usr/local/bin
* CygIPC's semaphore files (which it uses to maintain data) can
be found in
/tmp

H. If you have difficulty in getting PostgreSQL to work, note that
often things can be traced to something related to 'permissions' and
whether one piece of software is allowed access to a file or another
piece of software based on who is asking for what.

With all this rattling in your brain, let's get started.
______________________________________________________________________
STEPS TO INSTALL AND RUN POSTGRESQL UNDER CYGWIN

__________________________________________________________________
1. Log into Windows as a user with Administrative Rights.

__________________________________________________________________
2. Note where you will be installing Cygwin. Normally this is
C:\cygwin
default, but if different, make note of it. For the duration,
these instructions assume you used the default.

__________________________________________________________________
3. Add 'C:\cygwin\bin' to the system PATH environment variable.
* Click on the [Start] button
* RIGHT-click on 'My Computer'
* Choose 'Properties' from the popup menu
* Click the 'Advanced' tab
* Click the [Environment Variables' button.
* Under 'System Variables', scroll down and double-click on
'Path' to bring up a dialog box.
&&& * Carefully edit the 'Variable value:' field and add an entry
for C:\Cygwin\bin. I recommend adding it BEFORE the Windows
system paths for reasons explained below.
For example, it might read as

C:\Cygwin\bin;C:\WINDOWS\system32;C:\WINDOWS;...
^^^^^^^^^^^^^^
NOTE: If you screw up, click [Cancel] to go back, then
start again.

[The reason for placing Cygwin BEFORE the Windows directory is
to make sure PostgreSQL can find the right utilities that it
needs. For example, PostgreSQL relies on 'sort' and 'find',
who common Unix utilities. However, Windows ALSO offers
'sort' and 'find' commands. However, they behave differently.
In short, note whether you place Cygwin's path before or after
the Windows system paths, it has the potential to impact your
user experience. If you do not spend much time at the Windows
Command Prompt, odds are you won't notice the difference if
Cygwin is placed before the Windows system paths. But if you
use the above Windows utilities, note you'll find the Cygwin
versions work differently.]

* Click [Ok] to save your changes, and keep clicking [Ok] to
close out of all dialog boxes and windows.

__________________________________________________________________
4. Install Cygwin as usual.
This instruction is purposefully vague, as there are many ways in
which Cygwin could be installed. Most folks simply visit

http://www.cygwin.com

and run the setup.exe file directly from the site. If you do this,
setup.exe guides you through the process, though you may wish to
read up on Cygwin itself on the website first.

__________________________________________________________________
5. Once Cygwin has finished installing, run the Cygwin BASH Shell
(normally an icon is created on the Desktop or under
[Start] | All Programs | Cygwin) and type the following commands
(the $ is the prompt...do not type this):

$ chmod 777 /tmp
$ chmod a+rx /usr/bin /usr/bin/*

This ensures that the directories/files have the right permissions
for what we are doing. /tmp has full access by everyone, and we
have added read/write access for all to the /usr/bin directory so
you can execute programs regardless of what account you are logged
in with. ***

__________________________________________________________________
6. At this point, we needed the latest CVS snapshot version of
cygwin1.dll. ***
There appears to be a bug in the current release which causes
trouble when you want to run the client 'psql' program to hook into
'postmaster' on the same computer. NOTE: If you only connect to
PostgreSQL via TCP/IP connections, you may skip this step.

* Download the latest CVS snapshot build by visiting

http://cygwin.com/snapshots/

and clicking on the latest cygwin1-YYYYMMDD.dll.bz2 file,
makin sure to save it within the Cygwin tree.
These instructions assume a file called
'cygwin1-20030504.dll.bz2' and that it is stored in /tmp
(i.e., C:\cygwin\tmp).
* Run the Cygwin BASH Shell and enter the following commands:

$ cd /tmp
$ bunzip2 cygwin1-20030504.dll.bz2

* Exit the BASH shell and make sure no other Cygwin programs
are running.
* From Windows itself, using whatever mechanism you are
comfortable with, drill down to
C:\cygwin\bin
* Locate the file 'cygwin1.dll' and rename it 'cygwin1.dll.old'.
* Now navigate to
C:\cygwin\tmp
and rename 'cygwin1-20030504.dll.bz2' to 'cygwin1.dll'
* Copy the file 'cygwin1.dll' in C:\cygwin\tmp over to
C:\cygwin\bin
* You have now effectively updated your cygwin1.dll file to an
updated version that should work.

__________________________________________________________________
7. Install CygIPC as per its instructions.
Basically, visit this link to download CygIPC v1.13.2-1:

http://www.neuro.gatech.edu/users/cwilson/cygutils/cygipc/cygipc-1.13-2.tar.bz2

Make sure to save the file somewhere within Cygwin's space. These
instructions assume you saved the file in C:\Cygwin\tmp.

Now run the Cygwin BASH Shell and type the following commands:

$ cd /
$ bunzip2 -c /tmp/cygipc-1.13-2.tar.bz2 | tar xvf -

This should decompress CygIPC to the right locations.

For reference, note the CygIPC page is listed at

http://www.neuro.gatech.edu/users/cwilson/cygutils/cygipc/index.html

and the instructions they provide for installing CygIPC are at

http://www.neuro.gatech.edu/users/cwilson/cygutils/how_to_install.html

__________________________________________________________________
8. At this point, you are ready to follow the instructions written by
Jason Tishler, which can be found either in the Cygwin file located
at /usr/doc/Cygwin/postgresql-7.3.2.README
(i.e., C:\cygwin\usr\doc\Cygwin\postgresql-7.3.2.README)
or online at

http://www.tishler.net/jason/software/postgresql/postgresql-7.3.2.README

Note that when you reach Step #10 in the README file, if you wish to
access the PostgreSQL database engine internally (using sockets),
you must have done step #6 above (at least until the official
Cygwin1.dll is updated).

If you have no intention of accessing PostgreSQL internally, but
rather intend, like many people, to access the database via TCP/IP
connections, then also note you must add a step to the instructions
in the README, basically editing the files

/usr/share/postgresql/data/postgresql.conf
/usr/share/postgresql/data/pg_hba.conf

'postgresql.conf' controls whether TCP/IP connections are allowed at
all, and 'pg_hba.conf' specifies who is allowed to connect to what.

In the following steps, it is assumed you will use the PICO editor
within the Cygwin BASH shell to edit the file above. However,
you could also edit this file from Windows using an editor that
does not mangle the file (Do NOT use Windows NotePad). For example,
you could go to [Start] | All Programs | Accessories | WordPad, then
click File | Open... and navigate to

C:\cygwin\usr\share\postgresql\data\postgresql.conf
C:\cygwin\usr\share\postgresql\data\pg_hba.conf

and edit the files as indicated below. Your choice.

______________________________________________________________
Step #8.1: Setup PostgreSQL to allow TCP/IP connections.

* Run Cygwin BASH Shell and type the commands:

$ cd /usr/share/postgresql/data
$ pico postgresql.conf

* Hit [PageDown] until you see
______________________________________________________
...
#
# Connection Parameters
#
#tcpip_socket = false
#ssl = false
....
______________________________________________________

and change the tcpip_socket line to
______________________________________________________
...
#
# Connection Parameters
#
tcpip_socket = true
#ssl = false
....
______________________________________________________

* Now hit [CTRL]-[X], then [Y], then [Enter] to save
the file. You have now enabled TCP/IP connections.
* Next open the pg_hba.conf file using the commands:

$ cd /usr/share/postgresql/data
$ pico pg_hba.conf

read the file and understand what it is telling you, then
make adjustments accordingly. By default this file will
allow anyone on 'localhost' (the same PC that PostgreSQL is
running on) to connect. However, if you are running software
such as pgAdmin II, EMS PostgreSQL Manager, PG Explorer, or
any of the other such utilities from a DIFFERENT PC than the
the one installed Cygwin/PostgreSQL onto, you must modify
this file to permit your client PC access.
______________________________________________________________

&&&
9. At this point, you should be good to go. However, please note
the following. PostgreSQL, like many Unix services, uses a PID
(Process ID) file. Specifically, when 'postmaster' is run, it
it creates a file

/usr/share/postgresql/data/postmaster.pid

which contains the process ID that identifies the server
process. And when 'postmaster' shuts down, it deletes this PID
file...in theory.

The idea, in most cases, is that this allows you to scripts
for various purposes (e.g., shutting down 'postmaster'),
where you simply access this postmaster.pid file to obtain the
process id, compared to having to do a command like 'ps -ef'
and pipe the output to 'grep' or a file, where you then have to
search for the process id.

The challenge is that sometimes the PID file is NOT deleted.
This is a problem, as 'postmaster' will fail to start if the
postmaster.pid file already exists when you start up the
service. For example, in the NT service configuration
assumed here, it seems fair that you will want your PostgreSQL
engine to shutdown cleanly when Windows XP is told to shutdown
or restart, and that PostgreSQL will start up cleanly on the
next (re)boot.

Unfortunately, I often find that, for whatever reason, the
postmaster.pid file is left behind on reboot, and this is a
problem. If, on rebooting your Windows XP box, you find
PostgreSQL is no longer running, either check your Windows
Application Event Log (though this tends not to provide much
detail beyond the fact PostgreSQL failed to start) or look at
the PostgreSQL log file for details.

To check the Windows Application Event Log:

* Click on the [Start] button
* RIGHT-click on 'My Computer'
* Choose 'Manage' from the popup menu
* Look in
Computer Management (Local)
[-] System Tools
[-] Event Viewer
| +--- Application

To check the PostgreSQL log file:

* Run Cygwin BASH Shell and type the command:

$ more /var/log/postmaster.log

The solution to this is simply to manually DELETE the PID
file and then start 'postmaster'. However, if your intention
is to have a production level PostgreSQL server, this is
hardly sufficient. As one possible alternative, you may wish
to simply delete any such PID file when you restart your PC.
One way to do this is to modify the /etc/rc.d/rc.sysinit file
(which already is set to delete the PostgreSQL socket files in
/tmp) as follows (note the 'chmod' commands are used to make
sure Cygwin is able to delete the files:

* Run Cygwin BASH Shell and type the commands:

$ cd /etc/rc.d
$ pico rc.sysinit

* Hit [PageDown] until you see
______________________________________________________
...
# Delete Postgres sockets
rm -f /tmp/.s.PGSQL.*
....
______________________________________________________

and change it to the following:
______________________________________________________
...
# Delete Postgres sockets
chmod 777 /tmp/.s.PGSQL.*
rm -f /tmp/.s.PGSQL.*

# Delete Postgres PID file
chmod 777 /usr/share/postgresql/data/postmaster.pid
rm -f /usr/share/postgresql/data/postmaster.pid
...
______________________________________________________

* Now hit [CTRL]-[X], then [Y], then [Enter] to save
the file. You have now configured Cygwin to delete the
postmaster.pid file at each bootup.

Please note this is not a silver bullet however. There may
still be times when you find that PostgreSQL has not started,
and more often than not, it is due to a lingering postmaster.pid
file. If this is the case, you may need to seek other methods
(e.g., .BATch files, etc.) to make sure the PID file is deleted
on Windows startup. Just be sure whatever method you choose that
you have deleted the PID file BEFORE the 'postmaster' service
attempts to start. Other possibilities include, but are not
limited to, using software like KIXtart (www.kixtart.org) or
FireDaemon (www.firedaemon.com). I leave that to your
imagination. This is just to make you aware that this PID file
can be a pain the tush if it exists on startup.

______________________________________________________________________
FINAL COMMENTS

For those wishing to access the PostgreSQL engine (postmaster) via
TCP/IP, note the psql command changes slightly. Whereas locally you
would type something like

$ psql -U postgres template1

for a TCP/IP connection, you would type

$ psql -h localhost -U postgres template1

This assumes the default PostgreSQL TCP/IP port (5432). For more
detailed instructions, type

$ psql --help

for more information.

Also note that this message, like Cygwin and PostgreSQL, is a work
in progress. I just wanted to get something out there that might help
those who are looking for the steps necessary and were having trouble
similar to myself. Hope this helps.