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

서론

PostgreSQL의 논리적 복제를 사용하면 데이터베이스 간에 특정 테이블, 행 또는 열을 복제할 수 있어 정교한 데이터 분산 패턴이 가능해집니다. 전체 데이터베이스 클러스터를 복사하는 물리적 스트리밍 복제와 달리, 논리적 복제는 어떤 데이터가 어디로 복제될지에 대해 세밀한 제어를 제공합니다.

논리적 복제와 물리적 복제 비교

물리적 스트리밍 복제(Physical Streaming Replication)

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

논리적 복제(Logical Replication)

• 선택적 테이블/행 복제
• 버전 간 호환 가능
• 쓰기 가능한 구독자(Subscriber)
• 오버헤드 높음
• 유연한 데이터 분산

논리적 복제 활용 사례

1. 선택적 데이터 분산: 특정 테이블을 다른 지역(Region)으로 복제
2. 멀티 마스터 설정: 양방향 동기화가 가능한 여러 개의 쓰기 가능 데이터베이스 구성
3. 버전 간 업그레이드: 이전 버전에서 새 버전의 PostgreSQL로 데이터 복제
4. 데이터 집계: 여러 소스의 데이터를 하나로 통합
5. GDPR 준수: 민감하지 않은 열만 선택적으로 복제

사전 요구 사항 및 설정

구성 요구 사항

게시자(Publisher - 소스) 설정:

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

구독자(Subscriber - 타겟) 설정:

# 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    all    replication_user    subscriber_ip/32    md5

기본적인 논리적 복제 설정

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,
    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단계: 게시(Publication) 생성

게시자에서:

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

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

-- 또는 행 필터 사용 (PostgreSQL 15 이상)
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,
    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단계: 구독(Subscription) 생성

구독자에서:

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);

행 필터링(Row Filtering)

활성 레코드만 복제:

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'));

다중 게시(Multiple Publications)

-- 게시자: 여러 게시물 생성
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;

양방향 복제 (멀티 마스터)

2방향 동기화 설정

데이터베이스 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);

충돌 해결

논리적 복제는 기본적으로 "마지막 쓰기 승리(last write wins)" 정책을 사용합니다.

-- 충돌 추적을 위해 복제 id(replica identity) 설정
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();

2. 버전 번호 부여:

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 \n    -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: 병렬 초기 동기화

-- 빠른 초기 동기화를 위해 여러 워커(worker) 사용
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, pid, subenabled, subconninfo FROM pg_subscription;

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

ALTER SUBSCRIPTION my_sub ENABLE;

오류 확인:

SELECT * FROM pg_stat_subscription;

이슈 2: 복제 지연(Lag) 증가

부하가 많은 테이블 식별:

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: 복제 슬롯 팽창(Bloat)

슬롯 사용량 확인:

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(Replica Identity) 사용

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

-- Full: 모든 열 사용 (오버헤드 높음)
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. 초기 동기화 중 제약 조건 비활성화

-- 빠른 초기 로드를 위해 일시적으로 트리거 비활성화
ALTER TABLE my_table DISABLE TRIGGER ALL;

-- 초기 동기화 완료 후
ALTER TABLE my_table ENABLE TRIGGER ALL;

3. 병렬 적용 (PostgreSQL 16 이상)

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

모범 사례

1. 항상 복제 ID 사용: 테이블에 PRIMARY KEY 또는 UNIQUE 제약 조건이 있는지 확인하십시오.
2. 복제 지연 모니터링: 지연 시간이 5분을 초과하거나 크기가 100MB를 넘을 경우 알림을 설정하십시오.
3. 행 필터 사용 시 주의: 필터는 게시자에서 평가되므로 성능에 영향을 줄 수 있습니다.
4. 충돌 대비: 멀티 마스터 설정 시 충돌 감지 로직을 구현하십시오.
5. 장애 조치(Failover) 절차 테스트: 데이터베이스 간 전환 연습을 수행하십시오.
6. 정기 유지 관리: 오래된 복제 슬롯을 정리하십시오.

결론

논리적 복제는 PostgreSQL을 위한 강력한 데이터 분산 기능을 제공합니다. 주요 장점은 다음과 같습니다.

• 선택적 테이블 및 행 복제
• 버전 간 호환성
• 양방향 멀티 마스터 구성
• 세밀한 데이터 프라이버시 제어

간단한 단방향 복제로 시작하여 충분히 테스트한 후, 필요에 따라 더 복잡한 토폴로지로 확장해 보시기 바랍니다.