Upgrading postgresql from 9.5.x to 9.6.x with Hstore

upgrade_postgres.txt

# Orignal instructions here: https://kkob.us/2016/01/09/homebrew-and-postgresql-9-5/

1. Stop postgresql
    $ brew services stop postgresql

2. Install postgresql 9.6
    $ brew update && brew upgrade postgresql

3. Make a new 9.6 database
    $ initdb /usr/local/var/postgres9.6 -E utf8

4. mv /usr/local/Cellar/postgresql/9.6.1/bin/pg_ctl /usr/local/Cellar/postgresql/9.6.1/bin/pg_ctl.bak
5. Create a shell wrapper /usr/local/Cellar/postgresql/9.6.1/bin/pg_ctl for pg_ctl.bak
    #!/bin/sh
    ln -sf /usr/local/Cellar/postgresql/9.6.1/lib/postgresql /usr/local/lib/
    exec /usr/local/Cellar/postgresql/9.6.1/bin/pg_ctl.bak "$@"

6. Rename /usr/local/Cellar/postgresql/9.6.1/bin/pg_dump -> pg_dump.bak
7. Create a shell wrapper /usr/local/Cellar/postgresql/9.6.1/bin/pg_dump for pg_dump.bak
   #!/bin/sh
   ln -sf /usr/local/Cellar/postgresql/9.5.3/lib/postgresql /usr/local/lib/
   exec /usr/local/Cellar/postgresql/9.6.1/bin/pg_dump.bak "$@"

8. Rename /usr/local/Cellar/postgresql/9.6.1/bin/pg_dumpall -> pg_dumpall.bak
9. Create a shell wrapper /usr/local/Cellar/postgresql/9.6.1/bin/pg_dumpall for pg_dumpall.bak
   #!/bin/sh
   ln -sf /usr/local/Cellar/postgresql/9.5.3/lib/postgresql /usr/local/lib/
   exec /usr/local/Cellar/postgresql/9.6.1/bin/pg_dumpall.bak "$@"

10. Make all the wrappers executable with `chmod +x`
11. Migrate the data to the new 9.6 db `$ pg_upgrade -d /usr/local/var/postgres -D /usr/local/var/postgres9.6 -b /usr/local/Cellar/postgresql/9.5.3/bin/ -B /usr/local/Cellar/postgresql/9.6.1/bin/ -v`
12. Remove the shell wrappers and rename the .bak files to original names
13. Move the 9.5 data directory to where postgresql wants it
   $ mv /usr/local/var/postgres /usr/local/var/postgres9.5
   $ mv /usr/local/var/postgres9.6 /usr/local/var/postgres

14. Start the new 9.6 server `brew services start postgresql`
15. Remove and reinstall the pg gem `gem pristine pg`