A PostgreSQL security minor release lands and you run 20 self-managed 18.x servers, primaries with streaming standbys: how do you roll it out safely, and in what order?
answer
- triage the CVEs against what you run
- notes for every skipped minor
- standbys before their primary
- keep the mixed-minor window short
- prove it with server_version_num
basics
~20 sRead the notes for every skipped minor, canary first, then in each cluster update standbys before the primary, restart each, keep the mixed-version window short, run the post-update steps and confirm server_version_num on all 20 servers.
solid answer
~50 sStart with the notes: which CVEs touch features you use, and which "Migration to Version" actions apply to which servers, including every minor a lagging server skips. Stage the packages everywhere, then canary one low-risk cluster. Inside each replicated cluster update the **standbys first**: the manual says a new minor reads WAL from an older minor more reliably than the reverse, and mixed minors are not formally supported, so keep that window short. Among 18.3's out-of-cycle fixes was a replay failure typically hit by a standby on the latest minor reading an older primary's WAL. Then restart the primary in a short window, or switch over to an updated standby. Configuration changes go on every node, since `ALTER SYSTEM` writes a local file that is not replicated; `REINDEX` and `ANALYZE` run once on the primary and reach standbys through WAL. Finish by collecting `server_version_num` from all 20.
code
bash · 8 lines# On each standby in turn, then on the primary last
psql -Atc 'SHOW server_version_num' # e.g. 180004 before
psql -c 'CHECKPOINT' # a restartpoint on a standby
pg_ctl -D "$PGDATA" stop -m fast
# install the staged 18.6 server packages here
pg_ctl -D "$PGDATA" start
psql -Atc 'SHOW server_version_num' # expect 180006
psql -Atc 'SELECT pg_is_in_recovery()' # t on a standby, f on the primarygo deeper
Recall that a minor update is a binary swap and restart, and that in a primary-standby pair the standbys go first.
Explain why standbys go first, a newer minor reading older WAL, and which follow-up actions replicate and which must be repeated per node.
Show the whole procedure: triage, condition queries, staged packages, canary, short mixed windows, per-node configuration and a version check that proves completion.
Weigh speed against risk across the fleet: patch-now criteria for exposed CVEs, soak time otherwise, and a cadence that keeps no server several minors behind.
## 1. Triage the release A security minor is not one decision for the whole fleet. Read the release notes, and for each fix ask whether it affects what you run: - **Exposure**: is the vulnerable feature reachable, by whom? A flaw reachable by any authenticated user on an internet-facing service is urgent; one that needs a feature you do not use can follow the normal cadence. - **Post-update actions**: list every item in the "Migration to Version" sections of this release **and of every minor a server skips**. A server still on 18.1 inherits the 18.2 `ltree` reindex in addition to 18.6's own items. - **Timing**: out-of-cycle follow-ups happen. 18.5 was withdrawn before release, 18.3 came two weeks after 18.2, and 17.2 a week after 17.1. For a fix you are exposed to, patch now; for the rest, a short canary soak costs little. ## 2. Prepare the fleet 1. Inventory the running versions: `SHOW server_version_num` on every host, so you know which servers skip which minors. 2. Run the condition queries from the notes on each primary: installed extensions, affected index definitions, the plugins of logical replication slots. 3. Stage the new packages on every host without restarting, so the restart window contains only the restart. 4. Canary: one non-critical cluster goes first, through the whole procedure, and is watched before the rest follow. ## 3. Order inside a replicated cluster The manual's guidance for streaming replication is explicit. Running different minors on the primary and its standbys is **likely to work**, because minors do not change disk formats, but it has **no formal support**, and the safest policy is to **update the standbys first**: a new minor is more likely to read WAL from an older minor than an old minor is to read WAL from a newer one. 1. Update and restart each standby in turn. A standby that restarts reconnects and catches up from the primary, provided the WAL it needs is still retained or archived. 2. Check replication is healthy again before moving on. 3. Update the primary last. A short restart is a brief outage for writers; the alternative is a planned switchover to an already updated standby, whose mechanics belong to promotion and, under an HA manager, to that manager. Keep the mixed window short. The 18.3 notes describe a bug fixed out of cycle: replaying a multixid truncation record generated by an older minor could fail with "could not access status of transaction", typically on a **standby of the latest minor consuming WAL from an older primary**, which is exactly the state standby-first creates. Standby-first stays the documented order; the lesson is to finish the cluster promptly rather than leave it half-patched. A manual `CHECKPOINT` shortly before each stop leaves the shutdown checkpoint less to write, which shortens the restart. ## 4. Where each post-update action runs | Action | Run it on | Why | |---|---|---| | `REINDEX` of affected indexes | The primary, once | Index builds are WAL-logged and replay on standbys, which are read-only anyway | | `ANALYZE` to fix stored statistics | The primary, once | `pg_class` and `pg_statistic` changes replicate through WAL | | A new or changed setting | Every node | `postgresql.conf` and the `postgresql.auto.conf` written by `ALTER SYSTEM` are local files, not replicated | | Data cleanup | The primary | Ordinary writes replicate | The configuration row is the one teams miss. A setting added only on the primary works until the first failover, when the promoted standby runs with whatever its own files say, often the default. ## 5. Verify and close - Collect `server_version_num` from all 20 servers and compare with the target, `180006` for 18.6. A server that still reports the old number was patched on disk and not restarted, or not patched. - Confirm each post-update action ran where the table above says. - Check replication on every cluster, and the logs for errors the notes warned about. - Record what was done per server, so the next round knows which notes each one has already absorbed. - Review why any server needed special handling. A server that is regularly one or two minors behind the rest turns every future round into a multi-release update with a longer list of post-update actions. Out of scope here: switchover mechanics, HA-manager rolling restarts and managed-service patch windows each have their own owner.
- One of the 20 PostgreSQL servers is still on 18.1 when 18.6 lands. What changes for it?It can go straight to 18.6, since minors are cumulative, but it inherits every post-update action from 18.2, 18.3 and 18.4 as well as 18.6, such as the 18.2 reindex of `ltree` indexes. Check those conditions on it specifically, and find out why it fell behind, because a server that misses rounds is the one that ends up exposed.
- When would you not apply a PostgreSQL security minor immediately on every server?When the fixed problems do not touch features you expose. Then a few days on a canary cluster is cheap insurance: out-of-cycle releases sometimes follow scheduled ones, such as 18.3 two weeks after 18.2, and 18.5 was withdrawn for a regression before shipping. If a fix covers something reachable by your users, patch now and accept the small regression risk.
- Update the primary first so standbys never replay WAL from an older server.
- Mixed minor versions are fully supported, so standbys can lag a release for weeks.
- Run REINDEX on every standby too, since index rebuilds are not replicated.
- Set new parameters with ALTER SYSTEM on the primary; standbys inherit them through WAL.
- A green package-install report proves the fleet is patched.