[PostgreSQL] 논리 복제(publication/subscription) — 리포팅 서버에 주문 테이블만 실시간으로 넘긴다

설치·접속: PostgreSQL 설치와 접속

부제: 물리 스트리밍 복제는 클러스터를 통째로 복제하지만, 리포팅 전용 서버엔 주문 테이블 하나만 실시간으로 흘리고 싶을 때

-- [발행 서버] wal_level=logical 인 상태에서, 넘길 테이블만 골라 발행한다
CREATE PUBLICATION pub_report FOR TABLE orders, products;

-- [구독 서버] 같은 스키마의 테이블을 만들어 두고 구독을 건다
CREATE TABLE orders   (id int PRIMARY KEY, customer text, amount numeric);
CREATE TABLE products (id int PRIMARY KEY, name text, price numeric);
CREATE SUBSCRIPTION sub_report
  CONNECTION 'host=pub-server dbname=postgres user=postgres password=pw'
  PUBLICATION pub_report;

-- 구독 서버에서: 구독을 만든 순간 기존 행이 통째로 복사된다(initial sync)
postgres=# SELECT * FROM orders ORDER BY id;
 id | customer | amount
----+----------+--------
  1 | kim      |    150
  2 | lee      |     80
  3 | park     |    300
(3 rows)

-- 발행 서버에서 INSERT INTO orders VALUES (4,'choi',500);  → 구독 서버에 곧바로 흐른다(streaming)
postgres=# SELECT * FROM orders WHERE id=4;
 id | customer | amount
----+----------+--------
  4 | choi     |    500
(1 row)

-- 발행에 넣지 않은 audit_log 테이블은 구독 서버에서 계속 비어 있다
postgres=# SELECT count(*) AS audit_rows FROM audit_log;
 audit_rows
------------
          0

CREATE SUBSCRIPTION을 실행하는 순간 두 가지가 한꺼번에 일어난다. 먼저 발행에 담긴 테이블의 기존 행 전체가 복사되고(initial data sync), 그 뒤부터는 발행 서버의 INSERT/UPDATE/DELETE가 WAL을 논리적으로 디코딩해 실시간으로 흐른다(streaming). 핵심은 FOR TABLE orders, products처럼 테이블을 골라서 넘긴다는 점이다. 물리 스트리밍 복제가 클러스터 전체를 바이트 단위로 복제하는 것과 달리, 논리 복제는 리포팅 서버에 필요한 주문·상품 테이블만 보내고 audit_log 같은 내부 테이블은 아예 넘기지 않는다. 위 출력에서 발행에 넣지 않은 audit_log가 구독 쪽에서 0행으로 남아 있는 게 그 증거다.

함정은 DDL과 시퀀스가 복제되지 않는다는 것. 발행 서버에서 ALTER TABLE orders ADD COLUMN note text를 해도 구독 서버 테이블에는 그 컬럼이 생기지 않는다(양쪽에 각각 ALTER를 쳐야 한다). 시퀀스 현재값도 넘어가지 않으므로, 나중에 구독 서버를 승격해 쓰려면 setval로 시퀀스를 맞춰줘야 한다. 그리고 복제할 테이블은 UPDATE/DELETE를 위해 PRIMARY KEY(또는 REPLICA IDENTITY) 가 있어야 한다.

물리 복제 vs 논리 복제

항목	물리 스트리밍 복제	논리 복제(pub/sub)
복제 단위	클러스터 전체(모든 DB)	테이블 단위로 선택
메이저 버전	양쪽 동일해야 함	이종 버전 가능(PG15→16 등)
방향	단방향(standby는 읽기 전용)	단방향·양방향 모두 가능
구독 서버 쓰기	불가(read-only)	가능(다른 테이블에 쓰기·별도 인덱스 OK)
DDL 복제	자동(블록 단위라 스키마 포함)	안 됨(양쪽 수동 ALTER)
시퀀스 값	복제됨	복제 안 됨(승격 시 setval 필요)
용도	HA/DR, 읽기 부하 분산	리포팅 분리, 무중단 업그레이드, CDC, 부분 통합

무중단·전체 이중화가 목적이면 물리 복제가 맞다. 골라서·버전 넘어서·양방향으로 흘려야 하면 논리 복제다.

이렇게도 쓴다

행 필터로 조건에 맞는 행만 넘긴다. amount > 100인 결제만 리포팅 서버로. (PG15+)

CREATE PUBLICATION pub_pay FOR TABLE payments WHERE (amount > 100);
-- 발행 서버에 (50),(150),(90),(300) 을 넣어도 구독 서버엔 150,300 두 행만 도착한다
-- postgres=# SELECT * FROM payments ORDER BY id;
--  id | amount
-- ----+--------
--   2 |    150
--   4 |    300

 

발행 중인 상태에서 테이블을 추가로 얹어 발행 범위를 넓힌다. (조합: ALTER SUBSCRIPTION REFRESH)

-- [발행] 나중에 audit_log 도 넘기기로 함
ALTER PUBLICATION pub_report ADD TABLE audit_log;
-- [구독] 새로 추가된 테이블을 인식하고 기존 행까지 다시 동기화
ALTER SUBSCRIPTION sub_report REFRESH PUBLICATION;
-- 이제 audit_log 의 기존 2행이 구독 서버로 복사된다

 

PG15 → PG16 무중단 메이저 업그레이드. 신버전 서버를 구독으로 붙여 따라잡게 한 뒤 앱을 전환한다.

-- [PG16 신서버 = 구독] 구서버의 전체 테이블을 발행받아 최신 상태로 유지
CREATE SUBSCRIPTION sub_upgrade
  CONNECTION 'host=old_pg15 dbname=app user=rep password=...'
  PUBLICATION pub_all;
-- 지연이 0에 수렴하면 앱 커넥션을 PG16 으로 돌리고, 구독을 DROP → 시퀀스 setval 로 마무리

 

양방향(멀티 마스터) 으로 두 서버가 서로를 발행·구독한다. 자기가 만든 변경이 되돌아오는 무한루프는 origin으로 막는다. (PG16+)

-- 양쪽에서 서로 상대 PUBLICATION 을 구독하되, 로컬에서 생긴 변경만 보낸다
CREATE SUBSCRIPTION sub_b_to_a
  CONNECTION 'host=node_b dbname=app user=rep password=...'
  PUBLICATION pub_b
  WITH (origin = none);   -- 다른 노드에서 온(=재전파된) 변경은 다시 보내지 않음

 

충돌로 복제가 멈추면 잠깐 끄고 원인을 처리한 뒤 다시 켠다. (구독 서버에서 같은 PK가 이미 있을 때 등)

ALTER SUBSCRIPTION sub_report DISABLE;
-- 구독 서버에서 충돌 행을 정리하거나, pg_replication_origin_advance 로 문제 LSN 을 건너뛴 뒤
ALTER SUBSCRIPTION sub_report ENABLE;

 

복제가 살아 있는지 양쪽에서 확인한다. (조합: pg_stat_replication / pg_stat_subscription)

-- [발행] 누가 어디까지 받아갔나
SELECT application_name, state, sync_state, sent_lsn, replay_lsn
  FROM pg_stat_replication;
--  application_name |   state   | sync_state | sent_lsn  | replay_lsn
-- ------------------+-----------+------------+-----------+-----------
--  sub_report       | streaming | async      | 0/1570030 | 0/1570030

-- [구독] 내가 어디까지 받았나
SELECT subname, received_lsn, latest_end_lsn FROM pg_stat_subscription;

 

정리할 땐 구독을 먼저 DROP 한다. 그래야 발행 서버의 replication slot 이 함께 해제된다(안 그러면 slot 이 남아 WAL 이 계속 쌓인다).

DROP SUBSCRIPTION sub_report;   -- NOTICE: dropped replication slot "sub_report" on publisher