Upgrading Homebrew-installed Postgres 9.3 to 9.5

February 8th, 2016  |  Published in Uncategorized

I didn’t read the instructions when I let PostgreSQL get upgraded from 9.3 to 9.5 with brew upgrade. This is what I had to do to migrate my data after I had already upgraded:

# Switch back to the previous version of postgres, postgis
brew switch postgres 9.3.5_1
brew switch postgis 2.1.4_1
# Start the server (run this in a separate shell)
postgres -D /usr/local/var/postgres
# Dump all my databases
pg_dumpall > pg_dump
# Stop the server
pg_ctl -D /usr/local/var/postgres stop
# Switch back to the up-to-date version of postgres, postgis
brew switch postgresql 9.5.0
brew switch postgis 2.2.1
# Move the old data directory out of the way
mv /usr/local/var/postgres/ /usr/local/var/postgres9.3
# Initialize the data directory for Postgres 9.5
initdb /usr/local/var/postgres
# Start the server
postgres -D /usr/local/var/postgres
# Import the database dump
psql -d postgres -f pg_dump
# Delete the dump file
rm pg_dump
# Stop the server
pg_ctl -D /usr/local/var/postgres stop
# Start the server with launchctl
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist