[PostgreSQL] "실수로 지운 게 어제 오후 3시" — WAL과 PITR로 특정 시점 복구

설치·접속: PostgreSQL 설치와 접속

부제: 하루 한 번 백업만으로는 그 사이 데이터를 잃는다 — WAL을 이어 붙여 원하는 시각까지 되감는 원리

-- PITR의 재료: WAL(변경 로그)이 어디까지 쌓였는지, 아카이빙이 켜졌는지
SHOW wal_level;      -- 복구용 로그를 남기려면 replica 이상
SHOW archive_mode;   -- WAL을 안전한 곳에 보관하려면 on 이어야
SELECT pg_current_wal_lsn() AS current_lsn,
       pg_walfile_name(pg_current_wal_lsn()) AS wal_file;

 wal_level      -->  replica
 archive_mode   -->  off
 current_lsn |         wal_file
-------------+--------------------------
 0/4B013E80  | 00000001000000000000004B

PITR(Point-In-Time Recovery)의 원리는 이렇다. PostgreSQL은 모든 변경을 WAL(Write-Ahead Log) 이라는 순차 로그에 먼저 적는다. 위 current_lsn은 지금 이 순간 WAL이 도달한 위치다. 그래서 "기준이 되는 물리 백업(base backup) 하나 + 그 이후 쌓인 WAL 전부"가 있으면, WAL을 원하는 시각까지만 재생(replay)해 그 시점의 상태를 정확히 복원할 수 있다. 하루 한 번 백업이라도 WAL만 이어져 있으면 어제 오후 3시로 되감는 게 가능한 이유다.

단, 위 서버는 archive_mode=off라 아직 PITR 준비가 안 됐다. WAL이 재활용되며 사라지기 전에 별도 보관소로 복사(archiving)해 둬야 하기 때문이다. 아카이빙이 도는지는 pg_stat_archiver로 확인한다.

SELECT archived_count, last_archived_wal, failed_count FROM pg_stat_archiver;

 archived_count | last_archived_wal | failed_count
----------------+-------------------+--------------
              0 |                   |            0

archived_count=0 — 한 건도 보관된 게 없다. PITR를 켜려면 (1) wal_level=replica 이상, (2) archive_mode=on + archive_command로 WAL을 보관소에 복사, (3) pg_basebackup으로 기준 백업 확보, (4) 복구 시 recovery.signal + recovery_target_time으로 목표 시각 지정. 이 네 가지가 맞물려야 "특정 시점 복구"가 성립한다.

이렇게도 쓴다

복구 목표를 시각/트랜잭션ID/이름표 중 무엇으로 잡을지 정한다. (조합: 복구 파라미터)

# postgresql.conf (복구 서버)
recovery_target_time = '2026-07-16 15:00:00+09'   # 이 시각 직전까지 재생
# 또는 recovery_target_xid / recovery_target_name

 

WAL을 보관소로 복사하는 아카이브 명령을 건다. (조합: archive_command)

archive_mode = on
archive_command = 'test ! -f /wal_archive/%f && cp %p /wal_archive/%f'

 

WAL 파일이 서버에 몇 개나 쌓여 있는지 본다. (조합: pg_ls_waldir)

SELECT count(*), pg_size_pretty(sum(size)) FROM pg_ls_waldir();

 

지금 이 순간의 스냅샷을 남기려 강제로 WAL을 끊는다. (조합: pg_switch_wal)

SELECT pg_switch_wal();   -- 현재 WAL 세그먼트를 마감하고 아카이빙 유도

 

두 시점 사이 WAL이 얼마나 쌓였는지 바이트로 잰다. (조합: LSN 뺄셈)

SELECT pg_size_pretty(pg_wal_lsn_diff('0/4B013E80','0/4A000000'));

 

전용 도구로 base backup + WAL 관리를 자동화한다. (조합: pgBackRest / barman)

pgbackrest --stanza=shop backup   # 아카이빙·보존·PITR를 한 도구로