Index

Peregrinations in the realm of PostgreSQL upgrades

January 2015.
Posted on 2015-01-03.

The FreeBSD logo The PostgreSQL Logo

The system used is FreeBSD 10.1 STABLE.

Situation


You upgraded your PostgreSQL v9.4 database server, and now it won't start.

Starting the server says:

FATAL:  database files are incompatible with server
DETAIL:  The database cluster was initialized with PG_CONTROL_VERSION 937, but the server was compiled with PG_CONTROL_VERSION 942.
HINT:  It looks like you need to initdb.
pg_ctl: could not start server
Examine the log output.

Installing an older 9.3 and starting it yields:

FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 9.4, which is not compatible with this version 9.3.5.
pg_ctl: could not start server
Examine the log output.

So 9.4 tells us that the files belong to 9.3.7 (does such a thing even exist?), and 9.3 tells us that the files belong to 9.4.

Huh.

Explanation


According to this anwser to this question, the format of some database/control files changed right after 9.4b1.

Solving the problem


We're going to upgrade the database manually.

Install portdowngrade.

make -C /usr/ports/ports-mgmt/portdowngrade install clean

Use it to download version 9.4b1 of the PostgreSQL port.

portdowngrade databases/postgresql94-server r366094

Build the port into a separate directory.

make -C /root/postgresql94-server WRKDIRPREFIX=/root/pgsql/old

Copy your old data nearby

cp -rp /.zfs/snapshot/2015-01-03/usr/local/pgsql/data /root/pgsql/data-old

Build and install the latest version of the port.

make -C /usr/ports/databases/postgresql94-server install clean

Also install the latest contrib tools (to get pg_upgrade).

make -C /usr/ports/databases/postgresql94-contrib install clean

Initialize the new data files.

/usr/local/etc/rc.d/postgresql initdb

And now the most important part: converting the data files:

su -l -c default pgsql -c 'pg_upgrade -d /root/pgsql/data-old -D /usr/local/pgsql/data -b /root/pgsql/old/usr/ports/databases/postgresql94-server/work/stage/usr/local/bin -B /usr/local/bin -v'

Who said FreeBSD couldn't use pg_upgrade!

Once upgraded, the server is ready to be started again.

/usr/local/etc/rc.d/postgresql start

Of course, this situation can never happen to you since your backups are always up to date and since you upgrade PostgreSQL using the FreeBSD method correctly (dump and restore).