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
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!