I wrote a bare-bones installation guide for PostgreSQL, see Simple PostgreSQL Install Guide ( simple-pg-install.html).
For more generic downloading and compiling instructions see http://www.postgresql.org/docs/admin/install855.htm.
PostgreSQL 7 is out at the time of this writing. It comes with some exciting new features such as referential integrity, to_char, optimizer overhaul and an enhanced psql.
The more advanced features of PG 7 are critical to OpenACS (such as lztext which will allow bigger comments, static pages, and referential integrity) so that's the version we support. Upgrading from PG 6.5 to PG 7 is not too hard although you'll need to pg_dump and restore your database plus an initdb.
If you want to use RPMs, please use the official PostgreSQL RPMs. They are available at Postgres' website. Lamar Owen maintains those RPMs and is also a member of the OpenACS team, so we know exactly how those packages are done and our tests and documentation are based on that.
Note: You can use the nspostgres.so driver included in the AOLserver distribution, but if you want to compile the latest driver, you'll need the AOLserver source distribution.
Also, to do any compiling in C, you'll need a compiler and the right libraries installed in your system. AOLserver, PostgreSQL and the PG driver were tested with gcc (the GNU Compiler Collection) and gmake (GNU Make). You will need gcc (egcs in Red Hat, pgcc in Mandrake/Stampede) and the glibc (GNU C library) installed (for GNU/Linux distributions this usually means packages like glibc and glibc-devel).
The PostgreSQL driver now comes with the AOLserver distribution (nspostgres.so), but if you are having problems, you can always get the latest PostgreSQL driver from http://openacs.org/sdm and compile it. If you are using the driver that comes with AOLserver, skip the next step.
Edit the Makefile to include the correct path to your PostgreSQL and AOLserver directories. If you are using the RPM version of PostgreSQL, make sure you have the devel package installed as well. You need to pay attention to these lines:
PGLIB=/usr/local/pgsql/lib # Where your PG libraries are installed
PGINC=/usr/local/pgsql/include # Where your PG includes are installed
NSHOME=/home/aolserver # Where your AOLserver is installed
NSINC=/usr/local/src/aolserver3_0/include # Where you untarred AOLserver
Do a make and then make install. The file postgres.so will be copied to
the AOLserver's bin directory.
If you are running PG 7, make a symbolic link from libpq.so.2.0 pointing to libpq.so.2.1 because AOLserver looks for libpq.so.2.0 when loading the driver:
cd /usr/local/pgsql/lib
ln -s libpq.so.2.1 libpq.so.2.0 (as user postgres)
Note: These are not absolutely necessary for running OpenACS, but I included here because it tells you how to get more out of PostgreSQL. It is good reading especially if you want to do something serious with your copy of OpenACS.
You'll need to make sure the Postgres postmaster is running, first. This is the process that forks a Postgres backend when AOLserver (or any other application, including PSQL) initiates a backend connection. I've got my .ini file configured so idle connections never get released by AOLserver, so this forking happens only once per connection per lifetime of the server (the MaxOpen and MaxIdle in the pools section of the nsd.ini, as in the example above).
Here's the command I use to run Postmaster from my /etc/rc.d/init.d/postgresql script:
su -l postgres -c '/usr/local/pgsql/bin/postmaster -B 1000 -o "-S 2000" -S -D /usr/local/pgsql/data'
For the RPM version should be something like this:
su -l postgres -c '/usr/bin/postmaster -B 1000 -o "-S 2000" -S -D /var/lib/pgsql/data'
Some explanations - "-B 1000" tells it to allocate 1000 blocks of shared memory (rather than the default 64, which is way puny). I've compiled my copy of postgres with a 16K blocksize, so this is 16MB of shared memory space, i.e. the most postgres will use without a kernel recompile. If you've compiled with the default 8K blocksize (RPM version), "-B 2000" will work. You needn't do this for testing, but for an active system helps a lot.
The '-o "-S 2000" ' tells each backend to use up to 2 MB (2000 x 1KB) of RAM for sorting, etc before spilling to disk.
The other "-S" (to the postmaster itself, don't confuse with the above where -o is used to pass flags to forked backends) tells it to run "silently", in the background.
-D is used to pass the path to the database which you've hopefully already run initdb on, etc.
Again, this is not required to run OpenACS.
By default PostgreSQL is compiled with a blocksize of 8 Kb. You can compile PostgreSQL to have 16 Kb blocksize instead, which will allow for bigger text and lztext data types.
Refer to the Simple PostgreSQL Installation Guide for instructions on how to do this.
Make sure PostgreSQL is running fine with all the environment variables set (the RPM version does that all for you).
- Login as "postgres" (the PostgreSQL super user) and create a user for AOLserver
in PostgreSQL. If your AOLserver runs as "nsadmin" , that should be the user
to create with the command createuser nsadmin. In PG 6.5, you will be asked
if the user is a super user and allowed to create dabatases, respond YES (y)
to both. In PG 7 it will ask you if this user is allowed to create databases
and if this user is allowed to create new users, respond YES (y) to both as
well.
From now on, become the user AOLserver will connect to PostgreSQL as (e.g. nsadmin).
- Come up with a name for your Database (Usually it will be the name of
the web service you're setting up. I'll use yourdb as example). Then create
the database with the command: createdb yourdb.
- cd to the www/install directory of the OpenACS distribution and load the country/state/zip codes with the command :
./load-geo-tables yourdb
- cd to the www/doc/sql directory. If you are running the RPM version of
PostgreSQL, edit the file postgres.sql and uncomment the following lines, commenting
the two similar lines right below them:
--create function plpgsql_call_handler() RETURNS opaque --as '/usr/lib/pgsql/plpgsql.so' language 'c';
- Edit the file load-data-model.sql. Uncomment the line \i postgres65.sql
only if you are running PG 6.5.x.
(Optional - Deprecated) If you are running PG 6.5.3 and have the Tcl package
loaded (or compiled --with-tcl) you may comment the \i postgres65.sqlline and uncomment the \i postgres-pgtcl.sql line.
- Load the data model into yourdb with the command:
psql -f load-data-model.sql yourdb
Alternatively and for debugging purposes you can do (I always do):
psql -f load-data-model.sql yourdb 2> datamodel.txt
to save PG's output to a file called datamodel.txt, which you can review and look for errors. If you have a bunch of " ERROR" messages in this file, then you forgot to configure one of the OpenACS files.
If anything goes wrong, it is easier to simply destroy the db ( command
dropdb yourdb) and recreate it after you've reviewed your steps.
- Do a "psql yourdb". You should end up with a prompt after a
couple of messages indicating that it has successfully connected with the database.
Do a "\d" to see all the tables in your db. Once you're certain you can
connect from the account via psql, you should have no problem connecting via
AOLserver. [DRB]