With `role = "auto"`, failover goes wrong in three ways: two primaries, a demoted primary that keeps the role, and writes stuck on a dead primary

[roshangara]

PgDog version

main at 298565e (2026-10-06). Reproduced first on 5d516b5.

Description

We run PgDog in front of a Patroni cluster (PostgreSQL 18.6) with every server on role = "auto". Failover tests showed three bugs in LoadBalancer. All the code below is unchanged on current main.

1. Two primaries after a failover. redetect_roles() elects the non-recovery server whose LSN stats were fetched most recently (targets.sort_by_cached_key(|target| target.0.lsn_age(now))). The old primary can come back before it is turned into a replica: a crash and restart, a network partition, a promotion while the old primary still runs. Then both servers report pg_is_in_recovery() = false. Whichever answered last wins, the election flips between them, and writes go to both. The old primary's writes are lost when it is rewound.

   • Reproduction: a primary and a streaming standby, lsn_check_interval = 100, one client inserting every 20 ms. Promote the standby while the old primary keeps running. In three runs, the old primary acknowledged 62, 115 and 117 of about 250 writes after the promotion, and the elected primary changed 7 to 14 times.
   • Reproduction in a 3-node Patroni lab: stop the primary, promote a replica, restart the old primary on its old timeline for 15 s. Writes split 151/143, and pg_rewind later discarded the 151.

2. A primary back in recovery keeps the role. When no server reports being a primary, roles change only if every server has valid stats (} else if targets.iter().all(|target| target.0.valid()) {). If another server hasn't answered an LSN check yet (e.g. it is down), a primary that comes back as a replica stays Primary. Writes then fail with cannot execute ... in a read-only transaction. This is one way to get Pgdog sometimes do not switch primary #1255.

3. A write waits on the dead primary for the whole checkout_timeout. get_primary() checks out from the pool that is primary when the write arrives and stays on it. If that server goes down, the write waits in its pool's queue even after another server has been elected. It fails with checkout timeout. If the old server comes back as a replica first, the write fails with cannot execute ... in a read-only transaction instead, which is the sequence logged in fix(pool): fence stale automatic-primary checkouts #1494.

   • Reproduction: checkout_timeout = 10000, one client inserting every 20 ms. Stop the primary with pg_ctl stop -m immediate and promote the standby 1.1 s later. The next write waited the full 10 s and failed with checkout timeout. Writes resumed 8.8 s after the promotion. Two runs gave the same result.

Expected

• A promoted server always wins the election over the old primary. A promotion starts a new timeline, so the timeline orders them whatever their LSNs and whenever they were last checked.
• A server that reports being in recovery stops being the primary, even while another server has no stats.
• A write waiting for the primary moves to the newly elected one within checkout_timeout.

Fix branches

Both branches are on main 5d516b5 and merge cleanly with 298565e. Each change comes with tests that fail on main.

• Bugs 1 and 2: https://github.com/roshangara/pgdog/tree/fix/lb-elect-by-timeline (also opened as fix(lb): elect the automatic primary by timeline, demote a primary in recovery #1678)
  • The LSN query also returns the primary's timeline: ('x' || substr(pg_walfile_name(pg_current_wal_lsn()), 1, 8))::bit(32)::int. It is 0 on replicas and on Aurora.
  • The election orders servers by timeline, then LSN, then stats age.
  • A primary that reports being in recovery is demoted even while another server has no stats.
  • With this branch, no write after the promotion reached the old primary (0 of about 254, three runs), and the elected primary changed once.
• Bug 3: https://github.com/roshangara/pgdog/tree/fix/lb-primary-follows-election (also opened as fix(lb): a write waiting for the automatic primary follows the election #1679)
  • With automatic roles, get_primary() follows the election for up to checkout_timeout.
  • The election channel is written only when the elected pool changes (send_if_modified).
  • With this branch, writes resumed as soon as the standby was promoted, with no checkout timeout.

The two branches edit neighbouring lines of redetect_roles(), so the second one needs a trivial rebase.

Configuration

[general]
lsn_check_interval = 100
checkout_timeout = 10000

[[databases]]
name = "app"
host = "10.0.0.1"
role = "auto"

[[databases]]
name = "app"
host = "10.0.0.2"
role = "auto"

Note

Written with Claude.

[levkk]

Fixed 1/3. If you want to split these up into individual issues, we could track them better.

[roshangara]

Thanks for #1738. Split as asked:

• After a promotion, pg_control_checkpoint() reports the old timeline for minutes, so #1738 doesn't separate the two primaries yet #1739: follow-up to issue 1. After a promotion, pg_control_checkpoint() keeps the old timeline until the first checkpoint completes, 270 s with default settings on PostgreSQL 18.3, so the two primaries still tie. Reading the timeline from pg_walfile_name() fixes it.
• With role = "auto", a primary that comes back in recovery keeps the primary role while another server has no LSN stats #1740: issue 2, a primary in recovery keeps the role while another server has no stats.
• With role = "auto", a write waiting on a dead primary stays there for the whole checkout_timeout after a new primary is elected #1741: issue 3, a write waiting on a dead primary doesn't follow the election.

Each one links a branch on current main. Closing this one in favor of those.