Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save olebedev/7af9c166caf0e1bf1852e44624136150 to your computer and use it in GitHub Desktop.
Save olebedev/7af9c166caf0e1bf1852e44624136150 to your computer and use it in GitHub Desktop.
Upgrade PostgreSQL 9.4 to 9.5 on Mac OS X with Homebrew

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!

#!/bin/sh
echo 'This script will generate minimal optimizer statistics rapidly'
echo 'so your system is usable, and then gather statistics twice more'
echo 'with increasing accuracy. When it is done, your system will'
echo 'have the default level of optimizer statistics.'
echo
echo 'If you have used ALTER TABLE to modify the statistics target for'
echo 'any tables, you might want to remove them and restore them after'
echo 'running this script because they will delay fast statistics generation.'
echo
echo 'If you would like default statistics as quickly as possible, cancel'
echo 'this script and run:'
echo ' "/usr/local/Cellar/postgresql/9.5.0/bin/vacuumdb" --all --analyze-only'
echo
"/usr/local/Cellar/postgresql/9.5.0/bin/vacuumdb" --all --analyze-in-stages
echo
echo 'Done'
This script will generate minimal optimizer statistics rapidly
so your system is usable, and then gather statistics twice more
with increasing accuracy. When it is done, your system will
have the default level of optimizer statistics.
If you have used ALTER TABLE to modify the statistics target for
any tables, you might want to remove them and restore them after
running this script because they will delay fast statistics generation.
If you would like default statistics as quickly as possible, cancel
this script and run:
"/usr/local/Cellar/postgresql/9.5.0/bin/vacuumdb" --all --analyze-only
vacuumdb: processing database "formious": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "metry": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "formious": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "metry": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "formious": Generating default (full) optimizer statistics
vacuumdb: processing database "metry": Generating default (full) optimizer statistics
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
Done
#!/bin/sh
rm -rf '/usr/local/var/postgres94'
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
<key>KeepAlive</key>
<true/>
<key>Label</key>
<string>homebrew.mxcl.postgresql</string>
<key>ProgramArguments</key>
<array>
<string>/usr/local/opt/postgresql/bin/postgres</string>
<string>-D</string>
<string>/usr/local/var/postgres</string>
<string>-r</string>
<string>/usr/local/var/postgres/server.log</string>
</array>
<key>RunAtLoad</key>
<true/>
<key>WorkingDirectory</key>
<string>/usr/local</string>
<key>StandardErrorPath</key>
<string>/usr/local/var/postgres/server.log</string>
</dict>
</plist>
The files belonging to this database system will be owned by user "chbrown".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
creating directory /usr/local/var/postgres ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /usr/local/var/postgres/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D /usr/local/var/postgres -l logfile start
copying "/usr/local/var/postgres94/base/1/12360" to "/usr/local/var/postgres/base/1/2613"
copying "/usr/local/var/postgres94/base/1/12362" to "/usr/local/var/postgres/base/1/2683"
copying "/usr/local/var/postgres94/base/1/12222" to "/usr/local/var/postgres/base/1/2995"
copying "/usr/local/var/postgres94/base/1/12224" to "/usr/local/var/postgres/base/1/2996"
ok
Setting next OID for new cluster "/usr/local/Cellar/postgresql/9.5.0/bin/pg_resetxlog" -o 420396 "/usr/local/var/postgres" >> "pg_upgrade_utility.log" 2>&1
ok
Sync data directory to disk "/usr/local/Cellar/postgresql/9.5.0/bin/initdb" --sync-only "/usr/local/var/postgres" >> "pg_upgrade_utility.log" 2>&1
ok
Creating script to analyze new cluster ok
Creating script to delete old cluster ok
Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
./analyze_new_cluster.sh
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment