Database replication

The booking example raises two different questions. Can the second database show the latest booking to a customer? Would it retain that booking after the main database fails? A change can already be stored on the second server without being available to its queries, so those questions need different checks.

The detailed mechanism below is PostgreSQL physical streaming replication. Other databases can send different kinds of change records; the brief MySQL comparison explains why the PostgreSQL mechanism should not be treated as a universal database design.

How changes reach the second database#

The server accepting writes is the primary. A second server prepared to follow it is a standby. A PostgreSQL physical standby starts from a base backup, then applies records from the primary’s write-ahead log, or WAL. Applying those records is called replay.

Streaming replication sends WAL records as they are generated rather than waiting for a complete log file. The standby follows that stream instead of repeatedly taking a fresh copy of every table. It can receive records faster than it replays them.

PostgreSQL can also decode WAL for a logical change feed. That does not mean every database uses one log for crash recovery, replication and change feeds. MySQL replication uses binary-log events, while InnoDB has a separate redo log for crash recovery. Keep the database and replication method visible when reasoning about what a change record represents.

What the standby has received, stored and applied#

PostgreSQL streaming replication is asynchronous by default. The primary can confirm a commit before the standby has caught up. If the primary fails and the booking’s WAL is not available to the standby that takes over, the booking can be absent after the switch. Promoting a standby means making it the new primary.

To understand that risk, first separate the stages a change passes through. On the primary, pg_stat_replication reports the standby’s written, flushed and replayed WAL positions. A WAL position identifies progress through the log; it is not a duration.

The lag columns describe recent delays in reaching each stage. They are not predictions of how long catching up will take. In particular, a delay before query results show a change does not establish that its WAL is missing from the standby.

A dashboard showing four seconds of replay_lag therefore does not automatically mean that four seconds of transactions would disappear after failover. Identify the actual promotion target, compare its WAL progress with the primary and decide which storage stage protects against the failures you need to survive. Losing only the primary is different from also losing data that remains in the standby’s operating-system buffers.

Choose what a commit must wait for

Synchronous replication lets the primary wait for a standby acknowledgement before confirming a write transaction. Two settings work together: synchronous_standby_names selects the required standbys, and synchronous_commit selects the required stage. Setting synchronous_commit=on without configuring synchronous standbys does not create a remote durability guarantee.

With synchronous standbys configured, remote_write waits for their filesystem write. That protects against the standby’s PostgreSQL process crashing, but the data need not survive a crash of its operating system. on waits for a durable flush. remote_apply also waits for replay, so the transaction has become visible to standby queries. Waiting for more work can increase commit latency.

The local settings do not wait for a standby: local waits for the primary’s durable flush, while off does not even wait for that local flush. These choices concern when success is reported, not whether a standby exists.

The standby-selection rule matters too. In this illustrative configuration, FIRST 2 (s1, s2, s3) selects two standbys by priority; a later candidate can replace an unavailable earlier one. ANY 2 (s1, s2, s3) waits for acknowledgements from any two of the three. Neither asks for all three.

If too few required standbys are available, transactions waiting for synchronous acknowledgement can remain blocked. The primary does not silently change those transactions to asynchronous replication. Additional eligible candidates can help maintain the required number. Reducing that number or disabling the requirement changes the protection provided; it is an operational decision to make explicitly, not an invisible performance fix.

What replication does not protect#

A physical replica applies harmful changes as well as useful ones. If a migration deletes the wrong data, the standby can apply that deletion too. Once both copies contain the mistake, switching servers does not restore the earlier records. Keep a suitable backup and recovery history for that purpose.

Some replicated data types take a different approach to concurrent updates: a CRDT defines a merge operation that lets replicas converge after they exchange state. That is a property of the data type, not a replacement for the backup and lag decisions here.

Nor does a durable standby acknowledgement mean that every query already sees the change. With synchronous replication configured, on waits for the standby’s flush, not its replay. The distinction matters when an application writes to the primary and immediately reads from a standby.

A healthy replication connection does not guarantee prompt replay either. WAL can arrive faster than it is applied, and a standby query can conflict with a change ready for replay. PostgreSQL can wait for the conflicting query until the allowed replay delay is consumed, then cancel it so recovery can continue.

max_standby_streaming_delay controls that allowed delay; it is not a fresh, full runtime allowance for every query. hot_standby_feedback can prevent cleanup conflicts by asking the primary to retain row versions the standby still needs. It does not remove every kind of recovery conflict and can cause table bloat on the primary by delaying cleanup.

Questions people also ask

4 QUESTIONS

▸ How much data can I lose with asynchronous replication?

A generic replication-lag number cannot answer that. Identify the standby that will take over and which committed changes are safely available to it. PostgreSQL reports written, durably flushed and replayed WAL separately. A delay in applying a change does not mean the change is absent from the standby.

▸ What does synchronous replication actually wait for?

With synchronous standbys configured in PostgreSQL, synchronous_commit chooses the acknowledgement required. remote_write waits for a standby filesystem write, on waits for a durable flush, and remote_apply also waits for replay. Without synchronous_standby_names configured, setting synchronous_commit to on does not by itself make a transaction wait for a standby.

▸ Why is my replica behind even though nothing looks broken?

A standby can receive changes faster than it applies them. Replay can also wait for a conflicting query or an intentional apply delay. Its queries may therefore show older data even while the incoming change stream is healthy. Monitor receipt, flush and replay separately.

▸ Does a replica protect me from a bad deployment?

Not by itself. A physical replica also applies a mistaken deletion or migration. Once it has applied the mistake, switching to it does not recover the earlier data. Recovery needs a suitable backup and recovery history, not just another copy of the current state.