PostgreSQL minor version update (patch)

Minor update within the same major version (e.g. 17.4 → 17.x): release notes review, backup, and keeping the current RPM/deb packages for rollback. On a single server with a short outage; in a streaming replication cluster the standbys are updated first, then the primary via switchover (rolling); in a Patroni cluster the replicas are updated with patronictl restart and the leader after patronictl switchover. Includes extension updates, verification and a rollback (downgrade) runbook. At the end all servers run the same new minor version and you still have the previous version's files for rollback.

If the major version changes (e.g. 16 → 17), use pg-major-upgrade instead of this guide; updating the Patroni software itself is covered in pg-patroni.

Topology: 1 server, primary + standbys, or a Patroni cluster

Scenarios

• Minor update — Move to a newer minor version within the same major version (e.g. 17.4 → 17.6): security and bug fixes. Short outage on a single server, rolling in a cluster.
• Rollback: return to the previous minor version (downgrade) — A bug or regression appeared in the new minor version: roll back with the saved RPM/deb packages of the previous version. The data directory does not change.

Before you start

• PostgreSQL … is installed from the PGDG repository and running (pg-install). The update stays within the same major version; for … → another major version use the pg-major-upgrade guide.
• The release notes of every minor version between the current and the target version have been read. In particular, any extra action required in the "Migration to Version …" section (REINDEX, a settings change) has been noted: https://www.postgresql.org/docs/release/
• The servers can reach the PGDG repository or an internal mirror, and the mirror is up to date.
• The maintenance window is approved… (the service is down for a few seconds to a few minutes)… (connections drop for a few seconds at switchover/restart)…; the application team has been informed.
• The last backup succeeded… (pgbackrest info status ok)… and VM/disk snapshots of the servers can be taken.
• All standbys are streaming with no lag.
• Because the roles change after switchover, the pg_hba.conf of every node allows replication connections from the other nodes, and the … password is in the postgres user's ~/.pgpass file on all nodes.
• The cluster is not managed by an HA tool such as Patroni or repmgr. If Patroni manages it, choose Patroni under Topology; for repmgr see the Notes. The manual switchover steps on this path conflict with the HA tool's decisions.
• The Patroni cluster is healthy: one Leader, all replicas streaming, small Lag, and Maintenance mode off. … is the current leader; the replica names match the Patroni member names.
• patronictl commands that make changes (restart, switchover) require REST API authentication; the user/password is in /etc/patroni/patroni.env on all nodes (pg-patroni installation). If you use a different layout, restapi.authentication must be defined in the file patronictl reads with -c.
• Before the update, the previous version's RPM/deb files were saved to the … directory and the previous version number is in …/prev-version.txt. Without them you must download the previous version from the repository (EL: PGDG keeps older versions; Debian/Ubuntu: apt-archive.postgresql.org).

Phases of the guide

• Pre-checks — Record the current version · List the versions in the repository · Review the release notes · List the installed extensions · Check long-running and prepared transactions
• Backup and fallback point — Take a backup (pgBackRest) · Take a backup (pg_dumpall + snapshot) · Keep the current RPM/deb files
• Patroni cluster: pre-check — Verify cluster state and roles
• Update — Run a checkpoint · Stop PostgreSQL · Update the software · Start PostgreSQL
• Updating the primary (short outage) — Run a checkpoint · Stop the primary · Update the software · Start the primary
• Switchover and update of the old primary — Verify that replication is current · Stop application writes · Shut down the old primary cleanly and note the last checkpoint · …: verify that all WAL has arrived and promote · Redirect the applications to the new primary · Create a replication slot for the old primary …
• Leader: switchover and update of the old leader — Old leader: update the software · Switchover: make … the leader · Restart the old leader (…) · Verify the final cluster state
• Extensions and verification — Update extensions (ALTER EXTENSION … UPDATE) · Update extensions (ALTER EXTENSION … UPDATE) · Apply the extra actions from the release notes · Verify version and status · Search the logs for errors · Post-update backup …
• Rollback (downgrade) — Check the previous version · …: stop, install the previous version, start · …: install the previous version and restart with Patroni · Leader: install the previous version · Hand over the leader and restart the old leader · …Stop the primary…Stop PostgreSQL… …