Point-in-Time Recovery (PITR)

Configure WAL archiving, take a base backup, and recover to the exact second before a destructive statement — then promote the recovered cluster to writable

Every backup this block has covered so far restores to exactly one point in time: whenever the backup was taken. Point-in-time recovery removes that limitation. By continuously archiving every WAL segment the server generates, alongside a base backup taken at any earlier point, you can replay WAL forward from that backup to any instant you choose — including, critically, the instant one second before someone ran a statement they should not have.\n\nThe scenario is the one every DBA eventually lives through: someone runs DELETE FROM orders WHERE true at 14:23. Every row is gone, and it is gone in every replica within seconds. The base backup from last night has the data — but it also has none of the legitimate orders placed between last night and 14:23. Neither extreme is what you want; you want the database exactly as it was at 14:22:59.\n\nThis lab builds the whole chain: enable archiving, take a base backup, capture a precise timestamp before deliberately deleting data, then recover to that exact second and promote the result to a normal, writable server — the same recovery a real production incident would demand, done here as a rehearsal.

Step 1: Enable WAL Archiving

mkdir -p /tmp/wal_archive && chmod 777 /tmp/wal_archive
echo "archive_mode = on" | as-postgres tee -a /var/lib/postgresql/18/data/postgresql.conf
echo "archive_command = 'cp %p /tmp/wal_archive/%f && chmod 644 /tmp/wal_archive/%f'" | as-postgres tee -a /var/lib/postgresql/18/data/postgresql.conf
as-postgres pg_ctl restart -D /var/lib/postgresql/18/data -l /var/lib/postgresql/18/data/pg_log/startup.log

archive_mode is a restart-only parameter, same category as wal_level. archive_command runs once per completed WAL segment, as the postgres OS user — %p is the segment's full path, %f is just its filename. chmod 777 on the archive directory is what lets the postgres OS user write into a directory the student created.

Step 2: A Base Backup Is the PITR Starting Point

pg_basebackup -U postgres -h /tmp -D /tmp/pitr_base -Fp -Xs -c fast

PITR always replays forward from a base backup — there is no way to recover to a point in time using WAL alone, with nothing to apply it to first.

Step 3: Simulate the Disaster, at a Known Timestamp

-- Capture the exact instant, just before the destructive statement
SELECT clock_timestamp();
-- note the value, then:
DELETE FROM orders WHERE true;

Step 4: Write the Recovery Configuration

cp -r /tmp/pitr_base /tmp/pitr_recover
touch /tmp/pitr_recover/recovery.signal
cat >> /tmp/pitr_recover/postgresql.auto.conf <<EOF
restore_command = 'cp /tmp/wal_archive/%f %p'
recovery_target_time = '2026-06-22 14:22:59.000000+00'
EOF

recovery.signal is what tells PostgreSQL, on startup, to enter recovery mode and look for a target instead of doing ordinary crash recovery. restore_command is the mirror image of archive_command — it tells the recovering server how to fetch each WAL segment it needs from the archive. The default recovery_target_action is pause: once the target is reached, the server stops applying WAL and sits in a paused, read-only state, giving you a chance to verify the data before doing anything irreversible.

Step 5: Start, Verify, Promote

pg_ctl start -D /tmp/pitr_recover -o "-p 5434" -l /tmp/pitr_recover/startup.log
psql -h /tmp -p 5434 -U postgres -d beer_db -c "SELECT count(*) FROM orders;"   # verify data is back
pg_ctl promote -D /tmp/pitr_recover                                              # now make it writable

Promotion is a one-way door: once promoted, the recovered instance starts generating its own new WAL history, diverging from the original timeline. Verify first; promote second.

WAL Archiving (archive_command)

A shell command PostgreSQL runs once for every WAL segment it finishes writing, copying that segment somewhere durable outside pg_wal/ before the server is allowed to recycle it. Archived WAL is what makes recovery to any point in time possible — without it, only a base backup's exact moment is ever recoverable.

recovery_target_time

Tells a recovering server to replay archived WAL only up to (and not past) a specific timestamp, then stop. Combined with a base backup taken earlier and the WAL archived since, this is what makes it possible to recover to literally any instant covered by that archived WAL — not just whenever the base backup happened to be taken.

pg_ctl promote

Ends recovery mode and makes a paused or standby server into a normal, independent, writable primary. It is a one-way operation: once promoted, the server begins writing its own new WAL history, and can no longer continue replaying the original timeline it recovered from.

📂 Create and Open the Archive Directory

Create a WAL archive directory that both the student and the postgres OS user (running archive_command) can write into.

mkdir -p /tmp/wal_archive && chmod 777 /tmp/wal_archive

student@lab:~$ mkdir -p /tmp/wal_archive && chmod 777 /tmp/wal_archive

⚙️ Enable Archiving in postgresql.conf

Append archive_mode and archive_command to postgresql.conf using the as-postgres helper — the student account cannot write to PGDATA directly. This cluster also starts at wal_level = minimal, and archive_mode refuses to enable on top of that, so raise wal_level in the same edit.

echo "archive_mode = on" | as-postgres tee -a /var/lib/postgresql/18/data/postgresql.conf

echo "archive_command = 'cp %p /tmp/wal_archive/%f && chmod 644 /tmp/wal_archive/%f'" | as-postgres tee -a /var/lib/postgresql/18/data/postgresql.conf

echo "wal_level = replica" | as-postgres tee -a /var/lib/postgresql/18/data/postgresql.conf

echo "max_wal_senders = 10" | as-postgres tee -a /var/lib/postgresql/18/data/postgresql.conf

student@lab:~$ echo "archive_mode = on" | as-postgres tee -a /var/lib/postgresql/18/data/postgresql.conf archive_mode = on student@lab:~$ echo "archive_command = 'cp %p /tmp/wal_archive/%f && chmod 644 /tmp/wal_archive/%f'" | as-postgres tee -a /var/lib/postgresql/18/data/postgresql.conf archive_command = 'cp %p /tmp/wal_archive/%f && chmod 644 /tmp/wal_archive/%f' student@lab:~$ echo "wal_level = replica" | as-postgres tee -a /var/lib/postgresql/18/data/postgresql.conf wal_level = replica student@lab:~$ echo "max_wal_senders = 10" | as-postgres tee -a /var/lib/postgresql/18/data/postgresql.conf max_wal_senders = 10

🔄 Restart to Apply archive_mode

archive_mode requires a full restart, not a reload — the same restart-vs-reload distinction from Lab 2.1.4.

as-postgres pg_ctl restart -D /var/lib/postgresql/18/data -l /var/lib/postgresql/18/data/pg_log/startup.log

student@lab:~$ as-postgres pg_ctl restart -D /var/lib/postgresql/18/data -l /var/lib/postgresql/18/data/pg_log/startup.log waiting for server to shut down.... done server stopped waiting for server to start.... done server started

✅ Confirm Archiving Is Active

Force a WAL segment switch and confirm pg_stat_archiver shows it was archived.

psql -U postgres -c "SELECT pg_switch_wal();"

sleep 2

psql -U postgres -c "SELECT archived_count, last_archived_wal, failed_count FROM pg_stat_archiver;"

student@lab:~$ psql -U postgres -c "SELECT pg_switch_wal();" pg_switch_wal --------------- 0/1A000148 (1 row) student@lab:~$ sleep 2 student@lab:~$ psql -U postgres -c "SELECT archived_count, last_archived_wal, failed_count FROM pg_stat_archiver;" archived_count | last_archived_wal | failed_count ----------------+--------------------------+-------------------- 1 | 000000010000000000000019 | 0 (1 row)

📸 Take the PITR Base Backup

Take a base backup — this is the fixed starting point every recovery in this lab replays forward from.

pg_basebackup -U postgres -h /tmp -D /tmp/pitr_base -Fp -Xs -c fast

student@lab:~$ pg_basebackup -U postgres -h /tmp -D /tmp/pitr_base -Fp -Xs -c fast

🧾 Seed Orders and Capture the Pre-Disaster Timestamp

Create an orders table with real data, then capture the exact instant just before deleting it.

psql -U postgres -d beer_db -c "CREATE TABLE orders (id serial PRIMARY KEY, customer text, total numeric(10,2), placed_at timestamptz DEFAULT now());" -c "INSERT INTO orders (customer, total) VALUES ('acme corp', 412.50), ('brewhouse ltd', 89.00), ('taproom inc', 1205.75);"

export TS=$(psql -U postgres -tAc "SELECT clock_timestamp();" | tail -n1)

echo "Recovery target: $TS"

student@lab:~$ psql -U postgres -d beer_db -c "CREATE TABLE orders (id serial PRIMARY KEY, customer text, total numeric(10,2), placed_at timestamptz DEFAULT now());" -c "INSERT INTO orders (customer, total) VALUES ('acme corp', 412.50), ('brewhouse ltd', 89.00), ('taproom inc', 1205.75);" SET CREATE TABLE INSERT 0 3 student@lab:~$ export TS=$(psql -U postgres -tAc "SELECT clock_timestamp();" | tail -n1) student@lab:~$ echo "Recovery target: $TS" Recovery target: 2026-06-26 16:02:55.516795+00

💥 Run the Disaster — Then Force the WAL Switch That Archives It

Run the destructive statement from the scenario, then force a WAL segment switch. The DELETE lands in whichever segment is currently active, and archive_command only ever runs on a completed segment — without forcing the switch, the WAL recovery actually needs would still be sitting unarchived in the primary's pg_wal/.

psql -U postgres -d beer_db -c "DELETE FROM orders WHERE true;"

psql -U postgres -c "SELECT pg_switch_wal();"

sleep 2

student@lab:~$ psql -U postgres -d beer_db -c "DELETE FROM orders WHERE true;" SET DELETE 3 student@lab:~$ psql -U postgres -c "SELECT pg_switch_wal();" SET pg_switch_wal --------------- 0/8009418 (1 row) student@lab:~$ sleep 2

📝 Write the Recovery Configuration

Copy the base backup to a recovery directory, add recovery.signal, and configure restore_command plus recovery_target_time using the exact timestamp captured before the delete.

mv /tmp/pitr_base /tmp/pitr_recover

touch /tmp/pitr_recover/recovery.signal

printf "restore_command = 'cp /tmp/wal_archive/%%f %%p'\nrecovery_target_time = '$TS'\n" >> /tmp/pitr_recover/postgresql.auto.conf

student@lab:~$ mv /tmp/pitr_base /tmp/pitr_recover student@lab:~$ touch /tmp/pitr_recover/recovery.signal student@lab:~$ printf "restore_command = 'cp /tmp/wal_archive/%f %p'\nrecovery_target_time = '$TS'\n" >> /tmp/pitr_recover/postgresql.auto.conf

▶️ Start the Recovery Instance

Start the recovery directory as its own instance on port 5434 and watch it replay WAL toward the target.

pg_ctl start -D /tmp/pitr_recover -o "-p 5434" -l /tmp/pitr_recover/startup.log

student@lab:~$ pg_ctl start -D /tmp/pitr_recover -o "-p 5434" -l /tmp/pitr_recover/startup.log waiting for server to start.... done server started

🔍 Verify the Data, Then Free Up Disk Space

Confirm the orders are actually back, and the instance is still read-only, before doing anything irreversible. Then clear out the WAL archive — recovery has already replayed everything it needed from it, and promotion needs to write a fresh WAL timeline, which this VM does not have room for otherwise.

psql -h /tmp -p 5434 -U postgres -d beer_db -c "SELECT pg_is_in_recovery();" -c "SELECT count(*) FROM orders;"

rm -rf /tmp/wal_archive/*

student@lab:~$ psql -h /tmp -p 5434 -U postgres -d beer_db -c "SELECT pg_is_in_recovery();" -c "SELECT count(*) FROM orders;" SET pg_is_in_recovery -------------------- t (1 row) count ------- 3 (1 row) student@lab:~$ rm -rf /tmp/wal_archive/*

🚀 Promote to Writable

Promote the recovered instance to a normal, independent, writable server.

pg_ctl promote -D /tmp/pitr_recover

student@lab:~$ pg_ctl promote -D /tmp/pitr_recover waiting for server to promote.... done server promoted

Lab 2.3.5 complete — and Block 2.3 complete. You built the full point-in-time recovery chain, end to end:\n\n\n WAL archiving : ✅ archive_mode + archive_command, restarted\n pg_stat_archiver : ✅ archiving confirmed live, zero failures\n Base backup : ✅ fixed PITR starting point\n Precise timestamp capture : ✅ clock_timestamp() before the disaster\n recovery.signal + target : ✅ restore_command + recovery_target_time\n Verified before promoting : ✅ data confirmed, still read-only\n pg_ctl promote : ✅ writable, recovery complete\n

Enable JavaScript to run the live terminal and track your progress.