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
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
/etc/make.conf file contains the line:
thus placing all work directories within the
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
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
It turned out that my
/etc/hosts file contained the two IPv6 link local addresses
fe80:2::1 linking them to the
localhost domain name. Changing the domain name to
localhost-6ll made PostgreSQL much happier.
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.
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