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'; |
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 |
You should check dataload.txt for errors:
grep ERROR /tmp/dataload.txt | less |
Tip: 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.