Search
4 results for “high-availability”
Search results
Database Replication
Why asynchronous replication can silently lose the most recent commits if the primary crashes, what synchronous_commit's three levels actually each guarantee, and how to measure replication lag instead of assuming it's small.
A PostgreSQL primary crashes right after committing a transaction, under asynchronous replication. Is that transaction guaranteed to exist on the standby?
No. Asynchronous replication (the default) confirms a commit on the primary without waiting for the standby to receive or apply the corresponding WAL records, there's typically a small delay, often under a second, between a commit and its visibility on the standby. If the primary crashes in that window, before the WAL records reached the standby, that transaction is lost even though the client was already told it committed successfully. This is the specific, documented risk asynchronous replication accepts in exchange for not adding network round-trip latency to every commit.
What is the actual difference between synchronous_commit = on, remote_write, and remote_apply?
`on` (the standard synchronous setting) waits for the standby to write the commit record to its own disk, "2-safe" durability, data is lost only if both primary and standby crash simultaneously. `remote_write` only waits for the standby to receive the record and hand it to its operating system, not for a disk flush, weaker durability (a standby OS crash before its own flush could still lose it) in exchange for a faster commit. `remote_apply` is the strongest of the three, it waits until the standby has actually replayed the transaction and made it visible to queries, which is what allows read queries against the standby to see a transaction immediately after the primary reports it committed.
How do you actually measure replication lag, rather than assuming it's negligible?
Replication lag is the gap between WAL records generated on the primary and WAL records actually applied on the standby, and it's measured concretely by comparing the primary's current WAL write position, from `pg_current_wal_lsn()`, against the standby's last replayed position, from `pg_last_wal_replay_lsn()`, via `pg_wal_lsn_diff()`. A large or growing byte gap is a real health signal, it points at the primary generating WAL faster than the network or standby can keep up, or the standby itself being under heavy load, not something to infer from "it's usually under a second" folklore. Monitoring this value directly, not assuming a small delay, is what actually catches a standby falling dangerously behind before a failover makes that lag visible as lost data.