MySQL이나 PostgreSQL를 운영하면서 버전 업그레이드나 서버 교체 등의 작업을 할 때
원본 클러스터 외에 신규 클러스터를 생성하여 복제를 걸어놓고
신규 클러스터로 커넥션을 모두 옮기면서 복제를 끊고 신규 클러스터로 서비스하는 작업들을 많이 하곤 합니다.

이런 작업은 AWS Aurora-PostgreSQL를 사용할 때도 마찬가지인데요
다만 Aurora-PostgreSQL은 physical replication slot을 사용하는 streaming replication을 지원하지 않고
logical replication만을 지원하고 있습니다.
매번 미뤄왔던 Logical replication을 이번 기회에 정리해보았습니다

PostgreSQL Replication

PostgreSQL의 Replication 방식에는 아래와 같이 세가지가 있으며 이 중 Aurora PostgreSQL은 logical replication 방식만 지원하고 있습니다. 참고로 aurora가 아닌 일반 RDS의 read replica는 streaming replication으로 구현됩니다

Log shipping
streaming replication
logical replication

자세한 건 아래 링크 참고
https://kimdubi.github.io/postgresql/postgresql_replication/

logical replication 이란?

Source DB에서 복제할 내용을 publication 하면 Target DB가 그 내용을 Subscription 하여 복제할 내용을 가져오는 방식입니다
그 외 특징으로는

기본 베이스는 PK 기반의 복제이기 때문에 Source DB의 테이블은 PK가 꼭 있어야 합니다.
기존 MySQL의 Replication이나 PostgreSQL Streaming replication 처럼 binary , transaction log 기반의 복제에 비해 PubSub 기반의 logical replication이기 때문에 훨씬 더 유연하다는 장점이 있습니다.
Subscription 하는 TargetDB는 복제와 상관없이 Write 트래픽을 받을 수 있습니다. Source DB와 스키마가 100% 일치하지 않아도 복제가 가능합니다.
Streaming replication과는 달리 Logical Replication은 서로 다른 버전끼리의 복제가 가능합니다.
debezium 같은 kafka 커넥터도 Logical replication 기반입니다
data 를 transforming하여 이관할 수 있습니다.
- 15버전부터는 pubsub에 where조건도 추가하여 원하는 데이터만 pub / sub 할 수 있습니다.
- 전체 데이터를 대상으로 복제하는 Streaming replication 과는 달리, 필요한 특정 테이블만 복제가 가능합니다.
PubSub의 기본원리와 마찬가지로 하나의 Publication에 여러개의 Subscription이 가능합니다. (여러 Target DB, 여러 Source DB 가 붙을 수 있음)
- 하나의 Target DB가 여러개 Source DB를 Subscription 하여 통합 DB를 구성할 수 있습니다.
- MSA 아키텍처에서 주문 shard 통합 DB 같은 구성이 쉽습니다.

반면에 단점으로는

Publishing하는 Source DB와 Subscription 하는 Target DB의 테이블명이 동일해야 합니다.
복제 대상 테이블은 반드시 PK 혹은 UK가 필요합니다.
양방향 Replication은 지원하지 않습니다
DDL은 복제되지 않습니다 (truncate 포함)
sequence 데이터도 복제되지 않습니다
LOB 같은 large object는 복제되지 않습니다
Subscription Table은 Publishing 원본 테이블보다 컬럼이나, 인덱스를 더 가져도 상관없지만, Publication과 컬럼 이름이나 타입은 최소한 매칭이 되어야합니다
RDS에서는 super user 권한이 필요하여 보안 관련 문제가 있을 수 있습니다

Logical replication 테스트

RDS는 파라미터그룹에서 rds.logical_replication = 1 설정만 하고 아래 config, pg_hba.conf 부분은 스킵하면 됩니다.

config - native postgresql

* postgresql.conf (source / target )

listen_addresses = '*'
wal_level = logical
max_wal_senders = 8 ## source
max_replication_slots = 4
max_logical_replication_workers = 4 ## target
max_worker_processes = 8 ## target


* pg_hba.conf ( RDS는 스킵 )

# TYPE DATABASE USER ADDRESS METHOD
host all postgres 172.17.0.1/32 trust


* listen_address / pg_hba.conf 가 빠지면 아래와 같은 접속 에러가 발생한다

[postgres@0d3b57e1fae5 PGSQL]$ psql -U postgres -h 172.17.0.4 -d postgres -p 5432
psql: error: could not connect to server: Connection refused
Is the server running on host "172.17.0.4" and accepting
TCP/IP connections on port 5432?

[postgres@0d3b57e1fae5 PGSQL]$ psql -U postgres -h 172.17.0.4 -d postgres -p 5432
psql: error: FATAL: no pg_hba.conf entry for host "172.17.0.2", user "postgres", database "postgres"

config - RDS

* postgresql.conf 파라미터그룹 ( source / target )

listen_addresses = '*'
rds.logical_replication = 1

source DDL schema migration

[postgres@source]$ pg_dumpall -h localhost -s > pg_schema.sql
[postgres@target]$ psql -h localhost -f pg_schema.sql

=> logical replication은 DDL은 반영하지 않기 때문에 logical replication 걸기 전, Target에 source와 동일한 DDL을 미리 반영해놔야 합니다.

Source 에서 Publication 생성

testdb=# CREATE PUBLICATION test_pub FOR ALL TABLES;
CREATE PUBLICATION

testdb=# select * from pg_publication;
pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate
----------+----------+--------------+-----------+-----------+-----------+-------------
test_pub | 10 | t | t | t | t | t
(1 row)

Target에서 Subscription 생성

CREATE SUBSCRIPTION test_sub CONNECTION 'host=172.17.0.4 dbname=testdb user=postgres' PUBLICATION test_pub ;

logical replication 진행 중 로그

* Source DB log
2022-07-19 13:29:09.427 UTC [1148] LOG: logical decoding found consistent point at F/A036550
2022-07-19 13:29:09.427 UTC [1148] DETAIL: There are no running transactions.
2022-07-19 13:29:09.437 UTC [1149] LOG: duration: 0.299 ms statement: SELECT pg_catalog.set_config('search_path', '', false);
2022-07-19 13:29:09.437 UTC [1149] LOG: starting logical decoding for slot "test_sub"
2022-07-19 13:29:09.437 UTC [1149] DETAIL: Streaming transactions committing after F/A036588, reading WAL from F/A036550.
2022-07-19 13:29:09.438 UTC [1149] LOG: logical decoding found consistent point at F/A036550
2022-07-19 13:29:09.438 UTC [1149] DETAIL: There are no running transactions.

2022-07-19 13:29:09.456 UTC [1151] LOG: duration: 0.493 ms statement: SELECT pg_catalog.set_config('search_path', '', false);
2022-07-19 13:29:09.457 UTC [1151] LOG: duration: 0.290 ms statement: BEGIN READ ONLY ISOLATION LEVEL REPEATABLE READ
2022-07-19 13:29:09.458 UTC [1150] LOG: duration: 1.257 ms statement: SELECT c.oid, c.relreplident, c.relkind FROM pg_catalog.pg_class c INNER JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) WHERE n.nspname = 'public' AND c.relname = 'claim'
2022-07-19 13:29:09.460 UTC [1150] LOG: duration: 1.686 ms statement: SELECT a.attname, a.atttypid, a.atttypmod, a.attnum = ANY(i.indkey) FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_index i ON (i.indexrelid = pg_get_replica_identity_index(17098)) WHERE a.attnum > 0::pg_catalog.int2 AND NOT a.attisdropped AND a.attrelid = 17098 ORDER BY a.attnum
2022-07-19 13:29:09.462 UTC [1150] LOG: duration: 0.203 ms statement: COPY public.claim TO STDOUT
2022-07-19 13:29:09.462 UTC [1150] LOG: duration: 0.135 ms statement: COMMIT

2022-07-19 13:29:09.469 UTC [1151] LOG: logical decoding found consistent point at F/A0365C0
2022-07-19 13:29:09.469 UTC [1151] DETAIL: There are no running transactions.
2022-07-19 13:29:09.470 UTC [1152] LOG: duration: 0.404 ms statement: SELECT pg_catalog.set_config('search_path', '', false);
2022-07-19 13:29:09.471 UTC [1151] LOG: duration: 0.987 ms statement: SELECT c.oid, c.relreplident, c.relkind FROM pg_catalog.pg_class c INNER JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) WHERE n.nspname = 'public' AND c.relname = 'internal_inventory'
2022-07-19 13:29:09.471 UTC [1152] LOG: duration: 0.280 ms statement: BEGIN READ ONLY ISOLATION LEVEL REPEATABLE READ
2022-07-19 13:29:09.472 UTC [1151] LOG: duration: 1.023 ms statement: SELECT a.attname, a.atttypid, a.atttypmod, a.attnum = ANY(i.indkey) FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_index i ON (i.indexrelid = pg_get_replica_identity_index(17050)) WHERE a.attnum > 0::pg_catalog.int2 AND NOT a.attisdropped AND a.attrelid = 17050 ORDER BY a.attnum
2022-07-19 13:29:09.473 UTC [1151] LOG: duration: 0.118 ms statement: COPY public.internal_inventory TO STDOUT
2022-07-19 13:29:09.473 UTC [1151] LOG: duration: 0.079 ms statement: COMMIT

.
.
.
.

=> 복제 대상 테이블을 하나하나 COPY TO STDOUT 수행함. 작업 도중 DML, select 가능함 , DDL은 불가

Target DB Log

2022-07-19 13:29:09.431 UTC [1261] LOG: duration: 41.724 ms statement: CREATE SUBSCRIPTION test_sub CONNECTION 'host=172.17.0.4 dbname=testdb user=postgres' PUBLICATION test_pub;
2022-07-19 13:29:09.434 UTC [1414] LOG: logical replication apply worker for subscription "test_sub" has started
2022-07-19 13:29:09.440 UTC [1415] LOG: logical replication table synchronization worker for subscription "test_sub", table "claim" has started
2022-07-19 13:29:09.451 UTC [1416] LOG: logical replication table synchronization worker for subscription "test_sub", table "internal_inventory" has started
2022-07-19 13:29:09.464 UTC [1415] LOG: logical replication table synchronization worker for subscription "test_sub", table "claim" has finished
2022-07-19 13:29:09.467 UTC [1417] LOG: logical replication table synchronization worker for subscription "test_sub", table "internal_sku" has started
2022-07-19 13:29:09.473 UTC [1416] LOG: logical replication table synchronization worker for subscription "test_sub", table "internal_inventory" has finished
2022-07-19 13:29:09.477 UTC [1418] LOG: logical replication table synchronization worker for subscription "test_sub", table "internal_stock" has started
2022-07-19 13:29:09.492 UTC [1417] LOG: logical replication table synchronization worker for subscription "test_sub", table "internal_sku" has finished
2022-07-19 13:29:09.496 UTC [1419] LOG: logical replication table synchronization worker for subscription "test_sub", table "location" has started
2022-07-19 13:29:09.502 UTC [1418] LOG: logical replication table synchronization worker for subscription "test_sub", table "internal_stock" has finished
2022-07-19 13:29:09.505 UTC [1420] LOG: logical replication table synchronization worker for subscription "test_sub", table "tb_test" has started
2022-07-19 13:29:09.517 UTC [1419] LOG: logical replication table synchronization worker for subscription "test_sub", table "location" has finished
2022-07-19 13:29:09.520 UTC [1421] LOG: logical replication table synchronization worker for subscription "test_sub", table "user_qna" has started
2022-07-19 13:29:09.553 UTC [1421] LOG: logical replication table synchronization worker for subscription "test_sub", table "user_qna" has finished
2022-07-19 13:29:09.557 UTC [1422] LOG: logical replication table synchronization worker for subscription "test_sub", table "user_qna_process" has started
2022-07-19 13:29:09.586 UTC [1422] LOG: logical replication table synchronization worker for subscription "test_sub", table "user_qna_process" has finished
2022-07-19 13:29:31.004 UTC [725] LOG: checkpoints are occurring too frequently (12 seconds apart)
2022-07-19 13:29:31.004 UTC [725] HINT: Consider increasing the configuration parameter "max_wal_size".
2022-07-19 13:29:45.536 UTC [725] LOG: checkpoints are occurring too frequently (14 seconds apart)
2022-07-19 13:29:45.536 UTC [725] HINT: Consider increasing the configuration parameter "max_wal_size".
2022-07-19 13:29:59.282 UTC [725] LOG: checkpoints are occurring too frequently (14 seconds apart)
2022-07-19 13:29:59.282 UTC [725] HINT: Consider increasing the configuration parameter "max_wal_size".
.
.
.
2022-07-19 13:29:09.505 UTC [1420] LOG: logical replication table synchronization worker for subscription "test_sub", table "tb_test" has finished

=> replication worker 개수만큼 병렬로 복제 수행되며 별도의 완료 로그는 없음

모니터링 관련 테이블 조회를 통해 따로 확인해야함

복제 완료 확인

target - pg_subscription_rel

testdb=# select srsubid, pg_filenode_relation(0,srrelid), srsublsn, srsubstate from pg_subscription_rel;
srsubid | pg_filenode_relation | srsublsn | srsubstate
---------+----------------------+-----------+------------
16512 | claim | F/A0365C0 | r
16512 | internal_inventory | F/A0365F8 | r
16512 | internal_sku | F/A036630 | r
16512 | internal_stock | F/A036668 | r
16512 | location | F/A0366A0 | r
16512 | user_qna | F/A036710 | r
16512 | user_qna_process | F/A036748 | r
16512 | tb_test | F/A0388B0 | r
(8 rows)

=> 위에서 srsubstate 값이 s,d 면 복제가 초기 sync작업이 진행중인 것
초기 sync완료 후 변경분만 복제하는 상태가 r 이므로 모든 테이블이 r이 되면 정상

source - pg_stat_replication

testdb=# SELECT application_name, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) lag FROM pg_stat_replication;
application_name | lag
------------------+-----
test_sub | 0
(1 row)

=> master에서 replication lag 이 얼마나 발생하고 있는지를 조회해서 확인 가능 (0이면 복제 다 따라갔다)

후속작업 seqeunce 맞춰주기

source seqeunce

testdb=# select last_value from tb_test_ai_seq;
last_value
------------
180642450
(1 row)

target sequence

testdb=# select last_value from tb_test_ai_seq;
last_value
------------
1

testdb=# select * from tb_test order by ai desc limit 1;
ai | a | b | c | d | ee
-----------+----+----+----+----+----
180642450 | hi | hi | hi | hi | hi
(1 row)

testdb=# insert into tb_test (a,b,c,d,ee) values('hi','hi','hi','hi','hi');
INSERT 0 1

testdb=# select * from tb_test where ai=1;
ai | a | b | c | d | ee
----+----+----+----+----+----
1 | hi | hi | hi | hi | hi

=> sequence 값은 가져오지 않기 때문에 복제해온 데이터는 동일하지만 target db에서 데이터를 넣을 때 시퀀스 값이 문제가 된다
그래서 따로 수동으로 맞춰줘야함

sequence 맞춰주기

select concat('select setval(''',split_part(column_default,'''',2),''',(select max(',column_name,') from ',table_name,'));') as tt
from information_schema.columns where column_default like 'nextval%';
 
                                              tt
------------------------------------------------------------------------------------------------
select setval('ab_permission_view_id_seq',(select max(id) from ab_permission_view));
select setval('ab_permission_view_role_id_seq',(select max(id) from ab_permission_view_role));
select setval('ab_register_user_id_seq',(select max(id) from ab_register_user));
 
-- ===================
-- sequence 동기화 실행
-- ===================
testdb=> select setval('ab_register_user_id_seq',(select max(id) from ab_register_user));
setval
--------
14
(1 row)

작업 완료 후 정리

* Source DB

testdb=# select pg_drop_replication_slot('test_sub');
testdb=# drop publication test_pub;

Target DB

testdb=# ALTER SUBSCRIPTION test_sub DISABLE;
ALTER SUBSCRIPTION
testdb=# ALTER SUBSCRIPTION test_sub set (slot_name=none);
ALTER SUBSCRIPTION
testdb=# DROP SUBSCRIPTION test_sub;
DROP SUBSCRIPTION

이슈 별 처리 방법

SUBSCRIPTION 생성이 안될 땐 아래처럼 수동으로 생성한다

SOURCEDB=# SELECT pg_create_logical_replication_slot('test_repl_slot', 'pgoutput');
TARGETDB=# CREATE SUBSCRIPTION test_sub CONNECTION 'host=172.17.0.4 dbname=testdb user=postgres' PUBLICATION test_pub WITH (slot_name=test_repl_slot,create_slot=false);

복제 duplicate 충돌나면 ?

target=# insert into tb_test (ai,a,b,c,d,ee) values (1,'hi','hi','hi','hi','hi');
source=# insert into tb_test (ai,a,b,c,d,ee) values (1,'hi','hi','hi','hi','hi');
INSERT 0 1

### target db duplicate key error
2022-07-19 14:31:38.057 UTC [1414] ERROR: duplicate key value violates unique constraint "tb_test_pkey"
2022-07-19 14:31:38.057 UTC [1414] DETAIL: Key (ai)=(1) already exists.
2022-07-19 14:31:38.060 UTC [722] LOG: background worker "logical replication worker" (PID 1414) exited with exit code 1
2022-07-19 14:31:38.065 UTC [1688] LOG: logical replication apply worker for subscription "test_sub" has started

### source db는 logical replication 재시작 로그가 계속 떨어짐
2022-07-19 14:49:57.790 UTC [1671] LOG: starting logical decoding for slot "test_sub"
2022-07-19 14:49:57.790 UTC [1671] DETAIL: Streaming transactions committing after F/A03F040, reading WAL from F/A03E8A8.
2022-07-19 14:49:57.791 UTC [1671] LOG: logical decoding found consistent point at F/A03E8A8
2022-07-19 14:49:57.791 UTC [1671] DETAIL: There are no running transactions.

=> target에서 SELECT pg_replication_origin_advance() ;커맨드를 통해 재설정 해야함
(이 부분은 다시 정리 예정)

pg_dumpall -s 권한에러로 계정 생성 구문 백업 실패할 때

$ pg_dumpall -U master -s -h dbatest-pg4.rds.amazonaws.com -p 6432 -f commerce_bk.sql

pg_dumpall: error: query failed: ERROR:  permission denied for table pg_authid
pg_dumpall: error: query was: SELECT oid, rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, rolreplication, rolbypassrls, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, rolname = current_user AS is_current_user FROM pg_authid WHERE rolname !~ '^pg_' ORDER BY 2

=> –exclude-database rdsadmin , –no-role-password 옵션 추가한다
ex) pg_dumpall -U master –exclude-database rdsadmin –no-role-passwords -s -h localhost -p 5432 -f bk.sql
위 커맨드로 데이터를 export했을 때 export된 덤프 파일(“bk.sql”)의 내용을 텍스트 편집기를 통해 살펴보면 아래와 같이 비밀번호 설정 부분이 제외되서 export가 되기 때문에
이 부분을 직접 편집하고 혹시 rdsadmin 관련 내용이 있으면 지워줘야한다.

replication 모니터링 테이블

### Source - 생성한 publication과 설정 조회

testdb=# select * from pg_publication;
pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate
----------+----------+--------------+-----------+-----------+-----------+-------------
test_pub | 10 | t | t | t | t | t
(1 row)

puballtables : true인 경우 데이터베이스의 모든 테이블을 자동으로 publishing 대상에 포함합니다.
pubinsert : true인 경우 publishing하는 테이블에 대해 INSERT 작업이 복제됩니다.
pubupdate : true이면 publishing하는 테이블에 대해 UPDATE 작업이 복제됩니다.
pubdelete : true이면 publishing하는 테이블에 대해 DELETE 작업이 복제됩니다.

### Source - 복제할 대상 데이터를 담아두는 replication slot 조회, Replication이 현재 정상인지도 확인 가능 (active column)

testdb=# select * from pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_fl
ush_lsn
----------------+----------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+-------------
--------
test_repl_slot | pgoutput | logical | 16384 | testdb | f | f | | | 185645376 | F/A0364E0 | F/A036518
test_sub | pgoutput | logical | 16384 | testdb | f | t | 2113 | | 185645384 | F/A047580 | F/A0475B8
(2 rows)

Source - publication 중인 테이블 조회

testdb=# select * from pg_publication_tables;
pubname | schemaname | tablename
----------+------------+--------------------
test_pub | public | tb_test
test_pub | public | user_qna_process
test_pub | public | claim
test_pub | public | user_qna
test_pub | public | internal_sku
test_pub | public | internal_stock
test_pub | public | internal_inventory
test_pub | public | location
(8 rows)

Source - replication status 조회 ( show slave status )

testdb=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 2113
usesysid | 10
usename | postgres
application_name | test_sub
client_addr | 172.17.0.2
client_hostname |
client_port | 33042
backend_start | 2022-07-20 03:55:49.313368+00
backend_xmin |
state | streaming
sent_lsn | F/A0475B8
write_lsn | F/A0475B8
flush_lsn | F/A0475B8
replay_lsn | F/A0475B8
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async

sent_lsn : subscription에 전송한 마지막 WAL 위치 입니다.
write_lsn : 이 secondary 서버가 디스크에 기록한 마지막 WAL 위치입니다.
flush_lsn : 이 secondary 서버가 디스크에 플러시한 마지막 WAL 위치입니다.
replay_lsn : 이 secondary 서버의 데이터베이스에 재생된 마지막 WAL 위치입니다.
write_lag : 최근 WAL을 로컬로 플러시하고 이 대기 서버가 작성했다는 알림을 수신하는 데 경과된 시간입니다(아직 플러시하거나 적용하지 않음).
flush_lag : 최근 WAL을 로컬로 플러시하고 이 대기 서버가 작성하고 플러시했지만(아직 적용하지 않음) 알림을 수신하는 사이에 경과된 시간입니다.
replay_lag : 최근 WAL을 로컬로 플러시하고 이 대기 서버가 작성, 플러시 및 적용했다는 알림 수신 사이에 경과된 시간입니다.

testdb=# SELECT application_name, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) lag FROM pg_stat_replication;
application_name | lag
------------------+-----
test_sub | 0
(1 row)

target - subscription 받은 테이블들의 현재 상태 조회

testdb=# select srsubid, pg_filenode_relation(0,srrelid), srsublsn, srsubstate from pg_subscription_rel;
srsubid | pg_filenode_relation | srsublsn | srsubstate
---------+----------------------+-----------+------------
16512 | claim | F/A0365C0 | r
16512 | internal_inventory | F/A0365F8 | r
16512 | internal_sku | F/A036630 | r
16512 | internal_stock | F/A036668 | r
16512 | location | F/A0366A0 | r
16512 | user_qna | F/A036710 | r
16512 | user_qna_process | F/A036748 | r
16512 | tb_test | F/A0388B0 | r
(8 rows)

i – initial setting 상태
d – 데이터 복사 중
s – 동기화됨
r – Ready(일반 복제)
srsublsn은 s ~ r 상태에 대한 끝 LSN을 나타냄

커맨드 요약

rds.logical_replication 확인

postgres=> show rds.logical_replication;
 rds.logical_replication
-------------------------
 on
(1 row)

postgres=> show wal_level;
 wal_level
-----------
 logical
(1 row)

source DB schema dump & target에 import

source] $ pg_dumpall -U master -h dbatest-pg4.rds.amazonaws.com -p 5432  --exclude-database rdsadmin --no-role-passwords -s -f dbatest_source.sql
source] source dump파일에서 RDS 권한 관련 계정 부분 삭제 ( 위 이슈별처리방법 참고 )

target] $ psql -U master -h dbatest-pg-33.rds.amazonaws.com -p 5432 -f dbatest_source.sql
target] 위의 source dump 파일엔 계정의 패스워드가 없다. target에서 수동으로 계정의 패스워드를 설정해준다.

source publication 생성

postgres=> \c testdb
testdb=> CREATE PUBLICATION test_pub FOR ALL TABLES;

target subscription 생성

postgres=> \c testdb
testdb=> CREATE SUBSCRIPTION test_sub CONNECTION 'host=dbatest-pg4.rds.amazonaws.com port=6432 dbname=testdb user=master password=!dlatl00' PUBLICATION test_pub ;

복제 정상 확인

testdb=> SELECT application_name, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) lag FROM pg_stat_replication;
     application_name      | lag
---------------------------+-----
 test_sub                  | 136

testdb=>
testdb=> SELECT application_name, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) lag FROM pg_stat_replication;
     application_name      | lag
---------------------------+-----
 test_sub                  |  88

testdb=> SELECT application_name, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) lag FROM pg_stat_replication;
     application_name      | lag
---------------------------+-----
 test_sub                  |   0

sequence 맞춰주기

testdb=> select concat('select setval(''',split_part(column_default,'''',2),''',(select max(',column_name,') from ',table_name,'));') as tt
from information_schema.columns where column_default like 'nextval%';
                                             tt
--------------------------------------------------------------------------------------------
 select setval('business_model_code_id_seq',(select max(id) from business_model_code));
 select setval('mdm_sap_action_id_seq',(select max(id) from mdm_sap_action));
 select setval('seller_master_id_seq',(select max(id) from seller_master));
 select setval('seller_master_history_id_seq',(select max(id) from seller_master_history));
(4 rows)

testdb=>  select setval('business_model_code_id_seq',(select max(id) from business_model_code));
 select setval('mdm_sap_action_id_seq',(select max(id) from mdm_sap_action));
 select setval('seller_master_id_seq',(select max(id) from seller_master));
 select setval('seller_master_history_id_seq',(select max(id) from seller_master_history));
 setval
--------

(1 row)

 setval
--------
   1342
(1 row)

 setval
--------
    713
(1 row)

 setval
--------
   1828
(1 row)

어플리케이션 커넥션 변경
pub / sub 정리
완료

PostgreSQL Replication#

logical replication 이란?#

Logical replication 테스트#

config - native postgresql#

config - RDS#

source DDL schema migration #

Source 에서 Publication 생성#

Target에서 Subscription 생성#

logical replication 진행 중 로그#

Target DB Log#

복제 완료 확인#

후속작업 seqeunce 맞춰주기#

작업 완료 후 정리#

이슈 별 처리 방법#

replication 모니터링 테이블#

Source - publication 중인 테이블 조회#

Source - replication status 조회 ( show slave status )#

target - subscription 받은 테이블들의 현재 상태 조회#

커맨드 요약#