Lv.3 중급PostgreSQL

2026.05.1322분 읽기Lv.3 중급

시리즈PostgreSQL 백업/복구 완전 정복 · 파트 4/6시리즈 허브 보기

PostgreSQL 백업/복구 Part 4 — PITR(Point-in-Time Recovery) 구현 가이드

PITR은 설정 기능이 아니라 베이스 백업, 연속된 WAL 아카이브, 복구 목표, 검증 절차가 함께 맞아야 성공하는 운영 절차다. recovery_target_time부터 named restore point까지 복구 목표 선택이 복구 품질을 좌우하고, pause 상태에서의 데이터 검증이 서비스 전환보다 앞서야 한다. PITR의 신뢰도는 평소 WAL 아카이빙 모니터링과 정기 복구 훈련에서 결정된다.

시리즈 구성

• 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 — 백업 자동화 & 모니터링, 그리고 복구 훈련 (연재 예정)

목차

1. 들어가며 — PITR이 타임머신인 이유
2. PITR 전제 조건 체크리스트
3. 복구 목표(Recovery Target) 파라미터 완전 정리
4. PITR 단계별 실전 가이드
5. 복구 목표 유형별 실전 요약
6. 타임라인(Timeline) — PITR의 핵심 개념
7. 실전 시나리오별 복구 가이드
8. PITR 관련 흔한 오류와 해결법
9. 복구 훈련 — 정기적으로 실제로 해봐야 한다
10. 마치며

1. 들어가며 — PITR이 타임머신인 이유

Part 3에서 pg_basebackup과 WAL 아카이빙의 설정 방법을 익혔다. 이번 파트는 그 모든 준비가 실제로 데이터를 살리는 순간에 대한 이야기다.

다음 시나리오를 상상해보자.

상황: 오늘 오후 2시 35분, 개발자가 실수로
      중요 테이블에 WHERE 없이 DELETE를 실행했다.

마지막 전체 백업: 어제 새벽 1시
마지막 WAL 아카이빙: 2분 전 (오후 2시 33분)

질문: 오후 2시 34분 59초 상태로 복구할 수 있는가?
답:   WAL 아카이빙이 설정되어 있다면 — 가능하다.

이것이 PITR(Point-in-Time Recovery)이다. 베이스 백업과 이후 축적된 WAL을 순서대로 재생(replay)하여 임의의 과거 시점으로 데이터베이스를 되돌리는 기술이다.

2. PITR 전제 조건 체크리스트

PITR을 수행하려면 사전에 다음이 모두 갖춰져 있어야 한다.

□ wal_level = replica (또는 logical)
□ archive_mode = on
□ archive_command 설정 및 정상 동작 확인
□ pg_basebackup으로 생성한 베이스 백업 존재
□ 베이스 백업 시점 ~ 복구 목표 시점까지의 WAL 아카이브 연속 보존
□ backup_manifest 파일 존재 (PostgreSQL 13+, pg_verifybackup용)

가장 흔한 PITR 실패 원인은 WAL 아카이브가 중간에 끊겨 있는 경우다. 아카이빙 실패를 방치하면, 정작 복구가 필요한 순간에 WAL 체인에 구멍이 생겨 목표 시점까지 재생할 수 없다. Part 3에서 다룬 pg_stat_archiver 모니터링이 그래서 중요하다.

3. 복구 목표(Recovery Target) 파라미터 완전 정리

PostgreSQL은 어느 시점까지 WAL을 재생할지 지정하는 여러 복구 목표 방식을 지원한다. 이 중 하나만 사용할 수 있으며, 둘 이상 지정하면 마지막 항목이 적용된다.

3.1 시간 기준 — recovery_target_time (가장 일반적)

# 특정 타임스탬프까지 복구 (타임존 명시 권장)
recovery_target_time = '2026-04-14 14:34:59 Asia/Seoul'

# UTC 명시
recovery_target_time = '2026-04-14 05:34:59 UTC'

가장 직관적이고 실무에서 가장 많이 쓰인다. "사고가 발생한 시각 1분 전"처럼 사람이 이해하기 쉬운 기준으로 복구 지점을 지정할 수 있다.

3.2 트랜잭션 ID 기준 — recovery_target_xid

# 특정 트랜잭션 ID 직전까지 복구
recovery_target_xid = '1234567'

사고를 일으킨 트랜잭션 ID를 정확히 알고 있을 때 유용하다. 해당 트랜잭션 직전까지만 재생하므로 오염된 변경을 정확히 배제할 수 있다.

-- 사고 발생 후 PostgreSQL 로그에서 트랜잭션 ID 확인
-- LOG: transaction 1234567 at ...
SELECT xmin, xmax, * FROM pg_class LIMIT 10;

3.3 LSN 기준 — recovery_target_lsn

# 특정 WAL 물리 위치까지 복구
recovery_target_lsn = '0/15D68C50'

타임스탬프보다 더 정밀하게 복구 지점을 제어할 수 있으나, LSN 값을 미리 알고 있어야 한다는 전제가 필요하다.

-- 현재 LSN 확인
SELECT pg_current_wal_lsn();

3.4 Named Restore Point — recovery_target_name

-- 중요 작업 전 복원 포인트 생성
SELECT pg_create_restore_point('before_schema_migration_v2');
SELECT pg_create_restore_point('before_bulk_delete');

# 해당 이름의 복원 포인트까지 복구
recovery_target_name = 'before_schema_migration_v2'

이름 있는 복원 포인트는 타임스탬프나 XID보다 훨씬 직관적이다. 대규모 스키마 변경, 배치 삭제, 마이그레이션 등 위험한 작업 전에 반드시 복원 포인트를 생성하는 습관을 들이자. pg_create_restore_point()는 비용이 없다.

3.5 즉시 복구 — recovery_target = 'immediate'

# 베이스 백업이 일관성을 갖춘 시점까지만 복구 (WAL 추가 재생 없음)
recovery_target = 'immediate'

3.6 recovery_target_action — 복구 완료 후 동작

복구 목표 도달 후 서버가 어떻게 동작할지를 결정하는 가장 중요한 파라미터 중 하나다.

# 권장: pause로 먼저 데이터를 검증한 후 promote 여부 결정
recovery_target_action = 'pause'

pause 상태에서 데이터를 확인한 뒤:

-- 복구 지점이 맞으면 → 복구 완료 후 운영 모드 전환
SELECT pg_wal_replay_resume();

-- 복구 지점이 틀리면 → 서버 종료 후 목표 시각을 조정하여 재시도

4. PITR 단계별 실전 가이드

시나리오: 2026년 4월 14일 오후 2시 35분에 테이블 DROP이 발생했고, 2시 34분 59초 상태로 복구한다.

Step 1. 상황 파악 및 복구 준비

# 서비스 즉시 중단 (추가 변경 방지)
sudo systemctl stop postgresql

# 현재 데이터 디렉토리를 보존 (삭제 금지 — 나중에 참조할 수 있음)
mv /var/lib/postgresql/17/main \
   /var/lib/postgresql/17/main_DAMAGED_$(date +%Y%m%d_%H%M%S)

# 복구용 새 데이터 디렉토리 생성
mkdir /var/lib/postgresql/17/main
chown postgres:postgres /var/lib/postgresql/17/main

손상된 데이터 디렉토리를 즉시 삭제하지 않는 것이 중요하다. 복구 시도 중 참조하거나 포렌식이 필요할 수 있다.

Step 2. 베이스 백업 복원

# tar 포맷 백업의 경우
BACKUP_DIR="/backups/basebackup/20260414_010000"

# base.tar.gz → 데이터 디렉토리로 압축 해제
sudo -u postgres tar -xzf "${BACKUP_DIR}/base.tar.gz" \
  -C /var/lib/postgresql/17/main

# pg_wal 디렉토리가 없으면 생성
mkdir -p /var/lib/postgresql/17/main/pg_wal

# plain 포맷 백업의 경우
# rsync -a "${BACKUP_DIR}/" /var/lib/postgresql/17/main/

Step 3. WAL 아카이브 연속성 확인

복구 목표 시점까지의 WAL 아카이브가 연속으로 존재하는지 확인한다. 체인에 구멍이 있으면 복구가 중단된다.

# backup_label에서 필요한 첫 번째 WAL 세그먼트 확인
cat /var/lib/postgresql/17/main/backup_label
# START WAL LOCATION: 0/3000028 (file 000000010000000000000003)
# START TIME: 2026-04-14 01:00:05 KST

# 해당 세그먼트부터 아카이브에 존재하는지 확인
ls /var/lib/postgresql/archive/ | grep "^000000010000000000000003"

# 아카이브 내 최신 WAL 파일 확인
ls -lht /var/lib/postgresql/archive/ | head -10

Step 4. recovery.signal 생성 및 복구 파라미터 설정

PostgreSQL 12부터 recovery.conf는 폐지됐다. recovery.signal 파일을 데이터 디렉토리에 생성하고, 복구 파라미터는 postgresql.conf 또는 postgresql.auto.conf에 작성한다.

# recovery.signal 파일 생성 (내용은 비어있어도 됨)
sudo -u postgres touch /var/lib/postgresql/17/main/recovery.signal

# postgresql.auto.conf에 복구 파라미터 추가
sudo -u postgres tee -a /var/lib/postgresql/17/main/postgresql.auto.conf << 'EOF'

# PITR 복구 설정
# WAL 파일을 아카이브에서 가져오는 명령
restore_command = 'cp /var/lib/postgresql/archive/%f %p'

# 복구 목표 시각 (사고 발생 직전)
recovery_target_time = '2026-04-14 14:34:59 Asia/Seoul'

# 복구 완료 후 pause 상태에서 데이터 검증
recovery_target_action = 'pause'
EOF

PostgreSQL 12 이전에는 recovery.conf 파일을 별도로 작성했다. v17/v18에서 이 파일은 인식되지 않으며, recovery.signal과 postgresql.auto.conf를 사용해야 한다.

Step 5. 복구 시작 및 진행 모니터링

# PostgreSQL 시작 — 자동으로 복구 모드 진입
sudo systemctl start postgresql

# 복구 진행 상황 실시간 모니터링
sudo tail -f /var/log/postgresql/postgresql-17-main.log

정상 진행 중에 볼 수 있는 로그 메시지다.

LOG:  starting point-in-time recovery to 2026-04-14 14:34:59+09
LOG:  restored log file "000000010000000000000003" from archive
LOG:  redo starts at 0/3000028
LOG:  consistent recovery state reached at 0/3000100
LOG:  restored log file "000000010000000000000047" from archive
LOG:  recovery stopping before commit of transaction 1234568,
      time 2026-04-14 14:35:02.341+09
LOG:  pausing at the end of recovery
HINT: Execute pg_wal_replay_resume() to promote.

복구 진행 중 상태 확인:

-- 복구 모드인지 확인
SELECT pg_is_in_recovery();
-- t (true = 복구 모드)

-- 현재 재생 중인 WAL 위치 확인
SELECT pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();

Step 6. 데이터 검증 — 이 단계가 가장 중요하다

recovery_target_action = 'pause'로 복구가 일시 정지된 상태에서, 데이터가 올바르게 복구됐는지 확인한다. 복구 시점이 맞지 않으면 이 단계에서 발견해야 한다.

-- DROP됐던 테이블이 다시 존재하는지 확인
\dt public.*

-- 테이블에 데이터가 있는지 확인
SELECT COUNT(*) FROM orders;
SELECT MAX(created_at) FROM orders;

-- 복구 목표 직전까지의 데이터가 맞는지 확인
SELECT * FROM orders ORDER BY created_at DESC LIMIT 5;

검증 결과에 따른 분기:

✅ 데이터가 올바른 경우
   → Step 7로 진행 (복구 완료)

❌ 복구 시각이 너무 이른 경우 (데이터 부족)
   → 서버 종료 후 recovery_target_time을 더 늦게 수정하여 재시도

❌ 복구 시각이 너무 늦은 경우 (사고 데이터 포함)
   → 서버 종료 후 recovery_target_time을 더 이르게 수정하여 재시도

목표 시각을 조정해야 할 경우:

# 서버 종료
sudo systemctl stop postgresql

# postgresql.auto.conf의 recovery_target_time 수정
nano /var/lib/postgresql/17/main/postgresql.auto.conf

# recovery.signal 파일 다시 생성 (복구 완료 시 자동 삭제됨)
sudo -u postgres touch /var/lib/postgresql/17/main/recovery.signal

# 베이스 백업부터 다시 복원 후 재시도

Step 7. 복구 완료 — 운영 모드 전환

데이터 검증이 완료됐으면 복구를 마무리하고 운영 모드로 전환한다.

-- 복구 완료 및 운영 모드로 프로모트
SELECT pg_wal_replay_resume();

# recovery.signal 파일이 사라져야 정상
ls /var/lib/postgresql/17/main/recovery.signal
# ls: cannot access ...: No such file or directory

# 운영 모드 확인
psql -U postgres -c "SELECT pg_is_in_recovery();"
# f (false = 정상 운영 모드)

Step 8. 복구 직후 필수 작업

-- 시퀀스 현재값 확인 (복구 후 시퀀스가 과거 값으로 돌아갈 수 있음)
SELECT setval('orders_id_seq', (SELECT MAX(id) FROM orders));

-- VACUUM ANALYZE 실행 (통계 정보 갱신)
VACUUM ANALYZE;

# 즉시 새 베이스 백업 실행
# 복구 후 새로운 타임라인이 생성되므로 새 백업이 필수
pg_basebackup -h localhost -U backup_user \
  -D /backups/basebackup/$(date +%Y%m%d_%H%M%S)_post_recovery \
  -Ft -Xs -P --checkpoint=fast

PITR 복구 후에는 새로운 타임라인이 생성된다. 이전 타임라인의 WAL로는 새 타임라인의 변경을 추적할 수 없으므로, 복구 직후를 새로운 백업 기준점으로 삼아야 한다.

5. 복구 목표 유형별 실전 요약

6. 타임라인(Timeline) — PITR의 핵심 개념

PITR을 이해하려면 타임라인 개념을 반드시 알아야 한다. 복잡한 복구 시나리오에서 특히 중요하다.

PITR 복구 후 운영을 재개하면 PostgreSQL은 새로운 타임라인을 시작한다. 이 덕분에 동일한 베이스 백업으로 여러 번의 복구 시도를 서로 충돌 없이 수행할 수 있다.

# WAL 아카이브에서 타임라인 히스토리 파일 확인
ls /var/lib/postgresql/archive/*.history
# 예: 00000002.history ← 타임라인 2가 생성됐음을 의미

# 히스토리 파일 내용 확인
cat /var/lib/postgresql/archive/00000002.history
# 1    0/3F000000    no recovery target specified
# → 타임라인 1에서 0/3F000000 LSN까지 재생 후 타임라인 2 시작

7. 실전 시나리오별 복구 가이드

시나리오 A: 실수로 테이블 DROP

restore_command = 'cp /var/lib/postgresql/archive/%f %p'
recovery_target_time = '2026-04-14 14:34:59 Asia/Seoul'
recovery_target_action = 'pause'

시나리오 B: 스키마 마이그레이션 실패 롤백

-- 마이그레이션 실행 전에 반드시 실행
SELECT pg_create_restore_point('before_v3_migration');

restore_command = 'cp /var/lib/postgresql/archive/%f %p'
recovery_target_name = 'before_v3_migration'
recovery_target_action = 'pause'

시나리오 C: 최대한 최신 상태로 (전체 WAL 재생)

# 모든 WAL을 끝까지 재생 (복구 목표 미지정)
restore_command = 'cp /var/lib/postgresql/archive/%f %p'
recovery_target_action = 'promote'

시나리오 D: 데이터 검증용 복사본 생성 (프로덕션 무중단)

별도 서버에 복구본을 만들어 데이터를 검증하고, 필요한 데이터만 추출해서 운영 DB에 적용하는 방식이다. 서비스 중단 없이 복구가 가능한 가장 안전한 방법이다.

# 1. 별도 서버에 베이스 백업 복원
mkdir /restore/verify_db
tar -xzf /backups/basebackup/20260414_010000/base.tar.gz \
  -C /restore/verify_db

# 2. 복구 설정
touch /restore/verify_db/recovery.signal
cat >> /restore/verify_db/postgresql.auto.conf << 'EOF'
restore_command = 'cp /var/lib/postgresql/archive/%f %p'
recovery_target_time = '2026-04-14 14:34:59 Asia/Seoul'
recovery_target_action = 'pause'
port = 5433
EOF

# 3. 복구본 서버 시작
pg_ctl -D /restore/verify_db start

# 4. 필요한 테이블 데이터 추출
pg_dump -h localhost -p 5433 -U postgres -t orders \
  -Fc -f /tmp/orders_recovered.dump

# 5. 추출한 데이터를 운영 DB에 선택적 적용
pg_restore -h localhost -p 5432 -U postgres \
  -d production_db -t orders \
  --data-only /tmp/orders_recovered.dump

8. PITR 관련 흔한 오류와 해결법

오류 1: WAL 파일을 찾을 수 없음

FATAL: could not find file "000000010000000000000047" in archive

원인: 아카이브에 해당 WAL 파일이 없거나 restore_command가 잘못됐다.

# restore_command 직접 테스트
cp /var/lib/postgresql/archive/000000010000000000000047 /tmp/test_wal
echo $?   # 0이어야 정상

# 아카이브에 해당 파일이 있는지 확인
ls -la /var/lib/postgresql/archive/ | grep "000000010000000000000047"

오류 2: 복구 목표 시각이 아카이브 범위를 벗어남

FATAL: recovery ended before configured recovery target was reached

원인: recovery_target_time이 WAL 아카이브의 마지막 시각보다 미래로 설정됐다.

# 가장 최근 WAL 파일의 내용 확인
pg_waldump -p /var/lib/postgresql/archive \
  $(ls /var/lib/postgresql/archive | grep -v history | tail -1) \
  | tail -5

오류 3: recovery.conf 파일 인식 안 됨 (v12 이상)

WARNING: ignoring recovery.conf; use postgresql.conf or
         postgresql.auto.conf for recovery parameters

해결: recovery.conf를 제거하고 recovery.signal 파일을 생성한 뒤, 파라미터는 postgresql.auto.conf에 작성한다.

오류 4: 복구 후 시퀀스 중복 키 오류

ERROR: duplicate key value violates unique constraint "orders_pkey"

원인: 복구 후 시퀀스가 과거 값으로 돌아가 새 INSERT 시 기존 ID와 충돌한다.

-- 각 테이블의 시퀀스를 현재 MAX 값으로 재설정
SELECT setval(pg_get_serial_sequence('orders', 'id'),
              (SELECT MAX(id) FROM orders));

9. 복구 훈련 — 정기적으로 실제로 해봐야 한다

PITR 복구는 장애 발생 후 처음 시도하면 반드시 실수가 생긴다. 분기 1회 이상 실제 복구 훈련(Restore Drill)을 강력히 권장한다.

훈련 체크리스트:

□ 별도 환경에서 베이스 백업 복원 성공
□ 지정한 시각으로 PITR 복구 성공
□ 복구 후 데이터 무결성 확인 (행 수, 최신 레코드 시각 등)
□ recovery.signal 파일이 정상 삭제됐는지 확인
□ 전체 복구 소요 시간 측정 (RTO 검증)
□ 복구 직후 새 베이스 백업 생성 확인
□ 복구 절차 문서 최신화

10. 마치며 — 준비된 복구만이 진짜 복구다

PITR은 "있으면 좋은 기능"이 아니라 프로덕션 PostgreSQL 운영의 필수 안전망이다.

준비의 3원칙:

1. WAL 아카이빙은 항상 모니터링한다 — 조용한 실패가 가장 위험하다
2. 중요 작업 전에는 항상 복원 포인트를 생성한다 — pg_create_restore_point()는 비용이 없다
3. 정기적으로 실제 복구를 연습한다 — 복구 절차는 근육 기억이 되어야 한다

"재해는 예고 없이 오지만, 복구는 준비한 자에게만 성공한다."

다음 파트에서는 내장 도구의 한계를 넘어서는 엔터프라이즈 백업 도구 3인방 — pgBackRest, Barman, WAL-G를 심층 비교한다.

참고 자료

• PostgreSQL 공식 문서 — 25.3 Continuous Archiving and PITR
• PostgreSQL 공식 문서 — Recovery Target Settings
• PostgreSQL 18 Release Notes