PascalScheffers(pascal@scheffers.net) 5 April 2001 I was a bit scared to 'just update to Postgres 7.1 and be done with it' So with the help of Don Baccus and Roberto Mello this is a how-to upgrade a (live) OpenACS system with a minimal amount of down-time. I wrote this document while trying the entire procedure over-and-over again on a Redhat 6.2 system with the Postgres 7.0.3 RPM-version installed. This should work on many other setups, but you may, obviously need to change some paths. You will end up with a system that has both Postgres 7.0 and 7.1 installed and running. Uninstalling the old version is an exercise I will leave to the reader. I would not recommend this procedure on a machine with less than 256MB of RAM. You will only need Postgres and the latest OpenNSD Postgres driver to upgrade your system. First, obtain the latest version, at the time of this writing that would be Release Candidate 2 from ftp://ftp.postgresql.org/pub/dev. Be sure to check the FTP archive link, as the Postgres FTP server may keep the RC2 file online after the final version has been released. And do not forget to use a mirror site. Remember, the Postgres team is doing some awesome programming, but they are not gods and release candidates are probably not the best choice for a production system... (although if you have the nerve/backups it will be helpful if you do run a production system on a release candidate version and report the bugs you find) Because I am running a Redhat 6.2 system with the Postgres 7.0.3 RPM version, /usr/local/pgsql is conveniently free. If you have 7.0.3 installed at that location, I suggest you use /usr/local/psql. All command samples in this document use the /usr/local/psql location, to prevent all the cut-n-pasters out there from messing up their 7.0.3 version by mistake. The rest of the text assumes you are installing under pgsql. su - cd /usr/local mkdir psql chown pascal.pascal pgsql (substitute the user name that will be compiling Postgres) su - pascal cd /usr/local/psql tar xzf /tmp/postgresql-7.1RC2.tar.gz mv postgresql-7.1RC2/* . rmdir postgresql-7.1RC2 Compiling Postgres is extremely simple. This section contains only a brief configuration guide. If you think you have some special needs, you should refer to the Postgres installation documentation. You must not compile Postgres as root. You can mess up your system big time of you do. This procedure was borrowed from /usr/local/pgsql/INSTALL. There are some configuration options for ./configure that you may want to look at. If you are not compiling under /usr/local/pgsql you MUST at least specify '--prefix=/usr/local/psql' (or wherever your source is) '--with-pgport=5433' instead of 5432, because you'll get conflicts with 7.0.3 if you don't change the port. You can also use the -p option for the postmaster, or set the PGPORT environment variable '--enable-locale' and '--enable-multibyte' for Unicode support '--with-[perl && python && tcl && CXX] for native language support libs '--with-openssl=DIRECTORY' so you can safely access your Postgres over the Net. '--enable-syslog' for those of you who like to use syslog. Again, check the INSTALL file and the Postgres admin guide for more info. The simplest form would be: ./configure --prefix=/usr/local/psql options... gmake gmake install All as the some unprivileged user, that way you are 100% sure that you are not touching any of the Postgres 7.0.3 files. This will install everything under /usr/local/pgsql, including the manpages. You should update your man paths to include the new locations or move the man files to some common location (/usr/local/man, for example). You can also specify where 'gmake install' puts them with a ./configure option. I decided to use a different user account for 7.1, this makes it much simpler to configure, start and stop the database service. This is not mandatory, but if you do not do it, you will probably make a deadly mistake somewhere along the line. First you need to add the new Postgres user account, which will own the database files and server processes. I usually do not set a password on these accounts, I just 'su' into them when I need to. su - adduser postgr71 mkdir /usr/local/psql/data chown postgr71.postgr71 /usr/local/psql/data su - postgr71 Now change .bash_profile (or .profile), the PATH must have '/usr/local/psql/bin' as its first entry. You must also add the PGDATA and PGPORT exports. My profile looks like: PATH=/usr/local/psql/bin:$PATH:$HOME/bin export PGDATA=/usr/local/psql/data export PGPORT=5433 This assumes that Postgres 7.0.3 is running on port 5432. After you remove 7.0.3 from your system you may want to consider changing the port back to 5432. Now, log out and back in as postgr71 and check that the command which initdb returns /usr/local/psql/bin/initdb. If your are feeling lucky, you can skip the test. But don't forget to start the postmaster. su - postgr71 initdb pg_ctl -l logfile start createdb test psql test You should now be able to create some tables in test, have fun with outer joins, etc. But I just exited psql (with '\q') and typed 'dropdb test' You will want to add this brand new postgres instance to your automated startup, for that please refer to your OS manual. After and installing Postgres, you need to compile the latest postgres driver for OpenNSD. Get that from the sourceforge.net CVS repository: cd /usr/local/aolserver (or whereever your aolserver source resides) cvs -d:pserver:anonymous@cvs.acs-pg.sourceforge.net:/cvsroot/acs-pg login cvs -z3 -d:pserver:anonymous@cvs.acs-pg.sourceforge.net:/cvsroot/acs-pg co driver-2.3 The driver source must be under the AOLServer source directory. Now you need to change some parameters in driver-2.3/makefile (yes, that is makefile not Makefile). There are plenty of samples in the file, but these need your immediate attention: PGLIB=/usr/local/psql/lib # Where your PG libraries are installed PGINC=/usr/local/psql/include # Where your PG includes are installed NSHOME=/home/aolserver # Where your AOLserver is installed NSINC=/usr/local/aolserver/include # Where you untarred AOLserver Making should be simple, error free and fast. After make you should copy the new driver to your AOLServer bin dir, but don't overwrite the original postgres driver! cd /usr/local/aolserver/ make cp postgres.so /home/aolserver/bin/postgres71.so Now you are all set to convert the database. If you are upgrading a live system, this is the place where the downtime should start. I suggest you first run this procedure on a non-production machine (assuming you do have a shadow box). If you do not stop your AOLServer process, you will lose the changes that are made in the time it takes to backup/restore/restart-AOLServer. To be safe, I would just swallow the couple of minutes downtime you need if you practice and prepare the procedure. Now, with the old psql tool, backup the existing data. This sample assumes your nsadmin user has access to the old psql tool. The umask will prevent the rest of the world from reading all the users' passwords from the database dump. su - nsadmin (or the postgres super user) umask 077 pg_dump your_web_service_name > /tmp/database.psql su - chown postgr71.postgr71 /tmp/database.psql The 7.0 version of pg_dump is a bit brain dead with regards to dump order and dependancies. You need to load the acs/www/doc/sql/postgres.sql file before restoring, otherwise nothing will work. You need to edit that file first, though. At the beginning of that postgres.sql you will find two lines like: create function plpgsql_call_handler() RETURNS opaque as '/usr/local/pgsql/lib/plpgsql.so' language 'c'; You need to change the path to where you installed Postgres 7.1. Now you can continue to create a new db user and restore your data: su - postgr71 createuser nsadmin (answer 'yes' to both questions) createdb -U nsadmin your_web_service_name psql -f /web/your_service/www/doc/sql/postgres.sql your_web_service_name nsadmin psql -f /tmp/database.psql your_web_service_name nsadmin 2>/tmp/dataload.txt The first psql command should NOT result in any errors. You should fix them before you continue, otherwise the load will fail. Just 'dropdb your_web_service_name' and redo it. You should check dataload.txt for errors: grep ERROR /tmp/dataload.txt | less The only errors you should find are duplicate definitions, if you find anything else: fix it! I managed to get some errors because some tables and data were owned by someone other than nsadmin. I suggest you strip all lines that begin with '\connect ' from the database.psql file. There are two changes that you need to make. You should backup your old nsd.tcl first, of course. The first change is in the ns/db/drivers section. The name of the Postgres driver should read postgres71.so: ns_section "ns/db/drivers" ns_param postgres ${bindir}/postgres71.so ;# Load PostgreSQL driver The second change concerns the port numbers for the connections in the ns/db/pool/* sections: ns_section "ns/db/pool/????" ns_param Driver postgres ns_param Connections 5 ns_param DataSource localhost:5433:localacs ns_param User nsadmin # ns_param Password "" ns_param Verbose On ns_param LogSQLErrors On ns_param ExtendedTableInfo On # ns_param MaxOpen 1000000000 # ns_param MaxIdle=1000000000 Save the file, (re)start your AOLServer instance and you are up and running on Postgres 7.1. Congratulations! In the OpenACS installation guide, you will have to either specify the full path to the 7.1 version of psql or adjust the PATH environment variable to point to the 7.1 version. You must also remember to set the port number for postgres 7.1 correctly, as shown in the previous section.