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!
hero
Thanks, dude.