Peregrinations in the realm of PostgreSQL upgrades
January 2015.
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).