Logical Replication

Postgresql, EPAS/Replication

Logical Replication

havy 2025. 6. 17. 09:47

Logical Replication은 하나 이상의 특정 데이터베이스 및 테이블을 복제하는 방식이다. Master 노드를 Publisher (Publication), Slave 노드를 Subscriber (Subscription)라고 한다.

구독자가 게시자가 되는 다중 형태로 구성 가능하며, 구독에는 게시보다 많은 컬럼 정의가 가능하고 컬럼 순서에는 영향을 받지 않는다.

Logical Replication 장점
여러 데이터베이스 간 데이터 통합
서로 다른 버전 간의 Replication
서로 다른 데이터베이스 간의 데이터 마이그레이션

Logical Replication 한계
DDL은 복제되지 않음
시퀀스는 복제하지 않음
복제 대상 테이블은 PK나 Unique key가 있어야 함
Large object는 복제를 지원하지 않음

[Master]

1) Config 설정

vi postgresql.conf

listen_addresses = '*'
port = 5444
wal_level = logical	wal_level을 logical로 설정하면, Replica 레벨에서 수집하는 정보에 추가적으로 Logical Decoding을 지원하기 위한 정보까지 추가된다. 즉, logical 설정 시 WAL 파일이 많이 생성되므로 디스크 여유공간 확인이 필요하다

vi pg_hba.conf (Slave 정보 기입)

host replication repl 192.168.56.152/24 trust

2) EPAS 재기동

3) Replication 계정 생성

edb=# create user repl replication identified by 'repl';
CREATE ROLE
edb=# \x
Expanded display is on.
edb=# \du
List of roles
-[ RECORD 1 ]----------------------------------------------------------
Role name | aq_administrator_role
Attributes | No inheritance, Cannot login +
| Profile default
-[ RECORD 2 ]----------------------------------------------------------
Role name | capture_admin
Attributes | No inheritance, Cannot login +
| Profile default
-[ RECORD 3 ]----------------------------------------------------------
Role name | enterprisedb
Attributes | Superuser, Create role, Create DB, Replication, Bypass RLS+
| Profile default
-[ RECORD 4 ]----------------------------------------------------------
Role name | repl
Attributes | Replication +
| Profile default

4) Replication 대상 테이블 선정

하나 이상의 테이블에 대하여 Replication 구성이 가능하다. 기존에 생성된 테이블이나 새로 생성하는 테이블을 선택하여 복제를 구성할 수 있다.

edb=# create table a (a int, b date);
CREATE TABLE
edb=# insert into a values(1,now());
INSERT 0 1
edb=# insert into a values(2,now());
INSERT 0 1
edb=# insert into a values(3,now());
INSERT 0 1
edb=# insert into a values(4,now());
INSERT 0 1

edb=# select * from a;
a | b
---+---------------------------
1 | 07-MAY-25 17:16:20.377711
2 | 07-MAY-25 17:16:24.372521
3 | 07-MAY-25 17:16:26.466037
4 | 07-MAY-25 17:16:29.232314

5) Publication 생성
Replication으로 지정할 테이블을 포함하도록 Publication을 생성한다.

edb=# create publication pub1 for table a;
CREATE PUBLICATION
edb=# select * from pg_publication;
oid | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot
-------+---------+----------+--------------+-----------+-----------+-----------+-------------+------------
16396 | pub1 | 10 | f | t | t | t | t | f
(1 row)

edb=# select * from pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+-----------+----------+-----------
pub1 | public | a | {a,b} |
(1 row)

6) DB User 권한 부여

edb=# grant select on table a to repl;
GRANT

[Standby]

1) Replication 대상 테이블 생성
Logical Replication은 DDL은 복제가 되지 않으므로 Replication 구성 전에 복제 대상 테이블을 생성해야 한다.

edb=# create table a (a int, b date);
CREATE TABLE

2) Subscription 생성
edb=# create subscription sub1 connection 'dbname=edb host=192.168.56.151 port=5444 user=repl password=repl' publication pub1;
NOTICE: created replication slot "sub1" on publisher
CREATE SUBSCRIPTION

edb=# select * from pg_subscription;
-[ RECORD 1 ]-------+-----------------------------------------------------------------
oid | 24580
subdbid | 14401
subskiplsn | 0/0
subname | sub1
subowner | 10
subenabled | t
subbinary | f
substream | f
subtwophasestate | d
subdisableonerr | f
subpasswordrequired | t
subrunasowner | f
subconninfo | dbname=edb host=192.168.56.151 port=5444 user=repl password=repl
subslotname | sub1
subsynccommit | off
subpublications | {pub1}
suborigin | any

참고로 Replication slot을 따로 생성하지 않은 경우 Subscription이 생성되면서 Master 노드에 Subscription 이름으로 Replication slot이 생성된다. (기본 Output 옵션은 pgoutput임)

Replication 확인

[Master]

$ps -ef |grep post
enterpr+ 2063 2033 0 17:20 ? 00:00:00 postgres: walsender repl edb 192.168.56.152(17594) START_REPLICATION

$tail -f enterprisedb-2025-05-07_171503.log
2025-05-07 17:20:27 KST STATEMENT: START_REPLICATION SLOT "sub1" LOGICAL 0/0 (proto_version '4', origin 'any', publication_names '"pub1"')
2025-05-07 17:20:27 KST LOG: logical decoding found consistent point at 0/40745C0
2025-05-07 17:20:27 KST DETAIL: There are no running transactions.
2025-05-07 17:20:27 KST STATEMENT: CREATE_REPLICATION_SLOT "pg_24580_sync_24577_7501576693917798344" LOGICAL pgoutput (SNAPSHOT 'use')
2025-05-07 17:20:27 KST LOG: starting logical decoding for slot "pg_24580_sync_24577_7501576693917798344"
2025-05-07 17:20:27 KST DETAIL: Streaming transactions committing after 0/40745F8, reading WAL from 0/40745C0.
2025-05-07 17:20:27 KST STATEMENT: START_REPLICATION SLOT "pg_24580_sync_24577_7501576693917798344" LOGICAL 0/40745F8 (proto_version '4', origin 'any', publication_names '"pub1"')
2025-05-07 17:20:27 KST LOG: logical decoding found consistent point at 0/40745C0
2025-05-07 17:20:27 KST DETAIL: There are no running transactions.
2025-05-07 17:20:27 KST STATEMENT: START_REPLICATION SLOT "pg_24580_sync_24577_7501576693917798344" LOGICAL 0/40745F8 (proto_version '4', origin 'any', publication_names '"pub1"')

[Slave]

$ps -ef |grep post
enterpr+ 1836 1749 0 17:20 ? 00:00:00 postgres: logical replication apply worker for subscription 24580

$tail -f enterprisedb-2025-05-07_165409.log
2025-05-07 16:54:09 KST LOG: redo done at 0/40448D0 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2025-05-07 16:54:09 KST LOG: checkpoint starting: end-of-recovery immediate wait
2025-05-07 16:54:09 KST LOG: checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.002 s, total=0.006 s; sync files=2, longest=0.001 s, average=0.001 s; distance=0 kB, estimate=0 kB; lsn=0/4044908, redo lsn=0/4044908
2025-05-07 16:54:09 KST LOG: database system is ready to accept connections
2025-05-07 16:54:09 KST LOG: dbms_aq launcher started
2025-05-07 16:59:09 KST LOG: checkpoint starting: time
2025-05-07 16:59:10 KST LOG: checkpoint complete: wrote 2 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.202 s, sync=0.002 s, total=0.218 s; sync files=1, longest=0.002 s, average=0.002 s; distance=14 kB, estimate=14 kB; lsn=0/4048160, redo lsn=0/4048128
2025-05-07 17:20:27 KST LOG: logical replication apply worker for subscription "sub1" has started
2025-05-07 17:20:27 KST LOG: logical replication table synchronization worker for subscription "sub1", table "a" has started
2025-05-07 17:20:27 KST LOG: logical replication table synchronization worker for subscription "sub1", table "a" has finished

Slave에서 drop subscription sub1; 수행 시, replication 중지되며, 복제 프로세스도 모두 사라짐

'Postgresql, EPAS > Replication' 카테고리의 다른 글

Streaming Replication (0)	2025.06.18

현재글Logical Replication

HAVY

Today :
Yesterday :

HAVY