PostgreSQL 복제 완전 가이드 2025: Streaming Replication, Logical Decoding, Failover, Patroni 심층 분석

들어가며: 단일 DB의 위험

한 번쯤은 겪어봤을 상황

자정 새벽 2시. 알림이 울린다.

"Production DB 다운."

당신의 PostgreSQL 서버 하나가 멈췄다. 모든 서비스가 죽었다. 원인은:

• Disk failure.
• 네트워크 단절.
• OS 크래시.
• 누군가의 실수로 DROP TABLE.
• 서버 재부팅 후 안 올라옴.

복제 없는 DB는 single point of failure. 한 번의 사고가 전체 서비스를 마비시킨다.

복제가 주는 것

PostgreSQL Replication으로 얻는 것:

1. High Availability: Primary 다운 → Replica가 승격 → 서비스 지속.
2. Read Scaling: Read 쿼리를 여러 replica에 분산.
3. Backup Source: Replica에서 백업 → primary 부담 없음.
4. Geographic Distribution: 여러 지역에 데이터.
5. Zero-Downtime Upgrade: Replica 업그레이드 후 failover.
6. CDC (Change Data Capture): 다른 시스템으로 데이터 동기화.

이 모든 것이 WAL이라는 하나의 기반 위에 있다.

이 글에서 다룰 것

1. WAL (Write-Ahead Log) 복습: 복제의 기반.
2. Physical Streaming Replication: 바이트 단위 복제.
3. Synchronous vs Asynchronous: 성능과 안정성.
4. Hot Standby: Read replica.
5. Replication Slots: 안전한 WAL 유지.
6. Logical Decoding & Replication: Logical 수준.
7. pglogical, Debezium: 실전 도구.
8. Failover 자동화: Patroni, repmgr.
9. PgBouncer: 연결 풀링.
10. 실전 운영 가이드.

1. WAL: 복제의 기반

WAL 복습

(WAL 가이드의 핵심)

Write-Ahead Log: 데이터베이스의 모든 변경을 먼저 log에 기록. 이후 실제 data file에 반영.

Transaction
    ↓
WAL record (먼저!)
    ↓ (fsync로 디스크에)
데이터 파일 업데이트

이점:

• Durability: Commit 후 장애 → WAL replay로 복구.
• Crash recovery: DB 재시작 시 WAL 재생.
• 복제: WAL을 다른 서버에 보내면 그 서버도 같은 상태.

WAL의 구조

PostgreSQL의 WAL:

• Segments: 기본 16MB 파일들.
• LSN (Log Sequence Number): WAL 위치의 고유 식별자.
• 포맷: Binary, PostgreSQL 버전별.

$PGDATA/pg_wal/
├── 000000010000000100000001  (16MB)
├── 000000010000000100000002
├── 000000010000000100000003
└── archive_status/

파일 이름: <TimelineID><LogID><Segment>.

Replication의 본질

PostgreSQL 복제 = WAL 전송 + 재생:

Primary                 Standby
───────                 ───────
 Commit
  ↓
 WAL write              
  ↓
 → → Network → → →      WAL receive
                         ↓
                        WAL replay
                         ↓
                        Same state

모든 복제 방식이 이 기본 원리 위에 있다. 차이는 언제, 어떻게 전송하느냐.

2. Physical Streaming Replication

개요

PostgreSQL 9.0부터 표준이 된 streaming replication:

• Primary가 WAL을 생성.
• Standby가 WAL을 스트리밍으로 수신.
• Standby가 재생해서 같은 상태 유지.

"Physical"의 의미: 바이트 단위 WAL. 같은 PostgreSQL 버전, 같은 플랫폼 간만 복제.

구조

┌──────────────┐              ┌──────────────┐
│   Primary    │              │   Standby    │
│              │              │              │
│ walsender ───┼──WAL stream─→│ walreceiver  │
│              │              │      ↓       │
│              │              │  startup     │
│              │              │   (replay)   │
└──────────────┘              └──────────────┘

Primary 측:

• walsender 프로세스가 각 standby 당 하나.
• WAL이 생성되는 대로 standby에 전송.

Standby 측:

• walreceiver 프로세스가 WAL 수신.
• startup 프로세스가 WAL 재생.

설정: Primary

postgresql.conf:

# WAL 수준
wal_level = replica                # physical replication용

# 최대 WAL 송신자 수
max_wal_senders = 10

# 복제용 연결 허용
max_replication_slots = 10

# WAL archiving (권장)
archive_mode = on
archive_command = 'cp %p /archive/%f'

# 복제 대역폭 (선택)
wal_compression = on               # WAL 압축

pg_hba.conf:

host replication repluser 10.0.0.0/8 md5

Base Backup

Standby는 primary의 복사본으로 시작:

pg_basebackup \
  -h primary.example.com \
  -D /var/lib/postgresql/standby \
  -U repluser \
  -P -R \
  --wal-method=stream

옵션:

• -R: Standby 설정 파일 자동 생성 (standby.signal, postgresql.auto.conf).
• -P: 진행률 표시.
• --wal-method=stream: Backup 중에도 WAL 스트리밍.

Standby 시작

standby.signal 파일이 있으면 PostgreSQL은 standby 모드로 시작.

postgresql.auto.conf:

primary_conninfo = 'host=primary.example.com user=repluser password=...'
primary_slot_name = 'standby1_slot'

이제 standby 시작:

systemctl start postgresql

Primary의 WAL을 스트리밍으로 수신 + 재생. Primary와 같은 상태 유지.

Replication 확인

Primary에서:

SELECT * FROM pg_stat_replication;

 pid  | usename  | state     | sync_state | replay_lag
------+----------+-----------+------------+------------
 1234 | repluser | streaming | async      | 00:00:00.123

• state: streaming이면 정상.
• sync_state: async, sync, potential.
• replay_lag: 재생 지연 시간.

Standby에서:

SELECT pg_is_in_recovery();
-- t (true) → standby 모드

3. Synchronous vs Asynchronous

Asynchronous Replication (기본)

Primary가 WAL을 commit 즉시 반환. Standby는 비동기로 따라잡음.

Client: COMMIT
  ↓
Primary: WAL write → fsync → "COMMITTED"
  ↓ (비동기)
Standby: WAL 수신 → 재생

장점:

• 빠른 commit: 네트워크 대기 없음.
• Standby 장애가 primary에 영향 없음.
• 기본값.

단점:

• Replica lag: 수 ms ~ 수 초.
• Primary 장애 시 최신 데이터 손실 가능.

Synchronous Replication

Primary가 WAL이 standby까지 전달된 후에 commit:

Client: COMMIT
  ↓
Primary: WAL write
  ↓
Primary → Standby: WAL 전송
Standby: 수신 + 확인
  ↓
Primary: "COMMITTED" 반환

설정:

synchronous_commit = remote_write  # 또는 remote_apply
synchronous_standby_names = 'standby1'

synchronous_commit 레벨:

• off: Commit 시 fsync 안 함. 매우 빠름, 약간의 데이터 손실 가능.
• local: 로컬 fsync만 (async replication).
• remote_write: Standby가 WAL 수신 완료 (OS buffer).
• on (기본): Standby가 WAL fsync 완료.
• remote_apply: Standby가 WAL 재생 완료 (가장 엄격).

레벨별 보장:

성능 영향

실측 (100 ms RTT로 remote standby):

동기 복제는 네트워크 RTT에 민감. 같은 DC 내에서만 실용적.

다중 Standby

Priority-based:

synchronous_standby_names = 'FIRST 2 (standby1, standby2, standby3)'

먼저 두 standby의 확인을 기다림.

Quorum-based:

synchronous_standby_names = 'ANY 2 (standby1, standby2, standby3)'

아무 두 standby의 확인이면 OK. 더 탄력적.

실전 선택

Async:

• 일반적인 용도.
• 대규모 시스템.
• Cross-region replica.

Sync (same DC):

• 금융, 결제.
• 데이터 손실 허용 불가.
• HA 필수.

Mixed:

• 1 sync + 1 async (backup).
• 같은 DC는 sync, 다른 DC는 async.

4. Hot Standby: Read Replicas

Hot vs Warm Standby

Warm standby: Recovery 모드, 쿼리 불가. 단지 대기.

Hot standby: Recovery 중에도 읽기 쿼리 허용. PostgreSQL 9.0+ 기본.

설정

Standby의 postgresql.conf:

hot_standby = on                    # 기본 on
hot_standby_feedback = on           # Primary에 피드백
max_standby_archive_delay = 30s     # conflict 허용 시간
max_standby_streaming_delay = 30s

Read-Only 쿼리

Standby에서:

-- 가능
SELECT * FROM users WHERE id = 1;

-- 불가 (에러)
INSERT INTO users VALUES (...);
UPDATE users SET ...;

에러 메시지:

ERROR: cannot execute INSERT in a read-only transaction

Replication Conflicts

문제: Standby가 primary의 WAL을 재생 중인데, 현재 standby에서 실행 중인 쿼리가 그 변경사항과 충돌.

예시:

1. Standby에서 SELECT * FROM users (long query).
2. Primary에서 VACUUM users → 사용되지 않는 row 제거.
3. Standby가 이 VACUUM WAL 재생 → long query의 snapshot 깨짐.
4. 쿼리 취소 (query conflict).

해결 옵션:

1. max_standby_streaming_delay:

max_standby_streaming_delay = 60s

Conflict 발생 시 최대 60초 대기 후 쿼리 취소. Replication lag 증가.

2. hot_standby_feedback:

hot_standby_feedback = on

Standby가 primary에 사용 중인 snapshot 알림. Primary가 해당 row의 vacuum을 지연.

장점: Conflict 없음. 단점: Primary에 bloat 증가 가능.

3. replication slot with status update: 비슷한 효과.

Read Scaling

여러 Standby로 읽기 분산:

                Primary (write)
                   ↓↓↓ WAL
        ┌──────────┼──────────┐
        ↓          ↓          ↓
    Standby1   Standby2   Standby3
   (read)      (read)     (read)

Load balancer로 읽기 분산:

• HAProxy, PgBouncer, pgcat.
• READ ONLY 쿼리만 standby로.
• 쓰기는 primary로.

주의:

• Replication lag: 방금 쓴 데이터가 안 보일 수 있음.
• Read-your-writes 보장 안 됨 (async인 경우).

Causal Reads

동기 복제 + synchronous_commit=remote_apply:

• 쓰기 후 standby에서 즉시 읽기 가능.
• 하지만 성능 비용.

또는 애플리케이션 수준:

• 쓰기 직후엔 primary에서 읽기.
• 일정 시간 후 standby로.

5. Replication Slots

문제: WAL 정리

Primary가 WAL을 계속 생성. Disk 공간을 위해 오래된 WAL은 삭제.

시나리오:

1. Standby가 네트워크 단절로 한참 사라짐.
2. Primary가 wal_keep_size 이상 WAL 생성.
3. Primary가 오래된 WAL 삭제.
4. Standby 복귀.
5. 필요한 WAL이 없음 → 복제 깨짐.
6. pg_basebackup으로 처음부터 다시 해야.

Replication Slot

Replication slot은 primary에게 "이 WAL을 standby가 확인할 때까지 보관하라"고 알림.

생성:

SELECT pg_create_physical_replication_slot('standby1_slot');

Standby 설정:

primary_slot_name = 'standby1_slot'

작동:

• Primary가 slot의 "last used LSN"을 추적.
• 이 LSN 이후의 WAL은 삭제하지 않음.
• Standby가 LSN 확인 → primary가 이전 WAL 삭제.

위험

Replication slot의 함정:

• Standby가 오래 다운 → WAL 무한 축적.
• Primary 디스크가 가득 참.
• 전체 데이터베이스 다운.

방어:

max_slot_wal_keep_size = 10GB   # PostgreSQL 13+

Slot 때문에 유지해야 할 WAL이 10GB 넘으면 slot을 강제 무효화. 복제는 깨지지만 primary는 살아남음.

Slot 관리

모니터링:

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

제거 (사용 안 하는 slot):

SELECT pg_drop_replication_slot('old_slot');

주의: Active slot은 drop 불가. Standby 먼저 분리.

6. Logical Decoding & Replication

Physical vs Logical

Physical replication:

• 바이트 단위 WAL 복제.
• 완전 동일한 복제본 (같은 버전, 같은 플랫폼).
• Read replica, HA 용도.

Logical replication:

• WAL을 SQL 수준의 변경으로 디코딩.
• INSERT, UPDATE, DELETE.
• 선택적 복제 (특정 테이블, 스키마).
• 크로스 버전 가능.
• CDC (Change Data Capture)의 기반.

Logical Decoding

Logical decoding은 WAL을 논리적 변경 스트림으로 변환:

Physical WAL:
  XLOG_INSERT: relid=16384, tuple=...
  XLOG_HEAP_UPDATE: ...
  
Logical stream:
  BEGIN;
  INSERT INTO users (id, name) VALUES (1, 'Alice');
  COMMIT;

설정

Primary:

wal_level = logical               # physical보다 더 많은 정보

Output Plugins

Logical decoding은 플러그인 기반. WAL을 어떤 포맷으로 출력할지 결정:

pgoutput (기본):

• PostgreSQL 10+ 내장.
• Native logical replication용.

wal2json:

• JSON 포맷.
• 외부 도구 친화적.

decoderbufs:

• Protocol Buffers.
• Debezium이 사용.

test_decoding:

• 디버깅용.

Native Logical Replication

PostgreSQL 10+ 기본 기능:

Publisher (publisher):

CREATE PUBLICATION mypub FOR TABLE users, orders;

Subscriber:

CREATE SUBSCRIPTION mysub
  CONNECTION 'host=primary.example.com dbname=mydb user=repluser'
  PUBLICATION mypub;

작동:

• Subscriber가 publisher에 연결.
• 초기 데이터 copy (COPY).
• 이후 logical WAL 스트리밍.
• 변경을 subscriber에 적용.

특징

선택적 복제:

-- 특정 테이블만
CREATE PUBLICATION mypub FOR TABLE users;

-- 모든 테이블
CREATE PUBLICATION mypub FOR ALL TABLES;

-- 일부 연산만
CREATE PUBLICATION mypub FOR TABLE users WITH (publish = 'insert,update');

크로스 버전:

• Publisher PG 14 → Subscriber PG 15: OK.
• 버전 업그레이드 시 유용.

Write on Subscriber:

• Subscriber도 write 가능 (publisher로 반영 안 됨).
• Read replica와 다름.

Limitations

Physical replication과 달리:

1. Sequence는 복제 안 됨: 별도 처리.
2. TRUNCATE는 복제 안 됨 (기본).
3. Large object (LOB)는 복제 안 됨.
4. DDL은 복제 안 됨: CREATE TABLE 등.
5. Materialized view refresh는 복제 안 됨.

이런 제약 때문에 Full DR (disaster recovery)에는 부적절. Physical replication이 더 적합.

용도

Logical replication이 적합한 경우:

1. CDC (Change Data Capture): Kafka로 변경 이벤트 전송.
2. Cross-version migration: PG 14 → PG 16.
3. Schema selective replication.
4. Multi-master (양방향): 복잡하지만 가능.
5. Data warehouse 동기화: PG → BigQuery 등.

Physical이 적합한 경우:

1. HA failover.
2. Read replica.
3. 완전 백업.
4. 정확한 복제본 필요.

7. Debezium과 CDC

Debezium 소개

Debezium은 CDC의 표준 도구. PostgreSQL, MySQL, MongoDB, Oracle 등을 지원. Kafka Connect 기반.

아키텍처

PostgreSQL
    ↓ (logical decoding)
Debezium (PG connector)
    ↓ (Kafka Connect)
Apache Kafka
    ↓ (consumers)
Elasticsearch, Snowflake, 다른 DB, ...

흐름:

1. Debezium이 PostgreSQL에 logical replication slot 생성.
2. WAL을 logical 형태로 받음.
3. 각 변경을 Kafka 메시지로 발행.
4. 다운스트림 시스템이 consume.

설정

PostgreSQL:

wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

Debezium connector:

{
  "name": "my-pg-connector",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "pg.example.com",
    "database.user": "debezium",
    "database.password": "secret",
    "database.dbname": "mydb",
    "database.server.name": "my-pg",
    "plugin.name": "pgoutput",
    "table.include.list": "public.users,public.orders",
    "slot.name": "debezium_slot",
    "publication.name": "debezium_pub"
  }
}

이벤트 포맷

Debezium의 변경 이벤트 (JSON):

{
  "before": null,
  "after": {
    "id": 1,
    "name": "Alice",
    "email": "alice@example.com"
  },
  "source": {
    "version": "2.5.0.Final",
    "connector": "postgresql",
    "name": "my-pg",
    "ts_ms": 1704153600000,
    "snapshot": "false",
    "db": "mydb",
    "schema": "public",
    "table": "users",
    "txId": 12345,
    "lsn": 123456789
  },
  "op": "c",
  "ts_ms": 1704153600123
}

op:

• c: create (INSERT).
• u: update.
• d: delete.
• r: read (snapshot).

활용

1. Microservice 간 데이터 동기화:

• Monolithic DB → 여러 microservice로 이벤트.

2. 검색 인덱스 업데이트:

• Postgres → Elasticsearch 실시간 동기화.

3. 데이터 웨어하우스:

• OLTP → Snowflake, BigQuery.

4. Cache invalidation:

• DB 변경 → Redis 캐시 삭제.

5. Audit logging:

• 모든 DB 변경 기록.

주의사항

Schema evolution: 테이블 구조 변경 시 consumer가 적응해야.

Order guarantees:

• Single table 내 순서 보장.
• Cross-table 순서는 복잡 (transaction 경계).

Exactly-once vs At-least-once: 보통 at-least-once, 응용 계층에서 idempotent 처리.

WAL retention: Debezium이 뒤처지면 WAL 쌓임.

8. Failover Automation

Manual Failover

단계:

1. Primary 죽음을 감지.
2. Standby를 primary로 승격:

SELECT pg_promote();

3. Application의 connection string 변경.
4. 다른 standby들의 primary 변경.

문제: 사람이 해야. 수 분~수십 분 다운타임.

Patroni

Patroni: PostgreSQL HA의 사실상 표준. Zalando가 개발.

아키텍처:

          DCS (Distributed Configuration Store)
           (etcd, Consul, ZooKeeper)
                     ↑ ↓
        ┌────────────┼────────────┐
        ↓            ↓            ↓
   Patroni       Patroni      Patroni
   + PG 1       + PG 2        + PG 3
  (leader)   (replica)     (replica)

작동:

• 각 노드에 Patroni 프로세스.
• DCS (etcd 등)를 통해 조율.
• Leader election으로 primary 결정.
• 자동 failover.

Patroni Configuration

scope: my-pg-cluster
namespace: /db/
name: node1

restapi:
  listen: 0.0.0.0:8008

etcd:
  hosts: etcd1:2379,etcd2:2379,etcd3:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      parameters:
        max_connections: 100
        shared_buffers: 4GB
  
  initdb:
    - encoding: UTF8
    - data-checksums

postgresql:
  listen: 0.0.0.0:5432
  data_dir: /var/lib/postgresql/data
  authentication:
    replication:
      username: repluser
      password: secret
    superuser:
      username: postgres
      password: secret

Failover 시나리오

Primary가 죽으면:

1. 다른 노드들이 primary의 healthcheck 실패를 감지 (~10초).
2. Standby 중 가장 최신 것 선택 (lag 기준).
3. 해당 standby가 primary로 승격 (pg_promote).
4. 다른 standby들이 새 primary를 가리키도록 재설정.
5. Virtual IP 또는 DNS 업데이트.

보통 30초~2분 내 완료.

Split-Brain 방지

문제: 네트워크 파티션으로 두 개의 primary가 동시에 존재할 수 있음.

Patroni의 해결:

• DCS (etcd 등)가 consensus 기반.
• 과반을 가진 쪽만 primary.
• 소수파는 자동 demotion.

pg_rewind

Old primary를 new primary의 standby로 다시 추가:

pg_rewind --target-pgdata=/var/lib/postgresql/data \
          --source-server='host=new-primary user=repluser'

WAL이 분기된 이후만 재동기화. 전체 pg_basebackup보다 빠름.

Patroni가 자동으로 처리.

대안 도구

repmgr: Patroni 전에 널리 쓰였음. 여전히 사용.

pg_auto_failover: Citus Data (현재 Microsoft) 개발. 단순함.

Stolon: Sorint.lab 개발. Kubernetes 친화적.

CloudNativePG: Kubernetes operator. 현대적.

선택:

• Bare metal, 기존 인프라: Patroni.
• Kubernetes: CloudNativePG 또는 Patroni operator.

9. PgBouncer: 연결 풀링

왜 필요한가

PostgreSQL의 연결은 비싸다:

• 각 연결이 별도 프로세스.
• 연결당 수 MB 메모리.
• 연결 생성 시간 수십 ms.

문제: 수천 개의 웹 서버 → 수천 개 연결 → 메모리 부족, 성능 저하.

PostgreSQL의 권장 연결 수: 일반적으로 CPU 수의 2~4배.

해결: Connection pooling.

PgBouncer

PgBouncer는 가장 널리 쓰이는 PostgreSQL 연결 풀러. 가볍고 빠름.

아키텍처:

[App1] [App2] [App3] [App4] ... [App100]
   ↓      ↓      ↓      ↓          ↓
   connections (to PgBouncer)
              ↓
         PgBouncer
              ↓
   few connections (to PG)
              ↓
         PostgreSQL

수백 개의 App 연결 → 수십 개의 PG 연결.

Pool Modes

Session pooling:

• 한 client가 연결을 갖는 동안 전용 PG 연결.
• 가장 안전 (session state 유지).
• 풀 효율 낮음.

Transaction pooling:

• 각 transaction이 끝나면 연결 반환.
• 훨씬 효율적.
• 주의: Session-level features (prepared statements, temp tables) 복잡.

Statement pooling:

• 각 statement마다 반환.
• 가장 공격적.
• 제약 많음.

실전 선택: Transaction pooling이 표준.

설정

pgbouncer.ini:

[databases]
mydb = host=pg.example.com port=5432 dbname=mydb

[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
max_client_conn = 10000
default_pool_size = 20
reserve_pool_size = 5

10,000 client 연결 → 20 PG 연결.

통계

psql -h localhost -p 6432 -d pgbouncer

SHOW POOLS;
SHOW CLIENTS;
SHOW SERVERS;
SHOW STATS;

각 풀의 사용률, 대기 시간 등.

대안

pgcat: Rust로 작성. 멀티 primary, 부하 분산.

odyssey: Yandex 개발. PgBouncer보다 더 많은 기능.

application-level pooling: HikariCP (Java), node-pg-pool 등. 단일 프로세스 내.

실전 아키텍처

Client → PgBouncer (session pool) → PgBouncer (transaction pool) → PostgreSQL

두 단계의 pooling으로 세션 기능과 효율을 모두 확보.

10. 실전 운영

모니터링

중요 메트릭:

1. Replication lag:

SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) 
FROM pg_stat_replication;

2. Checkpoint 활동:

SELECT * FROM pg_stat_bgwriter;

3. Cache hit ratio:

SELECT sum(blks_hit) / (sum(blks_hit) + sum(blks_read))::float 
FROM pg_stat_database;

4. Long-running queries:

SELECT pid, state, query_start, query 
FROM pg_stat_activity 
WHERE state != 'idle' 
ORDER BY query_start;

5. Connections:

SELECT count(*), state FROM pg_stat_activity GROUP BY state;

Tools:

• Prometheus + postgres_exporter.
• pgBadger: 로그 분석.
• pganalyze: SaaS.

Backup 전략

1. pg_basebackup:

• 완전 물리 백업.
• 매일.

2. WAL archiving:

• 연속 백업.
• Point-in-time recovery.

3. Logical backup (pg_dump):

• Schema 변경, 테이블 이전에.
• Plaintext 또는 custom format.

4. PITR (Point-In-Time Recovery):

• Base backup + WAL replay.
• 원하는 시점으로 복구.

# Base backup
pg_basebackup -D /backup/base -P

# WAL archive
archive_command = 'cp %p /backup/wal/%f'

# 복구 시
restore_command = 'cp /backup/wal/%f %p'
recovery_target_time = '2025-04-15 10:00:00'

5. Streaming replica as backup:

• Standby에서 pg_basebackup.
• Primary에 부담 안 줌.

Upgrade 전략

Major upgrade (PG 15 → 16):

1. pg_upgrade:

• 같은 서버에서 in-place upgrade.
• 빠르지만 다운타임 있음.

2. Logical replication:

• 새 버전 PG로 logical replication.
• 기본 데이터 동기화 후 cutover.
• 다운타임 최소.

3. Blue-green:

• 새 서버에 새 버전.
• Logical replication 또는 dump/restore.
• DNS 전환.

장애 대응

증상: Primary 다운

1. Patroni가 자동 failover (~30초).
2. Application reconnect (connection pool 재설정).
3. 모니터링 알림 확인.
4. 원인 분석.

증상: Replication lag 급증

1. Standby 부하 확인.
2. Network latency 체크.
3. Primary의 write 스파이크?
4. hot_standby_feedback 영향?

해결:

• 일시적: 큰 쿼리 kill.
• 구조적: Standby 추가, sync → async 전환.

증상: Connection 한계

FATAL: sorry, too many clients already

1. pg_stat_activity 확인.
2. Idle connection 많으면 pool 조정.
3. max_connections 증가.
4. PgBouncer 도입.

Performance Tuning

shared_buffers: RAM의 25%.

effective_cache_size: RAM의 50-75%.

work_mem: 쿼리당 메모리. 너무 크면 OOM.

maintenance_work_mem: VACUUM, INDEX 용.

wal_compression: 대역폭 절약.

checkpoint_timeout: 기본 5분, 15분으로 증가 가능.

일반적 실수

1. synchronous_standby_names 설정 후 standby 제거 없이 primary 사용 중지:

• Primary가 무한 대기.
• Commit이 멈춤.

2. Replication slot 방치:

• WAL 무한 축적.
• Primary 디스크 부족.

3. Connection 누수:

• App이 connection 반환 안 함.
• Pool 고갈.

4. Vacuum 방치:

• Table bloat.
• 성능 저하.

5. Logical replication으로 DR 구축:

• DDL 복제 안 되는 등 제약.
• Physical replication 권장.

퀴즈로 복습하기

Primary WAL record:
  XLOG_HEAP_INSERT rel=16384 blkno=123 offset=5 data=...

Standby replays exactly:
  rel=16384 blkno=123 offset=5 → write data=...

Primary WAL (logical):
  BEGIN;
  INSERT INTO users (id, name) VALUES (1, 'Alice');
  UPDATE users SET email='alice@new.com' WHERE id=1;
  COMMIT;

Subscriber applies:
  실제 SQL 실행.

                Primary
                   ↓
      ┌────────────┼────────────┐
      ↓            ↓            ↓
  Physical     Physical     Logical
  Standby1     Standby2    Subscriber
  (HA)         (Read)      (→ Kafka)

Primary (Seoul)
  ├─ Sync Standby (Seoul DC2)
  └─ Async Standby (Frankfurt)

synchronous_standby_names = 'ANY 1 (standby1, standby2, standby3)'

마치며: 복제의 예술

핵심 정리

1. WAL: 복제의 기반. 모든 방식의 공통점.
2. Physical streaming: HA와 read scaling의 표준.
3. Sync vs Async: Latency와 durability의 trade-off.
4. Hot standby: Read replica.
5. Replication slots: Safe WAL retention (with risks).
6. Logical replication: Selective, cross-version, CDC.
7. Patroni: 자동 failover의 표준.
8. PgBouncer: Connection 관리.

실전 아키텍처

Production PostgreSQL stack:

                 Application
                      ↓
                  PgBouncer (pooling)
                      ↓
               ┌──────┴──────┐
               ↓             ↓
           Primary      ← Patroni → Standby
              │ (sync replication)
              │
              ├─→ Async standby (read)
              ├─→ Async standby (DR, cross-region)
              └─→ Logical replication (→ Kafka, DW)
                      ↓
                  Monitoring
                  (Prometheus + Grafana)
                      ↓
                  Backup
                  (pg_basebackup + WAL archive)

운영 원칙

1. 모니터링 먼저: Replication lag, connections, slow queries.
2. 자동 failover: 사람 개입 최소화.
3. 정기 backup: WAL archive + base backup.
4. Capacity planning: Disk, memory, connections.
5. Upgrade 테스트: Staging 환경 필수.
6. 장애 대응 리허설: 연습 안 하면 실전에서 망함.

마지막 교훈

PostgreSQL은 가장 완성된 오픈소스 DB 중 하나다. 복제 기능도 수십 년간의 진화 결과다:

• 2000년대: 간단한 파일 기반 복제.
• 2010: Streaming replication (9.0).
• 2012: Cascading replication.
• 2016: Logical replication 도입.
• 2017: Native logical replication (10).
• 2020+: Parallel apply, improved monitoring.

현재 PostgreSQL은 Teradata, Oracle 같은 상용 DB와 견줄 만한 복제 기능을 가진다.

이 글의 지식은 production DB 운영자에게 필수다:

• 왜 lag가 증가하는지.
• 왜 connection이 막히는지.
• 왜 failover가 실패하는지.
• 왜 slot이 디스크를 채우는지.

이 모든 질문의 답이 WAL, replication, connection pool의 작동 원리에 있다.

당신의 PostgreSQL이 프로덕션에서 돌아간다면, 이 지식이 당신의 서비스를 지킨다. 새벽 2시 알림이 울렸을 때, 당황하지 않고 차분히 진단하고 대응할 수 있는 힘은 이해에서 온다.

PostgreSQL은 좋은 도구다. 하지만 좋은 엔지니어가 좋은 운영을 만든다. 이 글이 그 시작이 되기를.

참고 자료

• PostgreSQL Documentation: High Availability, Load Balancing, and Replication
• PostgreSQL Documentation: Logical Replication
• Patroni Documentation
• PgBouncer Documentation
• Debezium PostgreSQL Connector
• The Internals of PostgreSQL
• PostgreSQL Performance Tuning (Annotated Wiki)
• pgAnalyze Blog
• Crunchy Data Blog
• Citus Data Blog (Microsoft)