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.
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:
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:
==> 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