Windows XP, Cygwin 1.3.22-1, PostgreSQL 7.3.2, CygIPC 1.13.2-1 installation steps

[Jason,

Hope this is ok to post.  Just figured I'd try to help others avoid the
issues I ran into.]


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

  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.  Voila!  Your system is like new.

  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 after the Windows
      system paths.  For example, it might read as

        C:\WINDOWS\system32;C:\WINDOWS;C:\Cygwin\bin;...
                                       ^^^^^^^^^^^^^^
        NOTE:  If you screw up, click [Cancel] to go back, then
               start again.
    * 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 755 /usr/bin /usr/bin/*

     This ensures that the directories/files have the right permissions
     for what we are doing. ***

     __________________________________________________________________
  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.
    ______________________________________________________________


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