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.
- macOS with PostgreSQL 9.6 installed through
- 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
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