# Postgres Replication Lag: Monitoring and Failover Practice URL: https://www.devopsness.com/blog/postgres-replication-lag-failover-practice Author: Kiril Urbonas Published: 2026-05-05 Updated: 2026-10-31 Categories: Infrastructure Tags: Postgres, Replication, Database, HA, Infrastructure Summary: Replication is the foundation of database HA. What we monitor, how we practice failover, and the gotchas that show up only when you actually fail over. --- # Postgres Replication Lag: Monitoring and Failover Practice Measure replication lag on the primary with `pg_stat_replication.replay_lag` (time) and `pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)` (bytes), and confirm it end to end with a heartbeat row you write on the primary and read on the replica. Page at 30 seconds sustained. Then drill failover quarterly, because the app's reconnect path is what actually fails. > [!VERIFIED] > PostgreSQL 18.6, against the PostgreSQL docs for cumulative statistics (pg_stat_replication), hot standby, replication settings and system administration functions, plus the Amazon RDS Multi-AZ failover guide. Checked October 2026. Replication "just works" until it doesn't. Most of the time it's fine, lag occasionally drifts, and failovers are rarely as clean as the docs suggest. This is what we monitor, what we drill, and what each drill tends to uncover. ## How replication works in practical terms Streaming replication ships the write-ahead log (WAL) from the primary to standbys, which replay it. Two modes: - **Asynchronous** (the default: `synchronous_standby_names` is empty, so commits don't wait for replicas). Standbys can fall behind, and a worst-case failover loses the last few seconds of commits. - **Synchronous.** The primary waits for a standby to confirm before a commit returns. Stronger guarantees, higher commit latency, and a slow standby slows every write. Most teams, us included, run async for read replicas and accept the bounded loss. RDS Multi-AZ instance deployments are a different thing: AWS keeps a synchronous standby in another AZ that you cannot read from, plus whatever async read replicas you add. ## The three lag measurements **Time, on the primary.** `replay_lag` is the time between the primary flushing WAL and the standby reporting it written, flushed and applied. For async standbys the docs describe it as approximately how long before recent transactions become visible on the replica. **Bytes, on the primary.** WAL distance is the better signal when the workload is bursty, because "two seconds behind" can mean 10 MB or 10 GB. ```sql -- run on the primary SELECT application_name, state, sync_state, replay_lag, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_bytes FROM pg_stat_replication; ``` ``` application_name | state | sync_state | replay_lag | replay_bytes ------------------+-----------+------------+-----------------+-------------- replica-1 | streaming | async | 00:00:00.004213 | 16384 ``` One gotcha the docs call out: when the standby is fully caught up and there's no WAL activity, the lag columns show the last measurement briefly and then go NULL. Decide whether your alerting treats NULL as zero or as missing data, or a quiet weekend will look like an outage. **End to end, through the app path.** A heartbeat catches replication delay and anything between app and database (poolers, caches, wrong endpoints): ```sql -- once, on the primary CREATE TABLE IF NOT EXISTS replication_heartbeat (id int PRIMARY KEY, ts timestamptz); -- every 10 seconds, on the primary INSERT INTO replication_heartbeat VALUES (1, now()) ON CONFLICT (id) DO UPDATE SET ts = excluded.ts; -- on the replica SELECT now() - ts AS heartbeat_lag FROM replication_heartbeat WHERE id = 1; ``` Prefer this over `now() - pg_last_xact_replay_timestamp()` on the replica, which keeps growing when the primary is simply idle, since it reports the time of the last replayed transaction. ## Alert thresholds We scrape with `postgres_exporter` into Prometheus. The rules, tuned over a couple of rounds from defaults that fired on every routine blip: ```yaml # prometheus/rules/pg-replication.yml groups: - name: pg-replication rules: - alert: PgReplicationLagWarning expr: pg_heartbeat_lag_seconds > 5 for: 1m labels: {severity: warning} - alert: PgReplicationLagCritical expr: pg_heartbeat_lag_seconds > 30 for: 1m labels: {severity: page} - alert: PgReplicationBytesGrowing expr: deriv(pg_replication_replay_bytes[15m]) > 0 and pg_replication_replay_bytes > 1e9 for: 15m labels: {severity: page} ``` `pg_heartbeat_lag_seconds` and `pg_replication_replay_bytes` come from custom queries (the heartbeat and the SQL above) added to the exporter, so name them however your setup does. ## What causes lag spikes - **A long query on the replica conflicting with replay.** WAL replay waits up to `max_standby_streaming_delay` (default 30s) and then cancels the query with `ERROR: canceling statement due to conflict with recovery`. Lag climbs steadily while it waits. Check `pg_stat_database_conflicts` on the standby. - **Replica disk I/O saturated** by a runaway query or a backup. Replay is I/O-bound. - **A write burst on the primary**, such as a batch job at 10x normal volume. Usually clears when the burst ends. - **Network trouble** between regions for cross-region replicas. - **`hot_standby_feedback = on`** avoids query cancellations by stopping vacuum on the primary from removing rows the replica still needs, and the docs warn it can bloat the primary. Turn it on for reporting replicas knowingly, not by default. What doesn't worry us: brief spikes during checkpoints, a steady sub-second floor, lag during a primary backup, or one replica lagging while others don't (pull it from read rotation and investigate). ## Failover practice We drill every quarter, during business hours but off-peak, pre-announced in the on-call channel. On RDS Multi-AZ the trigger is a reboot with failover: ``` $ aws rds reboot-db-instance --db-instance-identifier app-prod --force-failover $ aws rds describe-events --source-identifier app-prod --source-type db-instance --duration 30 ``` AWS documents failover as typically 60 to 120 seconds, longer with large transactions or lengthy recovery. Time three things: detection, app reconnection, and total customer-visible disruption. Then debrief. What drills typically find, and what they found for us: - **Slow reconnects.** Reconnects can take far longer than the failover itself when the pool's stale-connection detection is passive. Recycling connections aggressively on connection errors shortens that gap considerably. - **A hard-coded IP** for the primary in an old config, instead of the DNS endpoint. Failover changes the DNS record, so anything pinned to the IP keeps talking to the dead host. - **JVM DNS caching.** AWS recommends a JVM `networkaddress.cache.ttl` of no more than 60 seconds; some configurations cache forever. - **PgBouncer holding dead server connections.** Run `RECONNECT` on its admin console after failover, as covered in [PgBouncer in front of RDS](/blog/postgres-connection-pooling-pgbouncer). - **Writes sent to a read-replica endpoint** by a pod with a cached endpoint. Enforce read-only at the adapter so this fails loudly. ## Self-managed failover Without a managed service, promotion is explicit, and the old primary does not rejoin on its own: ```sql -- on the standby being promoted; waits up to 60s by default SELECT pg_promote(); SELECT pg_is_in_recovery(); -- false once promotion completes ``` The old primary must be rebuilt or rewound (`pg_rewind`) before it can follow the new one, and remaining replicas need to be repointed. Tools like Patroni automate this; whichever you use, drill it. ## Where this breaks These thresholds assume physical streaming replication. Logical replication's lag columns stay NULL unless the output plugin sends tracking messages, so rely on the heartbeat there. A replica intentionally delayed with `recovery_min_apply_delay` will alert constantly unless you exclude it. And if the replica is smaller than the primary to save money, a failover can succeed technically and then fall over under write load; we size them identically. For deciding which reads can tolerate lag at all, see [routing reads to Postgres replicas](/blog/postgres-read-replicas-routing-reads). ## Questions people ask ### How do I check replication lag in PostgreSQL? On the primary, query `pg_stat_replication`: `replay_lag` gives time and `pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)` gives bytes behind, per standby. On the replica, a heartbeat row written by the primary every few seconds gives end-to-end lag. Note that the lag columns go NULL when the standby is caught up and the primary is idle. ### Why is replay_lag NULL in pg_stat_replication? Because the standby is fully caught up and there's no new WAL. PostgreSQL's docs say the last measured lag shows briefly and then becomes NULL when there is no more WAL activity. It's normal on idle systems. For logical replication, lag columns are NULL unless the output plugin emits tracking messages. Treat NULL deliberately in alert rules. ### How long does an RDS Postgres Multi-AZ failover take? AWS documents failover times as typically 60 to 120 seconds, and longer with large transactions or a lengthy recovery. The endpoint's DNS record moves to the standby, so apps must reconnect and must not cache the old IP; AWS recommends a JVM DNS TTL of 60 seconds or less. Drill it with `aws rds reboot-db-instance --force-failover`. ## The call we'd make Watch three lag measurements, not one: time, bytes, and a heartbeat through the app path. Page at 30 seconds sustained, warn at 5. Drill failover quarterly even on a managed service, especially then, because the database side is handled and the app side is yours. Size replicas like the primary, and write down every drill's findings so the next one goes faster. ## Sources - [PostgreSQL Docs: pg_stat_replication and cumulative statistics](https://www.postgresql.org/docs/current/monitoring-stats.html) - [PostgreSQL Docs: Hot standby and query conflicts](https://www.postgresql.org/docs/current/hot-standby.html) - [PostgreSQL Docs: System administration functions (pg_promote, pg_wal_lsn_diff)](https://www.postgresql.org/docs/current/functions-admin.html) - [Amazon RDS: Failing over a Multi-AZ DB instance](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.MultiAZ.Failover.html)