DBAtlas · Database operations atlas

Replication lag / stopped

The standby/replica is falling behind the primary, or replication has stopped entirely. This tree narrows down the type of lag step by step (growing, constant, or stopped) to reach the root cause, such as an apply error, a single-thread bottleneck, a recovery conflict, or corruption.

How is the lag behaving?

Measure the situation first. Look at the replica stream and the lag metric: is the lag growing over time, stuck at a constant level, or has apply stopped completely? This splits the investigation into three main branches.

SELECT client_addr, state, write_lag, flush_lag, replay_lag,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS bytes_behind
FROM pg_stat_replication;
  • Apply stopped completely / there is an error
  • Lag keeps growing (cannot catch up)
  • Lag is constant but a long query is suspected
  • Far behind / appears inconsistent

What does the apply error say?

If replication has stopped, there is usually an error in an event being applied. Look at the error message: is it a row/table missing on the target, a primary key conflict, or a connection/network drop?

SHOW REPLICA STATUS\G  -- Last_Error, Last_SQL_Error, Replica_IO_Running, Replica_SQL_Running
  • Conflicting/missing row or table error
  • Network/connection dropped, IO thread stopped

Why can it not catch up?

If the lag keeps growing, the replica cannot keep up with the primary's write rate. Is single-thread apply the bottleneck, or is the replica's disk/IO slow? Look at the apply worker state and IO waits.

SHOW REPLICA STATUS\G  -- Seconds_Behind_Source and apply thread state
SELECT * FROM performance_schema.replication_applier_status_by_worker;
  • Single-thread apply + heavy write load
  • The replica's disk/IO is slow

Is there a long query on the standby?

A long query running on a read-only standby can block apply (recovery). In PostgreSQL, this is how a recovery conflict arises. Look at active queries and conflict counters on the standby.

SELECT now()-query_start AS dur, pid, state, query
FROM pg_stat_activity WHERE state='active' ORDER BY query_start;
SELECT confl_snapshot, confl_lock, confl_bufferpin FROM pg_stat_database_conflicts WHERE datname=current_database();
  • Yes, a long query is blocking apply
  • No, no query, but it is still behind

Root cause: Apply error (data conflict)

The replica is stuck on an event it cannot apply: a row missing on the target, a primary key conflict, or a missing table. Fix the cause of the inconsistency instead of blindly skipping.

Root cause: Connection / network drop

The receiver (IO/receiver) thread cannot connect to the primary. The network, authentication, or accumulated/purged log segments may be the problem.

Root cause: Single-thread apply bottleneck

While the primary writes in parallel, the replica applies changes in a single sequence and cannot keep up. Parallelize apply.

Root cause: Replica IO bottleneck

The apply thread is waiting on disk, not CPU. The replica's storage cannot keep up with the primary's write rate.

Root cause: Standby recovery conflict

A long read-only query on the standby is blocking the application of changes from the primary (vacuum/lock conflict). PostgreSQL resolves this by delaying apply or canceling the query.

Root cause: Replica too far behind / corrupt - rebuild it

The replica is too far behind to recover, the required logs have been removed, or the data is inconsistent. The safest path is to rebuild it from a fresh backup.

Open in DBAtlas →