What is it? #
Goal: perform a complete point-in-time recovery, from setting up archiving to promoting the recovered database — and then do it again, faster.
This is the final project because it exercises almost everything in the track: WAL, archiving, base backups, recovery targets, verification and timelines. It is also the single most valuable thing in it, because PITR performed for the first time during a real incident rarely goes well.
The scenario is deliberately realistic, including the awkward parts: you will not know the exact incident time at the start, you will have to find it, and you will deliberately get the recovery target wrong once so that recovering from a wrong guess becomes familiar rather than alarming.
Treat this as a drill. Time yourself, write down the commands for your own setup, and repeat it until it is boring.
Think of it like this #
Learning to use a parachute.
Reading the manual carefully is necessary and completely insufficient. The first time cannot be the real time.
And the part that matters is not the main procedure, which is simple. It is knowing what to do when something does not go as described.
Simple example #
A test database with archiving enabled and a base backup taken. Data is written steadily. At some point a script deletes a large amount of data and normal writes continue afterwards, so the damage is buried in the middle of ongoing activity.
Your job: find when it happened, recover to just before it, verify, and promote — without destroying the ability to try again.
Code #
# ---------- PART 1: a lab instance with archiving ----------
sudo -u postgres psql -c "CREATE DATABASE pitr_lab;"
sudo mkdir -p /var/lib/postgresql/archive
sudo chown postgres:postgres /var/lib/postgresql/archive
sudo chmod 700 /var/lib/postgresql/archive# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/archive/%f && cp %p /var/lib/postgresql/archive/%f'
# ^ refuses to overwrite an existing archived file
archive_timeout = 60 # short, so the lab archives promptly
max_wal_senders = 3
sudo systemctl restart postgresql # archive_mode needs a restart
# Confirm archiving works BEFORE relying on it:
sudo -u postgres psql -c "SELECT pg_switch_wal();"
ls -l /var/lib/postgresql/archive/
sudo -u postgres psql -c "SELECT archived_count, failed_count FROM pg_stat_archiver;"
# failed_count MUST be 0. If not, fix it now — nothing else will work.# ---------- PART 2: the base backup ----------
sudo -u postgres mkdir -p /var/lib/postgresql/basebackup
sudo -u postgres pg_basebackup \
-D /var/lib/postgresql/basebackup/base_$(date +%F_%H%M) \
-Ft -z -Xs -P -c fast
date -Is | sudo -u postgres tee \
/var/lib/postgresql/basebackup/base_*/TAKEN_AT-- ---------- PART 3: generate a realistic history ----------
\c pitr_lab
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer text NOT NULL,
total numeric(10,2) NOT NULL,
placed_at timestamptz NOT NULL DEFAULT now()
);
-- Write in batches over several minutes so there is a real timeline.
-- Run this a few times, pausing between runs:
INSERT INTO orders (customer, total)
SELECT 'Customer ' || i, round((random()*1000)::numeric, 2)
FROM generate_series(1, 5000) AS i;
SELECT count(*), max(placed_at) FROM orders;
-- Note the count. Let a few minutes and a couple of WAL switches pass.# ---------- PART 4: THE INCIDENT ----------
# Run this WITHOUT carefully noting the time — you will find it later,
# which is the realistic exercise.
sudo -u postgres psql -d pitr_lab -c "DELETE FROM orders WHERE id % 2 = 0;"
# Then keep writing, so the damage is buried in ongoing activity:
sudo -u postgres psql -d pitr_lab -c "
INSERT INTO orders (customer, total)
SELECT 'Post-incident ' || i, 50 FROM generate_series(1, 500) AS i;"
sudo -u postgres psql -d pitr_lab -c "SELECT count(*) FROM orders;"
# Fewer than before. Something is wrong.# ---------- PART 5: FIND WHEN IT HAPPENED ----------
# You need a timestamp. Guessing costs you a full replay cycle.
# If log_statement or log_min_duration_statement is enabled:
sudo grep -n "DELETE FROM orders" /var/log/postgresql/postgresql-*.log
# Otherwise, narrow it from the data itself: the gap in ids tells you
# roughly when even-numbered rows stopped existing.
sudo -u postgres psql -d pitr_lab -c "
SELECT max(placed_at) FROM orders WHERE id % 2 = 0;"
-- the newest surviving even row -> the delete came after this
sudo -u postgres psql -d pitr_lab -c "
SELECT min(placed_at) FROM orders WHERE customer LIKE 'Post-incident%';"
-- the first post-incident write -> the delete came before this
# The incident is between those two timestamps. Target just before the later one.# ---------- PART 6: PRESERVE THE CURRENT STATE ----------
# !! Never skip this. It is what makes a wrong target cheap.
sudo systemctl stop postgresql
STAMP=$(date +%F_%H%M)
sudo cp -a /var/lib/postgresql/16/main /var/lib/postgresql/16/main.broken.$STAMP
# Unarchived WAL holds the most recent transactions and exists nowhere else:
sudo mkdir -p /backup/pg_wal_rescue_$STAMP
sudo cp -a /var/lib/postgresql/16/main/pg_wal/* /backup/pg_wal_rescue_$STAMP/ || true
df -h # confirm there is room for all of this# ---------- PART 7: RESTORE THE BASE BACKUP (separate directory) ----------
sudo mkdir -p /var/lib/postgresql/16/recovery
sudo chown postgres:postgres /var/lib/postgresql/16/recovery
sudo chmod 700 /var/lib/postgresql/16/recovery
BASE=$(ls -d /var/lib/postgresql/16/../basebackup/base_* 2>/dev/null | tail -1)
sudo -u postgres tar -xzf "$BASE/base.tar.gz" -C /var/lib/postgresql/16/recovery
sudo -u postgres tar -xzf "$BASE/pg_wal.tar.gz" \
-C /var/lib/postgresql/16/recovery/pg_wal# ---------- PART 8: SET THE RECOVERY TARGET ----------
sudo -u postgres tee -a /var/lib/postgresql/16/recovery/postgresql.conf >/dev/null <<'EOF'
# --- PITR ---
restore_command = 'cp /var/lib/postgresql/archive/%f %p'
recovery_target_time = '2026-09-23 15:42:10+05:30' # <-- YOUR timestamp
recovery_target_action = 'pause' # verify before promoting
port = 5433 # never clash with prod
EOF
sudo -u postgres touch /var/lib/postgresql/16/recovery/recovery.signal# ---------- PART 9: RUN THE RECOVERY ----------
sudo -u postgres /usr/lib/postgresql/16/bin/pg_ctl \
-D /var/lib/postgresql/16/recovery \
-l /tmp/pitr_$STAMP.log start
tail -f /tmp/pitr_$STAMP.log
# EXPECT:
# LOG: starting point-in-time recovery to 2026-09-23 15:42:10+05:30
# LOG: restored log file "0000000100000000000000XX" from archive
# ...
# LOG: recovery stopping before commit of transaction NNNNN
# LOG: recovery has paused
#
# IF INSTEAD:
# FATAL: could not restore file "..."
# -> a GAP in the archive. The best achievable target is just before it.-- ---------- PART 10: VERIFY (read-only, nothing committed yet) ----------
psql -p 5433 -U postgres -d pitr_lab
SELECT count(*) FROM orders; -- back to the pre-delete count?
SELECT count(*) FROM orders WHERE id % 2 = 0; -- even rows present again?
SELECT max(placed_at) FROM orders; -- how recent is this?
SELECT count(*) FROM orders WHERE customer LIKE 'Post-incident%';
-- Should be 0 — those came AFTER the incident, so they are correctly absent.---------- PART 11: DELIBERATELY GET IT WRONG ----------
This is the most valuable part of the project. Do it on purpose.
A) Set recovery_target_time AFTER the delete, and confirm the even-numbered
rows are missing — the recovery faithfully included the incident.
B) Set it much earlier, and confirm you lost legitimate work that
happened before the incident.
For each attempt:
stop the instance
REMOVE the recovery directory entirely
re-extract the base backup
change the target
start again
The recovery directory CANNOT be reused — PostgreSQL removes
recovery.signal once recovery completes, and the data files have
already been rolled forward. Starting clean each time is the procedure.# The retry loop, as a script:
retry_pitr() {
local target="$1"
sudo -u postgres /usr/lib/postgresql/16/bin/pg_ctl \
-D /var/lib/postgresql/16/recovery stop || true
sudo rm -rf /var/lib/postgresql/16/recovery
sudo mkdir -p /var/lib/postgresql/16/recovery
sudo chown postgres:postgres /var/lib/postgresql/16/recovery
sudo chmod 700 /var/lib/postgresql/16/recovery
sudo -u postgres tar -xzf "$BASE/base.tar.gz" -C /var/lib/postgresql/16/recovery
sudo -u postgres tar -xzf "$BASE/pg_wal.tar.gz" -C /var/lib/postgresql/16/recovery/pg_wal
sudo -u postgres tee -a /var/lib/postgresql/16/recovery/postgresql.conf >/dev/null <<EOF
restore_command = 'cp /var/lib/postgresql/archive/%f %p'
recovery_target_time = '$target'
recovery_target_action = 'pause'
port = 5433
EOF
sudo -u postgres touch /var/lib/postgresql/16/recovery/recovery.signal
sudo -u postgres /usr/lib/postgresql/16/bin/pg_ctl \
-D /var/lib/postgresql/16/recovery -l /tmp/pitr_retry.log start
}
# retry_pitr '2026-09-23 15:41:00+05:30'-- ---------- PART 12: PROMOTE ----------
SELECT pg_wal_replay_resume();
SELECT pg_is_in_recovery(); -- f = writable
SELECT timeline_id FROM pg_control_checkpoint(); -- incremented# ---------- PART 13: FINISH PROPERLY ----------
# 1. ANALYZE — statistics did not survive
psql -p 5433 -U postgres -d pitr_lab -c "ANALYZE;"
# 2. NEW BASE BACKUP — you are on a new timeline, the old chain is invalid.
# Until this exists you have NO recovery capability.
sudo -u postgres pg_basebackup \
-p 5433 -D /var/lib/postgresql/basebackup/base_$(date +%F_%H%M)_postrecovery \
-Ft -z -Xs -P
# 3. Confirm archiving still works on the new timeline
psql -p 5433 -U postgres -c "SELECT * FROM pg_stat_archiver;"
# 4. Keep main.broken.* for now — it holds the post-incident writes---------- PART 14: WRITE IT DOWN ----------
The deliverable is not the recovered database. It is the document.
[ ] Exact commands for YOUR version and paths
[ ] How long each phase took (restore / replay / verify)
[ ] How you found the incident time
[ ] What the retry loop is
[ ] What was lost, and why
[ ] The two mandatory post-promotion steps
Then hand it to a colleague and have THEM perform the recovery using
only your document. Whatever they get stuck on is the part that would
have failed at 3am.How it works #
Expected result: a recovered database containing the deleted rows and correctly excluding the post-incident writes, plus a written procedure someone else has successfully followed.
The parts that make this a drill rather than a demonstration:
Not noting the incident time in advance forces you to find it, which is the realistic situation. The data itself narrows it down — the newest surviving even-numbered row and the oldest post-incident row bracket the deletion. Application logs and audit tables do the same job faster, which is a good argument for having them.
Continuing to write after the incident buries the damage in ongoing activity, and it makes the verification meaningful: those post-incident rows should be absent from the recovered database, because they happened after the point you recovered to. Confirming that is how you know the target took effect precisely.
Preserving the current state before starting is what makes the wrong-target experiment affordable. Without it, a bad guess would be unrecoverable.
Part 11 is the point of the whole project. Anyone can follow a working procedure. Knowing what a target that is too late looks like — the recovery succeeds, and the data is still missing — and what too early looks like, and being able to reset and retry in a couple of minutes, is the difference between a calm recovery and a frightening one.
The recovery directory cannot be reused between attempts. PostgreSQL removes recovery.signal when recovery completes, and the data files have already been rolled forward. Each attempt starts from a fresh extraction of the base backup, which is why the retry loop is worth having as a script.
The two post-promotion steps are the ones most often forgotten. ANALYZE because statistics do not survive, and a new base backup because promotion starts a new timeline — leaving a window with no recovery capability at all, immediately after an incident.
Real-world use #
This drill is the most valuable single exercise in the track. PITR is conceptually simple and operationally fiddly, and every detail that trips you up here would have tripped you up during a real incident, with an audience.
Time each phase and record the numbers. Restore duration and replay duration together are your real recovery time, and both grow with your data and with the gap since the last base backup. That relationship is the practical argument for base backup frequency.
Repeat the drill periodically, and after any significant change — a major version upgrade, a new backup tool, a storage migration. Procedures rot quietly.
The written procedure is the actual deliverable. During a real incident, recovery is often performed by whoever is available rather than whoever designed the system, under time pressure, possibly at night. A document that a colleague has successfully followed is worth considerably more than one that is merely correct.
In production, use pgBackRest, Barman or WAL-G rather than assembling this by hand. They manage base backups, archive integrity and the recovery process with verification this manual approach lacks. Having done it manually once is what lets you use them confidently and debug them when something does not behave as documented.
Finally, note what this exercise reveals about prevention. The recovery worked, and it still cost an hour and lost some data. Restore points before risky migrations, removing unnecessary DELETE privileges, and noticing incidents in minutes rather than hours are all cheaper than any recovery.
Common mistakes #
- Skipping the preservation copy, leaving no way to retry after a wrong recovery target.
- Trying to reuse the recovery directory between attempts instead of re-extracting the base backup.
- Omitting the time zone from recovery_target_time and landing at the wrong moment.
- Promoting immediately instead of pausing to verify the recovered data.
- Forgetting the new base backup after promotion, leaving no valid recovery chain on the new timeline.
Practice #
Complete the drill, then repeat it twice more and record your total time for each run — expect the third to be roughly half the first. Then extend it: recover using recovery_target_xid instead of a time, having found the transaction id in the logs; create a named restore point with pg_create_restore_point() before a deliberate mistake and recover to it; and simulate a gap by deleting one WAL segment from the archive, then confirm recovery stops there and determine the best achievable target. Finally, hand your written procedure to someone else and watch them follow it without helping.