PostgreSQL 복제 · WAL · PITR — 프로덕션 HA 구성

[Primary]  ──── WAL Stream ────►  [Standby (Hot Standby)]
    │
    └── WAL Archive ──► [S3 / NFS]  ◄── PITR 복구 시 사용

# /etc/postgresql/16/main/postgresql.conf

# 복제 설정
wal_level = replica              # replica 또는 logical
max_wal_senders = 5              # 최대 Standby 연결 수
wal_keep_size = 1GB              # WAL 보관 크기
hot_standby = on

# WAL 아카이빙 (PITR용)
archive_mode = on
archive_command = 'aws s3 cp %p s3://my-wal-bucket/archive/%f'
archive_timeout = 60             # 최대 60초마다 강제 아카이빙

# 성능 튜닝
wal_compression = zstd
checkpoint_completion_target = 0.9
max_wal_size = 4GB
min_wal_size = 1GB

# 모니터링
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

# /etc/postgresql/16/main/pg_hba.conf
# TYPE  DATABASE    USER        ADDRESS         METHOD
host    replication replicator  10.0.1.0/24     scram-sha-256

# 복제 전용 사용자 생성
sudo -u postgres psql -c "
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'strong-password';
"

sudo systemctl reload postgresql

# Standby에서: Primary의 베이스 백업으로 데이터 디렉터리 초기화
sudo -u postgres pg_basebackup \
  -h 10.0.1.10 \            # Primary IP
  -U replicator \
  -D /var/lib/postgresql/16/main \
  -Fp -Xs -P -R              # -R: recovery 설정 자동 생성

# -Fp: plain format
# -Xs: WAL streaming 포함
# -P: 진행률 표시
# -R: standby.signal + postgresql.auto.conf 자동 생성

# postgresql.auto.conf (pg_basebackup -R 이 자동 생성)
primary_conninfo = 'host=10.0.1.10 port=5432 user=replicator password=strong-password application_name=standby1'

# Standby 시작
sudo systemctl start postgresql

# 복제 상태 확인 (Primary에서)
sudo -u postgres psql -c "SELECT * FROM pg_stat_replication;"

-- Primary에서: 각 Standby의 WAL 지연 확인
SELECT
  application_name,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replication_lag,
  write_lag,
  flush_lag,
  replay_lag
FROM pg_stat_replication;

-- Standby에서: 수신 중인 WAL 상태
SELECT
  status,
  receive_start_lsn,
  received_tli,
  pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn())) AS replay_lag
FROM pg_stat_wal_receiver;

# 방법 1: pg_ctl promote (즉시 승격)
sudo -u postgres pg_ctl promote -D /var/lib/postgresql/16/main

# 방법 2: 트리거 파일 (권장 — 스크립트 자동화 용이)
sudo -u postgres touch /tmp/failover.trigger
# postgresql.conf에 설정: promote_trigger_file = '/tmp/failover.trigger'

# 승격 확인
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
# 결과: f (false) → Primary로 전환됨

# 구 Primary(장애 복구 후)에서
sudo systemctl stop postgresql

sudo -u postgres pg_rewind \
  --target-pgdata=/var/lib/postgresql/16/main \
  --source-server="host=10.0.1.20 user=replicator dbname=postgres"

# standby.signal 생성
sudo -u postgres touch /var/lib/postgresql/16/main/standby.signal

# primary_conninfo를 새 Primary(10.0.1.20)로 업데이트
sudo -u postgres psql -c "
ALTER SYSTEM SET primary_conninfo = 'host=10.0.1.20 port=5432 user=replicator password=strong-password';
"

sudo systemctl start postgresql

# 복구 서버에서: 최신 베이스 백업 다운로드
aws s3 cp s3://my-wal-bucket/basebackup/latest.tar.gz /tmp/
sudo -u postgres tar -xzf /tmp/latest.tar.gz -C /var/lib/postgresql/16/main

# recovery.conf 역할: postgresql.conf에 작성 (PG12 이후)

# postgresql.conf (복구 서버)

restore_command = 'aws s3 cp s3://my-wal-bucket/archive/%f %p'

# 목표 시점 지정 (실수로 DROP TABLE한 직전 시각)
recovery_target_time = '2026-05-19 14:30:00 KST'
recovery_target_action = 'promote'   # 복구 완료 후 자동 승격

# standby.signal 생성 (복구 모드로 시작)
sudo -u postgres touch /var/lib/postgresql/16/main/standby.signal

sudo systemctl start postgresql

# 복구 진행 로그 실시간 확인
sudo journalctl -u postgresql -f | grep -E "recovery|redo|consistent"

# 복구 완료 확인
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"

#!/bin/bash
# /usr/local/bin/pg-basebackup.sh

S3_BUCKET="s3://my-wal-bucket/basebackup"
DATE=$(date +%Y%m%d-%H%M)
BACKUP_DIR="/tmp/pg-backup-$DATE"

# 베이스 백업 생성
sudo -u postgres pg_basebackup \
  -D "$BACKUP_DIR" \
  -Ft -z \        # tar + gzip
  -Xs \           # WAL streaming 포함
  -P

# S3 업로드
aws s3 cp "$BACKUP_DIR/base.tar.gz" "$S3_BUCKET/$DATE/base.tar.gz"
aws s3 cp "$BACKUP_DIR/pg_wal.tar.gz" "$S3_BUCKET/$DATE/pg_wal.tar.gz"

# 로컬 정리
rm -rf "$BACKUP_DIR"

# 30일 이상 된 S3 백업 삭제
aws s3 ls "$S3_BUCKET/" | awk '{print $4}' | while read dir; do
  dir_date=$(echo "$dir" | cut -d'-' -f1)
  if [ "$(date -d "$dir_date" +%s 2>/dev/null)" -lt "$(date -d '30 days ago' +%s)" ]; then
    aws s3 rm "$S3_BUCKET/$dir" --recursive
  fi
done

echo "Backup completed: $DATE"

# 매일 새벽 1시 실행
echo "0 1 * * * root /usr/local/bin/pg-basebackup.sh >> /var/log/pg-backup.log 2>&1" > /etc/cron.d/pg-backup