PostgreSQL HA: Commit Durability and Consistent Reads During Failover

Hello PostgreSQL community,

I’m researching general PostgreSQL high-availability and transaction-durability behavior and would appreciate guidance on the following:

  1. Acknowledged commits: Under which PostgreSQL replication and failover configurations can a successfully acknowledged transaction still be lost?

  2. Uncertain COMMIT outcomes: If a database connection drops before the client receives a COMMIT acknowledgment, what is the recommended way to determine the transaction’s outcome safely?

  3. Consistency after failover: What approaches prevent stale reads from replicas or newly promoted primary nodes when authoritative transaction state is required?

  4. Database unavailability: What general application safeguards are recommended when security-sensitive operations must be denied because authoritative database state cannot be verified?

Are there particular PostgreSQL documentation references, Patroni configurations, or open-source testing tools you would recommend?

Thank you for your guidance.

@NevTech good questions. These come up a lot with Postgres HA setups, so here’s what usually matters.

Acknowledged commits usually get lost with async replication plus automatic failover. In Patroni, set synchronous_mode: true (with synchronous_commit = on) so only a sync standby can be promoted. synchronous_mode_strict goes further and blocks writes when no sync standby is available.

For an unknown COMMIT outcome, grab pg_current_xact_id() inside the transaction before committing, then after reconnecting run SELECT pg_xact_status('<xid>') (PG13+). An idempotency key with a unique constraint is the simpler fallback.

For stale reads, send authoritative reads to the primary, or use synchronous_commit = remote_apply, and check pg_is_in_recovery() on connect.

If state can’t be verified, fail closed and deny. Jepsen-style failover tests are worth running before you trust any of it.

Which PostgreSQL and Patroni versions are you on?

An acknowledged commit can be lost whenever the primary acknowledges before a standby has the WAL and failover then promotes that lagging standby.

To close the gap, use synchronous replication with synchronous_commit = on (or remote_apply), and in Patroni set synchronous_mode: true and synchronous_mode_strict: true so writes block instead of silently falling back to async.

For an in-doubt COMMIT, PostgreSQL’s pg_current_xact_id() / pg_xact_status() can help check a transaction’s status if the transaction ID was captured and its status is still retained. For application recovery, prefer a client-generated idempotency key stored with the business change in the same transaction, then check it on the current primary before retrying.

For reads that must be authoritative, go to the primary with target_session_attrs=read-write, or use remote_apply so synchronous standbys are current at acknowledgment.

For security-sensitive checks, fail closed: if no confirmed read-write primary is reachable, deny the request rather than fall back to a replica.

See Patroni’s replication modes and Percona’s HA guide.