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