Backend/데이터베이스

[PostgreSQL] Logical Replication로 데이터 Migration하기

냠냠:) 2024. 10. 13. 17:34

개요

PostgreSQL의 Logical Replication 기능을 사용하여 다른 PostgreSQL DB로 데이터 Migration 하기

 

 

Logical Replication이란?

Logical Replication은 PostgreSQL의 데이터베이스 간에 특정 테이블의 변경 사항을 동기화할 수 있는 기능이다. 기존의 Physical Replication과 달리, 테이블 단위(또는 테이블 그룹 단위)로 데이터 변경 사항을 추출하여 이를 다른 PostgreSQL 서버에 전송한다. 이를 통해 전체 데이터베이스가 아닌 일부 테이블만 복제할 수 있다.

 

Logical Replication은 WAL (Write-Ahead Logging)을 decoding하여 테이블의 변경 사항을 추출하는 방식으로 동작한다. PostgreSQL의 WAL은 기본적으로 데이터베이스의 모든 변경 사항을 기록하는데, Logical Replication은 이 WAL을 해석하여 테이블 단위로 변경된 데이터를 추출한다. 추출된 데이터는 subscriber로 전송되며, 해당 테이블의 데이터를 동기화하는 데 사용된다.

 

Logical Replication은 초기 Snapshot 복제를 통해 지정된 테이블의 데이터를 먼저 동기화한 후, WAL 로그를 기반으로 변경 사항을 실시간으로 전달하여 동기화를 유지한다. Snapshot 복제 후에는 트랜잭션 단위로 데이터 변경 사항만 전송되므로 네트워크 부하가 적다. 이를 통해 Publisher와 Subscriber 간의 데이터 일관성을 지속적으로 유지할 수 있다.

 
 

기존 Physical Replication의 한계

- 동일한 PostgreSQL 버전 필요

- 다른 플랫폼 간의 복제 불가(Window <-> Linux)

- 데이터베이스 수준의 복제만 가능하여 일부 데이터만 동기화 불가

- 슬레이브 DB에서 독립적으로 변경 작업 불가

 

 

주의사항

- Logical Replication은 PostgreSQL 간에만 동작한다. 다른 DBMS와는 호환되지 않으므로 양쪽 모두 PostgreSQL이어야 한다.

- Logical Replication은 PostgreSQL 10 버전 이상에서 사용할 수 있다.

- Logical Replication은 테이블의 데이터 변경 사항을 복제하지만, 스키마 변경 사항(예: 테이블의 구조 변경, 인덱스 추가, 시퀀스 등)은 자동으로 복제되지 않는다.

 

작업 전 사전 준비

Publisher DB

(RDS 경우) 파라미터 그룹에서 변경 후 재시작

(설치형 경우) postgresql.conf 수정 후 psql 재시작

  • postgresql.conf 설정 중 wal_level을 logical로 변경
SHOW wal_level; -- 현재 wal_level 확인
  • max_replication_slots를 예상되는 Subscription 개수에 맞게 설정
show max_replication_slots; -- 현재 slot 개수 확인
  • max_wal_senders (WAL 로그를 외부로 전송할 수 있는 프로세스 수를 설정, max_replication_slots보다 높게 설정)
SHOW max_wal_senders -- replication(논리 + 물리) 가능 개수 확인

Subscriber DB

  • max_replication_slots를 예상되는 Subscription 개수에 맞게 설정
show max_replication_slots;
  • max_logical_replication_workers를 예상되는 Subscription 개수 + 1개로 설정
SHOW max_logical_replication_workers
  • max_worker_processes를 max_logical_replication_workers + 1개로 설정
SHOW max_worker_processes; -- 현재 worker 프로세스 개수 확인

 

 

Publication

- 특정 테이블, 테이블 그룹에서 발생하는 변경사항을 묶어 놓은 것

- 테이블의 스키마(DDL)와 독립적으로 단순히 데이터를 복제하는 역할만 한다.

- 기본적으로 모든 작업(삽입, 수정, 삭제, 전체 삭제)이 복제되지만, 원하는 작업만 선택적으로 복제할 수도 있다.

- 레플리카 식별자를 꼭 가져야한다. 레플리카 식별자는 테이블에서 특정 행을 식별하는데 사용하는 키. 기본적으로는 PK가 사용되며, 만약 기본 키가 없다면 테이블의 다른 유일한 인덱스를 사용할 수 있다.

-- publisher db에서 수행
CREATE PUBLICATION my_publication for table table_1, table_2

-- 전체 테이블 복제 시
CREATE PUBLICATION my_publication for ALL TABLES

-- Insert Operation만 복제하고 싶은 경우
CREATE PUBLICATION my_publication for table table_1, table_2
WITH (publish = 'insert');

 

[동일 DB 복제] 동일 DB 서버 내에서 Logical Replication을 하고 싶다면, replication_slot을 수동으로 설정하면 된다.

-- source database
CREATE PUBLICATION test_publication FOR TABLE test_table;
SELECT pg_create_logical_replication_slot('test_replication_slot', 'pgoutput');

-- target database
CREATE SUBSCRIPTION test_subscription
    CONNECTION 'host=db_host port=5432 dbname=pub_db user=repliation_user password=repliation_password'
    PUBLICATION test_publication
    WITH (create_slot = false, SLOT_NAME ='test_replication_slot');
    
    
-- 삭제 시
# 1. 비활성화
ALTER SUBSCRIPTION test_subscription DISABLE;

# 2. 슬롯 해제
ALTER SUBSCRIPTION test_subscription SET (slot_name = NONE);

# 3. subscription 삭제
drop subscription test_subscription;

 

Subscription

- Publication을 받아 Subscriber DB에 적용하는 과정

- 스키마 복제는 지원하지 않기 때문에 동일한 테이블 구조를 미리 정의해야 한다.

- 테이블 이름과 컬럼 이름이 일치해야 정상적으로 동작한다. 그러나 컬럼 순서나 데이터 타입은 일치하지 않아도 된다.

- Replication에 사용되는 remote_user는 publisher db에서 admin 혹은 replication 권한을 가져야한다.

- Subscriber는 여러 Subscription을 동시에 가질 수 있다.

-- Subscriber db에서 수행

-- Subscription 생성 
CREATE SUBSCRIPTION my_subscription 
CONNECTION 'dbname=remote_db host=remote_host user=remote_user password=remote_password' 
PUBLICATION my_publication;


-- Subscription 삭제
DROP SUBSCRIPTION my_subscription;

 

[일시중지] Subscription을 일시 중단/재개 하고 싶다면 Subscription을 삭제하지말고 disable/enable 하는 것을 추천한다.

ALTER SUBSCRIPTION my_subscription DISABLE;
ALTER SUBSCRIPTION my_subscription ENABLE;

 

[데이터 Copy 설정] 초기 데이터를 가져오지 않고, Subscription 생성부터의 데이터를 복제하고 싶다면 copy_data 옵션을 사용하면 된다.

CREATE SUBSCRIPTION my_subscription 
CONNECTION 'dbname=remote_db host=remote_host user=remote_user password=remote_password' 
PUBLICATION my_publication
WITH (copy_data = false);

 

 

Monitoring

- Logical Replication 설정 후 관련 설정들을 조회하는 쿼리

Publisher DB

-- Publication 조회
SELECT * FROM pg_publication;

-- Replication 연결 확인
SELECT * FROM pg_stat_replication;

-- Publication slot 조회
SELECT * FROM pg_replication_slots;

Subscriber DB

-- Subcription 조회
SELECT * FROM pg_subscription;

-- Subcription 상태 조회
SELECT * FROM pg_stat_subscription;

-- Publication slot 조회 
SELECT * FROM pg_replication_slots;

-- replication worker 확인
show max_logical_replication_workers;

 

 

Error

Case 1. has neither REPLICA IDENTITY index nor PRIMARY KEY and published relation does not have REPLICA IDENTITY FULL

원인

- Publisher side insert/update 실패. 변경사항을 Subscriber에게 전송, ⇒ Subscriber도 유효하지 않은 Key로 실패

- Subscriber의 apply가 실패하기 때문에 replication stop

 

해결방안

- publisher / subscriber 간 테이블 PK 맞춰주기

 

Case 2. could not receive data from WAL stream

원인

- Default timeout(60초) 내에 wal sender와 wal receiver이 통신을 완료하지 못할 때가 있다.

- 또는 DB SSL 인증서 만료

 

해결방안

- [publisher] wal_sender_timeout 옵션 값 늘리기

 

기타

  • 데이터가 큰 테이블을 이관할 때 Index 설정은 데이터 이관 후 진행하는 것이 좋다.
    • 인덱스가 있는 상태에서 데이터 삽입 시 인덱스 리밸런싱이 발생하여 데이터 이관 소요 시간 증가

 

Reference

 

반응형

'Backend > 데이터베이스' 카테고리의 다른 글

오라클 19c 설치 (REHL 8) with RPM  (1) 2024.02.13
AWS RDS(MariaDB), MongoDB 운영 데이터를 개발로 이관하기  (0) 2024.02.13
MariaDB 명령어 정리 (테이블/유저/백업/복구)  (0) 2024.02.13
[데이터베이스 / DB] Error Code: 1175, You are using safe update mode and you tried to update a table without a WHERE.... 처리방법  (0) 2021.02.23

'Backend/데이터베이스'의 다른글

  • 현재글[PostgreSQL] Logical Replication로 데이터 Migration하기

관련글

티스토리툴바