Upgrade Postgres on AGLT2 dCache ADMIN-node

During our recent upgrade of dCache we also ended up upgrading our Postgres installation on the dCache PNFS node (head02) from 8.1.14 to 8.3.7. This resulted in an increase in our chimera SQL loading of a factor of 45.

We noticed problems after the upgrade with our SRM and gPlazma on our dCache ADMIN head-node head01. It is still running Postgres 8.1.14. We plan to upgrade to Postgres 8.3.7 on this node as well to help improve the basic setup on the ADMIN node.

The upgrade procedure is documented. Basically you need to dump the existing DB, upgrade and restore.

Procedure at AGLT2

First login as 'root' on head01.aglt2.org and shutdown dCache/SRM: service dcache stop

Next we create a "dump" of the existing postgres DB:

root@head01 /opt/d-cache/etc# pg_dumpall -U postgres  > /tmp/backup_pg.sql

This took about 20 minutes and produced a 16GB file.

Now we can stop postgres: service postgresql stop

Once this completes we need to preserve the old postgres install location by moving it:

root@head01 /opt/d-cache/etc# cd /var/lib/pgsql/
root@head01 /var/lib# mv pgsql pgsql.old

Now we can upgrade via RPM:

root@head01 ~/postgres_rpms# ls
compat-postgresql-libs-4-1PGDG.rhel4.x86_64.rpm
postgresql-8.3.7-1PGDG.rhel4.x86_64.rpm
postgresql-devel-8.3.7-1PGDG.rhel4.x86_64.rpm
postgresql-libs-8.3.7-1PGDG.rhel4.x86_64.rpm
postgresql-server-8.3.7-1PGDG.rhel4.x86_64.rpm
root@head01 ~/postgres_rpms# rpm -Uvh *.rpm
Preparing...                ########################################### [100%]
   1:postgresql-libs        ########################################### [ 20%]
   2:compat-postgresql-libs ########################################### [ 40%]
   3:postgresql             ########################################### [ 60%]
   4:postgresql-devel       ########################################### [ 80%]
   5:postgresql-server      ########################################### [100%]

Next we need to initialize the new DB structure:

root@head01 ~/postgres_rpms# su - postgres

-bash-3.00$ initdb -D /var/lib/pgsql/data --locale=C
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale C.
The default database encoding has accordingly been set to SQL_ASCII.
The default text search configuration will be set to "english".

fixing permissions on existing directory /var/lib/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers/max_fsm_pages ... 32MB/204800
creating configuration files ... ok
creating template1 database in /var/lib/pgsql/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

Success. You can now start the database server using:

    postgres -D /var/lib/pgsql/data
or
    pg_ctl -D /var/lib/pgsql/data -l logfile start

Now we need to apply our prior configuration details into the new postgres.conf and pg_hba.conf files but carefully editing the new versions based upon the entries in the old ones. This procedure took about 10 minutes of edits.

Then try starting up:

root@head01 ~/postgres_rpms# service postgresql start
Starting postgresql service:                               [  OK  ]

Success. Now restore the original DB ( as user 'postgres' or whoever owns your DB):

psql -f /tmp/backup_pg.sql template1

PROBLEM: I didn't verify I had enough space on the /var partition! It failed near the end when the partition filled up.

Now I am moving the old directory pgsql.old off /var/lib and onto /opt. Then I need to redo the loading...

Once this completes we can restart dCache:

service dcache start

Now we are running the dCache admin node on the newest postgres!

-- ShawnMcKee - 09 May 2009