Back to Thirty Seconds Before the Incident

목표

DELETE 를 잘못 돌린 뒤 사고 30초 전으로 되돌리는 일을 실제로 합니다.

환경

이 파드에는 PostgreSQL 16 이 이미 떠 있습니다(5432, DB labdb, 사용자 lab — 슈퍼유저입니다). 복구본은 같은 파드 안에 5433 으로 따로 띄웁니다.

export PATH=/usr/lib/postgresql/16/bin:$PATH
export PGDATA=/var/lib/postgresql/data

psql -h 127.0.0.1 -U lab -d labdb

작업 디렉터리

/root/work/wal       아카이브된 WAL
/root/work/base      베이스 백업 (원본 — 건드리지 않는다)
/root/work/restore   복구본 (base 를 복사해서 만든다)

단계

1. archive_mode = on + 재시작
2. pg_basebackup → /root/work/base
3. 데이터 2행 + 목표 시각 → 03-target.txt
4. delete from ops_note + pg_switch_wal()
5. restore 디렉터리 + recovery.signal
6. 5433 으로 띄워 확인 (2행이어야 함)
7. pg_dump 와 비교 → 07-dump.txt
8. 정리 → 08-notes.md

반드시 지킬 것

• 원본 위에 복구하지 않습니다. 다른 디렉터리, 다른 포트.
• 복구본 설정에 archive_mode = off 를 넣으세요. 안 넣으면 복구본이 원본의 아카이브를 덮어씁니다.
• 4단계에서 pg_switch_wal() 을 잊지 마세요. 아카이빙은 다 쓴 세그먼트만 복사하므로, 마지막 변경이 아직 안 넘어가 있습니다.

WAL 아카이빙을 켠다

archive_mode 를 켜고 WAL 이 /root/work/wal 로 복사되게 하세요. pg_stat_archiver.archived_count 가 0보다 커야 합니다.

lab 계정은 슈퍼유저입니다. psql -h 127.0.0.1 -U lab -d postgres -c "alter system set archive_mode = on" 처럼 하고, archive_command 는 test ! -f /root/work/wal/%f && cp %p /root/work/wal/%f 로 두세요 — test ! -f 가 덮어쓰기를 막습니다. archive_mode 는 재시작이 필요합니다: pg_ctl -D /var/lib/postgresql/data restart -m fast -w. 그다음 select pg_switch_wal() 로 한 개를 밀어내 확인하세요.

베이스 백업을 뜬다

pg_basebackup 으로 /root/work/base 에 데이터 디렉터리 전체를 받으세요.

pg_basebackup -h 127.0.0.1 -U lab -D /root/work/base -Fp -Xs -P. -Fp 는 풀어서(plain), -Xs 는 백업 도중 생긴 WAL 도 함께 스트리밍해 그것만으로도 뜰 수 있게 합니다. 나중 복구에서 이 디렉터리를 복사해서 씁니다 — 원본은 건드리지 않습니다.

되돌아갈 시각을 남긴다

ops_note 테이블을 만들고 행 두 개를 넣으세요. 그다음 현재 시각을 /root/work/03-target.txt 에 남깁니다. 이 시각이 복구 목표가 됩니다.

create table ops_note(id serial primary key, note text, at timestamptz default now()). 시각은 psql -tAc "select now()" > /root/work/03-target.txt 로 남기세요. 넣고 1초 이상 지난 뒤 시각을 찍어야 그 삽입이 목표 시점 안에 들어갑니다.

사고를 낸다

ops_note 의 행을 전부 지우세요(delete from ops_note). 테이블은 남깁니다. 그리고 pg_switch_wal() 로 그 변경이 담긴 WAL 을 아카이브로 밀어내세요.

WHERE 없는 DELETE 는 실무에서 실제로 자주 나는 사고입니다. pg_switch_wal() 을 부르지 않으면 마지막 변경이 아직 아카이브되지 않아 복구가 그 지점까지 못 갑니다 — 아카이빙은 다 쓴 세그먼트만 복사하기 때문입니다.

복구본을 만든다

베이스 백업을 /root/work/restore 로 복사하고, restore_command·recovery_target_time·port = 5433 을 설정한 뒤 recovery.signal 을 만드세요.

cp -r /root/work/base /root/work/restore && chmod 700 /root/work/restore. 설정은 restore/postgresql.auto.conf 에 씁니다: restore_command = 'cp /root/work/wal/%f %p', recovery_target_time = '<03-target.txt 의 값>', recovery_target_action = 'promote', archive_mode = off, port = 5433. archive_mode 를 끄는 것을 잊지 마세요 — 안 그러면 복구본이 원본의 아카이브를 덮어씁니다.

지운 데이터가 살아 있는지 본다

복구본을 5433 포트로 띄우고 ops_note 의 행 수를 확인하세요. 두 개여야 합니다. 원본(5432)은 여전히 0개입니다.

pg_ctl -D /root/work/restore -l /tmp/restore.log start -w -t 60. 안 뜨면 /tmp/restore.log 를 보세요 — 대부분 restore_command 경로나 목표 시각 형식 문제입니다. 확인: psql -h 127.0.0.1 -p 5433 -U lab -d labdb -c 'select * from ops_note'.

논리 백업으로는 왜 안 되는지 본다

지금(사고 후) 원본을 pg_dump -Fc 로 /root/work/labdb.dump 에 뜨세요. 이 덤프 안의 ops_note 가 몇 행인지 확인해 07-dump.txt 에 적으세요.

pg_dump -h 127.0.0.1 -U lab -Fc -d labdb -f /root/work/labdb.dump. 목록은 pg_restore -l /root/work/labdb.dump. 덤프는 뜬 순간의 사진이라 이미 지워진 뒤의 상태가 담깁니다. 어제 밤 덤프만 있었다면 RPO 는 24시간이라는 뜻입니다.

무엇이 없었으면 못 돌아왔는지

/root/work/08-notes.md 에 세 줄 이상. PITR 에 필요한 세 조각, archive_command 가 실패를 성공이라 보고하면 무슨 일이 생기는지, 그리고 RPO 관점에서 덤프만 두는 것이 무엇을 뜻하는지.

본문에 WAL, RPO, 타임라인 이 들어가야 합니다. 마지막 한 줄이 이 코스의 전부입니다 — 복구해 본 적 없는 백업은 백업이 아니다.