PostgreSQL 스트리밍 복제 — Primary/Replica 이중화 구성

# 두 서버 모두 설치
sudo apt update && sudo apt install -y postgresql-17

-- psql 접속 후
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'StrongPass!23';

sudo nano /etc/postgresql/17/main/postgresql.conf

listen_addresses = '*'
wal_level = replica          # 복제 WAL 활성화
max_wal_senders = 5          # 동시 Replica 수 (여유 있게 설정)
wal_keep_size = 512          # WAL 보관 크기 (MB)
synchronous_commit = on      # 동기 복제 시 'remote_apply'로 변경

sudo nano /etc/postgresql/17/main/pg_hba.conf

# Replica 서버 IP 허용
host  replication  replicator  192.168.1.11/32  md5

sudo systemctl restart postgresql

sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/17/main/*

# Primary에서 베이스 백업 받기
sudo -u postgres pg_basebackup   -h 192.168.1.10   -U replicator   -D /var/lib/postgresql/17/main   -Fp -Xs -P -R
# -R: standby.signal + recovery 설정 자동 생성

hot_standby = on             # 읽기 쿼리 허용
primary_conninfo = 'host=192.168.1.10 port=5432 user=replicator password=StrongPass!23'

sudo systemctl start postgresql

SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, sync_state
FROM pg_stat_replication;

client_addr   | state     | sync_state
--------------+-----------+-----------
192.168.1.11  | streaming | async

SELECT * FROM pg_stat_wal_receiver;
-- status: streaming 이면 정상

# Primary postgresql.conf
synchronous_standby_names = 'replica1'
synchronous_commit = remote_apply

# Replica postgresql.conf
application_name = replica1

# Replica 서버에서
sudo -u postgres pg_ctl promote -D /var/lib/postgresql/17/main
# 또는
sudo -u postgres psql -c "SELECT pg_promote();"

-- Primary에서
SELECT pg_create_physical_replication_slot('replica1_slot');

# Replica postgresql.auto.conf
primary_slot_name = 'replica1_slot'

-- 복제 지연(lag) 확인
SELECT
  client_addr,
  pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes,
  write_lag, flush_lag, replay_lag
FROM pg_stat_replication;