Upgrading to PostgreSQL 17 with Homebrew

Install PostgreSQL 17

Homebrew distributes Postgres as explicitly version-numbered packages. This allows you to have more than one version installed at a time, which is key for migrating data.

First, install the new version (17).

brew install postgresql@17

Don’t uninstall the old version (16) yet; you’ll need it for migrating data. For now, stop the service so it is no longer accepting connections.

brew services stop postgresql@16

Migrate your databases

Next, do a dry-run of the data migration using pg_upgrade with the --check option.

$(brew --prefix)/opt/postgresql@17/bin/pg_upgrade \
  -b $(brew --prefix)/opt/postgresql@16/bin \
  -B $(brew --prefix)/opt/postgresql@17/bin \
  -d $(brew --prefix)/var/postgresql@16 \
  -D $(brew --prefix)/var/postgresql@17 \
  --check

A “clusters are compatible” message means you are good-to-go:

Performing Consistency Checks
-----------------------------
Checking cluster versions                               ok
Checking database user is the install user              ok
Checking database connection settings                   ok
Checking for prepared transactions                      ok
Checking for contrib/isn with bigint-passing mismatch   ok
Checking data type usage                                ok

*Clusters are compatible*

To perform the actual migration, run the command again, this time without --check.

$(brew --prefix)/opt/postgresql@17/bin/pg_upgrade \
  -b $(brew --prefix)/opt/postgresql@16/bin \
  -B $(brew --prefix)/opt/postgresql@17/bin \
  -d $(brew --prefix)/var/postgresql@16 \
  -D $(brew --prefix)/var/postgresql@17

Afterwards, check your working directory for files created by the pg_upgrade command. There may be additional scripts that you can review and optionally run:

delete_old_cluster.sh
update_extensions.sql

Activate PostgreSQL 17

At this point your new installation of Postgres has all of your data, but isn’t actually running and listening for connections. Also, command-line tools like psql and createdb are still pointing to the old version.

Switch out 16’s binaries and put 17’s in your PATH like this:

brew unlink postgresql@16
brew link postgresql@17

Now when you run e.g. psql, you should get the new version.

$ psql --version
psql (PostgreSQL) 17.0 (Homebrew)

Finally, start the Postgres 17 service so that it is listening for connections. This command will automatically keep Postgres running across computer reboots.

brew services restart postgresql@17

Confirm that 16 is deactivated and 17 is started:

$ brew services
Name          Status  User      File
postgresql@16 none
postgresql@17 started mbrictson ~/Library/LaunchAgents/homebrew.mxcl.postgresql@17.plist

Recompile the pg gem

Finally, if you are using Ruby to connect to Postgres, you should recompile the pg gem to ensure it works with the new version. The gem pristine command will take care of it:

$ gem pristine pg
Restoring gems to pristine condition...
Building native extensions. This could take a while...
Restored pg-1.5.8

All done! You’ve now upgraded to PostgreSQL 17.

Additional reading

PostgreSQL 17 apparently includes an impressive speed boost for certain Active Record queries. Here are some more details:

• PostgreSQL 17 Released! (official announcement)
• As Rails developers, why we are excited about PostgreSQL 17
• Documentation → PostgreSQL 17 → Appendix E. Release Notes