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
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
Removing support functions from new cluster                 ok
Copying user relation files
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:

Running this script will delete the old cluster's data files:

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.