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

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@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.