Upgrading PostgreSQL from 9.1.5 to 9.2.0

Comments Off on Upgrading PostgreSQL from 9.1.5 to 9.2.0

Today, I decided to upgrade PostgreSQL from 9.1.5 to 9.2.0 on one of my computers. Ironically, PostgreSQL 9.2.1 was released today, but until the FreeBSD ports hierarchy catches up, the most recent version available is 9.2.0, unless of course you want to do everything yourself.

First, I manually built the new version of databases/postgresql92-client:

cd /usr/ports
svn up
cd databases/postgresql92-client
env DISABLE_CONFLICTS=1 make

Next, I logged in as the pgsql user and ran the new pg_dumpall executable, creating a SQL dump of the running database cluster:

/usr/ports/workdirs/usr/ports/databases/postgresql92-client/work/postgresql-9.2.0/src/bin/pg_dump/pg_dumpall | bzip2 -9c > all-db-9.2.0.sql.bz2
chmod 0400 all-db-9.2.0.sql.bz2

My /etc/make.conf file contains the line:

WRKDIRPREFIX=/usr/ports/workdirs

thus placing all work directories within the /usr/ports/workdirs hierarchy.

Back as the root user, I wiped clean the /usr/ports/workdirs hierarchy and stopped the PostgreSQL server:

rm -Rf /usr/ports/workdirs/usr
/usr/local/etc/rc.d/postgresql stop

I continued upgrading the client, the server, and the contrib parts, and specifying their new origin:

pkgdb -Ff
env DISABLE_CONFLICTS=1 /usr/local/sbin/portupgrade -fpvo databases/postgresql92-client postgresql-client-9.1.\*
env DISABLE_CONFLICTS=1 /usr/local/sbin/portupgrade -fpvo databases/postgresql92-server postgresql-server-9.1.\*
env DISABLE_CONFLICTS=1 /usr/local/sbin/portupgrade -fpvo databases/postgresql92-contrib postgresql-contrib-9.1.\*

Everything depending on the client part also needs upgrading:

/usr/local/sbin/portupgrade -fprvx postgresql-client -x postgresql-server -x postgresql-contrib databases/postgresql92-client

Finally, I was able to put aside the old database cluster, erect a new one, and get the database server running:

mv /usr/local/pgsql/data /usr/local/pgsql/data0
/usr/local/etc/rc.d/postgresql initdb
/usr/local/etc/rc.d/postgresql start

Back as the pgsql user, the time was appropriate for recreating the databases:

bzcat all-db-9.2.0.sql.bz2 | psql -f - template1

Back again as the root user, a last tuneup of the configuration files pg_hba.conf and postgresql.conf was committed to the CVS repository used for system configuration management before restarting the PostgreSQL server with the latest settings and removing the old database cluster.

/usr/local/etc/rc.d/postgresql restart
rm -Rf /usr/local/pgsql/data0

I observed some strange log messages when starting the PostgreSQL server:

LOG:  could not bind IPv6 socket: Can't assign requested address
HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
LOG:  could not bind IPv6 socket: Can't assign requested address
HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.

The server is running nonetheless, and there’s nothing special about my postgresql.conf file.

It turned out that my /etc/hosts file contained the two IPv6 link local addresses fe80::1 and fe80:2::1 linking them to the localhost domain name. Changing the domain name to localhost-6ll made PostgreSQL much happier.

Addendum

PostgreSQL 9.2.1 was added to the FreeBSD ports tree as part of r304804 on 2012-09-24 22:03:10 UTC. Upgrading client, server, and contrib, to this version of PostgreSQL, assuming you’re already at 9.2.0, is as simple as:

/usr/local/sbin/portupgrade -fprv databases/postgresql92-client

You should probably vacuum all tables and recreate any BTree/GIN indexes as instructed.

Addendum II

If you happen to be upgrading from a very old version of PostgreSQL, say 8.4.13, additional steps are necessary before dumping the database cluster with the temporary pg_dumpall. It is necessary to load the latest libpq.so rather the currently installed libpq.so. Study the output of the command:

ldconfig -r | grep 'search directories'

and adjust the following commands to suit your environment:

export LD_LIBRARY_PATH=/usr/ports/workdirs/usr/ports/databases/postgresql92-client/work/postgresql-9.2.1/src/interfaces/libpq:/lib:/usr/lib:/usr/lib/compat:/usr/local/lib:/usr/local/lib/compat/pkg:/usr/local/lib/pth
/usr/ports/workdirs/usr/ports/databases/postgresql92-client/work/postgresql-9.2.1/src/bin/pg_dump/pg_dumpall | bzip2 -9c > all-db-9.2.1.sql.bz2