This past week, I upgraded my primary PostgreSQL servers from version 13 to version 16. The process was relatively straightforward, but there were a few glitches that I wanted to document here.

My intention is to do this upgrade annually and usually stay a year behind the current release (mostly because I don't want to carefully track every piece of software that depends on PostgreSQL).

I was behind a couple of versions, mostly due to the sunsetting of Cartographica last year and my lack of interest in making changes to the backend while that was in flight.

Preparation

My upgrade process is a well-worn path at this point, automated using Ansible with scripts that I've been using for years. I have a staging server that I upgrade first and then test each application before I move on to production.

My upgrade process follows the old, reliable mechanism of dumping the database, upgrading the software, and then restoring the database. I've scripted this process using Ansible to ensure that I follow the indentical process each time.

For the official release notes for 14 and 15, see:

• PostgreSQL 14 Release Notes
• PostgreSQL 15 Release Notes

Upgrading

The actual upgrade process went off without a hitch. My ansible scripts did their jobs well, and I mostly remembered to get the backups out of the way so they didn't stop the processs.

Post-Upgrade

The upgrade process went smoothly, but I did run into a couple of items that caused problems later. In particular:

• There was a change in PostgreSQL 15 that modified how permissions were handled which tripped up one of my older applications that was not yet using my vault-based secrets management system. For the vault-based roles, I'd been explicitly granting permissions on the public schema to my owner role. However, that was now required in more circumstances. An easy enough fix, I just manually grant ALL on schema public to foo;

• As of PostgreSQL 16, roles must be assigned to the GRANT user unless they are superuser. See Postgres roles and privileges for detailed information on the change to my roles and privileges system. Amusingly, my article previously called out the security implications of not having a role-specific grant privilege, so I can't complain about the change, but it did affect my scripts. I've updated the previous post to reflect the changes.