Move each database primary at most once per planned node roll #5024

🤖 Generated by the Agentic Engineer

Evidence

From the #4885 spike, read on 2026-10-17:

• The only primary changes the retained history can see are one burst on 2026-10-13. Between 04:33 and 05:03 UTC the three workers restarted one after another while the merge queue rolled a node configuration change. All four database primaries were on one worker, and all four moved.
• In the 4.5 days after that, no node restarted and no database changed primary. The timeline numbers on 2026-10-17 are the same as on 2026-10-14 (226, 208, 176 and 55).
• So the primary changes come in bursts tied to node rolls, not at a steady rate. How many earlier ones were planned is not known, because nothing kept the record (Record which database is primary before and after each production node roll #5023).

Who it affects

Every application with a database: each primary change briefly stops its writes. Each change is also another chance for a replica to fall behind, which is the shape of the three standby-recovery incidents in #4980.

Hypothesis

During a planned node roll, if the workers that hold no primary restart first, and the primaries move once, by a supervised switchover onto a worker that has already restarted, then each database changes primary at most once per roll. Today, one roll can move a primary more than once.

Smallest useful change

Decide and document, from the recorder's two weeks of data, which supported control to use. The candidates are: a primary-aware restart order for the planned roll; a supervised CloudNativePG switchover before the primary's node restarts; or spreading the primaries over the workers. Then ship the one that meets the target with the least machinery. Unplanned failover stays exactly as it is.

Success signal

• Baseline: to be taken from the recorder (Record which database is primary before and after each production node roll #5023). The lifetime average is about 1.5 to 1.9 primary changes a day per cluster.
• Target: at most one primary change per cluster per planned roll, and none when a restarted node holds no primary.
• Guardrail: failover after an unplanned node or database fault stays automatic and no slower.
• Window: two weeks of node rolls after the change.

Acceptance criteria

• The chosen control, with the reason, is recorded from the recorder's data.
• A planned roll moves each primary at most once, shown in the deploy record.
• Unplanned failover is unchanged.

Rough effort: Medium.