Skip to content

Instantly share code, notes, and snippets.

@chbrown
Last active October 7, 2021 13:57
Show Gist options
  • Save chbrown/647a54dc3e1c2e8c7395 to your computer and use it in GitHub Desktop.
Save chbrown/647a54dc3e1c2e8c7395 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
@AndrewFreemantle
Copy link

👍 Thank you

@michelegera
Copy link

Thank you so much! 🙇‍♂️

Worked like a charm for 9.5 → 9.6

@fivescout
Copy link

Thanks for the write-up! worked like a charm.

@number5
Copy link

number5 commented Nov 29, 2016

Nice docs!

Btw, you can get older version of postgres this way (bit easier than using older git version)

brew tap homebrew/versions
brew update
brew install postgresql94

then you switch between versions using brew link/unlink e.g.

brew unlink postgresql
brew link postgresql94

@Krafalski
Copy link

Thank you thank you thank you!

@romanenko
Copy link

Thank you! <3

@arnaudlimbourg
Copy link

Thanks a lot!

@qkdreyer
Copy link

Thanks a lot man !

@bbirand
Copy link

bbirand commented Jan 5, 2017

Much thanks, so wow! 👍

@baptistemesta
Copy link

thanks! 👍

@adeperio
Copy link

thanks buddy, 9.4 -> 9.6

@Antoinerev
Copy link

Antoinerev commented Jan 20, 2017

I got stuck with this on server load on a perfectly fonctionnal project the day before, just after a security update for MAcOSX El Capitan : <PG::ConnectionBad: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
It took me hours of research and trials to finally understand I suddenly have a version problem with both PG and Ruby!!!! So thanks for saving me upgrade PG to 9.6.1!

@ispequalnp
Copy link

thank you! 9.4 -> 9.6

@elyak123
Copy link

Wow!!!! it really worked!! thankyou sooo much!!

@tk5446
Copy link

tk5446 commented Jul 2, 2017

9.3 -> 9.6 -- worked! many thanks

@elizabethsiegle
Copy link

THANK YOU! <3

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment