Upgrade Postgres to 9.4 using Homebrew on Mac OSX

postgres-upgrade-recipe.sh

#!/bin/bash

# This script can be used in "run & hope" mode or you can use it as a recipe to
# do things manually - you probably want the latter if you really care about
# the data in your databases.
# Happy hacking
# /Eoin/

# Tell bash to stop if something goes wrong
set -e

# Note this has to be set to the exact version you have installed via brew. You
# can get this via:

#   ls -l /usr/local/Cellar/postgresql

# In my case my postgres binaries were in `9.3.5_1`
OLDPG=9.3.5_1

# set this to your new PG version
NEWPG=9.4.0

# Stop current server (if started manually)
# pg_ctl -D /usr/local/var/postgres stop

# Stop current server (if starting from launchctl)
launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

# Backup current db
mv /usr/local/var/postgres/ /usr/local/var/postgres-$OLDPG

# Homebrew
# Check here if you have issues with either of these https://github.com/Homebrew/homebrew/wiki#troubleshooting
brew update
brew upgrade postgresql

# brew upgrade will create /usr/local/var/postgres using for you using `initdb`
# because it does not exist (we moved our existing one to /usr/local/var/postgres-$OLDPG).
# If this fails you could do it manually with:
# initdb /usr/local/var/postgres

# OS X launch agents for PG, so it starts on boot automatically
cp /usr/local/Cellar/postgresql/$NEWPG/homebrew.mxcl.postgresql.plist ~/Library/LaunchAgents/

# If pg_upgrade fails you might need to tweak how PG uses kernel resources. You
# can read more at http://www.postgresql.org/docs/9.3/static/kernel-resources.html
# sudo sysctl -w kern.sysv.shmall=65536
# sudo sysctl -w kern.sysv.shmmax=16777216


# The pg_upgrade script will create some other scripts that we can optionally
# run after it completes. They are created in teh CWD so we create a new tmp
# dir to work in and `cd` to it.
mkdir -p /tmp/pgupgrade && cd $_

# Upgrade old DB to new DB
# `man pg_upgrade` for details
# Note: As we are doing here, it is best practice to run this using the
# pg_upgrade binary from the new postgres (as it has knowledge of the new data
# format)
pg_upgrade -d /usr/local/var/postgres-$OLDPG/ \
    -D /usr/local/var/postgres \
    -b /usr/local/Cellar/postgresql/$OLDPG/bin \
    -B /usr/local/Cellar/postgresql/$NEWPG/bin

# Start new Postgres server (if using launchctl)
launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

# Start new server (if doing manually)
# pg_ctl -D /usr/local/var/postgres start

# Run the optional "analyze" script created by pg_upgrade
# `cat` it for more info - it is quite short
# ./analyze_new_cluster.sh

# Run the optional "delete" script created by pg_upgrade
# `cat` it for more info - it is quite short
# ./delete_old_cluster.sh

# Optional clean-up
# cd ~
# rm -rf /tmp/pgupgrade

After running pg_upgrade ... line I received this error:

*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

There seems to be a postmaster servicing the old cluster.
Please shutdown that postmaster and try again.
Failure, exiting

Fix from here:

mv /usr/local/var/postgres-$OLDPG/postmaster.pid /usr/local/var/postgres-$OLDPG/postmaster.pid.bak

A few more problems I ran into that might help passers by:

plv8 was not installed so there was an error about that. You can try installing plv8 but I couldn't get it working with 9.4 at the time of writing.

So I had to go through each database in my old cluster and run DROP EXTENSION plv8.

I had set CREATE EXTENSION plv8 in template0 though so I had to remove it from there too.

When trying to connect to template0 you may get the message psql: FATAL: database "template0" is not currently accepting connections. In that case run: UPDATE pg_database SET datallowconn = TRUE WHERE datname = 'template0';. But make sure after you drop the extension from here, you reset it to false because otherwise it will prevent pg_upgrade from working, as pg_upgrade will try to override template0 because it can connect to it (seems like a bug).

Has anyone else run into an error when doing pg_upgrade?

Error:
check for "/usr/local/var/postgres-9.3.4/pg_tblspc" failed: No such file or directory

have the same problem, as some of my folders were missing. Simply creating them did the trick:

mkdir /usr/local/var/postgres/{pg_tblspc,pg_twophase,pg_stat,pg_stat_tmp,pg_replslot,pg_snapshots,pg_xlog,pg_subtrans,pg_multixact,pg_clog,global}/

I fixed that error by running :

rm -rf /usr/local/var/postgres  # Be careful you ran the backup before.
initdb /usr/local/var/postgres -E utf8

Thank you for your script! It worked like a charm. If you from the future don't know from which version you're coming from (i.e.: you ran a global brew upgrade). You can do brew info postgresql and the version you're coming from will likely show up in the "Conflicts with" secion, right after the postgresql website URL.