Upgrading Postgres With Homebrew

I’ve battled with this before and thought I’d learned my lesson. Apparently not! Today I faced some issues upgrading PostgreSQL on my laptop again.

What I was trying to do

I downloaded a dump of a heroku postgres database to my local machine (thanks Render docs). No problem!

$ heroku pg:backups:capture --app my-heroku-app
$ heroku pg:backups:download --app my-heroku-app

Then I tried to restore it to the new host location, railway:

$ pg_restore --verbose  --no-acl --no-owner -d postgresql://USER:PASS@containers-us-east-9999.railway.app:5432/railway latest.dump

And there the errors began…

$ pg_restore --verbose  --no-acl --no-owner -d postgresql://USER:PASS@containers-us-east-9999.railway.app:5432/railway latest.dump
pg_restore: [archiver] unsupported version (1.14) in file header
$ postgres -v
dyld: Library not loaded: /usr/local/opt/icu4c/lib/libicui18n.64.dylib
  Referenced from: /usr/local/bin/postgres
  Reason: image not found
Abort trap: 6

How I fixed it

Some StackOverflowing indicated that pg_restore might be outdated. What the heck version was I even on?

$ which pg_restore
/usr/local/bin/pg_restore
$ ls -l /usr/local/bin/pg_restore 
lrwxr-xr-x  1 user  role  40 date /usr/local/bin/pg_restore -> ../Cellar/postgresql/11.5/bin/pg_restore
$ pg_restore -V
pg_restore (PostgreSQL) 11.5

A clue! I’ve used the pg_restore tools with Postgres version 11 to capture & download the database dump, but the Heroku database I’ve backed up from is running version 12, and railway is using version 13. The latest is version 14. What a mess.

My next step was to upgrade my local Postgres version to 12, to at least be compatible with the heroku source:

$ brew install postgresql@12

This took about 25 minutes to run all of the updates 🙀 Eventually it finished and I added the new version to PATH in my bash profile:

If you need to have postgresql@12 first in your PATH, run:
  echo 'export PATH="/usr/local/opt/postgresql@12/bin:$PATH"' >> /Users/user/.bash_profile

Then I tried restoring the db dump to Railway again…and it worked!:

$ pg_restore --verbose  --no-acl --no-owner -d postgresql://USER:PASS@containers-us-east-9999.railway.app:5432/railway latest.dump
pg_restore: connecting to database for restore
...

Testing New Database Host

I connected my locally running app the the new host and verified that all the controllers still worked, and that all ties had successfully been severed from Heroku 😢

BUT THEN I noticed that you can’t download your data from Railway. What the heck! Well you probably can—hopefully you can by installing their CLI and doing a dump that way. They’ve also created a plugin that you can run on your own server to automatically send backups to an AWS S3 bucket. This is supposed to be low maintenance though…

Since this particular app isn’t public facing at all, I decided to just run it entirely locally going forward. Make it as simple as possible.

Omg even `localhost` isn’t working 😭

Of course it’s not that easy! If I’m going to run it locally I need to get my local PG up & running. But I’m getting a big fat connection error connecting to localhost in Postico. Ugh.

If I’m basically starting from the beginning I may as well get completely up to date, so I install postgres 14 first…

$ brew install postgresql@14

…and update PATH again to get the correct version (may as well keep all versions in case needs be…):

# Postgres Installations
    # export PATH="/usr/local/opt/postgresql@10/bin:$PATH"
    # export PATH="/usr/local/opt/postgresql@12/bin:$PATH"
    export PATH="/usr/local/opt/postgresql@14/bin:$PATH"

With the bash profile updated and re-sourced, I’m now finally up to date:

$ postgres --version
postgres (PostgreSQL) 14.6 (Homebrew)

So I should be able to open the postgres server command line, right? Nope…!

$ psql
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
	Is the server running locally and accepting connections on that socket?
$ brew services
Name          Status User File
postgresql@10 none        
postgresql@12 none        
postgresql@14 none

Ok so no postgres service is actually running. So I start a service, but to no avail:

$ brew services start postgresql
Warning: Use postgresql@14 instead of deprecated postgresql
==> Successfully started `postgresql@14` (label: homebrew.mxcl.postgresql@14)
$ psql
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
	Is the server running locally and accepting connections on that socket?

I took it’s warning and tried again:

$ brew services start postgresql@14
/Users/user/Library/LaunchAgents/homebrew.mxcl.postgresql@14.plist: service already loaded
Error: Failure while executing; `/bin/launchctl bootstrap gui/501 /Users/user/Library/LaunchAgents/homebrew.mxcl.postgresql@14.plist` exited with 133.

So the service is running, it’s just not working! Lovely! I stopped it (brew services stop postgresql) and looked around the /usr/local/var directory to see what might be causing conflict. There were a few postgres versions and support files there:

drwx------  25 user  role   800B May 31  2020 postgres
drwx------  25 user  role   800B Mar 14  2019 postgres.old
drwx------  24 user  role   768B Mar 14  2019 postgresql@10
drwx------  24 user  role   768B Nov 13 18:37 postgresql@12

Then I tried restarting the postgres service, but it still didn’t launch successfully:

$ brew services restart -vvv postgresql
Warning: Use postgresql@14 instead of deprecated postgresql
/bin/launchctl enable gui/501/homebrew.mxcl.postgresql@14
/bin/launchctl bootstrap gui/501 /Users/user/Library/LaunchAgents/homebrew.mxcl.postgresql@14.plist
==> Successfully started `postgresql@14` (label: homebrew.mxcl.postgresql@14
$ brew services list
Name          Status     User   File
postgresql@10 none              
postgresql@12 none              
postgresql@14 error  256 niabia ~/Library/LaunchAgents/homebrew.mxcl.postgresql@14.plist

I googled this error and tried running brew postgresql-upgrade-database, thinking this felt vaguely familiar, and might get v14 into the state it needs to be. But this gave an error as well:

$ brew postgresql-upgrade-database
Warning: Use postgresql@14 instead of deprecated postgresql
Error: /usr/local/var/postgres.old already exists!
Remove it if you want to upgrade data automatically.

So then I googled this error and lo and behold! if my own post about this very issue didn’t come up as the 2nd result. Thanks old me! I took the advice of my risky former self and deleted the postgres.old directory and tried running it again:

$ rm -rf /usr/local/var/postgres.old
$ brew postgresql-upgrade-database
...
$ brew services list
Name          Status     User   File
postgresql@10 none              
postgresql@11 none              
postgresql@12 none              
postgresql@14 error  256 user ~/Library/LaunchAgents/homebrew.mxcl.postgresql@14.plist
$ psql
psql (14.6 (Homebrew))
Type "help" for help.

user=#

Update October 2023:
On encountering the same error again, the magic command was rm /usr/local/var/postgres/postmaster.pid as described in this StackOverflow post

OMG I’M IN!

It worked! I can access the postgres server command line, and connect in Postico. Turns out there wasn’t even any data in the old versions…I could have just scrapped it all and reinstalled from the beginning to save myself the time. Oh well, better safe than sorry!

Now to learn local Postgres again…