Fix PostgreSQL Replication Lag

What's Actually Happening

PostgreSQL streaming replication has significant lag. Replica is far behind primary.

The Error You'll See

```sql -- Check lag: SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, pg_wal_lsn_diff(sent_lsn, replay_lsn) as lag_bytes FROM pg_stat_replication;

-- Output shows large lag: lag_bytes: 1073741824 -- 1GB lag! ```

Why This Happens

1. 1.Network slow - High latency between nodes
2. 2.Disk slow on replica - I/O bottleneck
3. 3.Long queries on replica - Blocking replay
4. 4.WAL generation fast - Primary writes faster than replica
5. 5.Hot standby feedback - Conflicts delaying replay

Step 1: Check Lag

```sql -- Detailed lag: SELECT now() - pg_last_xact_replay_timestamp() AS replication_delay;

-- Check replay position: SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(); ```

Step 2: Check Performance

```bash # On replica, check disk I/O: iostat -x 1

# Check if long queries blocking: SELECT * FROM pg_stat_activity WHERE state = 'active'; ```

Step 3: Optimize WAL

-- On primary:
ALTER SYSTEM SET wal_keep_size = '2GB';
ALTER SYSTEM SET max_wal_senders = 10;
SELECT pg_reload_conf();

Step 4: Tune Replica

-- On replica:
ALTER SYSTEM SET hot_standby_feedback = on;
ALTER SYSTEM SET max_standby_streaming_delay = '30s';
SELECT pg_reload_conf();

PostgreSQL Replication Checklist

Verify the Fix

SELECT pg_wal_lsn_diff(sent_lsn, replay_lsn) FROM pg_stat_replication;
-- Output: Small value (< 1MB)

Related Issues

• [Fix PostgreSQL Connection Pool Exhausted](/articles/fix-postgresql-connection-pool-exhausted)
• [Fix PostgreSQL Query Timeout](/articles/fix-database-query-timeout)