Record which database is primary before and after each production node roll #5023

🤖 Generated by the Agentic Engineer

Evidence

The #4885 spike could not attribute primary changes to causes, because nothing keeps the record long enough:

• The four production PostgreSQL clusters have changed primary 55 to 226 times since they were created (their timeline numbers), but nothing records when or why.
• The database operator's own log starts at its last restart, so a node roll erases it. On 2026-10-17 it covered only the 4.5 days since 2026-10-13.
• The operator's metrics are not collected, so the 14-day metrics store has no history of primary changes either.

Who it affects

Anyone deciding how to cut primary changes (#4980). Without the record, each planned change to node rollouts is a guess, and its effect cannot be measured.

Hypothesis

If each production deploy writes down, for every database, which server is primary and its timeline before and after it rolls the nodes, then every primary change can be put down to either a planned roll (it happens inside a deploy) or something else (it happens between deploys).

Smallest useful change

A read-only step in the production deploy, before and after the node update, that prints each CloudNativePG cluster's current primary, its node and its timeline, and the boot time of each node. The output stays in the deploy run log (kept for 90 days). It reads cluster status only, never credentials, and never changes anything.

Success signal

• Baseline: zero recorded primary changes with a cause.
• Target: for two weeks of deploys, every timeline change is either inside a deploy that rolled nodes or listed as between deploys.
• Guardrail: the deploy takes at most a few seconds longer, and a failed read of this record is reported as missing in the log without blocking the deploy.

Acceptance criteria

• Each production deploy run shows, before and after the node update, every CNPG cluster's primary, its node and timeline, and every node's boot time.
• A read that fails says so in the log rather than printing an empty record.
• The step needs no permission beyond reading cluster status and nodes.
• After two weeks, the Databases keep a stable primary and every scheduled backup completes #4980 follow-up decision is written from this record.

Rough effort: Small.

Follows the #4885 spike decision.