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 servicesName Status User Plistpostgresql 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 startwaiting for server to start....2020-12-19 14:33:09.129 GMT  FATAL: database files are incompatible with server2020-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 waitingpg_ctl: could not start serverExamine 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.shRunning 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