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.

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.

-- 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):

-- 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:

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.
• 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:

-- 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.

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
• PostgreSQL Docs: Hot standby and query conflicts
• PostgreSQL Docs: System administration functions (pg_promote, pg_wal_lsn_diff)
• Amazon RDS: Failing over a Multi-AZ DB instance