PostgreSQL 백업/복구 Part 2 — pg_dump와 pg_dumpall 논리적 백업 실전
Pg_dump는 가장 익숙한 백업 도구지만, 프로덕션 재해 복구의 전부가 아니라 선택적 복구·버전 이전·개발 환경 복제에 강한 보완 도구다. 출력 형식 선택(plain/custom/directory)이 복구 유연성과 성능을 좌우하고, pg_restore의 병렬 복구와 선택적 복구는 실제 장애 대응 시간을 줄이는 핵심 기술이다. pg_dumpall은 클러스터 전역 객체(roles, tablespaces)를 챙기지만 plain SQL 중심이라 대규모 복구에는 혼합 전략이 필요하다.
시리즈 구성
- Part 1 — 백업의 기초와 전략
- Part 2 — 논리적 백업 — pg_dump & pg_dumpall 실전 활용 (현재 편)
- Part 3 — 물리적 백업 — pg_basebackup과 WAL 아카이빙 (연재 예정)
- Part 4 — PITR(Point-in-Time Recovery) 구현 가이드 (연재 예정)
- Part 5 — 엔터프라이즈 백업 도구 비교 — pgBackRest vs Barman vs WAL-G (연재 예정)
- Part 6 — 백업 자동화 & 모니터링, 그리고 복구 훈련 (연재 예정)
목차
- 들어가며 — 논리적 백업의 위치
- pg_dump vs pg_dumpall — 무엇이 다른가
- 출력 형식 완전 정리
- pg_dump 실전 레시피
- pg_restore 실전 레시피
- pg_dumpall 실전 레시피
- 비밀번호 보안 — .pgpass 파일 활용
- 백업 자동화 스크립트
- 대형 DB 성능 최적화
- 논리적 백업의 한계와 물리적 백업이 필요한 시점
- 마치며
1. 들어가며 — 논리적 백업의 위치
Part 1에서 PostgreSQL의 세 가지 백업 방식을 살펴봤다. 논리적 백업, 물리적 백업, WAL 아카이빙이 각각 다른 역할을 맡으며 이를 조합해야 실무 수준의 전략이 완성된다.
그 중 논리적 백업은 가장 먼저 접하게 되는 방식이다. pg_dump는 PostgreSQL과 함께 배포되는 표준 도구로, 데이터베이스를 SQL 또는 아카이브 형식으로 내보낸다. 다른 사용자의 읽기/쓰기를 차단하지 않고 일관된 스냅샷을 생성한다는 점이 핵심 장점이다.
단, PostgreSQL 공식 문서는 이 점을 명확히 짚는다.
"단순한 경우를 제외하고 pg_dump는 프로덕션 데이터베이스의 정기 백업 도구로 적합하지 않다."
— postgresql.org/docs/current/app-pgdump.html
pg_dump는 선택적 복구, 버전 간 마이그레이션, 개발·테스트 환경 복제에서 강력하다. 그러나 PITR이 필요한 프로덕션 재해 복구에는 물리적 백업이 필수다. 논리적 백업은 물리적 백업을 대체하는 것이 아니라 보완하는 역할로 운용해야 한다.
2. pg_dump vs pg_dumpall — 무엇이 다른가
두 도구는 백업 범위부터 다르다.
| 항목 | pg_dump | pg_dumpall |
|---|---|---|
| 대상 범위 | 단일 데이터베이스 | 클러스터 전체 |
| Roles 포함 | 미포함 | 포함 |
| Tablespaces 포함 | 미포함 | 포함 |
| 출력 형식 | plain, custom, directory, tar | plain(SQL)만 |
| 병렬 덤프 | directory 포맷 한정 | 미지원 |
| 선택적 복구 | 지원 | 미지원 |
| 복구 도구 | psql 또는 pg_restore | psql |
pg_dumpall은 내부적으로 각 데이터베이스에 대해 pg_dump를 호출하면서, roles·tablespaces 등 클러스터 전역 객체를 SQL로 함께 내보낸다. 단, 각 데이터베이스의 스냅샷 시점이 완전히 동기화되지는 않는다는 점에 주의해야 한다.
실무에서는 pg_dumpall --globals-only로 전역 객체만 별도 저장하고, 개별 데이터베이스는 pg_dump로 병렬 덤프하는 혼합 전략이 많이 쓰인다.
3. 출력 형식 완전 정리
형식 선택은 복구 유연성과 성능에 직결된다. pg_dump는 네 가지 형식을 지원한다.
| 형식 | 플래그 | 기본 압축 | 병렬 덤프 | 병렬 복구 | 선택적 복구 | 권장 용도 |
|---|---|---|---|---|---|---|
| Plain (SQL) | -Fp | 미지원 | 미지원 | 미지원 | 미지원 | 소형 DB, 직접 열람 |
| Custom | -Fc | 내장 | 미지원 | 지원 | 지원 | 대부분의 프로덕션 |
| Directory | -Fd | 내장 | 지원 | 지원 | 지원 | 초대형 DB, 속도 우선 |
| Tar | -Ft | 미지원 | 미지원 | 지원 | 지원 | 호환성 목적 |
Plain
사람이 읽을 수 있는 SQL 파일로 psql로 직접 복구한다. 선택적 복구와 병렬 처리가 모두 불가하며, 대형 DB에서는 파일 크기가 급격히 커진다.
Custom
대부분의 실무에서 기본값으로 쓰이는 형식이다. 압축이 내장되어 있고 pg_restore로 복구하며, 특정 테이블·스키마만 선택 복구할 수 있다. 병렬 복구(pg_restore -j)도 지원하며 단일 파일로 관리가 편리하다.
Directory
유일하게 병렬 덤프(-j)를 지원하는 형식이다. 테이블별로 별도 파일이 생성되므로 파일 수가 많아 전송·관리가 번거로울 수 있다. 대형 DB에서 덤프 속도가 중요한 경우의 선택지다.
Tar
Custom 포맷과 구조는 같지만 압축이 없다. 특별한 이유가 없다면 Custom보다 선호할 이유는 없다.
4. pg_dump 실전 레시피
4.1 기본 백업
# custom 포맷 기본 백업
pg_dump -Fc -U postgres -h localhost -d mydb \
-f /backups/mydb_$(date +%Y%m%d_%H%M%S).dump
# 원격 서버 백업
pg_dump -Fc -U postgres -h db.example.com -p 5432 -d mydb \
-f /backups/mydb_remote.dump
4.2 압축 조절
# 압축 없음 — 복구 속도 우선
pg_dump -Fc -Z 0 -U postgres -d mydb -f mydb_nocompress.dump
# 최대 압축 — 스토리지 비용 절감
pg_dump -Fc -Z 9 -U postgres -d mydb -f mydb_maxcompress.dump
# 외부 압축 — plain 포맷과 pigz 조합
pg_dump -Fp -U postgres -d mydb | pigz -p 4 > mydb.sql.gz
CPU가 병목이 아닌 환경에서 -Z 0으로 압축을 끄면 전체 덤프 속도가 빨라지는 경우가 있다. 스토리지 I/O 상황에 따라 효과가 달라지므로 실측 후 결정한다.
4.3 선택적 백업 — 테이블, 스키마 단위
# 특정 테이블만 백업
pg_dump -Fc -U postgres -d mydb \
-t users -t orders \
-f critical_tables.dump
# 와일드카드 패턴
pg_dump -Fc -U postgres -d mydb \
-t 'public.user_*' \
-f user_tables.dump
# 특정 스키마 전체 백업
pg_dump -Fc -U postgres -d mydb \
-n analytics \
-f analytics_schema.dump
# 특정 테이블 제외
pg_dump -Fc -U postgres -d mydb \
-T audit_logs -T temp_sessions \
-f mydb_no_logs.dump
특정 테이블만 덤프할 때, 해당 테이블이 참조하는 외래 키 대상 테이블은 포함되지 않는다. 복구 시 의존성 오류가 발생할 수 있으므로 관련 테이블을 함께 포함하거나 --disable-triggers 옵션을 함께 사용해야 한다.
4.4 스키마 전용 / 데이터 전용 / 섹션 백업
# 스키마(DDL)만 — 마이그레이션, 구조 버전 관리
pg_dump -Fp -s -U postgres -d mydb -f schema_only.sql
# 데이터만 — 스키마가 이미 존재하는 환경에 데이터 적재
pg_dump -Fc -a -U postgres -d mydb -f data_only.dump
# 섹션별 분리
pg_dump -Fc --section=pre-data -U postgres -d mydb -f pre_data.dump
pg_dump -Fc --section=data -U postgres -d mydb -f data.dump
pg_dump -Fc --section=post-data -U postgres -d mydb -f post_data.dump
섹션 분리는 대규모 복구 시 유용하다. 데이터를 먼저 적재한 후 인덱스를 마지막에 생성하면 복구 속도가 크게 향상된다.
4.5 대용량 파일 처리
# 파일 크기 제한 우회 — split으로 분할 저장
pg_dump -Fp -U postgres -d mydb | gzip | split -b 2G - /backups/mydb_part_
# 분할 파일 복구
cat /backups/mydb_part_* | gunzip | psql -U postgres -d mydb
# SSH 터널을 통한 원격 백업
pg_dump -Fc -U postgres -d mydb | ssh user@backup-server "cat > /backups/mydb.dump"
4.6 PostgreSQL 18 신규 옵션
PostgreSQL 18에서는 덤프 제어를 위한 옵션이 추가됐다. --sequence-data는 기본 제외되던 시퀀스 데이터를 포함하고, --statistics-only / --no-statistics / --no-data / --no-schema 옵션으로 덤프 내용을 세밀하게 제어할 수 있다. --no-policies는 Row Level Security 정책을 제외해 정책이 다른 환경으로 마이그레이션할 때 유용하다.
# 시퀀스 데이터 포함 (PostgreSQL 18+)
pg_dump -Fc --sequence-data -U postgres -d mydb -f mydb_with_seq.dump
# 통계 정보만 덤프 (PostgreSQL 18+)
pg_dump --statistics-only -U postgres -d mydb -f stats_only.dump
# RLS 정책 제외 (PostgreSQL 18+)
pg_dump -Fc --no-policies -U postgres -d mydb -f mydb_no_rls.dump
이 옵션들은 공식 문서를 통해 현재 운영 버전과의 호환성을 반드시 확인한 후 사용한다.
5. pg_restore 실전 레시피
pg_restore는 custom, directory, tar 포맷 아카이브를 복구한다. plain SQL 포맷은 psql로 복구한다.
5.1 기본 복구
# 전체 DB 복구 (DB가 이미 존재하는 경우)
pg_restore -U postgres -d mydb /backups/mydb.dump
# DB를 자동 생성하며 복구
pg_restore -U postgres -C -d postgres /backups/mydb.dump
# 오류 발생 시 즉시 중단
pg_restore -U postgres -d mydb --exit-on-error /backups/mydb.dump
5.2 병렬 복구
병렬 복구는 custom과 directory 포맷 모두에서 사용할 수 있다. 덤프 생성 시 병렬 옵션을 사용했는지와 무관하게 pg_restore -j를 적용할 수 있다.
# 4개 병렬 워커로 복구
pg_restore -U postgres -d mydb -j 4 /backups/mydb.dump
# directory 포맷 병렬 복구
pg_restore -U postgres -d mydb -j 4 -Fd /backups/mydb_dir/
복구 전 서버 파라미터를 임시 조정하면 속도를 추가로 높일 수 있다.
-- 복구 전 임시 설정 (복구 완료 후 반드시 원복)
ALTER SYSTEM SET shared_buffers = '4GB';
ALTER SYSTEM SET work_mem = '32MB';
ALTER SYSTEM SET max_wal_size = '32GB';
ALTER SYSTEM SET checkpoint_timeout = '60min';
ALTER SYSTEM SET maintenance_work_mem = '2GB';
ALTER SYSTEM SET autovacuum = 'off';
SELECT pg_reload_conf();
autovacuum = off는 데이터 안전성에 영향을 주므로 복구 완료 직후 반드시 재활성화해야 한다. 위 설정값은 서버 사양에 맞게 조정한다.
5.3 선택적 복구
# 특정 테이블만 복구
pg_restore -U postgres -d mydb -t users /backups/mydb.dump
# 특정 스키마만 복구
pg_restore -U postgres -d mydb -n analytics /backups/mydb.dump
# 아카이브 내 객체 목록 확인 (실제 복구 없이)
pg_restore --list /backups/mydb.dump
# 특정 섹션만 복구 — 인덱스/제약 재생성만
pg_restore -U postgres -d mydb --section=post-data /backups/mydb.dump
5.4 Plain SQL 복구
# 기본 복구
psql -U postgres -d mydb < backup.sql
# 오류 발생 시 즉시 중단 (권장)
psql -U postgres -d mydb \
-v ON_ERROR_STOP=1 \
-X \
< backup.sql
# 압축 파일 복구
gunzip -c backup.sql.gz | psql -U postgres -d mydb
ON_ERROR_STOP=1은 복구 중 오류가 발생하면 즉시 중단시켜 부분 적재 상태를 방지한다. 프로덕션 복구 시 항상 설정한다.
6. pg_dumpall 실전 레시피
pg_dumpall은 Roles, Tablespaces를 포함한 클러스터 전체를 백업한다. 서버 전체 이전이나 DR 환경 구성 시 pg_dump만으로는 복구할 수 없는 전역 객체를 챙기는 데 필수다.
6.1 기본 사용법
# 전체 클러스터 백업
pg_dumpall -U postgres -f /backups/cluster_$(date +%Y%m%d).sql
# 글로벌 객체만 (roles + tablespaces, DB 내용 제외)
pg_dumpall -U postgres --globals-only -f /backups/globals_$(date +%Y%m%d).sql
# Roles만
pg_dumpall -U postgres --roles-only -f /backups/roles.sql
# Tablespaces만
pg_dumpall -U postgres --tablespaces-only -f /backups/tablespaces.sql
6.2 복구
# 전체 클러스터 복구 (superuser 필요)
psql -U postgres -f /backups/cluster_20260414.sql
# 권장 혼합 전략: 글로벌 객체 복구 후 개별 DB는 pg_restore로
psql -U postgres -f /backups/globals_20260414.sql
pg_restore -U postgres -C -d postgres /backups/mydb.dump
pg_dumpall의 출력 형식은 plain SQL 전용이라 병렬 복구가 불가능하다. 대규모 클러스터에서는 pg_dumpall --globals-only로 전역 객체만 별도 저장하고, 개별 DB는 pg_dump -Fd -j N으로 병렬 덤프하는 혼합 전략이 권장된다.
7. 비밀번호 보안 — .pgpass 파일 활용
백업 자동화 시 비밀번호를 스크립트에 하드코딩하는 것은 보안상 위험하다. .pgpass 파일을 활용하면 비밀번호를 소스 밖으로 분리할 수 있다.
# ~/.pgpass 형식: hostname:port:database:username:password
echo "localhost:5432:mydb:backup_user:securepassword" >> ~/.pgpass
chmod 600 ~/.pgpass
# 이제 비밀번호 입력 없이 실행 가능
pg_dump -U backup_user -d mydb -Fc -f mydb.dump
환경 변수를 사용하는 경우 반드시 사용 후 해제해야 한다.
export PGPASSWORD="securepassword"
pg_dump -U backup_user -d mydb -Fc -f mydb.dump
unset PGPASSWORD
PGPASSWORD는 프로세스 목록에 값이 노출될 수 있어 장기 자동화 스크립트의 기본값으로는 권장하지 않는다. .pgpass 또는 pg_service.conf를 우선한다.
8. 백업 자동화 스크립트
8.1 일별 백업 스크립트
#!/bin/bash
set -euo pipefail
DB_HOST="localhost"
DB_USER="backup_user"
DB_NAME="mydb"
BACKUP_DIR="/backups/postgresql"
RETENTION_DAYS=30
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${TIMESTAMP}.dump"
LOG_FILE="/var/log/pg_backup.log"
mkdir -p "${BACKUP_DIR}"
log() {
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "${LOG_FILE}"
}
log "백업 시작: ${DB_NAME}"
if pg_dump -Fc \
-h "${DB_HOST}" \
-U "${DB_USER}" \
-d "${DB_NAME}" \
-f "${BACKUP_FILE}"; then
FILE_SIZE=$(du -sh "${BACKUP_FILE}" | cut -f1)
log "백업 완료: ${BACKUP_FILE} (${FILE_SIZE})"
else
log "ERROR: 백업 실패!"
exit 1
fi
DELETED=$(find "${BACKUP_DIR}" -name "${DB_NAME}_*.dump" \
-mtime +${RETENTION_DAYS} -delete -print | wc -l)
log "만료 백업 ${DELETED}개 삭제 (보관 기간: ${RETENTION_DAYS}일)"
8.2 크론 등록
# 매일 새벽 2시 실행
0 2 * * * /usr/local/bin/pg_backup_daily.sh >> /var/log/pg_backup.log 2>&1
# 글로벌 객체 백업 (매주 일요일 새벽 1시)
0 1 * * 0 pg_dumpall -U postgres --globals-only \
-f /backups/globals_$(date +\%Y\%m\%d).sql
스크립트는 성공/실패 로그, 보관 정책, 복구 테스트까지 함께 설계해야 한다. 백업 파일이 생성된 것만 확인하는 것으로는 부족하다.
9. 대형 DB 성능 최적화
병렬 덤프에서 -j는 병렬 작업 수만큼 DB 연결을 늘린다(병렬 수 + 1). max_connections 설정이 충분한지 확인하고, CPU·메모리·IOPS 지표를 모니터링하며 병렬 수를 조정해야 한다.
# 8코어 서버 기준 대형 DB 덤프 예시
pg_dump \
-Fd \
-j 6 \
-Z 0 \
-U postgres \
-d large_db \
-f /backups/large_db_dir/
# 병렬 복구
pg_restore \
-Fd \
-j 6 \
-U postgres \
-d large_db \
/backups/large_db_dir/
병렬도를 높일수록 항상 빨라지는 것은 아니다. 테이블 수가 적고 하나의 거대한 테이블이 대부분을 차지하는 구조에서는 병렬 효과가 제한적이다. 또한 압축 조정(-Z 0 vs 기본값)은 CPU와 I/O 병목 상황에 따라 효과가 달라지므로 실제 환경에서 측정해 결정한다.
10. 논리적 백업의 한계와 물리적 백업이 필요한 시점
논리적 백업이 강점을 발휘하는 상황과 그렇지 않은 상황을 명확히 구분해야 한다.
논리적 백업이 적합한 경우
- 특정 테이블·스키마의 선택적 복구
- PostgreSQL 메이저 버전 업그레이드 (낮은 버전에서 높은 버전으로)
- 다른 OS나 아키텍처로 데이터 이전
- 개발·테스트 환경 데이터 복제
- 장기 보관용 이식 가능한 스냅샷
물리적 백업이 반드시 필요한 경우
- PITR(Point-in-Time Recovery) 구현
- 대형 DB의 빠른 전체 복구 (재해 복구, RTO 단축)
- Streaming Replication Standby 초기화
- RPO를 수 분 이하로 설정해야 하는 환경
pg_dump만으로 프로덕션 백업을 완결지으려는 것은 위험하다. 논리 백업은 물리 백업을 보완하는 역할로 운용하는 것이 올바른 전략이다.
11. 마치며 — 복구해봐야 진짜 백업이다
pg_dump와 pg_restore는 배우기 쉽지만, 옵션 하나하나가 실제 복구 상황에서 중요한 차이를 만든다. 형식 선택, 병렬 설정, 선택적 복구 범위를 미리 결정해두고, 정기적으로 실제 복구 테스트를 수행하는 것이 가장 중요하다.
"한 번도 테스트해보지 않은 백업은 백업이 아니다."
다음 파트에서는 프로덕션 PITR의 기반이 되는 물리적 백업 — pg_basebackup과 WAL 아카이빙을 다룬다.