논리적 복제를 사용하여 PostgreSQL용 RDS에서 테이블을 복제하려면 어떻게 해야 하나요?

6분 분량

PostgreSQL용 Amazon Relational Database Service(RDS) 내 여러 데이터베이스 간에 확장을 사용하지 않고 테이블을 복제하고 싶습니다.

해결 방법

논리적 복제의 일반적인 사용 사례는 Amazon RDS for PostgreSQL 인스턴스 두 개 사이에서 일련의 테이블을 복제하는 것입니다. PostgreSQL용 RDS는 PostgreSQL 10.4 이상 버전에서 논리적 복제를 지원합니다. Amazon Aurora PostgreSQL 호환 에디션 버전 2.2.0 이후 버전의 경우 PostgreSQL 10.6 이후 버전에서 논리적 복제를 지원합니다.

여기에 제공된 해결 방법에서는 PostgreSQL용 RDS에서 논리적 복제를 사용하여 소스 테이블 두 개를 대상 테이블 두 개에 복제합니다. 논리적 복제의 경우, 우선 이미 소스 테이블에 있는 데이터를 1차로 로드한 다음 진행 중인 변경 사항을 계속 복제합니다.

논리적 복제 활성화

PostgreSQL용 RDS에서 논리적 복제를 켜려면, 사용자 지정 파라미터 그룹을 수정하여 rds.logical_replication을 1로 설정하고, rds.logical_replication을 DB 인스턴스에 연결합니다. 사용자 지정 파라미터 그룹이 DB 인스턴스에 연결되어 있는 경우, 파라미터 그룹을 업데이트하여 rds.logical_replication을 1로 설정합니다. rds.logical_replication 파라미터는 DB 인스턴스를 재부팅해야 적용되는 정적 파라미터입니다. DB 인스턴스가 재부팅되면 wal_level 파라미터가 logical로 설정됩니다.

wal_level 및 rds.logical_replication 값 확인:

postgres=> SELECT name,setting FROM pg_settings WHERE name IN ('wal_level','rds.logical_replication');
          name           | setting
-------------------------+---------
 rds.logical_replication | on
 wal_level               | logical
(2 rows)

소스 DB 인스턴스의 소스 데이터베이스에 연결

PostgreSQL용 소스 RDS DB 인스턴스의 소스 데이터베이스에 연결합니다. 소스 테이블 생성:

source=> CREATE TABLE reptab1 (slno int primary key);
CREATE TABLE
source=> CREATE TABLE reptab2 (name varchar(20));
CREATE TABLE

소스 테이블에 데이터 삽입:

source=> INSERT INTO reptab1 VALUES (generate_series(1,1000));
INSERT 0 1000
source=> INSERT INTO reptab2 SELECT SUBSTR ('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',((random()*(36-1)+1)::integer),1) FROM generate_series(1,50);
INSERT 0 50

소스 테이블에 게시 생성

소스 테이블에 대한 게시를 생성합니다. SELECT 쿼리를 사용하여 생성된 게시의 세부 정보 확인:

source=> CREATE PUBLICATION testpub FOR TABLE reptab1,reptab2;
CREATE PUBLICATION
source=> SELECT * FROM pg_publication;
  oid   | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot
--------+---------+----------+--------------+-----------+-----------+-----------+-------------+------------
 115069 | testpub |    16395 | f            | t         | t         | t         | t           | f
(1 row)

게시에 소스 테이블이 추가되었는지 확인:

source=> SELECT * FROM pg_publication_tables;
 pubname | schemaname | tablename
---------+------------+-----------
 testpub | public     | reptab1
 testpub | public     | reptab2
(2 rows)

참고: 데이터베이스 내 모든 테이블을 복제하려면 다음 명령 실행:

CREATE PUBLICATION testpub FOR ALL TABLES;

대상 데이터베이스에 연결하고 대상 테이블 생성

대상 DB 인스턴스에 있는 대상 데이터베이스에 연결합니다. 소스 테이블과 이름이 같은 대상 테이블을 생성합니다. 대상 테이블에서 SELECT 쿼리를 실행하여 대상 테이블에 데이터가 없는지 확인:

target=> CREATE TABLE reptab1 (slno int primary key);
CREATE TABLE
target=> CREATE TABLE reptab2 (name varchar(20));
CREATE TABLE
target=> SELECT count(*) FROM reptab1;
 count
-------
     0
(1 row)
target=> SELECT count(*) FROM reptab2;
 count
-------
     0
(1 row)

대상 데이터베이스에서 구독을 생성하고 확인

대상 데이터베이스에서 구독을 생성합니다. SELECT 쿼리를 사용하여 구독이 활성화되었는지 확인:

target=> CREATE SUBSCRIPTION testsub CONNECTION 'host=<source RDS/host endpoint> port=5432 dbname=<source_db_name> user=<user> password=<password>' PUBLICATION testpub;
NOTICE:  Created replication slot "testsub" on publisher
CREATE SUBSCRIPTION
target=> SELECT oid,subname,subenabled,subslotname,subpublications FROM pg_subscription;
  oid  | subname | subenabled | subslotname | subpublications
-------+---------+------------+-------------+-----------------
 16434 | testsub | t          | testsub     | {testpub}
(1 row)

중요: 데이터베이스 로그에 사용자 이름과 암호가 일반 텍스트 버전으로 저장되지 않도록 방지하려면 구독을 생성하기 전에 다음과 같은 명령 실행:

target=> SET log_min_messages to 'PANIC';
SET
target=> SET log_statement to NONE;
SET

구독이 생성되면 해당 구독이 소스 테이블에 있는 모든 데이터를 대상 테이블에 로드합니다. 대상 테이블에서 SELECT 쿼리를 실행하여 초기 데이터가 다음을 로드하는지 확인:

target=> SELECT count(*) FROM reptab1;
 count
-------
  1000
(1 row)
target=> SELECT count(*) FROM reptab2;
 count
-------
    50
(1 row)

소스 데이터베이스의 복제 슬롯 확인

대상 데이터베이스에서 구독을 생성하면 소스 데이터베이스에 복제 슬롯이 생깁니다. 소스 데이터베이스에서 다음과 같은 SELECT 쿼리를 실행하여 복제 슬롯 세부 정보 확인:

source=> SELECT * FROM pg_replication_slots;
 slot_name |  plugin  | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size
 ----------+----------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+------------+---------------
 testsub   | pgoutput | logical   | 115048 | source   | f         | t      |        846 |      |         6945 | 58/B4000568 | 58/B40005A0         | reserved   |
(1 row)

소스 테이블에서 복제 테스트

복제되는 소스 테이블에서 데이터를 변경하면 대상 테이블에 복제되는지 테스트하기 위해 소스 테이블에 행 삽입:

source=> INSERT INTO reptab1 VALUES(generate_series(1001,2000));
INSERT 0 1000
source=> INSERT INTO reptab2 SELECT SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',((random()*(36-1)+1)::integer),1) FROM generate_series(1,50);
INSERT 0 50
source=> SELECT count(*) FROM reptab1;
 count
-------
  2000
(1 row)
source=> SELECT count(*) FROM reptab2; count
-------
   100
(1 row)

대상 테이블의 행 수를 확인하여 복제 테스트

대상 테이블의 행 수를 확인하여 대상 테이블에 새 삽입이 복제되고 있는지 확인:

target=> SELECT count(*) FROM reptab1;
 count
-------
  2000
(1 row)
target=> SELECT count(*) FROM reptab2;
 count
-------
   100
(1 row)

논리적 복제 정리 및 비활성화

복제가 완료되고 더 이상 필요하지 않으면 논리적 복제를 정리하고 비활성화합니다. 비활성 복제 슬롯이 있으면 소스 DB 인스턴스에 WAL 파일이 쌓이게 됩니다. WAL 파일로 스토리지가 가득 차 중단을 초래할 수 있습니다.

대상 데이터베이스에서 구독 삭제:

target=> DROP SUBSCRIPTION testsub;
NOTICE:  Dropped replication slot "testsub" on publisher
DROP SUBSCRIPTION
target=> SELECT * FROM pg_subscription;
oid | subdbid | subname | subowner | subenabled | subconninfo | subslotname | subsynccommit | subpublications
----+---------+---------+----------+------------+-------------+-------------+---------------+-----------------
(0 rows)

참고: 구독을 삭제하면 해당 구독이 생성한 복제 슬롯도 삭제됩니다.

소스 데이터베이스에서 복제 슬롯이 삭제되었는지 확인하려면 소스에서 다음과 같은 SELECT 쿼리 실행:

source=> SELECT * FROM pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size
----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+------------+--------------
(0 rows)

게시를 삭제합니다. 게시가 삭제되었는지 확인:

source=> DROP PUBLICATION testpub;
DROP PUBLICATION
source=> SELECT * FROM pg_publication;
 oid | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot
-----+---------+----------+--------------+-----------+-----------+-----------+-------------+------------
(0 rows)
source=> SELECT * FROM pg_publication_tables;
 pubname | schemaname | tablename
---------+------------+-----------
(0 rows)

DB 인스턴스에 연결된 사용자 지정 파라미터 그룹에서 rds.logical_replication을 0으로 수정합니다. DB 인스턴스가 논리적 복제를 사용하지 않는 경우, DB 인스턴스를 재부팅합니다(필요한 경우).

사용량에 따라 max_replication_slots, max_wal_senders, max_logical_replication_workers, max_worker_processes 및 max_sync_workers_per_subscription 등을 검토합니다.

참고: 다음과 같은 명령을 실행하면 비활성 복제 슬롯이 있는지 확인하고 각 슬롯의 크기를 판단하여 필요한 경우 슬롯을 삭제합니다.

SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),restart_lsn)) AS replicationSlotLag, active FROM pg_replication_slots ;

SELECT pg_drop_replication_slot('Your_slotname_name');