FreeBSD and pg_upgrade

Thank you kreynolds, your post makes my current job look dead easy. I want to upgrade PostgreSQL from 9.3.12 to 9.5.4, and after checking through all of the release notes between the two versions, I have narrowed the relevant ones down to the two biggest releases: 9.4 and 9.5 (thanks in part to our devs not using the darkest corners of PostgreSQL features… yet).

A side note: PostgreSQL is a shining example of how to do release notes. All in one place, linked properly between versions, available back as far as the eye can see, to pre-1.0 in the 1990s!

Another win for documentation: the FreeBSD handbook. Curated by members of the project, a simple URL, and quality information.

I could use pg_dumpall > backup.sql, upgrade the package, then psql -f backup.sql postgres, or do it in place faster with pg_upgrade. FreeBSD doesn’t simply allow both old and new packages to be installed together, so enter jails.

# jailroot=/usr/tmp/pg_upgrade
# bsdinstall jail $jailroot
# pkg -r $jailroot install postgresql93-server

In the dialog boxes, choose a nearby mirror and no extra components. Wait for the installation to complete.

# su pgsql -c 'pg_dumpall -c | bzip2' > /usr/tmp/pgdump.sql.bz2
# service puppet stop
# service postgresql onestop
# pkg delete postgresql93-client postgresql93-contrib postgresql93-server
# mv /usr/local/pgsql/data{,.93}
# pkg install postgresql95-client postgresql95-contrib postgresql95-server
# service postgresql oneinitdb
# pg_controldata -D /usr/local/pgsql/data.93

“Latest checkpoint location” needs to be the same on master and replication slaves. pg_upgrade(1) has good information on the 16 step process.

# su -l pgsql -c "pg_upgrade -b $jailroot/usr/local/bin -B /usr/local/bin -d /usr/local/pgsql/data.93 -D /usr/local/pgsql/data -j 16 -k --check"
Checking for reg* system OID user data types       fatal
Your installation contains one of the reg* data types in user tables.
These data types reference system OIDs that are not preserved by
pg_upgrade, so this cluster cannot currently be upgraded. You can
remove the problem tables and restart the upgrade. A list of the problem
columns is in the file:
    tables_using_reg.txt
[root@flora ~]# wc -l ~pgsql/tables_using_reg.txt 
 38 /usr/local/pgsql/tables_using_reg.txt

Bother. I can’t use pg_upgrade. Oh well, I’ll talk to the devs for next time, and get on with dump and restore.

PostgreSQL backup and replication systems

There are too many PostgreSQL backup and replication systems for me to keep track in my head, so here’s a list, with a few details relevant to me. The high availability matrix helped, and I found BDR performance discussion helpful, even if I didn’t pay close attention to which-one-is-fastest.

Name Installation Summary
BDR  from source Custom psql binary, asynchronous multi-master replication, high performance
Bucardo  ports tree Asynchronous multi-master or master-slave replication
OmniPITR  github Scripts to manage and monitor WAL replication, hot backups from master or slave servers
pg-rman  ports tree
pgbarman  ports tree Hot physical backup, point-in-time recovery management
pgbarman plugin: pgespresso  ports tree Enables backup from standby server
pglogical  ports tree Logical (cf physical) master-slave replication, postgresql >= 9.4, high performance, flexible, no hot standby state
pgpool-II  ports tree Synchronous multi-master replication, requires conflict resolution
postgresql hot standby  built in
repmgr  ports tree Manage and monitor replication and failover to standby server
Slony-I  ports tree
Skytools, Londiste  ports tree Asynchronous master-slave replication

I reckon pgbarman plus pgespresso plus repmgr (all from 2ndQuadrant) will do what I want. OmniPITR probably would too, with what looks like easier setup against potentially fewer restore options.