Logical Replication은 V10부터 가능하며, 기존의 Replication의 한계를 해결할 목적으로 도입 됨
기존 Replication의 한계
- 일부 데이터 베이스 또는 테이블만 복제 할 수 없다.
- Standby 서버에는 Write 작업이 불 가능하다.
- 서로 다른 버전의 master로부터 복제를 할 수 없다.
- 서로 다른 플랫폼 간의 복제를 할 수 없다. (Linux <--> Window)
동작 방식
변경 사항을 synchronization worker가 변경 사항을 감지하여, 구독자 에게 전달하는 방식을 사용.
실제는 구독 서버에서 Master의 변경 사항을 가져가는 방식이다.
Master서버는 wal_level = logical 를 설정하고, Replication 유저를 생성해 준다.
Master서버에서 publication 설정을 해 주고, 구독 서버에서는 Master에서 생성해 준 replication 유저를 이용하여 변경 데이터를 가져가서 반영을 한다.
PUBLICATION 설정 방법
해당 데이터베이스 안의 모든 테이블
CREATE PUBLICATION my_publication FOR ALL TABLES;
특정 테이블만 전달하고자 한다면
CREATE PUBLICATION my_publication FOR TABLE emp , dept ;
INSERT Operation만 전달하고자 한다면
CREATE PUBLICATION my_publication FOR TABLE sales_log WITH (publish = 'insert') ;
V10 : insert, update, delete
V11 ~ : insert, update, delete, truncate
SUBSCRIPTION 설정 방법
CREATE SUBSCRIPTION my_subscription CONNECTION 'host=MasterIP user=MasterUser password=pwd port=MasterPort dbname=MasterDB' PUBLICATION my_publication ;
리플리케이션 슬롯은 master서버에 생성 된다.
Logical Replication의 한계
schema나 DDL은 복제 하지 않는다. (standby에서 동일한 테이블 또는 스키마를 생성해 줘야 한다. pg_dump --schema-only)
DML만 지원. truncate는 지원하지 않음(V11부터는 지원)
시퀀스는 복제 하지 않는다.
복제 대상 테이블은 PK나 UNIQUE Key가 있어야 한다.
Large Objects는 복제를 지원하지 않는다.
테스트는 V10과 V11만 테스트를 진행해 본다.
V11은 Truncate 지원여부를 확인하기 위하여 진행 함.
테스트 (10.20)
Master(publication) : 192.168.56.116
Replication(subscription) : 192.168.56.117
Master(publication)
-- wal 데이터에 logical replication에 필요한 정보를 저장하기 위한 레벨 설정
echo "wal_level = logical" >> $PGDATA/postgresql.conf
-- 외부에서 접속하기 위한 설정
echo "host all all 0.0.0.0/0 md5" >> $PGDATA/pg_hba.conf
-- 테스트 데이터 베이스 생성
CREATE DATABASE test ;
-- Replication 유저 생성
CREATE USER repl WITH REPLICATION PASSWORD 'repl1122' ;
GRANT ALL PRIVILEGES ON DATABASE test TO repl;
-- 테스트 테이블 생성
\c test postgres
CREATE TABLE tb1 ( id int primary key , name varchar(10) ) ;
-- 테스트 데이터베이스 안의 public 테이블인 tb1에 대한 권한 부여
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO repl;
-- publication 설정
CREATE PUBLICATION pub_tb1 FOR TABLE tb1;
CREATE PUBLICATION
Replication(subscription)
-- 테스트 데이터 베이스 생성
CREATE DATABASE test ;
-- 테스트 테이블 생성
\c test postgres
CREATE TABLE tb1 ( id int primary key, name varchar(10) ) ;
-- subscription 설정 (master의 publication 이름과 Replication 유저 정보 입력)
CREATE SUBSCRIPTION sub_tb1 CONNECTION 'host=192.168.56.116 user=repl password=repl1122 port=5410 dbname=test' PUBLICATION pub_tb1;
NOTICE: created replication slot "sub_tb1" on publisher
CREATE SUBSCRIPTION
Master(publication)
-- INSERT 테스트
\c test postgres
INSERT INTO tb1 VALUES ( 1 , 'name1' ) ;
SELECT * FROM tb1 ;
id | name
----+-------
1 | name1
(1 row)
Replication(subscription)
-- 데이터 확인
\c test postgres
SELECT * FROM tb1 ;
id | name
----+-------
1 | name1
(1 row)
Master(publication)
-- UPDATE 테스트
\c test postgres
UPDATE tb1 SET name = 'name2' ;
SELECT * FROM tb1 ;
id | name
----+-------
1 | name2
(1 row)
Replication(subscription)
-- 데이터 확인
\c test postgres
SELECT * FROM tb1 ;
id | name
----+-------
1 | name2
(1 row)
Master(publication)
-- DELETE 테스트
\c test postgres
DELETE FROM tb1 ;
SELECT * FROM tb1 ;
id | name
----+------
(0 rows)
Replication(subscription)
-- 데이터 확인
\c test postgres
SELECT * FROM tb1 ;
id | name
----+------
(0 rows)
Master(publication)
-- Truncate 테스트를 위한 데이터 입력
\c test postgres
INSERT INTO tb1 VALUES ( 1 , 'name1' ) ;
INSERT INTO tb1 VALUES ( 2 , 'name2' ) ;
SELECT * FROM tb1 ;
id | name
----+-------
1 | name1
2 | name2
(2 rows)
Replication(subscription)
-- 데이터 확인
\c test postgres
SELECT * FROM tb1 ;
id | name
----+-------
1 | name1
2 | name2
(2 rows)
Master(publication)
-- Truncate 작업 진행
\c test postgres
TRUNCATE TABLE tb1 ;
SELECT * FROM tb1 ;
id | name
----+------
(0 rows)
Replication(subscription)
-- 데이터 확인
\c test postgres
SELECT * FROM tb1 ;
id | name
----+-------
1 | name1
2 | name2
(2 rows)
-- 데이터 삭제 되지 않은 상태 임
Master(publication)
-- 구독서버에 있는 동일 데이터를 Master에 Insert 해봄
\c test postgres
INSERT INTO tb1 VALUES ( 1 , 'name1' ) ;
SELECT * FROM tb1 ;
id | name
----+-------
1 | name1
(1 row)
Replication(subscription)
-- 데이터 확인
\c test postgres
SELECT * FROM tb1 ;
id | name
----+-------
1 | name1
2 | name2
(2 rows)
-- 기존 데이터 때문에 에러 발생 (계속해서 발생 됨. 로그 파일 사이즈는 계속 커짐...)
Error 로그에는 기존에 있는 데이터라고 에러 메시지가 계속 출력 됨.
2022-04-19 17:58:17 KST [1960]: [1-1] user=,db=,remote=LOG: logical replication apply worker for subscription "sub_tb1" has started
2022-04-19 17:58:17 KST [1960]: [2-1] user=,db=,remote=ERROR: duplicate key value violates unique constraint "tb1_pkey"
2022-04-19 17:58:17 KST [1960]: [3-1] user=,db=,remote=DETAIL: Key (id)=(1) already exists.
2022-04-19 17:58:17 KST [1847]: [42-1] user=,db=,remote=LOG: worker process: logical replication worker for subscription 16390 (PID 1960) exited with exit code 1
2022-04-19 17:58:23 KST [1961]: [1-1] user=,db=,remote=LOG: logical replication apply worker for subscription "sub_tb1" has started
2022-04-19 17:58:23 KST [1961]: [2-1] user=,db=,remote=ERROR: duplicate key value violates unique constraint "tb1_pkey"
2022-04-19 17:58:23 KST [1961]: [3-1] user=,db=,remote=DETAIL: Key (id)=(1) already exists.
2022-04-19 17:58:23 KST [1847]: [43-1] user=,db=,remote=LOG: worker process: logical replication worker for subscription 16390 (PID 1961) exited with exit code 1
Master(publication)
-- 혹시 몰라서 Master에서 다른 명령어 수행해 봄
\c test postgres
DELETE FROM tb1 ;
SELECT * FROM tb1 ;
id | name
----+------
(0 rows)
Replication(subscription)
-- 데이터 확인
\c test postgres
SELECT * FROM tb1 ;
id | name
----+-------
1 | name1
2 | name2
(2 rows)
-- 기존 에러로 인하여 에러 이후에 수행되는 Master의 변경 사항이 반영 되지 않음
2022-04-19 18:02:55 KST [2039]: [1-1] user=,db=,remote=LOG: logical replication apply worker for subscription "sub_tb1" has started
2022-04-19 18:02:55 KST [2039]: [2-1] user=,db=,remote=ERROR: duplicate key value violates unique constraint "tb1_pkey"
2022-04-19 18:02:55 KST [2039]: [3-1] user=,db=,remote=DETAIL: Key (id)=(1) already exists.
2022-04-19 18:02:55 KST [1847]: [97-1] user=,db=,remote=LOG: worker process: logical replication worker for subscription 16390 (PID 2039) exited with exit code 1
2022-04-19 18:03:00 KST [2040]: [1-1] user=,db=,remote=LOG: logical replication apply worker for subscription "sub_tb1" has started
2022-04-19 18:03:00 KST [2040]: [2-1] user=,db=,remote=ERROR: duplicate key value violates unique constraint "tb1_pkey"
2022-04-19 18:03:00 KST [2040]: [3-1] user=,db=,remote=DETAIL: Key (id)=(1) already exists.
2022-04-19 18:03:00 KST [1847]: [98-1] user=,db=,remote=LOG: worker process: logical replication worker for subscription 16390 (PID 2040) exited with exit code 1
-- 조치를 위하여 구독 서버에서 subscription 삭제를 해 줌
DROP SUBSCRIPTION sub_tb1 ;
-- 이제 에러 메시지 더 이상 발생 안 함.
TRUNCATE TABLE tb1 ;
테스트 (11.15)
Truncate 명령어가 적용 되는지 테스트 진행
Master(publication) : 192.168.56.116
Replication(subscription) : 192.168.56.117
Master(publication)
-- wal 데이터에 logical replication에 필요한 정보를 저장하기 위한 레벨 설정
echo "wal_level = logical" >> $PGDATA/postgresql.conf
-- 외부에서 접속하기 위한 설정
echo "host all all 0.0.0.0/0 md5" >> $PGDATA/pg_hba.conf
-- 테스트 데이터 베이스 생성
CREATE DATABASE test ;
-- Replication 유저 생성
CREATE USER repl WITH REPLICATION PASSWORD 'repl1122' ;
GRANT ALL PRIVILEGES ON DATABASE test TO repl;
-- 테스트 테이블 생성
\c test postgres
CREATE TABLE tb1 ( id int primary key , name varchar(10) ) ;
-- 테스트 데이터베이스 안의 public 테이블인 tb1에 대한 권한 부여
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO repl;
-- publication 설정
CREATE PUBLICATION pub_tb1 FOR TABLE tb1;
CREATE PUBLICATION
Replication(subscription)
-- 테스트 데이터 베이스 생성
CREATE DATABASE test ;
-- 테스트 테이블 생성
\c test postgres
CREATE TABLE tb1 ( id int primary key, name varchar(10) ) ;
-- subscription 설정 (master의 publication 이름과 Replication 유저 정보 입력)
CREATE SUBSCRIPTION sub_tb1 CONNECTION 'host=192.168.56.116 user=repl password=repl1122 port=5411 dbname=test' PUBLICATION pub_tb1;
NOTICE: created replication slot "sub_tb1" on publisher
CREATE SUBSCRIPTION
Master(publication)
-- 테스트 데이터 입력
\c test postgres
INSERT INTO tb1 VALUES ( 1 , 'name1' ) ;
SELECT * FROM tb1 ;
id | name
----+-------
1 | name1
(1 row)
Replication(subscription)
-- 데이터 확인
\c test postgres
SELECT * FROM tb1 ;
id | name
----+-------
1 | name1
(1 row)
-- 테스트 데이터 입력
\c test postgres
TRUNCATE TABLE tb1 ;
SELECT * FROM tb1 ;
id | name
----+------
(0 rows)
Replication(subscription)
-- 데이터 확인
\c test postgres
SELECT * FROM tb1 ;
id | name
----+------
(0 rows)
2022-04-19 18:11:04 KST [2130]: [2-1] user=,db=,remote=LOG: logical replication table synchronization worker for subscription "sub_tb1", table "tb1" has finished
-- 테스트 데이터 입력
\c test postgres
INSERT INTO tb1 VALUES ( 1 , 'name1' ) ;
SELECT * FROM tb1 ;
id | name
----+-------
1 | name1
(1 row)
Replication(subscription)
-- 데이터 확인
\c test postgres
SELECT * FROM tb1 ;
id | name
----+-------
1 | name1
(1 row)