PostgreSQL 스키마 백업/복원 완벽 가이드 – pg_dump 마스터하기

PostgreSQL 데이터베이스를 운영하다 보면 백업은 필수입니다. 서버 이전, 개발/테스트 환경 구성, 데이터 손실 방지 등 다양한 상황에서 백업이 필요하죠. 오늘은 PostgreSQL의 핵심 백업 도구인 pg_dump와 pg_restore를 활용해 스키마를 백업하고 복원하는 모든 방법을 실전 예제와 함께 완벽하게 정리해드립니다.

PostgreSQL 백업의 종류

PostgreSQL은 크게 3가지 백업 방법을 제공합니다:

1. SQL 덤프 (pg_dump)

• 특정 데이터베이스를 SQL 스크립트로 백업
• 가장 유연하고 이식성이 좋음
• 버전 간 호환성 우수
• 오늘 집중할 방법

2. 파일 시스템 레벨 백업

• 데이터 디렉토리 전체를 복사
• 빠르지만 버전 의존성 있음
• 전체 클러스터 백업만 가능

3. 연속 아카이빙 (WAL)

• 실시간 백업 (Point-In-Time Recovery)
• 대규모 운영 환경에 적합
• 설정이 복잡함

pg_dump란?

pg_dump는 특정 데이터베이스를 백업하는 PostgreSQL 유틸리티입니다. 데이터베이스의 스키마(테이블 구조)와 데이터를 SQL 스크립트나 바이너리 형식으로 내보냅니다.

주요 특징:

• 단일 데이터베이스 백업 전문
• 다양한 출력 형식 지원 (plain, custom, directory, tar)
• 선택적 백업 가능 (특정 테이블, 스키마만)
• 서버가 실행 중일 때 백업 가능
• 트랜잭션 일관성 보장

기본 백업 명령어

1. 전체 데이터베이스 백업 (기본)

# 기본 형식 (SQL 텍스트 파일)
pg_dump -U postgres -d mydb > mydb_backup.sql

# 또는 -h 옵션으로 호스트 지정
pg_dump -U postgres -h localhost -d mydb > mydb_backup.sql

# 포트 지정
pg_dump -U postgres -h localhost -p 5432 -d mydb > mydb_backup.sql

결과: mydb_backup.sql 파일 생성 (SQL 스크립트 형태)

2. 압축 백업 (Custom 형식)

# Custom 형식 (압축, pg_restore 전용)
pg_dump -U postgres -F c -d mydb -f mydb_backup.dump

# 또는
pg_dump -U postgres --format=custom -d mydb -f mydb_backup.dump

장점:

• 파일 크기가 작음 (자동 압축)
• 선택적 복원 가능
• 병렬 복원 지원

3. 압축 수준 지정

# 압축 수준 0-9 (0=압축 안 함, 9=최대 압축)
pg_dump -U postgres -F c -Z 9 -d mydb -f mydb_backup.dump

# gzip으로 추가 압축
pg_dump -U postgres -d mydb | gzip > mydb_backup.sql.gz

스키마만 백업하기

데이터 없이 테이블 구조(DDL)만 백업하고 싶을 때 사용합니다.

스키마 전용 백업

# 스키마만 백업 (테이블 구조, 인덱스, 제약조건 등)
pg_dump -U postgres -d mydb --schema-only > mydb_schema.sql

# 또는 -s 옵션
pg_dump -U postgres -d mydb -s > mydb_schema.sql

# Custom 형식으로
pg_dump -U postgres -d mydb -s -F c -f mydb_schema.dump

백업 내용:

• CREATE TABLE 문
• CREATE INDEX 문
• 제약 조건 (PRIMARY KEY, FOREIGN KEY, CHECK 등)
• 시퀀스, 뷰, 함수, 트리거
• 권한 설정

제외 내용:

• INSERT 문 (실제 데이터)

데이터만 백업

# 데이터만 백업 (INSERT 문만)
pg_dump -U postgres -d mydb --data-only > mydb_data.sql

# 또는 -a 옵션
pg_dump -U postgres -d mydb -a > mydb_data.sql

선택적 백업

특정 테이블만 백업

# 단일 테이블 백업
pg_dump -U postgres -d mydb -t users > users_backup.sql

# 여러 테이블 백업
pg_dump -U postgres -d mydb -t users -t orders -t products > tables_backup.sql

# 와일드카드 사용
pg_dump -U postgres -d mydb -t 'public.user*' > user_tables.sql

특정 테이블 제외

# 특정 테이블 제외하고 백업
pg_dump -U postgres -d mydb -T logs -T temp_data > mydb_no_logs.sql

# 여러 테이블 제외
pg_dump -U postgres -d mydb -T logs -T temp_* > mydb_backup.sql

특정 스키마만 백업

PostgreSQL에서 스키마는 네임스페이스 개념입니다 (public, private 등).

# 특정 스키마만 백업
pg_dump -U postgres -d mydb -n public > public_schema.sql

# 여러 스키마 백업
pg_dump -U postgres -d mydb -n schema1 -n schema2 > schemas_backup.sql

# 특정 스키마 제외
pg_dump -U postgres -d mydb -N old_schema > mydb_without_old.sql

pg_dump 주요 옵션 정리

출력 형식 옵션 (-F, –format)

형식	코드	설명	복원 방법
Plain	p	SQL 텍스트 파일 (기본값)	psql
Custom	c	압축된 바이너리 (권장)	pg_restore
Directory	d	디렉토리 형식 (병렬 백업/복원)	pg_restore
Tar	t	tar 아카이브	pg_restore

권장: Custom 형식 (압축 + 유연성)

연결 옵션

-h, --host=HOSTNAME        # 데이터베이스 서버 호스트
-p, --port=PORT            # 포트 번호 (기본: 5432)
-U, --username=USERNAME    # 사용자명
-W, --password             # 비밀번호 프롬프트 강제
-w, --no-password          # 비밀번호 프롬프트 표시 안 함

백업 내용 옵션

-s, --schema-only          # 스키마만 (DDL)
-a, --data-only            # 데이터만 (DML)
-c, --clean                # DROP 문 포함
-C, --create               # CREATE DATABASE 문 포함
-O, --no-owner             # 소유자 복원 안 함
-x, --no-privileges        # 권한 백업 안 함

압축 옵션

-Z, --compress=0-9         # 압축 수준 (0=없음, 9=최대)

선택 옵션

-t, --table=TABLE          # 특정 테이블만
-T, --exclude-table=TABLE  # 특정 테이블 제외
-n, --schema=SCHEMA        # 특정 스키마만
-N, --exclude-schema=SCHEMA # 특정 스키마 제외

기타 유용한 옵션

-v, --verbose              # 상세 출력
-f, --file=FILENAME        # 출력 파일명
--column-inserts           # INSERT 문에 컬럼명 포함
--inserts                  # COPY 대신 INSERT 사용
--no-tablespaces           # 테이블스페이스 정보 제외

실전 백업 시나리오

시나리오 1: 개발 환경 백업

# 전체 백업 (압축)
pg_dump -U postgres -h localhost -d mydb_dev \\
  -F c -Z 9 \\
  -f /backup/mydb_dev_$(date +%Y%m%d_%H%M%S).dump

# 로그 테이블 제외 백업
pg_dump -U postgres -h localhost -d mydb_dev \\
  -T logs -T audit_logs \\
  -F c -f /backup/mydb_dev_no_logs.dump

시나리오 2: 운영 환경 백업 (스크립트)

#!/bin/bash
# backup_postgres.sh

# 변수 설정
DB_NAME="mydb_prod"
DB_USER="postgres"
DB_HOST="localhost"
BACKUP_DIR="/backup/postgres"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.dump"

# 백업 디렉토리 생성
mkdir -p ${BACKUP_DIR}

# 백업 실행
pg_dump -U ${DB_USER} -h ${DB_HOST} -d ${DB_NAME} \\
  -F c -Z 9 \\
  -f ${BACKUP_FILE}

# 백업 확인
if [ $? -eq 0 ]; then
    echo "백업 성공: ${BACKUP_FILE}"

    # 30일 이상 된 백업 파일 삭제
    find ${BACKUP_DIR} -name "${DB_NAME}_*.dump" -mtime +30 -delete
else
    echo "백업 실패!"
    exit 1
fi

실행 권한 부여:

chmod +x backup_postgres.sh

Cron 등록 (매일 새벽 2시):

crontab -e

# 추가
0 2 * * * /path/to/backup_postgres.sh >> /var/log/postgres_backup.log 2>&1

시나리오 3: 원격 서버 백업

# 원격 서버에서 로컬로 백업
pg_dump -U postgres -h remote.server.com -p 5432 -d mydb \\
  -F c -f /local/backup/remote_mydb.dump

# SSH 터널을 통한 백업
ssh -L 5433:localhost:5432 user@remote.server.com
pg_dump -U postgres -h localhost -p 5433 -d mydb -F c -f mydb.dump

시나리오 4: 대용량 데이터베이스 백업 (병렬)

# Directory 형식으로 병렬 백업 (4개 작업)
pg_dump -U postgres -d large_db \\
  -F d -j 4 \\
  -f /backup/large_db_dir

# -j: 병렬 작업 수 (CPU 코어 수만큼 권장)

복원 방법 – psql vs pg_restore

백업 형식에 따라 복원 방법이 다릅니다.

Plain 형식 (SQL 파일) → psql 사용

# 기본 복원
psql -U postgres -d mydb < mydb_backup.sql

# 새 데이터베이스 생성 후 복원
createdb -U postgres mydb_restored
psql -U postgres -d mydb_restored < mydb_backup.sql

# 압축된 파일 복원
gunzip -c mydb_backup.sql.gz | psql -U postgres -d mydb

# 또는
zcat mydb_backup.sql.gz | psql -U postgres -d mydb

Custom/Directory/Tar 형식 → pg_restore 사용

# 기본 복원
pg_restore -U postgres -d mydb mydb_backup.dump

# 기존 객체 삭제 후 복원
pg_restore -U postgres -d mydb -c mydb_backup.dump

# 데이터베이스 생성 포함 복원
pg_restore -U postgres -C -d postgres mydb_backup.dump

# 병렬 복원 (4개 작업)
pg_restore -U postgres -d mydb -j 4 mydb_backup.dump

# 특정 테이블만 복원
pg_restore -U postgres -d mydb -t users mydb_backup.dump

# 스키마만 복원
pg_restore -U postgres -d mydb -s mydb_backup.dump

# 데이터만 복원
pg_restore -U postgres -d mydb -a mydb_backup.dump

pg_restore 주요 옵션

기본 옵션

-d, --dbname=DBNAME        # 대상 데이터베이스
-c, --clean                # 복원 전 기존 객체 삭제
-C, --create               # 데이터베이스 생성
-a, --data-only            # 데이터만 복원
-s, --schema-only          # 스키마만 복원

선택적 복원

-t, --table=TABLE          # 특정 테이블만
-n, --schema=SCHEMA        # 특정 스키마만
-N, --exclude-schema=SCHEMA # 특정 스키마 제외
-T, --exclude-table=TABLE  # 특정 테이블 제외

성능 옵션

-j, --jobs=NUM             # 병렬 작업 수
--disable-triggers         # 트리거 비활성화
--no-owner                 # 소유자 설정 안 함
--no-privileges            # 권한 설정 안 함

오류 처리

-e, --exit-on-error        # 오류 시 중단
-v, --verbose              # 상세 출력
--single-transaction       # 단일 트랜잭션으로 실행

실전 복원 시나리오

시나리오 1: 개발 환경으로 운영 DB 복제

# 1. 운영 DB 백업
pg_dump -U postgres -h prod-server -d mydb_prod \\
  -F c -f mydb_prod.dump

# 2. 개발 DB 생성
createdb -U postgres mydb_dev

# 3. 복원
pg_restore -U postgres -d mydb_dev mydb_prod.dump

# 4. 민감 데이터 마스킹 (선택)
psql -U postgres -d mydb_dev -c "
  UPDATE users SET email = 'test' || id || '@example.com';
  UPDATE users SET phone = '010-0000-0000';
"

시나리오 2: 특정 테이블만 복원

# users 테이블만 백업
pg_dump -U postgres -d mydb -t users -F c -f users.dump

# 다른 DB에 복원
pg_restore -U postgres -d testdb users.dump

# 또는 전체 백업에서 특정 테이블만 복원
pg_restore -U postgres -d mydb -t orders full_backup.dump

시나리오 3: 스키마 구조만 복제

# 스키마만 백업
pg_dump -U postgres -d mydb -s -f mydb_schema.sql

# 새 DB에 스키마 적용
createdb -U postgres mydb_test
psql -U postgres -d mydb_test < mydb_schema.sql

시나리오 4: 데이터 마이그레이션

# 구 서버에서 백업
pg_dump -U postgres -h old-server -d mydb \\
  -F c -f mydb_migration.dump

# 신 서버로 파일 전송
scp mydb_migration.dump user@new-server:/tmp/

# 신 서버에서 복원
ssh user@new-server
createdb -U postgres mydb
pg_restore -U postgres -d mydb -j 4 /tmp/mydb_migration.dump

pg_dumpall – 전체 클러스터 백업

여러 데이터베이스와 글로벌 객체(역할, 테이블스페이스)를 한 번에 백업합니다.

기본 사용법

# 전체 클러스터 백업
pg_dumpall -U postgres > all_databases.sql

# 글로벌 객체만 백업 (역할, 테이블스페이스)
pg_dumpall -U postgres --globals-only > globals.sql

# 역할(사용자)만 백업
pg_dumpall -U postgres --roles-only > roles.sql

# 테이블스페이스만 백업
pg_dumpall -U postgres --tablespaces-only > tablespaces.sql

전체 복원

# 전체 클러스터 복원
psql -U postgres < all_databases.sql

주의: pg_dumpall은 Plain 형식만 지원합니다.

백업 파일 검증

백업이 제대로 되었는지 확인하는 방법입니다.

1. 파일 크기 확인

# 백업 파일 크기 확인
ls -lh mydb_backup.dump

# 예상 크기와 비교
psql -U postgres -d mydb -c "
  SELECT pg_size_pretty(pg_database_size('mydb'));
"

2. 백업 내용 확인 (Custom 형식)

# 백업 파일에 포함된 객체 목록 확인
pg_restore -l mydb_backup.dump

# 결과 예시:
# ; Archive created at 2025-10-02 14:30:25 KST
# ;     dbname: mydb
# ;     TOC Entries: 235
# 3; 2615 2200 SCHEMA - public postgres
# 204; 1259 16385 TABLE public users postgres
# 205; 1259 16391 TABLE public orders postgres

3. 테스트 복원

# 임시 데이터베이스에 복원 테스트
createdb -U postgres mydb_test
pg_restore -U postgres -d mydb_test mydb_backup.dump

# 데이터 확인
psql -U postgres -d mydb_test -c "\\dt"
psql -U postgres -d mydb_test -c "SELECT COUNT(*) FROM users;"

# 테스트 DB 삭제
dropdb -U postgres mydb_test

비밀번호 없이 백업하기

매번 비밀번호를 입력하지 않으려면 .pgpass 파일을 사용합니다.

.pgpass 파일 생성 (Linux/Mac)

# 홈 디렉토리에 .pgpass 파일 생성
vi ~/.pgpass

# 형식: hostname:port:database:username:password
# 추가
localhost:5432:*:postgres:your_password
192.168.1.100:5432:mydb:dbuser:dbpass

# 권한 설정 (필수!)
chmod 600 ~/.pgpass

.pgpass 파일 생성 (Windows)

# %APPDATA%\\postgresql\\pgpass.conf 파일 생성
C:\\Users\\YourName\\AppData\\Roaming\\postgresql\\pgpass.conf

# 동일한 형식으로 작성
localhost:5432:*:postgres:your_password

이후 백업 시 비밀번호 입력 불필요:

pg_dump -U postgres -d mydb -F c -f mydb.dump
# 비밀번호 프롬프트 없이 실행됨

문제 해결 가이드

문제 1: “pg_dump: error: connection failed”

원인: PostgreSQL 서버 미실행 또는 연결 정보 오류

해결:

# PostgreSQL 서비스 확인
sudo systemctl status postgresql

# 또는
pg_isready -h localhost -p 5432

# 서비스 시작
sudo systemctl start postgresql

문제 2: “permission denied”

원인: 백업 디렉토리 권한 부족

해결:

# 디렉토리 권한 확인
ls -ld /backup/

# 권한 부여
sudo chmod 755 /backup/
sudo chown postgres:postgres /backup/

문제 3: “out of memory”

원인: 대용량 데이터베이스 백업 시 메모리 부족

해결:

# Custom 형식 + 압축 사용
pg_dump -U postgres -d large_db -F c -Z 9 -f large_db.dump

# 또는 Directory 형식으로 병렬 백업
pg_dump -U postgres -d large_db -F d -j 4 -f large_db_dir

문제 4: “role does not exist”

원인: 복원할 DB에 원본 DB의 역할(사용자)이 없음

해결:

# 글로벌 객체 먼저 복원
pg_dumpall -U postgres --globals-only > globals.sql
psql -U postgres < globals.sql

# 또는 --no-owner 옵션 사용
pg_restore -U postgres -d mydb --no-owner mydb.dump

문제 5: “already exists” 오류

원인: 복원할 DB에 이미 객체가 존재

해결:

# 기존 객체 삭제 후 복원
pg_restore -U postgres -d mydb -c mydb.dump

# 또는 DB를 처음부터 재생성
dropdb -U postgres mydb
createdb -U postgres mydb
pg_restore -U postgres -d mydb mydb.dump

백업 모범 사례

1. 정기 자동 백업

# Cron 설정 예시
0 2 * * * /path/to/backup_script.sh    # 매일 새벽 2시
0 2 * * 0 /path/to/weekly_backup.sh    # 매주 일요일
0 2 1 * * /path/to/monthly_backup.sh   # 매월 1일

2. 3-2-1 백업 원칙

• 3개의 복사본: 원본 + 백업 2개
• 2가지 다른 매체: 로컬 디스크 + 외부 스토리지
• 1개는 오프사이트: 클라우드 스토리지 (AWS S3, GCS 등)

3. 백업 주기

환경	전체 백업	증분 백업	보관 기간
개발	주 1회	불필요	1개월
테스트	주 2회	불필요	2개월
운영	매일	시간당 (WAL)	3개월+

4. 백업 검증

#!/bin/bash
# 백업 후 자동 검증 스크립트

BACKUP_FILE="$1"

# 백업 파일 존재 확인
if [ ! -f "$BACKUP_FILE" ]; then
    echo "백업 파일 없음!"
    exit 1
fi

# 백업 파일 크기 확인 (최소 1MB)
MIN_SIZE=1048576
FILE_SIZE=$(stat -f%z "$BACKUP_FILE")
if [ $FILE_SIZE -lt $MIN_SIZE ]; then
    echo "백업 파일 크기 이상!"
    exit 1
fi

# 백업 파일 무결성 확인
pg_restore -l "$BACKUP_FILE" > /dev/null
if [ $? -ne 0 ]; then
    echo "백업 파일 손상!"
    exit 1
fi

echo "백업 검증 성공!"

5. 백업 파일 이름 규칙

# 권장 형식
dbname_YYYYMMDD_HHMMSS.dump
dbname_YYYYMMDD_HHMMSS_schema.sql
dbname_YYYYMMDD_HHMMSS_data.dump

# 예시
mydb_20251002_143025.dump
mydb_20251002_143025_schema.sql

클라우드 백업 통합

AWS S3에 백업

#!/bin/bash
# AWS S3 백업 스크립트

DB_NAME="mydb"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="/tmp/${DB_NAME}_${DATE}.dump"
S3_BUCKET="s3://my-postgres-backups"

# 백업 생성
pg_dump -U postgres -d ${DB_NAME} -F c -f ${BACKUP_FILE}

# S3에 업로드
aws s3 cp ${BACKUP_FILE} ${S3_BUCKET}/

# 로컬 파일 삭제
rm ${BACKUP_FILE}

# 30일 이상 된 S3 백업 삭제
aws s3 ls ${S3_BUCKET}/ | while read -r line; do
    file=$(echo $line | awk '{print $4}')
    file_date=$(echo $file | grep -oP '\\d{8}')
    if [ $(($(date +%s) - $(date -d $file_date +%s))) -gt 2592000 ]; then
        aws s3 rm ${S3_BUCKET}/${file}
    fi
done

마무리 – 핵심 정리

기본 백업 명령어:

# 전체 백업 (권장)
pg_dump -U postgres -d mydb -F c -Z 9 -f mydb_backup.dump

# 스키마만 백업
pg_dump -U postgres -d mydb -s -f mydb_schema.sql

# 특정 테이블만 백업
pg_dump -U postgres -d mydb -t users -F c -f users.dump

기본 복원 명령어:

# Custom 형식 복원
pg_restore -U postgres -d mydb mydb_backup.dump

# SQL 파일 복원
psql -U postgres -d mydb < mydb_backup.sql

PostgreSQL 백업은 데이터 안전의 생명줄입니다. 오늘 소개한 방법들을 실무에 적용하시고, 정기적인 백업과 복원 테스트를 통해 데이터를 안전하게 보호하세요!

여러분의 백업 전략은 무엇인가요? 유용한 팁이나 경험이 있다면