OpenACS on Windows: Installing PostgreSQL

Overview
These directions describe how to install and configure PostgreSQL for use with OpenACS on Windows.  PostgreSQL doesn't natively run on Windows (yet), so we are going to install it as part of the Cygwin environment.  We are going to set the database up to run as a service, which means it can be running when nobody is logged in to the machine.

THINGS THAT SHOULD BE CLARIFIED ARE IN ALL CAPS.
This document last modified: 12 March 2003

Installing PostgreSQL / Cygwin

I SHOULD UPDATE THE INSTRUCTIONS BELOW TO REFLECT THE CREATION AND USE OF A POSTGRES ACCOUNT AS IN JASON TISHLER'S README.

Before beginning, make sure that you are a local machine administator. This is necessary because you will be installing services, etc.

1. Install the latest verion (1.3.19+) of Cygwin
   • Get setup.exe from the "Install Cygwin now" link on the Cygwin homepage. (http://sources.redhat.com/cygwin/) Save it to C:\download\cygwin.
   • Run C:\download\cygwin\setup.exe
     1. Select "Install from Internet"
     2. Use C:\cygwin for your install root directory, default text file type Unix, and install for all users.  The Unix mount time is necessary for several programs to work properly.  (XFree86, etc.)
     3. Use C:\download\cygwin as your local package directory
     4. I used a direct connection and a host in the UK - you should use whatever works for you.
     5. In addition to installing the default packages, install cygrunsrv 0.95-1+ under "Admin", PostgreSQL 7.3.2-1+ under "Database", less 378-1+ under "Text", and bzip2 1.0.2-2+ under "Utils". You could get away with installing fewer packages, but I don't know the dependencies. Lots of the other packages are useful, so poke around for more yummy programs to play with.
     6. If you want to build the PostgreSQL driver for AOLserver, make sure that the "src" box is checked for PostgreSQL.  We will use the source to build a native Windows version of libpq.
     7. Once Cygwin is installed, open up a Bash Shell. If you see "I have no name!@compname", or garbage@compname, you need to create an /etc/passwd file.  If you are logged in with a Windows domain account, also use the -c options to mkpasswd.  Run the following commands from the Cygwin bash shell:

        /bin/mkpasswd -l > /etc/passwd /bin/mkgroup -l > /etc/group

   • Add C:\cygwin\bin to the PATH system environment variable.  It must come before the System32 directory, because PostgreSQL needs to use cygwin's sort.exe.  It is also necessary so that the Cygwin IPC service we are about to install can find cygwin1.dll.  Finally, we may be using some Cygwin commands later, so we want them to be in the path.
   • You can now safely delete the C:\download\cygwin folder and all of its contents if you choose
   
   
2. Install Charles Wilson's Cygwin IPC package. According to the webpage, this work is going to be absorbed by the forthcoming "Cygwin Daemon", a new native component of the Cygwin distribution.  But for now, you'll still need this package.
   • Download the precompiled version 1.13-2 binaries from http://www.neuro.gatech.edu/users/cwilson/cygutils/cygipc/cygipc-1.13-2.tar.bz2 and save the file to C:\cygwin
   • Run the following commands from a Win2k cmd prompt:  CAN WE USE TIGHT SECURITY? DO WE NEED TO EDIT THE REGISTRY?

     cd c:\cygwin bunzip2 cygipc-1.13-2.tar.bz2 tar xvf cygipc-1.13-2.tar cd usr\local\bin ipc-daemon --install-as-service net start ipc-daemon

   • You can now safely delete C:\cygwin\cygipc-1.13-2.tar if you choose
   
   
3. Build and Initialize PostgreSQL
   • This step can generate some ugly messages that look like errors. But eventually you should see the message, "All Win32 parts have been built!". Run the following commands from a Win2k cmd prompt, assuming you are using PostgreSQL version 7.3.2-1 (if you aren't you may also have to fix the dsp file for nspostgres):  ADD INSTRUCTIONS FOR DEBUG BUILD?

     cd c:\cygwin\usr\src\postgresql-7.3.2-1\src nmake /f win32.mak

   • YOU MIGHT NEED DIFFERENT STEPS ON WIN2K SERVER.  SEE http://archives.postgresql.org/pgsql-cygwin/2001-11/msg00043.php  Run the following command from a Cygwin bash shell:

     cd echo export PGDATA=/usr/share/postgresql/data >> .profile source .profile initdb

   • Edit the PostgreSQL access permissions:
     

     Warning if you are installing on a machine using DHCP If you are doing a test install of OpenACS on a machine that is using DHCP then your IP address could change between when you install PostgreSQL and when you (re)install OpenACS. If you are in this situation, you should disconnect from the network before you start AOLserver and the OpenACS install in the following sections. This will ensure that AOLserver is listening on 127.0.0.1, the localhost address. You should not edit the PostgreSQL access permissions as specified in this step.

     Edit C:\cygwin\usr\share\postgresql\data\pg_hba.conf with an editor that will preserve the Unix line endings, like the Windows port of Emacs, which is also good for many other reasons. Alternately, you can edit it with whatever editor you like and run a dos2unix command on the file afterwards. Add the line
     host all all your-ip-address 255.255.255.255 trust
     right underneath the line
     host all all 127.0.0.1 255.255.255.255 trust
     You can get your IP address by executing the command ipconfig from a command prompt.
   • You should now be able to run postmaster -i from a cygwin bash prompt. This is useful for testing, since a lot of debug information will get printed out by default. You can also run PostgreSQL in the background with postmaster -i &. You can only have one postmaster process running, so if you are going to install PostgreSQL as a service, make sure you stop whatever you start during these tests.
   
   
4. Install PostgreSQL as a service that depends on ipc-daemon. If you run into problems starting PostgreSQL as a service, remove it with cygrunsrv -R postmaster and add --stderr logfile to the cygrunsrv command below. Run the following commands from a Win2k cmd prompt:

   cygrunsrv -I postmaster -p /usr/bin/postmaster -a "-D /usr/share/postgresql/data -i" -y ipc-daemon -s INT -o net start postmaster

   
   
5. This step is optional, but I highly recommend it. Install pgAdmin II, a Windows-based design and management system for PostgreSQL that works over ODBC.


6. Create a file called recreate-acs-db.txt in your Cygwin home dir. (Probably C:\cygwin\home\Administrator - run cd; pwd to confirm.)  Copy the following script into the file.  When run, it drops the "acs" database and recreates it.  Use a password you will remember!  To run the script in your Cygwin bash shell, make sure PostgreSQL is running, then execute source recreate-acs-db.txt.

   droplang -h localhost -U acs plpgsql acs dropdb -h localhost -U acs -W -e acs dropuser -h localhost -e acs createuser -d -a -P -h localhost acs createdb -h localhost -U acs -W -e acs createlang -h localhost -U acs plpgsql acs

References

• Jason Tishler's PostgreSQL 7.3.2 README
  http://www.tishler.net/jason/software/postgresql/postgresql-7.3.2.README
• PostgreSQL Win32 Installation Doc
  http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=install-win32.html

• PostgreSQL Windows 2000/NT Installation FAQ
  http://www.us.postgresql.org/docs/faq-mswin.html

• Kevin Lo's directions for installing PostgreSQL on Windows 2000
  http://people.freebsd.org/~kevlo/postgres/portNT.html