Promoting a PostgreSQL Standby with Patroni Without Data Loss

Problem statement: you need to move the primary role to another node — planned or otherwise — and you need a guarantee, not a hope, that no committed transaction is lost in the process.

Patroni can give that guarantee, but only if four settings are consistent with each other. This page is about those four settings, the arithmetic that ties them together, and how to verify the guarantee held after the fact. The broader mechanics are covered in replica failover and promotion mechanics; this page is the Patroni-specific runbook.

Symptom Identification

You need this configuration if any of the following describes your cluster:

  • patronictl list shows standbys with a non-trivial Lag in MB column during normal operation, and no setting prevents one of them being promoted.
  • The Patroni cluster has synchronous_mode: false (the default) but the team believes failover is zero-loss because synchronous_standby_names is set in postgresql.conf.
  • A previous failover was followed by user reports of “missing” recent records, and nobody can state how many transactions were lost or prove that any were.
  • maximum_lag_on_failover is at its default and has never been discussed as a durability budget.
  • Patroni once refused to promote during an incident and the response was to force it, rather than to read why it refused.

The last one is worth dwelling on. A refusal to promote is Patroni declining to lose data without permission. Forcing it converts a controlled outage into an uncontrolled data-loss event, and it usually happens at 3 a.m. under pressure because nobody had decided the policy in advance.

Root Cause Analysis

Zero-loss promotion requires two independent guarantees, and each is easy to have without the other.

The first is that some standby has the data. That is what synchronous replication provides: the commit does not return to the client until a synchronous standby has the WAL record. Setting synchronous_standby_names in postgresql.conf achieves this.

The second is that the failover controller promotes that particular standby. Nothing in postgresql.conf provides this. If Patroni is free to promote whichever node it likes, it may promote an asynchronous one that never received the transaction — and the fact that a synchronous standby existed and had the data is irrelevant, because that node is now a stale sibling of the new primary.

Patroni’s synchronous_mode is precisely the bridge between the two. When it is on, Patroni manages synchronous_standby_names itself, records the current synchronous node in the consensus store, and refuses to promote any node that is not in that set. The two guarantees become one.

The second root cause is the TTL inequality. Even with the right candidate chosen, promotion is only safe if the old primary has certainly stopped accepting writes. Patroni relies on a lease: the primary holds a key in etcd with a ttl, renews it every loop_wait, and demotes itself if it cannot. For this to fence reliably, the primary must have given up before anyone else takes over:

text
ttl  >  loop_wait + 2 × retry_timeout

If retry_timeout is large enough that a primary can spend longer than ttl failing to reach etcd while still believing itself primary, there is a window in which two nodes are writable. The inequality is not a tuning suggestion; it is the safety condition.

Lease expiry and the safety margin before promotion A time axis with two lanes. On the primary lane, lease renewals occur every ten seconds until a partition begins; the primary then fails to renew and demotes itself at the moment the thirty second lease expires. On the standby lane, the leader key is observed as stale and a promotion is attempted only after expiry. The gap between the primary's self-demotion and the standby taking the key is the safety margin, and it is positive only while ttl exceeds loop_wait plus twice retry_timeout. old primary standby renew every loop_wait (10 s) partition begins renewals fail — still believes it is primary ttl expires → self-demote leader key observed stale, waits for expiry takes leader key, promotes safety margin positive only while ttl > loop_wait + 2 × retry_timeout Shrink ttl to speed up failover and this margin shrinks with it; take it negative and both nodes are writable.

Step-by-Step Resolution

Step 1 — Verify the TTL inequality holds

bash
patronictl -c /etc/patroni.yml show-config | grep -E 'ttl|loop_wait|retry_timeout'

With the common defaults ttl: 30, loop_wait: 10, retry_timeout: 10, the inequality reads 30 > 10 + 20, which is equality, not inequality — the margin is zero. Raise ttl to 40 or lower retry_timeout to 7.

Inline verification: python3 -c "ttl,lw,rt=40,10,10; print('safe' if ttl > lw + 2*rt else 'UNSAFE')" prints safe.

Step 2 — Turn on synchronous mode and decide the strict setting

bash
patronictl -c /etc/patroni.yml edit-config -s synchronous_mode=true

Then make the strict decision deliberately. synchronous_mode_strict: false keeps writes flowing when the last synchronous standby dies, at the cost of a window where zero-loss promotion is impossible. true blocks writes instead. Write the choice, and the reason for it, into the runbook — because whichever one bites you, the incident review will ask who chose it.

Inline verification: patronictl list gains a Sync Standby column; exactly one node should show Sync Standby while the others show Replica.

Step 3 — Set the durability floor

bash
patronictl -c /etc/patroni.yml edit-config -s maximum_lag_on_failover=1048576

One megabyte of WAL is a common starting point. Express it as a policy statement rather than a number: “we will accept losing at most one megabyte of WAL — roughly a few thousand small transactions — to restore service automatically; beyond that a human decides.”

Inline verification: during a drill, pause replay on the intended candidate (SELECT pg_wal_replay_pause()), let it fall behind the threshold, kill the primary, and confirm Patroni refuses to promote that node and logs the reason.

Step 4 — Run a switchover, not a failover, whenever you can

bash
# Planned move: the old primary is shut down cleanly before anything is promoted.
patronictl -c /etc/patroni.yml switchover \
  --leader pg-node-1 --candidate pg-node-2 --scheduled now

switchover requires a healthy leader and refuses if the candidate is too far behind. That refusal is the feature. failover is the unplanned path and should be reserved for when the leader is genuinely gone.

Inline verification: patronictl list shows pg-node-2 as Leader and pg-node-1 as Replica with a running state and near-zero lag.

Step 5 — Prove the promotion was lossless

sql
-- On the NEW primary: where did its own WAL history start?
SELECT timeline_id, redo_lsn FROM pg_control_checkpoint();

-- The switchpoint recorded when it left recovery:
SELECT * FROM pg_read_file('pg_wal/' || lpad(to_hex(timeline_id), 8, '0') || '.history');

For a clean switchover the old primary’s final flush LSN equals the new primary’s start LSN. Any gap is exactly the data that was lost, measured in bytes.

Inline verification: the .history file’s switchpoint LSN matches the last value the old primary reported in pg_current_wal_flush_lsn() before shutdown.

Configuration Snippet

yaml
# /etc/patroni.yml — the settings that determine whether promotion loses data.
scope: pg-prod
namespace: /service/

bootstrap:
  dcs:
    # Safety condition: ttl > loop_wait + 2 × retry_timeout.
    # 40 > 10 + 14 leaves a 16-second margin.
    ttl: 40
    loop_wait: 10
    retry_timeout: 7

    # Durability floor: refuse to auto-promote a candidate further behind
    # than this many WAL bytes. This IS your RPO, expressed in bytes.
    maximum_lag_on_failover: 1048576

    # Bridge between "a standby has the data" and "that standby is promoted".
    synchronous_mode: true
    # false = availability over durability when the last sync standby dies.
    # true  = durability over availability (writes block). Choose deliberately.
    synchronous_mode_strict: false

    postgresql:
      use_pg_rewind: true          # rejoin diverged nodes instead of rebuilding
      parameters:
        wal_log_hints: on          # REQUIRED for pg_rewind — needs a restart to enable
        synchronous_commit: 'on'   # do not set to 'off': it defeats synchronous_mode

postgresql:
  callbacks:
    # Fires on role change; use it to repoint the routing tier deterministically
    # rather than waiting for a health check to notice.
    on_role_change: /usr/local/bin/patroni-repoint.sh

Two of these interact in a way that catches people. synchronous_commit: 'off' at the PostgreSQL level makes commits return before the local WAL flush, which silently voids the guarantee synchronous_mode is trying to provide — Patroni will happily manage a synchronous standby set whose acknowledgements no longer gate anything. And wal_log_hints cannot be turned on without a restart, so a deployment that discovers it is off during an incident is committed to rebuilding every diverged standby from a base backup.

What each synchronous_mode combination guarantees at promotion A two by two matrix. Rows are synchronous mode off or on. Columns are the strict flag off or on. With synchronous mode off the strict flag has no effect and promotion may lose whatever the chosen candidate had not received. With synchronous mode on and strict off, promotion is lossless while a synchronous standby exists and falls back to accepting writes unreplicated when none does. With synchronous mode on and strict on, promotion is always lossless because writes block rather than proceed unreplicated. strict = false strict = true synchronous_mode = false synchronous_mode = true no promotion guarantee strict has no effect here loss bounded only by maximum_lag no promotion guarantee a common misconfiguration: strict set, mode forgotten lossless while a sync node exists last sync node dies → writes continue availability over durability always lossless last sync node dies → writes block durability over availability The top-right cell is the dangerous one: it looks configured for durability and provides none.

Verification and Rollback

Confirming the configuration is live, not merely written to a file:

bash
# The running cluster's view — this is what actually governs behaviour.
patronictl -c /etc/patroni.yml show-config

# Per-node state, including which node is currently synchronous.
patronictl -c /etc/patroni.yml list
# Expect exactly one Leader, exactly one Sync Standby, the rest Replica.

# The PostgreSQL side that Patroni manages for you — do not edit by hand.
psql -h leader -c "SHOW synchronous_standby_names;"

Drill the guarantee rather than assuming it, on a deployment shaped like production:

bash
# 1. Write a marker transaction and record its LSN.
psql -h leader -c "CREATE TABLE IF NOT EXISTS failover_probe(t timestamptz);"
psql -h leader -c "INSERT INTO failover_probe VALUES (now());
                   SELECT pg_current_wal_flush_lsn();"

# 2. Hard-kill the leader (SIGKILL, not a clean shutdown — that is the point).
ssh pg-node-1 'sudo kill -9 $(pgrep -f "postgres: .*writer")'

# 3. After Patroni promotes, the marker must be present on the new leader.
psql -h $(patronictl list -f json | jq -r '.[]|select(.Role=="Leader").Host') \
     -c "SELECT count(*) FROM failover_probe;"

If the marker is missing, the promotion was not lossless and the configuration does not do what you believed. That is far better learned in a drill than in an incident.

Rollback. To revert to the previous behaviour, patronictl edit-config -s synchronous_mode=false. Do this only as a deliberate, time-boxed measure — for example, when a synchronous standby is unavailable for an extended maintenance and blocking writes is unacceptable. Set a reminder to re-enable it, because a deployment that quietly stays asynchronous is one whose next failover loses data.

Edge Cases and Gotchas

A cascading standby can never be the synchronous node

A standby that replicates from another standby rather than from the primary is invisible to synchronous_standby_names, because the primary has no replication connection to it. In a cascading topology the synchronous set can only be drawn from the first level. If your only same-region node is a cascade leaf, you have no zero-loss candidate no matter what is configured — the fix is topological, not a setting.

maximum_lag_on_failover is measured against the leader’s last known position

Patroni compares each candidate against the last position it observed from the leader, which is stored in the consensus store. If the leader died between updates, that reference is slightly stale and the comparison is optimistic by up to one loop_wait of WAL. On a very high write-rate cluster this is worth accounting for: set the threshold below your true tolerance by roughly one loop-wait’s worth of WAL.

Manual pg_promote() bypasses everything on this page

Running pg_promote() or pg_ctl promote directly on a node that Patroni manages promotes it without any of the checks described here — no lease verification, no candidate comparison, no synchronous constraint. Patroni will then discover a second leader and act to resolve it, usually by demoting and rewinding one of them. Never promote a Patroni-managed node by hand; use patronictl so the safety machinery runs.

FAQ

Does synchronous_mode alone guarantee zero data loss?

It guarantees that a synchronous standby has the transaction before the commit returns, which is the necessary half. The sufficient half is that the failover controller actually promotes that standby. Patroni enforces this by only allowing a node in the synchronous set to be promoted while synchronous_mode is on, so both halves hold together. Turning synchronous_mode off and relying on synchronous_standby_names alone breaks the second half.

What does synchronous_mode_strict actually change?

It decides what happens when no synchronous standby is available. With strict off, Patroni clears synchronous_standby_names so the primary keeps accepting writes unreplicated — availability is preserved and zero-loss promotion is not. With strict on, the primary blocks writes until a synchronous standby returns — durability is preserved and the service stalls. There is no correct default; it is a business decision that must be made explicitly.

Why did Patroni refuse to promote any node?

Almost always because every candidate exceeded maximum_lag_on_failover, or because synchronous_mode is on and no node in the synchronous set is reachable. Both are the configuration working as designed: it is refusing to trade durability for availability without being told to. patronictl list shows each node’s lag, and the Patroni log states the specific reason for each rejected candidate.

Is switchover safer than failover?

Substantially. A switchover shuts the old primary down cleanly first, which means it has flushed everything and cannot accept another write, so the fencing problem disappears and data loss is structurally impossible. Failover happens when the old primary is already gone and cannot be asked to cooperate. Whenever you have the option — maintenance, instance replacement, a node showing early failure signs — convert the failover into a switchover.

← Back to Replica Failover & Promotion Mechanics