PostgreSQL HA: Commit Durability and Consistent Reads During Failover

PostgreSQL
1

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.