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
Cleanup
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