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.
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  FATAL: database files are incompatible with server 2020-12-19 14:33:09.129 GMT  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.
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
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