Upgrade PostgreSQL 9.4 to 9.5 on Mac OS X with Homebrew

_upgrade-pg9.4-to-pg9.5.md

First, check your current config (example output in homebrew.mxcl.postgresql.plist.xml lower down in this gist):

cat ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

Most importantly, note the -D /usr/local/var/postgres argument.

Second, shut down your current PostgreSQL.

launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

Third, update Homebrew:

# you'll have an easier time selecting the right 9.4 bin dir if you run `brew cleanup` now
# but DO NOT RUN `brew cleanup` if you've `brew update`'d on or after 2016-01-07
brew update && brew upgrade

Third and a half (skip to Fourth below if you are not as reckless with brew cleanup as I am):

Caution! Time is weird. This is for the 9.5->9.6 upgrade, but the rest of the notes are still for 9.4->9.5.

If you accidentally deleted the old postgresql version, it's pretty easy to get it back.

brew unlink postgresql # unlink the current (new) version
# Find the last 9.5 commit via `brew log postgresql` (hint: it's db96a14)
brew install https://raw.github.com/Homebrew/homebrew-core/db96a14/Formula/postgresql.rb
brew switch postgresql 9.6.1

Fourth, check your installed directories:

ls -1 /usr/local/Cellar/postgresql/

output on my machine:

9.4.5_2
9.5.0

Fifth, prepare your new database (adjusting for your value of -D from launchctl config file, if needed):

mv /usr/local/var/postgres /usr/local/var/postgres94
initdb --pgdata=/usr/local/var/postgres
# example output later in this gist

Sixth, run pg_upgrade (this is the good stuff, and the paths may need adjusting):

pg_upgrade \
  --old-datadir=/usr/local/var/postgres94 \
  --new-datadir=/usr/local/var/postgres \
  --old-bindir=/usr/local/Cellar/postgresql/9.4.5_2/bin \
  --new-bindir=/usr/local/Cellar/postgresql/9.5.0/bin \
  --verbose

That'll produce a ton of output and takes a minute or two, but should end with an "Upgrade Complete" message (see pg_upgrade.log later in gist).

It also creates analyze_new_cluster.sh and delete_old_cluster.sh files in your current directory.

Seventh, start up your shiny new PostgreSQL database:

launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

And ensure it's running properly:

psql postgres -c 'select version()'
>                                                   version
> --------------------------------------------------------------------------------------------------------------
>  PostgreSQL 9.5.0 on x86_64-apple-darwin15.2.0, compiled by Apple LLVM version 7.0.2 (clang-700.1.81), 64-bit
> (1 row)

Eighth, optimize the new databases (I've elided some databases in the output shown in analyze_new_cluster.sh.log later in the gist):

./analyze_new_cluster.sh

Ninth, delete the old database files:

./delete_old_cluster.sh

Tenth, delete the helper scripts,

rm analyze_new_cluster.sh delete_old_cluster.sh

And you're DONE!

analyze_new_cluster.sh

#!/bin/sh

echo 'This script will generate minimal optimizer statistics rapidly'
echo 'so your system is usable, and then gather statistics twice more'
echo 'with increasing accuracy.  When it is done, your system will'
echo 'have the default level of optimizer statistics.'
echo

echo 'If you have used ALTER TABLE to modify the statistics target for'
echo 'any tables, you might want to remove them and restore them after'
echo 'running this script because they will delay fast statistics generation.'
echo

echo 'If you would like default statistics as quickly as possible, cancel'
echo 'this script and run:'
echo '    "/usr/local/Cellar/postgresql/9.5.0/bin/vacuumdb" --all --analyze-only'
echo

"/usr/local/Cellar/postgresql/9.5.0/bin/vacuumdb" --all --analyze-in-stages
echo

echo 'Done'

analyze_new_cluster.sh.log

This script will generate minimal optimizer statistics rapidly
so your system is usable, and then gather statistics twice more
with increasing accuracy.  When it is done, your system will
have the default level of optimizer statistics.

If you have used ALTER TABLE to modify the statistics target for
any tables, you might want to remove them and restore them after
running this script because they will delay fast statistics generation.

If you would like default statistics as quickly as possible, cancel
this script and run:
    "/usr/local/Cellar/postgresql/9.5.0/bin/vacuumdb" --all --analyze-only

vacuumdb: processing database "formious": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "metry": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "formious": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "metry": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "formious": Generating default (full) optimizer statistics
vacuumdb: processing database "metry": Generating default (full) optimizer statistics
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics

Done

delete_old_cluster.sh

#!/bin/sh

rm -rf '/usr/local/var/postgres94'

homebrew.mxcl.postgresql.plist.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
  <key>KeepAlive</key>
  <true/>
  <key>Label</key>
  <string>homebrew.mxcl.postgresql</string>
  <key>ProgramArguments</key>
  <array>
    <string>/usr/local/opt/postgresql/bin/postgres</string>
    <string>-D</string>
    <string>/usr/local/var/postgres</string>
    <string>-r</string>
    <string>/usr/local/var/postgres/server.log</string>
  </array>
  <key>RunAtLoad</key>
  <true/>
  <key>WorkingDirectory</key>
  <string>/usr/local</string>
  <key>StandardErrorPath</key>
  <string>/usr/local/var/postgres/server.log</string>
</dict>
</plist>

initdb.log

The files belonging to this database system will be owned by user "chbrown".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /usr/local/var/postgres ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /usr/local/var/postgres/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /usr/local/var/postgres -l logfile start

pg_upgrade.log

copying "/usr/local/var/postgres94/base/1/12360" to "/usr/local/var/postgres/base/1/2613"
copying "/usr/local/var/postgres94/base/1/12362" to "/usr/local/var/postgres/base/1/2683"
copying "/usr/local/var/postgres94/base/1/12222" to "/usr/local/var/postgres/base/1/2995"
copying "/usr/local/var/postgres94/base/1/12224" to "/usr/local/var/postgres/base/1/2996"
                                                            ok
Setting next OID for new cluster                            "/usr/local/Cellar/postgresql/9.5.0/bin/pg_resetxlog" -o 420396 "/usr/local/var/postgres" >> "pg_upgrade_utility.log" 2>&1
ok
Sync data directory to disk                                 "/usr/local/Cellar/postgresql/9.5.0/bin/initdb" --sync-only "/usr/local/var/postgres" >> "pg_upgrade_utility.log" 2>&1
ok
Creating script to analyze new cluster                      ok
Creating script to delete old cluster                       ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    ./analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh