Upgrade Postgres DB

This week I was having issues running a project I haven’t worked on for a while as I couldn’t connect to my local database with TablePlus.

Running brew services I saw that postgresql had an error, and running brew services restart postgres resulted in the same error.

❯ brew services
Name       Status  User    Plist
postgresql error   michael /Users/michael/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

I tried running postgres directly using pg_ctl and it gave me the actual error:

pg_ctl -D /usr/local/var/postgres start
waiting for server to start....2020-12-19 14:33:09.129 GMT [58531] FATAL:  database files are incompatible with server
2020-12-19 14:33:09.129 GMT [58531] DETAIL:  The data directory was initialized by PostgreSQL version 12, which is not compatible with this version 13.1.
 stopped waiting
pg_ctl: could not start server
Examine the log output.

Aha! So postgres has been upgraded and I need to migrate my data. There are ways to do this using pg_upgrade but I didn’t have the old binaries installed. This is when I stumbled across postgresql-upgrade-database. This formula installs the old version of postgres and runs the upgrade for you

After running brew postgresql-upgrade-database I got the following output:

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
==> Upgraded postgresql data from 12 to 13!
==> Your postgresql 12 data remains at /usr/local/var/postgres.old

Another call to brew services start postgres and things were working as expected

Upgrade Postgres DB

Category

Topics