Upgrading from a PostgreSQL version Homebrew already abandoned

Posted on January 16, 2017 by Carsten Zimmermann

When my PostgreSQL installation didn’t come up after an unexpected reboot and showed errors about a missing libreadline.dylib (a familiar message after upgrading to macOS Sierra), I figured I could remedy it with a simple brew reinstall postgresql. Sadly, the PostgreSQL version Homebrew reinstalled was not the one that was already installed, but the most recent version it knew of.

I may have cranked brew cleanup postgresql a tad bit too early 😱 and so I ended up with my 9.3 installation files already gone and only v9.6.1 available. Sadly, the old binaries are needed to access the old data format.

The earliest PostgreSQL version Homebrew still had in stock was 9.4. I checked postgresql.org to see whether there were old source or binary distribution tarballs available. Indeed, it had a link pointing advanced users to a list of zip files containing binaries.

With all tools prepared (and some cross-referencing) I could repair my broken installation (assuming the new pgsql is already installed):

# Move the old data files out of the way
mv /usr/local/var/postgres /usr/local/var/postgres93

# Since my computer previously crashed, I had a stale pid file
rm /usr/local/var/postgres93/postmaster.pid

# Create a new data dir for the most recent pgsql version
initdb --pgdata=/usr/local/var/postgres

# Migrate data files, pointing old-bindir to the location of
# the extracted zip file
pg_upgrade \
  --old-datadir=/usr/local/var/postgres93 \
  --new-datadir=/usr/local/var/postgres \
  --old-bindir=$HOME/Downloads/pgsql/bin \
  --new-bindir=/usr/local/Cellar/postgresql/9.6.1/bin \

# Start PostgreSQL again
brew services start postgresql

Everthing should be up and running again.

comments powered by Disqus