🎓 Upgrading PostgreSQL installed with Homebrew

I found this great article that got me started https://ivdl.co.za/2023/08/16/upgrading-from-postgresql-14-to-15-installed-with-homebrew-on-an-apple-silicon-mac/ only problem was that I was getting an error when referencing the postgresql@15 part so had to change that.

Upgrading from Postgres 14 to 15

Following the article we first have to install postgresql@15 with brew install postgresql@15.

Then made sure that neither 14 or 15 were running brew services stop postgresql@14 & brew services stop postgresql@15

The next part is where I have problems with the command shown in the article.

# DO NOT RUN THIS
/opt/homebrew/Cellar/postgresql@15/15.4/bin/pg_upgrade -b /opt/homebrew/Cellar/postgresql@14/14.9/bin -d postgresql@14 -B /opt/homebrew/Cellar/postgresql@15/15.4/bin -D postgresql@15

I got a permission error to do with postgresql@15 🤷‍♂️

Running brew info postgresql@15 I found a path for postgresql@15

...
This formula has created a default database cluster with:
  initdb --locale=C -E UTF-8 /opt/homebrew/var/postgresql@15
...

I replaced postgresql@15 with /opt/homebrew/var/postgresql@15 and did the same with postgresql@14 to come up with the following command

/opt/homebrew/Cellar/postgresql@15/15.4/bin/pg_upgrade --old-bindir /opt/homebrew/Cellar/postgresql@14/14.9/bin --old-datadir /opt/homebrew/var/postgresql@14 --new-bindir /opt/homebrew/Cellar/postgresql@15/15.4/bin --new-datadir  /opt/homebrew/var/postgresql@15

This worked perfectly. I think we could use the brew info to build this command on any machine (I think Apple Intel installs will be different)

After this, we run brew services start postgresql@15 and check that our databases are available 👌

Upgrading from Postgres 15 to 16

We follow the same process as above. Install postgresql@16, brew install postgresql@16.

Stop 15 and 16, brew services stop postgresql@15 & brew services stop postgresql@16

Then upgrade,

/opt/homebrew/Cellar/postgresql@16/16.0/bin/pg_upgrade --old-bindir  /opt/homebrew/Cellar/postgresql@15/15.4/bin --old-datadir  /opt/homebrew/var/postgresql@15 --new-bindir /opt/homebrew/Cellar/postgresql@16/16.0/bin --new-datadir  /opt/homebrew/var/postgresql@16

Start postgresql@16, brew services start postgresql@16 and check our databases 🚀

Upgrading from Postgres 16 to 17

We follow the same process as above. Install postgresql@17, brew install postgresql@17.

Stop 16 and 17, brew services stop postgresql@16 & brew services stop postgresql@17

Then upgrade,

/opt/homebrew/Cellar/postgresql@17/17.0_2/bin/pg_upgrade --old-bindir  /opt/homebrew/Cellar/postgresql@16/16.4_2/bin --old-datadir  /opt/homebrew/var/postgresql@16 --new-bindir /opt/homebrew/Cellar/postgresql@17/17.0_2/bin --new-datadir  /opt/homebrew/var/postgresql@17

Start postgresql@17, brew services start postgresql@17 and check our databases 🚀

Also run /opt/homebrew/Cellar/postgresql@17/17.0_2/bin/vacuumdb --all --analyze-in-stages

Other bits

pg gem

gem uninstall pg and then bundle install from a Rails project or gem install pg - you want to see it building with native extensions 👌

pg_dump and pg_config

brew link --force libpq