Skip to content

Instantly share code, notes, and snippets.

@eoinkelly
Last active November 9, 2017 16:59
Show Gist options
  • Save eoinkelly/fd80465942c8ca4bd5c0 to your computer and use it in GitHub Desktop.
Save eoinkelly/fd80465942c8ca4bd5c0 to your computer and use it in GitHub Desktop.
Upgrade Postgres to 9.4 using Homebrew on Mac OSX
#!/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
@vjpr
Copy link

vjpr commented Jan 14, 2015

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).

@donpinkus
Copy link

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

@matDobek
Copy link

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}/

@ssaunier
Copy link

ssaunier commented Feb 6, 2016

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

@xjunior
Copy link

xjunior commented Mar 3, 2016

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment