Spencer Turner

How to fix your postgres install when you've run `brew upgrade` without backing up Postgres data over a major version

Doh! Third time now. Seriously. I just don't notice that it's a version upgrade, and set it off. Then "oh *!#$"

Now, I know I can remigrate most of the stuff I have running, I also have backups I can pull, so it's not the end of the world, but it's tedious and something clicked in my brain this time...

Homebrew maintains older versions. (Unless you're a fanatical cleaner-upper). If you haven't run clean, here's a solution.

Make a directory to copy files to:

mkdir /usr/local/var/pgsql.old

Copy your postgres folder

mv /usr/local/var/postgres /usr/local/var/pgsql.old

Create 'new' postgres files

initdb /usr/local/var/postgres -E utf8

Stop the process

launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

There's a facility called pg_upgrade for this...but you need the old and new binaries for it to work! Yay brew not deleting old versions! The command looks like this:

pg_upgrade -b /usr/local/Cellar/postgresql/{old_version}/bin -B /usr/local/Cellar/postgresql/{new_version}/bin -d /usr/local/var/{old-data-dir} -D /usr/local/var/{data-dir}

If you set the folder up as above, it'll look like this.

pg_upgrade -b /usr/local/Cellar/postgresql/9.2.4/bin -B /usr/local/Cellar/postgresql/9.3.0/bin -d /usr/local/var/pgsql.old/postgres -D /usr/local/var/postgres

You'll then see something like:

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is a superuser                       ok
Checking for prepared transactions                          ok
Checking for reg* system OID user data types                ok
Checking for contrib/isn with bigint-passing mismatch       ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok
Checking for presence of required libraries                 ok
Checking database user is a superuser                       ok
Checking for prepared transactions                          ok

If pg_upgrade fails after this point, you must re-initdb the new cluster before continuing.

Performing Upgrade

Analyzing all rows in the new cluster ok Freezing all rows on the new cluster ok Deleting files from new pg_clog ok Copying old pg_clog to new server ok Setting next transaction ID for new cluster ok Setting oldest multixact ID on new cluster ok Resetting WAL archives ok Setting frozenxid counters in new cluster ok Restoring global objects in the new cluster ok Adding support functions to new cluster ok Restoring database schemas in the new cluster ok Removing support functions from new cluster ok Copying user relation files ok Setting next OID for new cluster ok Sync data directory to disk 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

Restart postgres

launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

To find out how I can help you and your team please Get in touch for a free no-obligation chat.