Back to Thirty Seconds Before the Incident

Goal

You will actually practice rolling back to 30 seconds before the incident after a DELETE was run by mistake.

Environment

PostgreSQL 16 is already running in this Pod (port 5432, database labdb, user lab — a superuser). You start the recovered copy separately in the same Pod on port 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

Working directories

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

Steps

1. archive_mode = on + restart
2. pg_basebackup → /root/work/base
3. 2 rows of data + target time → 03-target.txt
4. delete from ops_note + pg_switch_wal()
5. restore directory + recovery.signal
6. Start it on 5433 and check (it must show 2 rows)
7. Compare with pg_dump → 07-dump.txt
8. Wrap up → 08-notes.md

Rules you must follow

• Do not recover on top of the original. Use a different directory and a different port.
• Put archive_mode = off in the recovered copy's settings. If you do not, the recovered copy overwrites the original's archive.
• Do not forget pg_switch_wal() in step 4. Archiving copies only finished segments, so the last change has not been handed over yet.

Turn on WAL archiving

Turn on archive_mode and make WAL get copied to /root/work/wal. pg_stat_archiver.archived_count must be greater than 0.

The lab account is a superuser. Do something like psql -h 127.0.0.1 -U lab -d postgres -c "alter system set archive_mode = on", and set archive_command to test ! -f /root/work/wal/%f && cp %p /root/work/wal/%f — test ! -f prevents overwriting. archive_mode requires a restart: pg_ctl -D /var/lib/postgresql/data restart -m fast -w. Then push one segment out with select pg_switch_wal() to confirm.

Take a base backup

Use pg_basebackup to copy the whole data directory into /root/work/base.

pg_basebackup -h 127.0.0.1 -U lab -D /root/work/base -Fp -Xs -P. -Fp means plain (unpacked) format, and -Xs streams the WAL generated during the backup as well, so the backup can stand on its own. In the later recovery you use a copy of this directory — do not touch the original.

Record the time to return to

Create the ops_note table and insert two rows. Then save the current time to /root/work/03-target.txt. This time becomes the recovery target.

create table ops_note(id serial primary key, note text, at timestamptz default now()). Save the time with psql -tAc "select now()" > /root/work/03-target.txt. Take the timestamp more than 1 second after the inserts so that they fall within the target point.

Cause the accident

Delete all rows from ops_note (delete from ops_note). Keep the table. Then use pg_switch_wal() to push the WAL containing that change out to the archive.

A DELETE without a WHERE clause is an accident that really does happen often in practice. If you do not call pg_switch_wal(), the last change has not been archived yet, so recovery cannot reach that point — because archiving copies only finished segments.

Create the recovered copy

Copy the base backup to /root/work/restore, set restore_command, recovery_target_time, and port = 5433, and then create recovery.signal.

cp -r /root/work/base /root/work/restore && chmod 700 /root/work/restore. Write the settings to restore/postgresql.auto.conf: restore_command = 'cp /root/work/wal/%f %p', recovery_target_time = '<03-target.txt 의 값>' (replace the placeholder with the actual value from the target time file), recovery_target_action = 'promote', archive_mode = off, port = 5433. Do not forget to turn archive_mode off — otherwise the recovered copy overwrites the original's archive.

Check that the deleted data is back

Start the recovered copy on port 5433 and check the row count of ops_note. It must be two. The original (5432) still has 0.

pg_ctl -D /root/work/restore -l /tmp/restore.log start -w -t 60. If it does not start, look at /tmp/restore.log — the cause is usually the restore_command path or the format of the target time. To check: psql -h 127.0.0.1 -p 5433 -U lab -d labdb -c 'select * from ops_note'.

See why a logical backup is not enough

Now (after the accident), dump the original with pg_dump -Fc to /root/work/labdb.dump. Check how many rows ops_note has in this dump and write that in 07-dump.txt.

pg_dump -h 127.0.0.1 -U lab -Fc -d labdb -f /root/work/labdb.dump. List the contents with pg_restore -l /root/work/labdb.dump. A dump is a snapshot of the moment it was taken, so it holds the state after the deletion. If you had only last night's dump, that would mean an RPO of 24 hours.

What you could not have returned without

Write at least three lines in /root/work/08-notes.md: the three pieces PITR needs, what happens if archive_command reports failure as success, and what keeping only dumps means from an RPO point of view.

The text must include WAL, RPO, and 타임라인 (the last one is the Korean word for "timeline"). The last line is the whole point of this course — a backup you have never recovered from is not a backup.