DB 인사이드 | PostgreSQL New Feature - 16 Release (3)

 

 

이번 문서에서는 PostgreSQL 16 Release New Feature 중 Replication에 대한 내용을 다룹니다.

 

Logical Replication From Standby Server

PostgreSQL 15 버전까지는 Primary Server에서만 Logical Replication의 게시(Publication)을 구성할 수 있었습니다. Replication이 다중으로 구성되어 있다면 그 만큼 Primary Server의 부하도 증가합니다.

PostgreSQL 16 버전부터는 Standby Server에서 게시(Publication)을 구성할 수 있습니다. 이 기능을 통해 Primary Server의 Replication 부하를 감소 시킬 수 있습니다.

📢 Primary → Physical Replication → Standby → Logical Replication → Standby 구성은 PostgreSQL 16 버전부터 구성 가능합니다.

📢 Primary → Logical Replication → Standby → Logical Replication → Standby 구성은 PostgreSQL 16 버전 이하에서도 구성 가능합니다.

📢 설명의 용이성을 위하여 Primary, Standby, Slave로 서버 구분을 하였습니다.

 

Standby Server에 Logical Replication 구성

PostgreSQL 16 버전부터는 Standby Server에서 Logical Replication 구성이 가능 합니다. 이러한 구성을 위해서는 Primary Server는 wal_level = logical로 설정 되어 있어야 합니다.

Primary Server Replication 설정

-- Replication Role 생성
-- physicaluser : Physical Replication(Streaming) / logicaluser : Logical Replication
CREATE ROLE physicaluser WITH REPLICATION LOGIN PASSWORD 'physicaluser' ;
CREATE ROLE logicaluser  WITH REPLICATION LOGIN PASSWORD 'logicaluser' ;

-- 게시(Publication) 생성
CREATE PUBLICATION my_publication FOR TABLE repltab ;
GRANT SELECT ON TABLE repltab TO logicaluser ;

📢 Standby Server Read-Only로 DDL 수행이 불가 하기 때문에 Primary Server에서 Logical Replication Role과 게시(Publication)을 생성합니다. 생성된 Role과 게시(Publication)는 Streaming Replication을 통해 Standby Server에 적용됩니다.

-- Physical Replication Slot 생성
SELECT pg_create_physical_replication_slot( 'pslot1' ) ;

## pg_hba.conf 설정
host    replication    physicaluser    10.10.45.241/32    trust

## PostgreSQL 재기동 (Test : Ubuntu)
systemctl restart postgresql@16-main

-- 테이블 조회
SELECT COUNT(*) FROM repltab ;
 count
----------
 10265000

 

Standby Server Physical Replication 구성 및 Logical Replication 구성

## Standby Server Physical Replication 구성
/usr/lib/postgresql/16/bin/pg_basebackup --host=10.10.45.240 --port=5432 --username=physicaluser --checkpoint=fast --pgdata=/var/lib/postgresql/16/main -R

## Standby Server postgresql.conf 설정
wal_level = logical
hot_standby = on
hot_standby_feedback = on
primary_slot_name = 'pslot1'

## pg_hba.conf 설정
host    all    logicaluser    10.10.45.242/32    trust

## PostgreSQL 재기동 (Test : Ubuntu)
systemctl restart postgresql@16-main

-- 테이블 조회
SELECT COUNT(*) FROM repltab ;
 count
----------
 10265000

 

Slave Server Logical Replication 구성

-- Publication Table 생성
CREATE TABLE repltab ( c1 INTEGER , c2 TEXT , c3 TEXT , c4 TEXT ) ;
CREATE INDEX repltab_idx01 ON repltab ( c1 ) ;

PostgreSQL 15 버전에서는 Slave Server에 구독(Subscription) 생성이 불가능 합니다.

-- PostgreSQL 15 버전에서 구독(Subscription) 생성 불가
CREATE SUBSCRIPTION my_subscription CONNECTION 'dbname=postgres host=10.10.45.241 port=5432 user=logicaluser password=logicaluser' PUBLICATION my_publication ;
ERROR:  could not create replication slot "my_subscription": ERROR:  logical decoding cannot be used while in recovery

PostgreSQL 16 버전부터는 Slave Server에 구독(Subscription) 생성이 가능합니다.

-- [Slave] 구독(Subscription) 생성 시 Hang 상태처럼 표시
-- Hang 상태는 아니며, Priamry Server로 부터 메시지를 대기하는 상태
CREATE SUBSCRIPTION my_subscription CONNECTION 'dbname=postgres host=10.10.45.241 port=5432 user=logicaluser password=logicaluser' PUBLICATION my_publication ;

-- [Primary] Primary Server에서 pg_log_standby_snapshot() Function 수행
SELECT pg_log_standby_snapshot() ;
 pg_log_standby_snapshot
-------------------------
 0/71000098

-- [Slave] Subscription 생성 완료
NOTICE:  created replication slot "my_subscription" on publisher
CREATE SUBSCRIPTION

📢 pg_log_standby_snapshot() Function
bgwriter나 Checkpointer가 WAL에 기록을 기다릴 필요 없이 실행 중인 트랜잭션의 Snapshot을 찍어 WAL에 기록합니다. 즉, Logical Replication Slot 생성 속도를 높이기 위해 이 함수를 사용할 수 있습니다.

-- 테이블 조회
SELECT COUNT(*) FROM repltab ;
 count
----------
 10265000

---

 

Logical Replication Parallel

PostgreSQL 16 버전에서 Parallel Apply Worker에 의한 Logical Replication이 가능합니다. 구독(Subscription) 생성 시 streming = parallel 옵션이 추가 되었습니다. 이로 인해 pg_subscription Catalog의 컬럼의 변화도 있습니다.

구분	Column	Description
수정	substream	Data Type이 boolean → char로 변경 f : 진행중인 트랜잭션의 Streming을 허용하지 않음 t : 진행중인 트랜잭션의 Streming을 허용 p : 사용 가능한 경우 Parallel Apply Worker를 통해 직접 변경사항 적용(사용 가능한 Parallel Apply Worker가 없는 경우 t와 동일)
추가	subpasswordrequired	인증을 위한 비밀번호 필요 여부
추가	subrunasowner	Subscription Owner의 권한으로 Subscription 사용 여부
추가	suborigin	none : Local에서만 생성된 데이터 전송 any : Local + Replication으로 생성된 데이터 전송

-- Subscription Syntax
CREATE SUBSCRIPTION my_subscription 
   CONNECTION 'dbname=postgres host=10.10.45.240 port=5432 user=logicaluser password=logicaluser'
   PUBLICAION my_publication 
   WITH ( streaming = parallel ) ;

SELECT subname , substream FROM pg_subscription ;
     subname     | substream
-----------------+-----------
 my_subscription | p

---

 

양방향 Logical Replication

PostgreSQL 16 버전 부터 양방향 Logical Replication이 가능합니다. 즉, Node 1은 Node 2에서 변경된 내용을 수신하여 적용하고 Node 1에서 변경된 내용을 Node 2로 전송하여 적용 시킬 수 있습니다. 반대로 Node 2는 Node 1에서 변경된 내용을 수신하여 적용하고 Node 2에서 변경된 내용을 Node 1으로 전송하여 적용 시킬 수 있습니다. 양방향 Logical Replication을 통해 두 Node 중 하나에서 수행된 모든 수정 사항을 두 Node 모두에 미러링되어 동기화 할 수 있습니다.

PostgreSQL 15 버전 까지는 이 기능을 지원하지 않기 때문에 Apply Worker는 데이터가 Local에서 생성되었는지 Replication을 통해 생성되었는지 알지 못합니다. 따라서 아래와 같은 무한 루프 상태가 됩니다.

1. Node 1에서 변경된 내용을 Node2로 전송 → Node2 데이터 적용
2. Node 2에서 변경된 내용을 Node1으로 전송 → Node1 데이터 적용
3. 1 → 2 → 1 → 2 → 1 → 2 → ... 무한 루프 발생

PostgreSQL 16에서 Subscription 생성 Syntax에 origin 파라미터가 도입되어 Apply Worker는 데이터가 Local에서 생성되었는지 Replication을 통해 생성되었는지 알 수 있게 되어 무한 루프 상태가 발생하지 않습니다.

-- 양방향 Logical Replication Syntax
CREAET SUBSCRIPTION {subscription_name} 
  CONNECTION {connection_info} 
  PUBLICATION {publication_name}
  WITH ( copy_data = false , origin = none ) ;

origin 파라미터는 두 개의 값을 지원합니다.

• none : Local에서만 생성된 데이터만 전송하도록 요청 (무한 루프 방지)
• any : Local + Replication으로 생성된 데이터 전송 (무한 루프 발생)

 

양방향 Logical Replication Test

## node 1, node 2 모두 wal_level = logical 설정
vi /etc/postgresql/16/main/postgresql.conf
wal_level = logical

Node 1 : Logical Replication 설정 후 재기동

CREATE ROLE logicaluser REPLICATION LOGIN PASSWORD 'logicaluser' ;
CREATE TABLE test001 ( c1 Integer ) ;
CREATE PUBLICATION node1_pub FOR TABLE test001 ;
GRANT SELECT ON TABLE test001 TO logicaluser ;

vi /etc/postgresql/16/main/postgresql.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             logicaluser     10.10.45.242/32         trust

systemctl restart postgresql@16-main

Node 2 : Logical Replication 설정 후 재기동

CREATE ROLE logicaluser REPLICATION LOGIN PASSWORD 'logicaluser' ;
CREATE TABLE test001 ( c1 Integer ) ;
CREATE PUBLICATION node2_pub FOR TABLE test001 ;
GRANT SELECT ON TABLE test001 TO logicaluser ;

vi /etc/postgresql/16/main/postgresql.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             logicaluser     10.10.45.241/32         trust

systemctl restart postgresql@16-main

Node 1 : Subscription 설정

CREATE SUBSCRIPTION node1_sub CONNECTION 'host=10.10.45.242 dbname=postgres port=5432' PUBLICATION node2_pub WITH (copy_data=false, origin=none) ;

Node 2 : Subscription 설정

CREATE SUBSCRIPTION node2_sub CONNECTION 'host=10.10.45.241 dbname=postgres port=5432' PUBLICATION node1_pub WITH (copy_data=false, origin=none) ;

양방향 Logical Replication 확인

-- Node 1
SELECT subname , suborigin
FROM   pg_subscription ;
  subname   | suborigin
------------+-----------
 node1_sub  | none

INSERT INTO test001 VALUES ( 1 ) ;
SELECT * FROM test001 ;
 c1
----
  1

-- Node 2
SELECT subname , suborigin
FROM   pg_subscription ;
  subname   | suborigin
------------+-----------
 node2_sub  | none

SELECT * FROM test001 ;
 c1
----
  1

INSERT INTO test001 VALUES ( 2 );
SELECT * FROM test001 ;
 c1
----
  1
  2

-- Node 1
SELECT * FROM test001 ;
 c1
----
  1
  2

📢 PostgreSQL 15에서는 무한 루프가 발생합니다.

PostgreSQL 15 양방향 Logical Replication 확인

-- Node 1
INSERT INTO test001 VALUES ( 1 ) ;
SELECT c1, COUNT(*) FROM test001 GROUP BY c1 ;
 c1 | count
----+-------
  1 |   1
SELECT c1, COUNT(*) FROM test001 GROUP BY c1 ;
 c1 | count
----+-------
  1 |   15

-- Node 2
SELECT c1, COUNT(*) FROM test001 GROUP BY c1 ;
 c1 | count
----+-------
  1 |   20

INSERT INTO test001 VALUES ( 2 ) ;
SELECT c1, COUNT(*) FROM test001 GROUP BY c1 ;
 c1 | count
----+-------
  1 |   454
  2 |     1
SELECT c1, COUNT(*) FROM test001 GROUP BY c1 ;
 c1 | count
----+-------
  1 |   502
  2 |    71

-- Node 1
SELECT c1, COUNT(*) FROM test001 GROUP BY c1 ;
 c1 | count
----+-------
  1 |   619
  2 |   187

---

 

WAL Sender 프로세스 이름 변경

PostgreSQL 16 버전 부터 WAL Sender 프로세스의 이름이 변경되었습니다. 기존에는 Logical Replication에 대한 정보만 표시 되었지만, dbname이 추가되어 표시됩니다.

## PostgreSQL 15
root@db2:~# ps -ef | grep walsender
postgres  726390  620309  0 11월24 ?      00:00:04 postgres: 15/main: walsender logicaluser 10.10.45.242(33792) START_REPLICATION

## PostgreSQL 16
root@db2:~# ps -ef | grep walsender
postgres  726595  621753  0 11월24 ?      00:00:42 postgres: 16/main: walsender logicaluser postgres 10.10.45.242(60894) START_REPLICATION

 

 

 

 

 

 

글 | DB기술기획팀

이미지 | 브랜드디자인팀

 

 

 

 

 

기획 및 글 | 플랫폼기술연구팀