15.5 복제 지연·끊김

SELECT application_name, client_addr, state,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn))    AS sent_lag,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn))   AS write_lag,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn))   AS flush_lag,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn))  AS replay_lag,
       write_lag, flush_lag, replay_lag,
       sync_state
  FROM pg_stat_replication;

# 네트워크
ping standby.example.com
# 또는 standby 호스트 자체 응답 안 함

# standby 호스트에서
iostat -xz 1 5
# %util, await 점검

-- standby 측에서 진단
SELECT pid, query, wait_event_type, wait_event,
       backend_xmin, now() - xact_start AS age
  FROM pg_stat_activity
 WHERE state = 'active';

-- primary
SELECT count(*) FROM pg_stat_replication WHERE state = 'streaming';
-- 평소보다 적으면 누군가 끊김

-- 어떤 slot이 inactive
SELECT slot_name, active, restart_lsn
  FROM pg_replication_slots
 WHERE NOT active;

# 로그 확인
tail -100 /var/lib/pgsql/17/data/log/postgresql-*.log

FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment ... has already been removed

sudo systemctl stop postgresql-17
sudo rm -rf /var/lib/pgsql/17/data/*
sudo -u postgres pg_basebackup -h primary -U repl_user \
  -D /var/lib/pgsql/17/data -X stream -P -R --slot=standby1
sudo systemctl start postgresql-17

-- subscriber 측
SELECT subname, received_lsn, latest_end_lsn, latest_end_time
  FROM pg_stat_subscription;

SELECT * FROM pg_stat_subscription_stats;
-- apply_error_count, sync_error_count 증가하면 충돌

-- subscriber에서 충돌 row 정리 후
ALTER SUBSCRIPTION sub_orders ENABLE;

-- primary 트랜잭션이 영원히 commit 안 됨

-- 임시 — fallback
ALTER SYSTEM SET synchronous_standby_names = '';
SELECT pg_reload_conf();

-- primary에서
SELECT * FROM pg_stat_replication;
-- backend_xmin 컬럼 — 가장 작은 xmin이 vacuum horizon

SELECT slot_name, xmin, catalog_xmin, restart_lsn,
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag
  FROM pg_replication_slots
 WHERE slot_type = 'logical';