PostgreSQL: Logical Replication

※ PostgreSQL: Logical Replication.

 

안녕하세요. 듀스트림입니다.

 

오늘 포스팅은 로지컬 레플리케이션에 대한 내용입니다.

 

PostgreSQL 10버전에서 공식으로 도입되어 17버전까지 많은 개선이 있었습니다.

버전	내용
10	공식 Logical Replication 도입 - Publisher/Subscriber 구조의 publish/subscribe 모델 지원 - 테이블 단위 복제 가능 - DML(INSERT/UPDATE/DELETE) 복제 지원
11	- 시리얼 컬럼 지원 개선 - 복제 지연 모니터링 기능 추가 - 복제 가능한 테이블의 제약조건 관련 개선
12	- REPLICA IDENTITY FULL 없이도 REPLICA IDENTITY USING INDEX 지원 - 복제 구독시 초기 데이터 동기화 성능 개선
13	- 논리 복제시 테이블 파티셔닝 지원 개선 - 복제 구독시 ALTER SUBSCRIPTION ... REFRESH PUBLICATION 지원
14	- 복제 구독시 데이터 동기화 병렬화 - 복제 구독시 apply worker의 동작 개선 - 복제시 DDL(스키마 변경) 일부 지원
15	- 논리 복제시 row filtering, column list 옵션 지원 - 복제 구독시 ALTER SUBSCRIPTION ... SKIP 지원 - 복제 구독시 WAL sender 동작 개선
16	- 스탠바이 서버에서 논리 복제 퍼블리셔 역할 가능 - 복제 구독자가 대용량 트랜잭션을 병렬로 적용 가능 - 논리 복제 관련 성능 개선
17	- Failover slot(복제 슬롯 동기화) 기능 도입로 HA 지원 강화 - pg_createsubscriber 유틸리티 추가 (물리적 스탠바이에서 논리 복제 구독 생성) - pg_upgrade 시 퍼블리셔의 논리 복제 슬롯/서브스크립션 상태 보존

---

1. Logical Replication?

• PostgreSQL의 로지컬 레플리케이션은 특정 테이블, 스키마 수준의 변경 데이터를 논리적으로 복제하는 기능입니다.
• WAL을 기반으로 복제되지만 물리적 블록 레벨이 아닌 INSERT/UPDATE/DELETE 단위의 변경 사항만 추출합니다.
  
  
• 특징
  
  • 특정 테이블만 복제 가능 (선택적 복제)
  • PK 또는 UK 필요
    • 없다면 REPLICA IDENTITY FULL 옵션으로 테이블 속성 변경  후 사용 가능
      → 테이블에 변경사항이 생길 시 전체 테이블을 복제하므로 자주 업데이트되는 대용량 테이블에서는 자원 소모가 큼
  • FK는 복제하지 않음
  • PostgreSQL 다른 버전간 복제 가능 (ex. 14 → 17)
  • 스트리밍 방식으로 실시간 반영
  • Kafka, Debezium 등 CDC 연동에 최적화

 

로지컬 레플리케이션 아키텍처

[그림 1] Logical Replication Architecture

 

Chapter 29. Logical Replication

---

2. 내부 동작 방식

[그림 2] 흐름도

 

핵심 컴포넌트

단계	주요 함수	설명
WAL 기록	heap_insert, XLogInsert	변경 사항을 WAL에 기록
디코딩	logical_read_xlog_page	WAL 디코딩 시작
정렬 버퍼	ReorderBufferCommit	트랜잭션 단위 정리
출력 플러그인	pgoutput_change	output plugin 가공
송신	WalSndLoop	subscriber로 전송
수신	WalReceiverMain	데이터 수신
반영	ApplyWorkerMain	변경 사항을 테이블에 적용

---

3. 테스트

이번 테스트는 캡처가 조금 어렵네요.

-- 슬롯 생성 확인 방법(Publisher)
SELECT * FROM pg_replication_slots;

-- 구독 상태 확인 방법(Subscriber)
SELECT * FROM pg_stat_subscription;

테스트 전,
1. wal_level = logical로 바꾸셔야 합니다.
2. pg_hba.conf에서 테스트에 사용할 User의 replication 허용이 필요합니다.
3. Replication 권한 추가가 필요합니다.

 

3.1 Primary → Subscriber

-- Publisher(Primary)
CREATE TABLE test_table (id int PRIMARY KEY, name text);
CREATE PUBLICATION pub_test FOR TABLE test_table;

-- Subscriber
CREATE TABLE test_table (id int PRIMARY KEY, name text);
CREATE SUBSCRIPTION sub_test 
  CONNECTION 'host=10.0.0.1 dbname=postgres user=replicator password=secret' 
  PUBLICATION pub_test;

-- Insert(Primary)
INSERT INTO test_table VALUES (1, 'Alice');

---

3.2 Standby에서 로지컬 슬롯 생성 (v.16+)

• standby에서 만든 논리적 슬롯은 실제로 복제를 실행하기 위한 것이 아니라 slot continuity 확보용
• Failover 시 standby → primary 승격 후 동일한 slot 이름을 미리 만들어 두면 복제 continuity 보장 가능
• subscriber는 ALTER SUBSCRIPTION ... SET CONNECTION으로 연결 변경

-- Standby
SELECT * FROM pg_create_logical_replication_slot('sub_test', 'pgoutput');

---

3.3 Failover 테스트 (v.16+)

• VIP 사용 시 아래의 ALTER SUBSCRIPTION ... 구문을 사용하지 않아도 자동으로 페일오버 적용됩니다.

-- Subscriber
ALTER SUBSCRIPTION sub_test 
  CONNECTION 'host=new_primary_ip dbname=postgres user=replicator password=passwd';
  
ALTER SUBSCRIPTION sub_test ENABLE;

 

+ PostgreSQL 17 버전부터는 Standby 서버에서 위 과정을 자동화해주는 유틸리티가 추가되었습니다.

# 사용 예시 (Standby)
pg_createsubscriber \
  --publisher-host=10.0.0.1 \
  --publisher-port=5432 \
  --publisher-user=postgres \
  --publisher-password=passwd \
  --subscriber-host=10.0.0.3 \
  --subscriber-port=5432 \
  --subscriber-user=postgres \
  --subscriber-password=passwd \
  --dbname=postgres \
  --slot=sub_slot \
  --publication=pub_test \
  --create-slot \
  --copy-data

 

++ 슬롯 문제 시 Subscrber에서 제거 방법

DELETE FROM pg_subscription WHERE subname = 'name';

---

4. LSN 동기화

LSN?
LSN(Log Sequence Number)은 PostgreSQL WAL 상의 특정 변경 위치를 의미하며 로지컬 레플리케이션에서는 복제 시작점 및 동기화 기준이 됩니다.

 

확인 방법

-- 현재 서버의 LSN
SELECT pg_current_wal_lsn();

-- replication slot 기준 LSN
SELECT slot_name, confirmed_flush_lsn FROM pg_replication_slots;

-- 구독자 적용 위치
SELECT * FROM pg_stat_subscription;

 

동기화 전략

방법	설명
pg_replication_slot_advance()	수동으로 복제 슬롯 위치를 조정할 수 있음
pg_replication_origin_advance()	Subscriber 측 origin 위치 조정에 사용
pg_dump + recreate	dump → 초기 데이터 적재 → subscription 재생성
pg_comparator, EXCEPT, row count	테이블 간 차이 검증 후 수동 정합성 보정

---

5. 주의사항

• pg_stat_subscription과 pg_replication_slots를 주기적으로 모니터링하여 복제 지연/누락 탐지
• 구독 상태가 r에서 d, i로 전이될 때는 slot 및 connection 상태 동기화 재점검
• HA 구성 시 standby에 논리 슬롯 미리 생성해두고 failover 대응
• ALTER SUBSCRIPTION ... SKIP으로 오류 난 트랜잭션 건너뛰기 가능 (데이터 손실 유의)
• logical_decoding_work_mem 조정으로 대량 트랜잭션 처리 개선 (기본값: 64MB)
• pgoutput 외에도 custom plugin으로 protobuf, JSON 등 메시지 포맷 변경 가능
• Debezium 연계 시 publication 필터 정확히 구성해야 Kafka topic 충돌 방지됨

+ LR을 이용한 업그레이드 시 컷오버 시점에 TO-BE에서 시퀀스 값 동기화 필요

---

오늘은 여기까지~