Franck PachotDatabase Developer Advocate
All minibooks
Database field guide · 18
Transport, apply, failover, and recovery objectives
Turn log transport and replicas into explicit durability, availability, consistency, RPO, and RTO guarantees.
Build the mental model before choosing the mechanism.
This minibook was AI-generated from Franck Pachot's archived blog posts. Links to the original articles are included for source context and verification.01
A replica may protect durability, serve reads, enable disaster recovery, or feed change data capture. Each purpose requires a different contract for acknowledgement and apply.
02
Synchronous transport adds a remote acknowledgement to commit. The exact wait point may prove receipt in memory, durable log storage, or applied state.
03
Asynchronous replication decouples commits from network latency but permits acknowledged changes to be absent after source loss. Lag must be measured in the log coordinate system.
04
A read replica can be transactionally consistent at its replay position while stale relative to the primary. Read routing must define monotonicity and read-your-writes behavior.
05
Promotion alone does not restore service. Fencing the old primary, changing routes, reconnecting pools, replaying transactions, and rebuilding protection determine real recovery time.
06
A resilient design measures detection, decision, promotion, routing, client recovery, and backlog drain under realistic faults.
07
Concrete mechanics, diagnostic evidence, and executable patterns to carry into a real system.
Compare primary flush LSN with standby receive, replay, and replay timestamp. Timestamp delay can be NULL or misleading when a system is idle.
-- Primary: SELECT pg_current_wal_flush_lsn();
-- Standby:
SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(),
now()-pg_last_xact_replay_timestamp() AS replay_delay;synchronous_standby_names defines which standbys participate; pg_stat_replication shows state, sync role, and byte positions. Validate the policy during a standby outage.
SHOW synchronous_commit;
SHOW synchronous_standby_names;
SELECT application_name, state, sync_state, sent_lsn, flush_lsn, replay_lsn
FROM pg_stat_replication;V$ARCHIVE_DEST_STATUS and V$DATAGUARD_STATS separate destination health, transport lag, and apply lag. Broker status adds role-transition readiness.
SELECT dest_id, status, database_mode, recovery_mode, error
FROM v$archive_dest_status WHERE status <> 'INACTIVE';
SELECT name, value, unit FROM v$dataguard_stats;A client can lose its connection after commit reaches the server but before acknowledgement returns. Retrying blindly duplicates effects; a stable request key makes outcome discovery safe.
CREATE UNIQUE INDEX payment_request_uk ON payment(request_id);
INSERT INTO payment(request_id, account_id, amount) VALUES ($1,$2,$3)
ON CONFLICT (request_id) DO NOTHING
RETURNING payment_id;08
Optional deep dives with the complete experiments and product-version context behind this guide.