pg DB 이중화 구성 (streaming replication) - primary crash

Primary DB 장애발생

- Stand by > Primary DB로 전환 fail over 해야함

 

 

Standby DB Promote 

1. (standby) 기존 standby 장비를 promote 승격하여 master로 승격

- server promoted 되면 정상

/usr/lib/postgresql/15/bin/pg_ctl -D /var/lib/postgresql/15/main/ "-o -c config_file=/etc/postgresql/15/main/postgresql.conf" promote



postgres@lkmpg:~$ /usr/lib/postgresql/15/bin/pg_ctl -D /var/lib/postgresql/15/main/ "-o -c config_file=/etc/postgresql/15/main/postgresql.conf" promote
waiting for server to promote.... done
server promoted

 

2. (현 primary) 복제 슬롯 생성 및 확인

select * from pg_create_physical_replication_slot('repl_slot_02');

select slot_name, slot_type, active from pg_replication_slots;
  slot_name   | slot_type | active
--------------+-----------+--------
 repl_slot_02 | physical  | f
(1 row)

 

3. (구 primary) 데이터 디렉토리 backup 후 pg_basebackup 수행

pg_basebackup -h 192.168.100.137 -D /var/lib/postgresql/15/main -U repl -P -v -X stream -S repl_slot_02 -R

postgres@lkmpg:~/15$ mv main main_backup
postgres@lkmpg:~/15$ pg_basebackup -h 192.168.100.137 -D /var/lib/postgresql/15/main -U repl -P -v -X stream -S repl_slot_02 -R
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/5F000028 on timeline 11
pg_basebackup: starting background WAL receiver
114956/114956 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/5F000138
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed

 

---

replication gap 모니터링

#!/bin/bash

# PostgreSQL 접속 정보
PGUSER="postgres"
PGHOST="localhost"
PGPORT="5432"
PGDATABASE="postgres"

# 실행 시간 출력#

while true; do
echo "===== $(date '+%Y-%m-%d %H:%M:%S') ====="

# replication gap 확인 (standby들이 얼마나 뒤처져 있는지)
psql -U $PGUSER -h $PGHOST -p $PGPORT -d $PGDATABASE -Atc "
SELECT
  application_name,
  client_addr,
  state,
  sync_state,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replication_gap,
  write_lag,
  flush_lag,
  replay_lag
FROM pg_stat_replication;
"
sleep 3

done