Upgrading PostgreSQL 9.5 to 9.6

On macOS with homebrew

Posted by Matthias Schmidt on June 21, 2017

There are plenty of guides to upgrade from 9.5 to 9.6, or any other minor version. Here is the short story:

$ initdb /usr/local/var/postgres9.6 -E utf8
$ pg_upgrade \
  -d /usr/local/var/postgres -D /usr/local/var/postgres9.6 \
  -b /usr/local/Cellar/postgresql/9.5.5/bin -B /usr/local/Cellar/postgresql/9.6.3/bin
$ mv /usr/local/var/postgres /usr/local/var/postgres9.5
$ mv /usr/local/var/postgres9.6 /usr/local/var/postgres

However, I never had much luck with this. There was always some extension conflict or some other not-easy-to-fix issue.

Something along the lines of:

FATAL:  incompatible library "/usr/local/lib/postgresql/hstore.so": version mismatch
DETAIL:  Server is version 9.6, library is version 9.5.

So this is the more manual bare-bone approach through backup and restore.

Status quo

  • macOS with PostgreSQL 9.6 installed through homebrew
  • PostgreSQL 9.5 is also still installed
  • PostgreSQL is not running

Get 9.5 to run again

Start PostgreSQL with old binaries, libraries and data.

$ mv /usr/local/lib/postgresql /usr/local/lib/postgresql9.6
$ ln -s /usr/local/Cellar/postgresql/9.5.5/lib/postgresql postgresql
$ /usr/local/Cellar/postgresql/9.5.5/bin/pg_ctl -D /usr/local/var/postgres start

Dump the whole database

$ /usr/local/Cellar/postgresql/9.5.5/bin/pg_dumpall > ~/dump.sql

Prepare 9.6

Restore everything to regular 9.6 status.

$ /usr/local/Cellar/postgresql/9.5.5/bin/pg_ctl -D /usr/local/var/postgres stop
$ rm /usr/local/lib/postgresql
$ mv /usr/local/lib/postgresql9.6 /usr/local/lib/postgresql
$ mv /usr/local/var/postgres /usr/local/var/postgres9.5
$ initdb /usr/local/var/postgres -E utf8
$ pg_ctl -D /usr/local/var/postgres start

Restore from dump

$ psql -f ~/dump.sql postgres


Make sure everything is there and works as expected. Then feel free to clean up after yourself.

$ rm -r /usr/local/var/postgres9.5
$ brew cleanup