Last active
November 9, 2017 16:59
-
-
Save eoinkelly/fd80465942c8ca4bd5c0 to your computer and use it in GitHub Desktop.
Upgrade Postgres to 9.4 using Homebrew on Mac OSX
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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 |
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.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
After running
pg_upgrade ...
line I received this error: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 installingplv8
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
intemplate0
though so I had to remove it from there too.When trying to connect to
template0
you may get the messagepsql: 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 preventpg_upgrade
from working, aspg_upgrade
will try to overridetemplate0
because it can connect to it (seems like a bug).