Database “postgres” does not exist

I recently encountered a strange error with PostgreSQL on my development machine:

psql: FATAL:  database "postgres" does not exist
DETAIL:  The database subdirectory "base/12641" is missing.

It was strange because most of my databases were working but a couple weren’t. Also, there was a postgres database - though the postgres role didn’t appear to exist. psql worked but psql -l would output the same error.

I didn’t want to lose my local data and spent a long time failing to find a proper fix - I tried a quick reinstall but that didn’t work either. In the end I resorted to blatting the current install and starting again.

How to dump/restore databases and reinstall PostgreSQL on a Mac with Homebrew

Dump any databases you wish to keep and stop PostgreSQL:

$ pg_dump -Fc --no-acl --no-owner my_database > my_database.dump
$ launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

Better to keep the existing data just in case this process needs to be rolled back:

$ mv /usr/local/var/postgres somewhere/safe

Reinstall and start PostgreSQL:

$ brew uninstall postgresql
$ brew install postgresql
$ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

Create and restore the old database(s):

$ createdb my_database
$ pg_restore --no-acl --no-owner -d my_database my_database.dump

If your luck is as good as mine then you should have a fully working PostgreSQL install with your previous database(s).

Why?

I think this problem was caused by me not reading the instructions and blindly upgrading PostgreSQL with brew upgrade postgresql to go from 9.4 to 9.5. Or was it 9.3 to 9.4…

The proper way to migrate PostgreSQL data between upgrades

Here’s how to properly upgrade PostgreSQL data using pg_upgrade - though I’m sure I did this!