검색어를 입력하세요.

PostgreSQL, 논리복제 실무 가이드

간지뽕빨리턴님 2025. 10. 18. 20:32
반응형

 

FEMS 사례를 바탕으로 개념을 충분히 풀어 설명하고, 옵션의 의미와 설정 방법, 재동기화, 트리거, 모니터링, Tailscale 네트워크 구성까지 단계별로 정리합니다.

1. 개념 심화와 동작 원리

1) 비유로 이해합니다

논리 복제는 신문 구독과 비슷합니다. 신문사가 오늘자 기사 목록을 만들어 두고, 구독자에게 순서대로 배달합니다. 구독자가 부재중이어도 배달부는 어디까지 전달했는지 기록합니다. PostgreSQL에서는 이 기록이 복제 슬롯입니다.

2) 내부 동작을 단계로 설명합니다

  1. 게시자는 커밋 시 변경을 WAL에 기록합니다.
  2. 논리 디코더가 WAL을 테이블/컬럼/값 이벤트로 변환합니다.
  3. 복제 슬롯이 읽은 위치를 저장해 재시작 시 이어갑니다.
  4. 구독자 워커가 이벤트를 수신해 동일 DML을 실행합니다.

3) 보장과 비보장

  • 보장: 트랜잭션 원자성, 내부 순서, 동일 행 변경 순서.
  • 비보장: 다중 게시자 전역 순서, DDL 자동 동기화, 시퀀스 동기화.

4) 복제 대상

항목 상태 비고
DML: INSERT, UPDATE, DELETE, TRUNCATE 복제됨 Publication 설정 준수
DDL: CREATE/ALTER TABLE 미복제 양쪽 스키마 별도 배포
시퀀스 미복제 setval() 보정 필요
Large Object 미복제 별도 전략 필요

5) FEMS 시나리오 적합성

로컬 DB에 안전 적재 후 연결 회복 시 중앙으로 밀어 올립니다. 슬롯이 진행 위치를 기억해 유실을 줄입니다.

2. 설계 지침

1) 키 설계

  • 가능하면 기본키 사용.
  • 불가 시 REPLICA IDENTITY FULL 고려.

2) 파티션·인덱스

  • 시간 파티셔닝으로 초기 스냅샷·보관 단순화.
  • 구독자 조회 인덱스 사전 구축.

3) 타임스탬프·중복 방지

  • 수집 시각과 장비 시각 분리 기록.
  • UNIQUE 제약 또는 UPSERT 전략.

3. 실습 스키마

FEMS 전력 계측 예시로 (site_code, log_time) 복합 기본키를 사용합니다.

CREATE TABLE energy_data (
  site_code     TEXT        NOT NULL,
  usage_kwh     NUMERIC,
  peak_kw       NUMERIC,
  power_factor  NUMERIC,
  log_time      TIMESTAMP   NOT NULL,
  PRIMARY KEY (site_code, log_time)
);
시퀀스는 복제되지 않습니다. 구독자에서 setval()로 보정합니다.

4. 설정 옵션 상세

게시자 postgresql.conf

파라미터 설명
wal_level = logical 논리 디코딩 활성
max_wal_senders 동시 전송 프로세스 수
max_replication_slots 복제 슬롯 상한
idle_replication_slot_timeout 미사용 슬롯 정리
listen_addresses Tailscale 사용 시 0.0.0.0 또는 TS IP 포함
host    replication    replicator    100.64.0.0/10    md5

구독자 워커 파라미터

파라미터 설명
max_logical_replication_workers 논리 복제 워커 상한
max_sync_workers_per_subscription 초기 스냅샷 병렬 수
max_worker_processes 전체 워커 상한
wal_receiver_timeout 등 불안정 네트워크 보강

Subscription 옵션

옵션 설명
copy_data 생성 시 스냅샷 복사
create_slot 슬롯 자동 생성
enabled 즉시 시작 여부
slot_name 슬롯 이름 고정
synchronous_commit 커밋 보장 수준
binary 이진 전송 사용
streaming 대용량 트랜잭션 스트리밍
origin 특정 원본만 적용

5. 단계별 구성

5-1 게시자 준비

복제 계정 생성
CREATE ROLE replicator WITH LOGIN ENCRYPTED PASSWORD '강력한_비밀번호';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replicator;
파라미터 적용
wal_level = logical
max_wal_senders = 10
max_replication_slots = 10
접속 허용
host replication replicator 100.64.0.0/10 md5

5-2 Publication 생성

CREATE PUBLICATION fems_pub
  FOR TABLE energy_data
  WITH (publish = 'insert, update, delete, truncate');

5-3 구독자 스키마 준비

CREATE TABLE energy_data (
  site_code TEXT NOT NULL,
  usage_kwh NUMERIC,
  peak_kw NUMERIC,
  power_factor NUMERIC,
  log_time TIMESTAMP NOT NULL,
  PRIMARY KEY (site_code, log_time)
);

5-4 Subscription 생성

CREATE SUBSCRIPTION fems_sub
  CONNECTION 'host=100.64.0.10 port=5432 dbname=fems user=replicator password=강력한_비밀번호'
  PUBLICATION fems_pub
  WITH (create_slot = true, enabled = true, copy_data = true);

5-5 동작 테스트

INSERT INTO energy_data(site_code, usage_kwh, peak_kw, power_factor, log_time)
VALUES ('SITE-A', 125.4, 150.0, 0.95, NOW());
SELECT * FROM energy_data ORDER BY log_time DESC;
구독자는 행을 찾지 못하는 UPDATE/DELETE를 건너뜁니다.

6. 행과 컬럼 필터

PostgreSQL 15는 컬럼 목록과 행 조건을 동시에 지정하여 부분 복제를 구성합니다.

CREATE PUBLICATION fems_pub_site_a
  FOR TABLE energy_data (site_code, usage_kwh, peak_kw, power_factor, log_time)
  WHERE (site_code = 'SITE-A');
Publication 변경 후 구독자에서 ALTER SUBSCRIPTION ... REFRESH PUBLICATION을 실행합니다.

7. 초기 로딩 전략

전부 스냅샷

데이터 양이 적을 때 적합합니다.

덤프 후 copy_data=false

대량 데이터에 적합합니다.

하이브리드

  1. 덤프로 구독자를 채움
  2. enabled=false로 생성
  3. 점검 후 ENABLE

8. 재동기화와 초기화

절차

  1. ALTER SUBSCRIPTION fems_sub DISABLE;
  2. 충돌 데이터 정리
  3. DROP SUBSCRIPTION fems_sub WITH (drop_slot);
  4. 옵션 선택해 재생성
  5. 필요 시 REFRESH PUBLICATION

충돌 유형

  • duplicate key: 충돌 행 정리 후 재시도
  • 외래 키 실패: 초기 적재 순서 조정 또는 임시 제약 비활성화

9. 트리거 설계

구독자 적용 워커는 session_replication_role='replica'로 동작합니다. 복제 유입 데이터에 대해 트리거를 실행하려면 ENABLE REPLICA 또는 ENABLE ALWAYS를 사용합니다.

ALTER TABLE energy_data ENABLE REPLICA TRIGGER trg_energy_audit;
ALTER TABLE energy_data ENABLE ALWAYS TRIGGER trg_energy_audit;
복제된 쓰기가 다시 다른 노드로 복제되는 구조는 루프를 유발합니다. 경로를 단방향으로 유지합니다.
CREATE TABLE energy_audit (
  at_time TIMESTAMP DEFAULT now(),
  op      TEXT,
  site_code TEXT,
  log_time  TIMESTAMP,
  who       TEXT
);
CREATE OR REPLACE FUNCTION fn_energy_audit() RETURNS trigger AS $$
BEGIN
  INSERT INTO energy_audit(op, site_code, log_time, who)
  VALUES (TG_OP, NEW.site_code, NEW.log_time, current_user);
  RETURN NEW;
END; $$ LANGUAGE plpgsql;
CREATE TRIGGER trg_energy_audit
AFTER INSERT OR UPDATE OR DELETE ON energy_data
FOR EACH ROW EXECUTE FUNCTION fn_energy_audit();

10. 성능과 용량 계획

슬롯·WAL

  • 비활성 슬롯은 WAL 누적 원인
  • 장기 중단 시 구독·슬롯 제거 후 재구독

적용 성능

  • 구독자 인덱스 사전 구축
  • 초기 스냅샷 병렬화
  • 파티셔닝 활용

지연 해석

  • 네트워크 지연+적용 시간의 합
  • 인덱스 부재·제약 충돌·무거운 트리거 점검

11. 모니터링과 장애 대응

필수 지표

SELECT subname, status, latest_end_time, last_error FROM pg_stat_subscription;
SELECT slot_name, active, restart_lsn FROM pg_replication_slots;
SELECT application_name, state, sent_lsn, write_lsn, flush_lsn, replay_lsn FROM pg_stat_replication;

경보 기준

  • 슬롯 비활성 10분 이상
  • status 비정상 또는 last_error 존재
  • WAL 디스크 사용량 80% 이상

12. Tailscale 네트워킹

Tailscale을 사용하면 100 대역 사설 IP로 안전하게 통신합니다.

host    replication    replicator    100.64.0.0/10    md5
CREATE SUBSCRIPTION fems_sub
  CONNECTION 'host=100.64.0.10 port=5432 dbname=fems user=replicator password=***'
  PUBLICATION fems_pub;
ACL과 비밀번호 정책, OS 방화벽을 함께 운영합니다.

13. 보안 베스트프랙티스

  • 복제 전용 사용자 최소 권한
  • TLS 적용
  • 비밀은 환경 변수·비밀 저장소 사용
  • 로그에 비밀번호 노출 금지

14. 장단점과 해결책

장점

  • 선택적 복제
  • 이기종 버전·플랫폼
  • 다중 게시자 집계
  • CDC 파이프라인 입력 적합

단점·대응

  • DDL 미복제 → 마이그레이션 후 REFRESH
  • 시퀀스 불일치 → setval() 보정
  • 슬롯로 WAL 누적 → 모니터링·타임아웃
  • PK 부재 → PK 설계 또는 REPLICA IDENTITY FULL
  • 다중 쓰기 충돌 → 단일 쓰기 원칙

15. 배포 체크리스트

  • 스키마 게시자·구독자 DDL 일치
  • 계정 최소 권한·정책
  • 네트워크 Tailscale/방화벽/pg_hba
  • 파라미터 senders·slots·workers 확보
  • 초기화 데이터 양에 맞는 전략
  • 모니터링 상태·슬롯·WAL 알림
  • 복구 DROP·재구독 절차 문서화

16. 마무리

논리 복제는 동기화 기능을 넘어 데이터 흐름 설계입니다. 개념과 옵션, 초기 로딩·재동기화, 트리거·모니터링, 네트워크 구성까지 운영에 필요한 내용을 담았습니다.

현장 문제와 해결 과정을 기록하면 다음 선택이 더 빠르고 안전해집니다. 더 좋은 방법이 있다면 계속 보완하겠습니다.

설정과 DDL, 스크립트를 버전 관리하고 지연·슬롯 상태 알림을 자동화하세요.

PostgreSQL 논리 복제 실무 가이드 · PostgreSQL 15 기준