PostgreSQL Replication Lag: Streaming Replication Delay

# PostgreSQL Replication Lag: Streaming Replication Delay

Your standby server is falling behind, and monitoring alerts show growing replication lag:

WARNING:  replication lag exceeds threshold: 300000000 bytes
ERROR:  standby server is not catching up

Replication lag means your standby servers are not receiving changes fast enough to stay synchronized with the primary. This affects data consistency and failover readiness.

Understanding Replication Lag

PostgreSQL streaming replication uses WAL (Write-Ahead Log) records to replicate changes. Lag occurs when the standby cannot apply WAL records as fast as the primary generates them.

Check current replication status:

```sql -- On primary server SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes, pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS lag_size FROM pg_stat_replication;

-- Human-readable format SELECT client_addr, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS lag_size, state, sync_state FROM pg_stat_replication; ```

-- On standby server
SELECT pg_last_wal_receive_lsn() AS received_lsn,
       pg_last_wal_replay_lsn() AS replayed_lsn,
       pg_is_in_recovery() AS is_standby,
       pg_last_xact_replay_timestamp() AS last_replay_time;

Measuring Replication Lag Types

PostgreSQL has three types of lag, each with different implications:

-- Detailed lag analysis
SELECT client_addr, usename, application_name,
       sent_lsn,
       write_lsn,
       flush_lsn,
       replay_lsn,
       -- Network lag: sent but not written
       pg_wal_lsn_diff(sent_lsn, write_lsn) AS write_lag_bytes,
       -- IO lag: written but not flushed
       pg_wal_lsn_diff(write_lsn, flush_lsn) AS flush_lag_bytes,
       -- Replay lag: flushed but not applied
       pg_wal_lsn_diff(flush_lsn, replay_lsn) AS replay_lag_bytes,
       -- Total lag
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS total_lag_bytes,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS total_lag_size
FROM pg_stat_replication;

Lag Type Interpretation

Common Causes and Solutions

Cause 1: Network Bandwidth Issues

Symptoms: High write_lag, stable replay_lag

Diagnosis: ```bash # Test network bandwidth between servers iperf3 -c standby_host

# Check network interface errors cat /proc/net/dev | grep -E 'eth|ens|enp'

# Monitor network during peak load sar -n DEV 5 10 ```

Solutions: ```conf # postgresql.conf - Increase send buffer wal_sender_timeout = 60s max_wal_senders = 5

# At OS level # Increase TCP buffers sudo sysctl -w net.core.wmem_max=16777216 sudo sysctl -w net.core.rmem_max=16777216 ```

Cause 2: Standby Disk I/O Bottleneck

Symptoms: High flush_lag or replay_lag, high disk I/O on standby

Diagnosis: ```bash # Check disk I/O on standby iostat -x 5 10

# Check if standby is using synchronous commit psql -h standby -c "SHOW synchronous_commit;"

# Check recovery delay psql -h standby -c "SELECT pg_is_in_recovery();" ```

Solutions: ```conf # On standby postgresql.conf wal_receiver_status_interval = 1s # Report status frequently hot_standby_feedback = on # Prevent bloat on primary

# Reduce I/O during recovery wal_compression = on checkpoint_completion_target = 0.9 ```

Cause 3: Long-Running Transactions on Primary

Symptoms: Lag spikes during certain operations, high replay_lag

Diagnosis: ```sql -- Find long-running transactions on primary SELECT pid, usename, state, query_start, now() - query_start AS duration, query FROM pg_stat_activity WHERE state IN ('active', 'idle in transaction') AND now() - query_start > interval '5 minutes' ORDER BY duration DESC;

-- Check for locks blocking replication SELECT locktype, relation::regclass, mode, pid FROM pg_locks WHERE NOT granted; ```

Solutions: ```sql -- Terminate problematic long transactions SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction' AND now() - query_start > interval '30 minutes';

-- Configure statement timeout ALTER DATABASE mydb SET statement_timeout = '5min'; ```

Cause 4: Hot Standby Feedback Conflicts

Symptoms: Replication pauses, recovery conflicts in logs

Diagnosis: ```sql -- Check for recovery conflicts SELECT datname, confl_tablespace, confl_lock, confl_snapshot, confl_bufferpin, confl_deadlock FROM pg_stat_database_conflicts;

-- View detailed conflict information SELECT * FROM pg_stat_activity WHERE wait_event_type = 'Recovery'; ```

Solutions: ```conf # On standby postgresql.conf hot_standby_feedback = on max_standby_streaming_delay = 30s max_standby_archive_delay = 30s

# Kill queries that conflict wal_receiver_status_interval = 1s ```

Cause 5: Primary Generating WAL Too Fast

Symptoms: Consistently growing lag, primary has heavy write load

Diagnosis: ```sql -- Check WAL generation rate SELECT pg_walfile_name(pg_current_wal_lsn());

-- Monitor WAL files SELECT name, size FROM pg_ls_waldir() ORDER BY modification DESC;

-- Check current WAL position SELECT pg_current_wal_lsn(), pg_current_wal_insert_lsn(); ```

Solutions: ```conf # On primary postgresql.conf max_wal_senders = 10 # Increase sender slots wal_keep_size = 2GB # Keep more WAL files wal_sender_timeout = 60s

# Increase bandwidth for replication # Use synchronous replication carefully synchronous_commit = off # For async replication # Or use remote_apply for synchronous synchronous_commit = remote_apply ```

Cause 6: Standby Hardware Undersized

Symptoms: Standby always behind, regardless of load

Diagnosis: ```bash # Compare specs # On primary lscpu | grep -E 'Model name|CPU\(s\)' free -h cat /proc/mdstat

# On standby lscpu | grep -E 'Model name|CPU\(s\)' free -h cat /proc/mdstat ```

Solutions: - Upgrade standby hardware to match primary - Add more RAM for shared_buffers - Use faster storage (SSD vs HDD) - Increase max_parallel_maintenance_workers

Configuration Tuning

Primary Server Configuration

```conf # postgresql.conf on primary # Replication settings wal_level = replica max_wal_senders = 10 wal_keep_size = 2GB max_replication_slots = 10

# Performance synchronous_commit = on wal_compression = on wal_buffers = 64MB

# Timeout wal_sender_timeout = 60s ```

Standby Server Configuration

```conf # postgresql.conf on standby # Recovery settings hot_standby = on hot_standby_feedback = on max_standby_streaming_delay = 30s

# Receiver settings wal_receiver_status_interval = 1s wal_receiver_timeout = 60s

# Performance wal_compression = on ```

Monitoring Replication Lag

Create Monitoring View

```sql CREATE OR REPLACE VIEW replication_status AS SELECT client_addr, usename, application_name, state, sync_state, sent_lsn, replay_lsn, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS lag_size, EXTRACT(EPOCH FROM (now() - reply_time)) AS seconds_since_reply FROM pg_stat_replication;

-- Query it SELECT * FROM replication_status; ```

Set Up Alerts

```sql CREATE OR REPLACE FUNCTION check_replication_lag() RETURNS TABLE( client_addr inet, lag_bytes bigint, lag_size text, status text ) LANGUAGE plpgsql AS $$ BEGIN RETURN QUERY SELECT client_addr, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn), pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)), CASE WHEN pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) > 10737418240 THEN 'CRITICAL' -- 10GB WHEN pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) > 1073741824 THEN 'WARNING' -- 1GB ELSE 'OK' END FROM pg_stat_replication; END; $$;

-- Run check SELECT * FROM check_replication_lag(); ```

Monitor with External Tools

# Simple lag check script
#!/bin/bash
LAG=$(psql -U postgres -t -c "SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) FROM pg_stat_replication LIMIT 1;")
if [ "$LAG" -gt 1073741824 ]; then
    echo "WARNING: Replication lag exceeds 1GB: $LAG bytes"
    # Send alert
fi

Replication Slots and Lag

Replication slots ensure WAL files are kept until all standbys receive them. However, inactive slots can cause disk bloat:

```sql -- List replication slots SELECT slot_name, slot_type, active, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_size FROM pg_replication_slots;

-- Drop inactive slots causing bloat SELECT pg_drop_replication_slot('unused_slot_name'); ```

Warning: Never drop a slot for an active standby!

Reinitializing a Lagged Standby

If the standby is too far behind and WAL files are gone:

```bash # On primary - take new base backup pg_basebackup -h primary_host -U replicator -D /var/lib/postgresql/16/main -Fp -Xs -P -R

# Or use rsync from another standby rsync -avz --delete standby1:/var/lib/postgresql/16/main/ /var/lib/postgresql/16/main/

# On standby - stop and replace data sudo systemctl stop postgresql sudo rm -rf /var/lib/postgresql/16/main/* # Copy new backup sudo pg_basebackup -h primary_host -U replicator -D /var/lib/postgresql/16/main -Fp -Xs -P -R sudo systemctl start postgresql ```

Verification

After fixes, verify replication is healthy:

```sql -- On primary - check standbys SELECT client_addr, state, sync_state, pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS lag FROM pg_stat_replication;

-- On standby - check position SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_is_in_recovery();

-- Verify replication slots SELECT * FROM pg_replication_slots; ```

Quick Reference