Replication lag / stopped

Symptom

The standby/replica is falling behind or has stopped completely; read replicas are showing stale data.

1. How large is the lag, and is it growing or steady

What is the lag in seconds/bytes? Is it a steady offset or continuously growing? If it grows, the replica cannot keep up with apply; if it is steady, there was a momentary stall.

Oracle

SELECT name, value, unit FROM v$dataguard_stats WHERE name LIKE '%lag%';

PostgreSQL

SELECT client_addr, state, write_lag, flush_lag, replay_lag FROM pg_stat_replication;

MySQL

SHOW REPLICA STATUS\G  -- Seconds_Behind_Source

SQL Server

SELECT ar.replica_server_name, drs.redo_queue_size, drs.log_send_queue_size FROM sys.dm_hadr_database_replica_states drs JOIN sys.availability_replicas ar ON drs.replica_id=ar.replica_id;

Redis

INFO replication  # master_repl_offset vs slave offset

2. Is replication running, are there errors

What is the IO/SQL thread state, the apply process, is the connection broken? If there is an error (e.g. conflicting row, missing table), apply has stopped; read the error message.

MySQL

SHOW REPLICA STATUS\G  -- Replica_IO_Running / Replica_SQL_Running / Last_Error

PostgreSQL

SELECT status, last_msg_receipt_time, latest_end_lsn FROM pg_stat_wal_receiver;  -- on the standby

Oracle

SELECT process, status, sequence#, thread# FROM v$managed_standby WHERE process LIKE 'MRP%' OR process LIKE 'RFS%';

3. Find the bottleneck

Why can it not keep up? Single-threaded apply plus a heavy write load (PG/MySQL mostly apply serially), an I/O bottleneck on the standby, the network, or a long query blocking apply (PG hot_standby_feedback / recovery conflict)?

4. Close the lag

Speed up apply: enable parallel apply (MySQL replica_parallel_workers — slave_parallel_workers before 8.0.26; PG has none, but there are recovery settings), improve standby I/O, resolve the query blocking apply. If it stopped with an error: fix the error (resolve the conflicting row manually) and resume replication.

5. Rebuild if it is broken

If the replica is far behind/broken, resynchronizing is cleanest: rebuild the standby from a fresh backup (PG pg_basebackup, MySQL clone/xtrabackup, Oracle RMAN duplicate). Critical: find the cause before rebuilding, or it will happen again.