PostgreSQL에서 논리적 복제 구현: 멀티 마스터 및 선택적 데이터 동기화

PostgreSQL의 논리적 복제는 전체 클러스터 대신 선택된 테이블을 복사해야 할 때 유용합니다. 보고 데이터베이스, 버전 업그레이드, 지역별 읽기 복사본 및 선택적 데이터 동기화에 유용하지만, 네이티브 PostgreSQL 논리적 복제는 즉시 사용 가능한 충돌 없는 멀티 마스터 시스템이 아닙니다.

논리적 복제 vs 물리적 복제

물리적 스트리밍 복제

• 전체 데이터베이스 클러스터 복제
• 바이너리 수준 복제
• 읽기 전용 복제본
• 동일한 PostgreSQL 버전 필요
• 낮은 오버헤드

논리적 복제

• 선택적 테이블/행 복제
• 크로스 버전 호환
• 쓰기 가능한 구독자
• 높은 오버헤드
• 유연한 데이터 분산

논리적 복제 사용 사례

1. 선택적 데이터 분산: 특정 테이블을 다른 지역으로 복제
2. 양방향 동기화 실험: 일반적으로 애플리케이션 수준 충돌 규칙을 사용하여 데이터베이스 간 신중하게 범위가 지정된 쓰기
3. 크로스 버전 업그레이드: 이전 PostgreSQL 버전에서 새 버전으로 복제
4. 데이터 집계: 여러 소스의 데이터 통합
5. GDPR 준수: 민감하지 않은 열만 복제

사전 요구 사항 및 설정

구성 요구 사항

게시자(소스)에서:

# postgresql.conf
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

구독자(대상)에서:

# postgresql.conf
max_replication_slots = 10
max_logical_replication_workers = 10
max_worker_processes = 16

PostgreSQL 재시작

sudo systemctl restart postgresql

네트워크 구성

데이터베이스가 통신할 수 있는지 확인:

# 구독자에서 게시자로 연결 테스트
psql -h publisher.example.com -U replication_user -d source_db

게시자에서 pg_hba.conf 구성:

# 복제 연결 허용
host    source_db    replication_user    subscriber_ip/32    scram-sha-256

기본 논리적 복제 설정

1단계: 복제 사용자 생성

게시자에서:

CREATE ROLE replication_user WITH REPLICATION LOGIN PASSWORD 'secure_password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replication_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO replication_user;

2단계: 소스 테이블 생성

게시자에서:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    amount DECIMAL(10,2),
    status VARCHAR(20),
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO users (username, email) VALUES 
    ('alice', '[email protected]'),
    ('bob', '[email protected]');

3단계: 게시 생성

게시자에서:

-- 모든 테이블 게시
CREATE PUBLICATION my_publication FOR ALL TABLES;

-- 또는 특정 테이블 게시
CREATE PUBLICATION my_publication FOR TABLE users, orders;

-- 또는 게시된 테이블에 행 필터 사용
CREATE PUBLICATION active_users FOR TABLE users WHERE (status = 'active');

게시 보기:

SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables;

4단계: 복제 테이블 생성

구독자에서:

-- 테이블은 동일한 구조를 가져야 함
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    amount DECIMAL(10,2),
    status VARCHAR(20),
    created_at TIMESTAMP DEFAULT NOW()
);

5단계: 구독 생성

구독자에서:

CREATE SUBSCRIPTION my_subscription
    CONNECTION 'host=publisher.example.com port=5432 dbname=source_db user=replication_user password=secure_password'
    PUBLICATION my_publication;

6단계: 복제 확인

게시자에서:

SELECT * FROM pg_stat_replication;
SELECT * FROM pg_replication_slots;

구독자에서:

SELECT * FROM pg_stat_subscription;
SELECT * FROM users;  -- 복제된 데이터가 보여야 함

고급 구성

열 수준 복제 (PostgreSQL 15+)

특정 열만 복제:

-- 게시자에서: 민감하지 않은 열만 복제
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    credit_card VARCHAR(20),  -- 복제되지 않음
    created_at TIMESTAMP
);

CREATE PUBLICATION customer_basic 
    FOR TABLE customers (id, name, email, created_at);

행 필터링

활성 레코드만 복제:

CREATE PUBLICATION active_data 
    FOR TABLE orders WHERE (status IN ('pending', 'processing'));

지역별 데이터 분산:

CREATE PUBLICATION us_customers 
    FOR TABLE customers WHERE (country = 'US');

CREATE PUBLICATION eu_customers 
    FOR TABLE customers WHERE (country IN ('UK', 'DE', 'FR'));

여러 게시

-- 게시자: 여러 게시 생성
CREATE PUBLICATION oltp_data FOR TABLE users, orders;
CREATE PUBLICATION analytics_data FOR TABLE logs, metrics;

-- 구독자: 여러 게시 구독
CREATE SUBSCRIPTION multi_sub
    CONNECTION 'host=publisher port=5432 dbname=mydb user=repuser password=pass'
    PUBLICATION oltp_data, analytics_data;

양방향 복제 주의 사항

네이티브 논리적 복제는 양방향으로 연결될 수 있지만, PostgreSQL은 충돌하는 쓰기를 자동으로 병합하지 않습니다. 각 행에 단일 작성자가 있고, 키가 충돌할 수 없으며, 애플리케이션이 충돌을 처리할 수 있는 경우에만 이 패턴을 사용하십시오.

양방향 동기화 예제 스켈레톤

데이터베이스 A 구성:

-- 게시 생성
CREATE PUBLICATION db_a_pub FOR TABLE shared_table;

-- 데이터베이스 B 구독
CREATE SUBSCRIPTION db_a_sub
    CONNECTION 'host=db-b.example.com dbname=mydb user=repuser'
    PUBLICATION db_b_pub
    WITH (origin = none);  -- 복제 루프 방지

데이터베이스 B 구성:

-- 게시 생성
CREATE PUBLICATION db_b_pub FOR TABLE shared_table;

-- 데이터베이스 A 구독
CREATE SUBSCRIPTION db_b_sub
    CONNECTION 'host=db-a.example.com dbname=mydb user=repuser'
    PUBLICATION db_a_pub
    WITH (origin = none);

충돌 처리

논리적 복제는 자동 "마지막 쓰기 승리" 충돌 해결을 제공하지 않습니다. 충돌하는 삽입, 업데이트 중 누락된 행, 제약 조건 위반 또는 중복 키는 데이터 문제를 해결할 때까지 적용 작업자를 중지시킬 수 있습니다.

-- 충돌 추적을 위해 복제 ID 설정
ALTER TABLE shared_table REPLICA IDENTITY FULL;

충돌 감소 전략:

1. 타임스탬프 기반 소유권 확인: updated_at 및 작성자 식별자를 추가하여 애플리케이션이 오래된 쓰기를 거부할 수 있도록 합니다.

CREATE TABLE shared_table (
    id SERIAL PRIMARY KEY,
    data TEXT,
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_shared_table_timestamp
    BEFORE UPDATE ON shared_table
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

1. 버전 번호 지정: 애플리케이션에서 버전을 증가시키고 오래된 버전을 기반으로 업데이트를 거부합니다.

CREATE TABLE shared_table (
    id SERIAL PRIMARY KEY,
    data TEXT,
    version INTEGER DEFAULT 1
);

초기 데이터 동기화 옵션

옵션 1: 자동 복사 (기본값)

-- 구독자가 기존 데이터를 자동으로 복사
CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=publisher dbname=mydb user=repuser'
    PUBLICATION my_pub
    WITH (copy_data = true);  -- 기본값

옵션 2: 수동 초기 동기화

대규모 데이터 세트의 경우 pg_dump 사용:

# 게시자에서 특정 테이블 덤프
pg_dump -h publisher.example.com -U postgres -d source_db \
    -t users -t orders --no-owner --no-acl > initial_data.sql

# 구독자에 로드
psql -h subscriber.example.com -U postgres -d target_db < initial_data.sql

# 초기 복사 없이 구독 생성
psql -h subscriber.example.com -U postgres -d target_db -c "
    CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=publisher dbname=source_db user=repuser'
    PUBLICATION my_pub
    WITH (copy_data = false);
"

옵션 3: 대규모 진행 중 트랜잭션 스트리밍

-- 커밋을 기다리는 대신 대규모 진행 중 트랜잭션 스트리밍
CREATE SUBSCRIPTION fast_sync
    CONNECTION 'host=publisher dbname=mydb user=repuser'
    PUBLICATION my_pub
    WITH (
        copy_data = true,
        streaming = on,
        synchronous_commit = off
    );

논리적 복제 모니터링

게시자 모니터링

-- 복제 슬롯 보기
SELECT 
    slot_name,
    plugin,
    active,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag_size
FROM pg_replication_slots;

-- 활성 복제 연결 보기
SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    state,
    sync_state,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) AS send_lag
FROM pg_stat_replication;

구독자 모니터링

-- 구독 상태 보기
SELECT 
    subname,
    pid,
    received_lsn,
    latest_end_lsn,
    pg_size_pretty(pg_wal_lsn_diff(latest_end_lsn, received_lsn)) AS lag
FROM pg_stat_subscription;

-- 복제 오류 확인
SELECT * FROM pg_stat_subscription WHERE last_msg_receipt_time < NOW() - INTERVAL '5 minutes';

모니터링 스크립트

#!/bin/bash
# logical-replication-monitor.sh

echo "=== 게시자 상태 ==="
psql -h publisher -d mydb -c "
    SELECT slot_name, active, 
           pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag
    FROM pg_replication_slots;"

echo ""
echo "=== 구독자 상태 ==="
psql -h subscriber -d mydb -c "
    SELECT subname, pid, 
           pg_size_pretty(pg_wal_lsn_diff(latest_end_lsn, received_lsn)) AS lag
    FROM pg_stat_subscription;"

문제 해결

문제 1: 구독이 데이터를 수신하지 않음

구독 상태 확인:

SELECT subname, subenabled, subconninfo FROM pg_subscription;
SELECT subname, pid, received_lsn, latest_end_lsn FROM pg_stat_subscription;

비활성화된 경우 구독 활성화:

ALTER SUBSCRIPTION my_sub ENABLE;

오류 확인:

SELECT * FROM pg_stat_subscription;

문제 2: 복제 지연 증가

느린 테이블 식별:

SELECT schemaname, tablename, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY (n_tup_ins + n_tup_upd + n_tup_del) DESC;

작업자 프로세스 증가:

# 구독자의 postgresql.conf
max_logical_replication_workers = 20
max_worker_processes = 30

문제 3: 복제 슬롯 블로트

슬롯 사용량 확인:

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

비활성 슬롯 삭제:

SELECT pg_drop_replication_slot('inactive_slot_name');

문제 4: 초기 동기화 실패

초기 동기화 다시 시작:

-- 구독 삭제 및 재생성
DROP SUBSCRIPTION my_sub;

CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=publisher dbname=mydb user=repuser'
    PUBLICATION my_pub
    WITH (copy_data = true);

성능 최적화

1. 적절한 복제 ID 사용

-- 기본값: PRIMARY KEY만
ALTER TABLE my_table REPLICA IDENTITY DEFAULT;

-- 전체: 모든 열 (더 높은 오버헤드)
ALTER TABLE my_table REPLICA IDENTITY FULL;

-- 인덱스: 특정 고유 인덱스 사용
CREATE UNIQUE INDEX replica_idx ON my_table(col1, col2);
ALTER TABLE my_table REPLICA IDENTITY USING INDEX replica_idx;

2. 대규모 트랜잭션을 위한 병렬 적용

CREATE SUBSCRIPTION parallel_sub
    CONNECTION 'host=publisher dbname=mydb user=repuser'
    PUBLICATION my_pub
    WITH (streaming = parallel);

모범 사례

1. 항상 복제 ID 사용: 테이블에 PRIMARY KEY 또는 UNIQUE 제약 조건이 있는지 확인
2. 복제 지연 모니터링: 복구 요구 사항 및 WAL 보존 예산에 따라 알림 설정
3. 행 필터를 신중하게 사용: 게시자에서 평가되는 필터는 성능에 영향을 미침
4. 충돌 계획: 양방향 쓰기를 시도하기 전에 충돌 감지 구현
5. 장애 조치 절차 테스트: 데이터베이스 간 전환 연습
6. 정기 유지 관리: 오래된 복제 슬롯 정리

핵심 요점

단방향 논리적 복제로 시작하십시오. 선택적 테이블 동기화, 보고용 복사본 및 많은 업그레이드 워크플로에 안정적입니다. 양방향 복제를 체크리스트 항목이 아닌 고급 설계 문제로 취급하십시오. 양쪽에 쓰기 전에 행 소유권, 키 생성 및 충돌 처리를 정의하십시오.