When upgrading my Mac, Postgres stopped working. This page documents my journey. First, the migration process gave me a long password for the postgres user. It also didn’t set up Postgres to start up automatically. netstat confirmed nothing running on 5432.
Posgres is started on the old machine using a launch daemon. I can see it at /Library/LaunchDaemons/postgresql-15.plist. Launch daemons require elevated access to run which seems related to the user recreation.
The plist file is a text file so I read it. It says that it runs at load with userName postgres. I ran dscl . list /Users and confirmed that user still exists.
I then came across this post which says I shouldn’t expect the database to start up from a time machine backup. Given that this is just a test database and there’s nothing in there I care about, I tried reinstalling Postgres. Which didn’t change anything.
I then checked /Library/PostgreSQL/15/data/log/<latest> and got:
2023-05-21 07:51:02.282 EDT [372] LOG: listening on IPv6 address "::", port 5432
2023-05-21 07:51:02.282 EDT [372] LOG: listening on IPv4 address "0.0.0.0", port 5432
2023-05-21 07:51:02.282 EDT [372] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-05-21 07:51:02.300 EDT [402] LOG: database system was interrupted; last known up at 2023-04-25 20:00:54 EDT
2023-05-21 07:51:02.667 EDT [402] LOG: invalid record length at 0/331FC48: wanted 24, got 0
2023-05-21 07:51:02.667 EDT [402] LOG: invalid primary checkpoint record
2023-05-21 07:51:02.667 EDT [402] PANIC: could not locate a valid checkpoint record
2023-05-21 07:51:02.668 EDT [372] LOG: startup process (PID 402) was terminated by signal 6: Abort trap: 6
2023-05-21 07:51:02.668 EDT [372] LOG: aborting startup due to startup process failure
2023-05-21 07:51:02.668 EDT [372] LOG: database system is shut down
That was helpful. I reset the log.
JeanneBrskysMBP:bin postgres$ ./pg_resetwal ../data
The database server was not shut down cleanly.
Resetting the write-ahead log might cause data to be lost.
If you want to proceed anyway, use -f to force reset.
JeanneBrskysMBP:bin postgres$ ./pg_resetwal -f ../data
Write-ahead log reset
After restarting, I saw postgres up again. Yay.
nyjeanne@JeanneBrskysMBP ~ % netstat | grep 5432
222187ba86974afb stream 0 0 222187b5bb6cdf9b 0 0 0 /tmp/.s.PGSQL.5432
And the startup log is happy now.
2023-05-21 08:09:08.930 EDT [374] LOG: database system is ready to accept connections
2023-05-21 08:14:08.987 EDT [403] LOG: checkpoint starting: time
2023-05-21 08:14:08.992 EDT [403] LOG: checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.001 s, total=0.005 s; sync files=2, longest=0.001 s, average=0.001 s; distance=0 kB, estimate=0 kB
The other problem is that I had removed the database from pgadmin. Luckily this doesn’t delete the database. I registered it again with the same info as the settings on the old mac and all was well. My data is still there too. Nice!