백업과 복구 — pg_dump·pg_basebackup·PITR

고급 파트의 시작. "백업 안 한 사람과 백업 안 했는데 했다고 믿었던 사람" — DB 운영의 격언. 20편은 PostgreSQL 의 3가지 백업 방식과 그 사촌 PITR(시점 복구) 까지 정리합니다.

백업 3가지 — 한 표

pg_dump — 일상의 백업

# SQL 텍스트 (가장 단순, 큰 DB 는 비효율)
pg_dump -U postgres mydb > mydb.sql

# 커스텀 (압축·병렬 복원 가능 — 권장)
pg_dump -U postgres -Fc -f mydb.dump mydb

# 디렉터리 + 병렬
pg_dump -U postgres -Fd -j 4 -f mydb_dir mydb
#                        ^ 4개 병렬 (큰 DB)

# 특정 테이블만
pg_dump -U postgres -t orders -t users mydb > partial.sql

# 스키마만 (데이터 X)
pg_dump -U postgres --schema-only mydb > schema.sql

# 데이터만 (테이블 정의 X)
pg_dump -U postgres --data-only mydb > data.sql

pg_restore — 복원

# 커스텀 형식
pg_restore -U postgres -d mydb mydb.dump

# 병렬 복원 (큰 DB)
pg_restore -U postgres -d mydb -j 4 mydb.dump

# 깨끗한 DB 에 복원 (기존 객체 DROP 후)
pg_restore -U postgres -d mydb --clean --if-exists mydb.dump

# 특정 테이블만
pg_restore -U postgres -d mydb -t orders mydb.dump

# SQL 텍스트는 psql 로
psql -U postgres mydb < mydb.sql

cron 으로 일일 백업 — 실전 스크립트

#!/bin/bash
# /usr/local/bin/pg_backup.sh
set -euo pipefail

BACKUP_DIR=/var/backups/pg
DATE=$(date +%Y%m%d-%H%M%S)
RETENTION_DAYS=14

mkdir -p "$BACKUP_DIR"

# 백업
pg_dump -U postgres -Fc -f "$BACKUP_DIR/mydb_$DATE.dump" mydb

# 오래된 것 제거
find "$BACKUP_DIR" -name "mydb_*.dump" -mtime +$RETENTION_DAYS -delete

# 원격 저장 (S3 등)
aws s3 cp "$BACKUP_DIR/mydb_$DATE.dump" "s3://my-backup/pg/"

echo "Backup completed: $DATE"

# cron 등록
0 3 * * * /usr/local/bin/pg_backup.sh >> /var/log/pg_backup.log 2>&1
# 매일 03:00 실행

pg_basebackup — 물리적 백업 (PITR 기반)

# 클러스터 파일 통째 복사
pg_basebackup -U replicator -D /var/backups/base -Fp -Xs -P

# -D : 저장 경로
# -Fp : plain (파일들) — tar 도 가능 (-Ft)
# -Xs : streaming — WAL 같이 받음 (시점 복구 가능)
# -P : 진행 표시

# 사전 — replication slot + 역할
CREATE USER replicator REPLICATION LOGIN PASSWORD '...';
# pg_hba.conf 에 replication 접속 허용

WAL 아카이브 + PITR — 임의 시점 복구

PITR(Point-In-Time Recovery) = "어제 17:43 직전 상태로 돌려달라" 가 가능한 복구. 베이스 백업 + 그 이후 WAL(Write-Ahead Log) 을 차곡차곡 모아둬야 함.

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'
# 또는 보통 S3·rsync 로 외부에

# 1) 일정 주기로 베이스 백업
pg_basebackup -D /backup/base-2026-05-17 -Fp -Xs -P

# 2) WAL 은 archive_command 가 계속 모음
# /var/lib/postgresql/wal_archive/ 에 쌓임

# 3) 복구 시점에 — 새 PG 인스턴스 시작
# postgresql.conf
restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2026-05-17 17:43:00'

# 또는
recovery_target_lsn = '0/3A0001E0'   # 특정 LSN 으로
recovery_target_xid = '12345'         # 특정 트랜잭션 직전

현대적 도구 — pgBackRest · WAL-G · Barman

# pg_dump·pg_basebackup 직접 운영은 손이 많이 감
# 실전에서는 백업 매니저 도구 사용

# pgBackRest (가장 추천)
pgbackrest --stanza=mydb backup --type=full
pgbackrest --stanza=mydb backup --type=incr   # 증분
pgbackrest --stanza=mydb restore --target-time="2026-05-17 17:43:00"

# WAL-G — Yandex 발, S3·GCS 호환
wal-g backup-push /var/lib/postgresql/data

# Barman — 이탈리아 발, 멀티 서버

백업 전략 — 한 표

3-2-1 백업 규칙

안티패턴 5가지