Lab 20: Drop a table, then recover the database to the second before you dropped it

Objective

Point-in-time recovery is the capability every backup strategy claims and few teams have actually exercised. It is not difficult, but it has several small steps that must all be right, and the first time to find that out is not during an incident.

By the end of this lab you will have performed a complete PITR: a base backup, work done after it, a destructive mistake, and a recovery to the second before the mistake — with the dropped table and the deleted rows both back, verified by counting them.

You will do it with recovery_target_action = pause, which is the setting that turns a recovery from a gamble into a decision: the server stops at the target, lets you look, and waits for you to say whether that was the right moment.

Architecture

One base backup plus the WAL archive is enough to reconstruct the cluster at any instant they jointly cover.

flowchart LR
    B["base backup\n/lab20/base\nSTART WAL 0/39000028"] --> R["restored copy\n/lab20/restore"]
    A["/archive\nsegments ...39, ...3A"] --> R
    C["recovery.signal\n+ restore_command\n+ recovery_target_time"] --> R
    R --> P["replay to target\nthen PAUSE"]
    P --> I["inspect: is this the right moment?"]
    I -->|yes| PR["pg_promote()\ntimeline 2, writable"]
    I -->|no| RE["stop, adjust target, start again"]

Requirements

A cluster with working WAL archiving, as configured in Lab 19. The lab uses rbpg-pitr, which archives to /archive.
A base backup taken after archiving was enabled. A backup older than the archive cannot be recovered forward.
Roughly 700 MB of free disk for two restored copies.

Scenario

At 06:04 someone ran a DELETE without a WHERE clause on a busy table, noticed, and then made it worse by dropping the table to “start clean”. The last base backup is from 06:03. Archiving has been running.

You need the database as it was at 06:04:03, and you need to be sure it is the right moment before anybody connects to it.

Tasks

Task 1 — Confirm the prerequisites, then take the backup

LAB="$HOME/rbpg-lab-20"
mkdir -p "$LAB"

docker exec rbpg-pitr bash -c "mkdir -p /lab20 && chown postgres:postgres /lab20"

docker exec -u postgres rbpg-pitr psql -X -c "
  SELECT name, setting FROM pg_settings
  WHERE name IN ('archive_mode','archive_command','wal_level');"
docker exec -u postgres rbpg-pitr psql -X -c \
  "SELECT archived_count, failed_count, last_archived_wal FROM pg_stat_archiver;"

docker exec -i -u postgres rbpg-pitr psql -X -c "CREATE DATABASE lab20;"
docker exec -i -u postgres rbpg-pitr psql -X -d lab20 <<'SQL'
CREATE TABLE ledger(id int PRIMARY KEY, amount numeric, note text);
INSERT INTO ledger SELECT g, g*1.5, 'before-backup' FROM generate_series(1,10000) g;
SQL

docker exec -u postgres rbpg-pitr pg_basebackup -D /lab20/base -Fp -c fast -Xstream
docker exec rbpg-pitr cat /lab20/base/backup_label | head -3 | tee "$LAB/timeline.txt"

Read-only / Safearchiving healthy, and a backup that knows where recovery starts— Captured from the lab run of 2026-08-28. failed_count must be 0 before you rely on this.

$ check the archiver, create the data, take the base backup, read its label

 archived_count | failed_count |    last_archived_wal     
----------------+--------------+--------------------------
           32 |            0 | 000000010000000000000037
(1 row)

211M	/lab20/base
START WAL LOCATION: 0/39000028 (file 000000010000000000000039)
CHECKPOINT LOCATION: 0/39000080
BACKUP METHOD: streamed

failed_count = 0 is not decoration. A backup taken while archiving is failing is a backup that cannot be recovered forward past the point the archive stopped, and Lab 19 showed how quietly that happens.

Task 2 — Do work, note the moment, then break something

docker exec -u postgres rbpg-pitr psql -X -d lab20 -c \
  "INSERT INTO ledger SELECT g+10000, g*2.5, 'after-backup' FROM generate_series(1,5000) g;"
docker exec -u postgres rbpg-pitr psql -X -d lab20 -c \
  "INSERT INTO ledger VALUES (99999, 0, 'MARKER-BEFORE-ACCIDENT');"

sleep 2
SAFE=$(docker exec -u postgres rbpg-pitr psql -X -tAc "SELECT now();")
echo "safe point: $SAFE"
sleep 2

# The accident.
docker exec -u postgres rbpg-pitr psql -X -d lab20 -c \
  "DELETE FROM ledger WHERE note = 'after-backup';"
docker exec -u postgres rbpg-pitr psql -X -d lab20 -c "DROP TABLE ledger;"
docker exec -u postgres rbpg-pitr psql -X -d lab20 -c "SELECT count(*) FROM ledger;"

# Push the WAL covering all of it into the archive.
docker exec -u postgres rbpg-pitr psql -X -c "SELECT pg_switch_wal();"
sleep 3
docker exec -u postgres rbpg-pitr psql -X -c \
  "SELECT archived_count, last_archived_wal FROM pg_stat_archiver;" | tee -a "$LAB/timeline.txt"

Data-loss risk5,000 rows deleted and the table dropped— Captured from the lab run of 2026-08-28.

$ insert, mark a safe point, then DELETE and DROP TABLE

INSERT 0 5000
INSERT 0 1
safe point: 2026-08-28 06:04:03.779666+00

DELETE 5000
DROP TABLE
ERROR:  relation "ledger" does not exist
LINE 1: SELECT count(*) FROM ledger;

pg_switch_wal 
---------------
0/3A1A7E70
(1 row)

archived_count |    last_archived_wal     
----------------+--------------------------
           36 | 00000001000000000000003A
(1 row)

Task 3 — Assemble the recovery

docker exec rbpg-pitr bash -c "
  cp -a /lab20/base /lab20/restore
  chown -R postgres:postgres /lab20/restore
  chmod 0700 /lab20/restore
"

docker exec rbpg-pitr bash -c "cat >> /lab20/restore/postgresql.auto.conf <<EOF
restore_command = 'cp /archive/%f %p'
recovery_target_time = '$SAFE'
recovery_target_action = 'pause'
port = 5470
archive_mode = off
EOF"

docker exec -u postgres rbpg-pitr touch /lab20/restore/recovery.signal
docker exec rbpg-pitr ls /lab20/restore/ | grep -E "recovery.signal|backup_label"

Configuration changefour settings and one empty file— Captured from the lab run of 2026-08-28.

$ copy the base backup, append recovery settings, create recovery.signal

backup_label
recovery.signal

Four things make this a recovery rather than a copy of a data directory:

recovery.signal — an empty file whose presence tells the server to enter archive recovery. Without it the server treats the directory as a crashed cluster and recovers only from the WAL inside pg_wal.
restore_command — how to fetch an archived segment. The mirror image of archive_command: %f is the wanted segment, %p is where to put it.
recovery_target_time — where to stop.
recovery_target_action = 'pause' — what to do on arrival.

And archive_mode = off matters more than it looks: without it the recovered cluster would start archiving into the same directory as the original, mixing two histories into one archive.

Task 4 — Run it

docker exec -u postgres rbpg-pitr /usr/lib/postgresql/18/bin/pg_ctl \
  -D /lab20/restore -l /tmp/lab20.log start
sleep 8
docker exec rbpg-pitr grep -E "starting point-in-time|restored log file|consistent|recovery stopping|pausing" \
  /tmp/lab20.log | tee "$LAB/recovery-log.txt"

Service impact possiblethe whole recovery, in six lines— Captured from the lab run of 2026-08-28.

$ start the restored cluster and read its recovery log

2026-08-28 06:04:23.232 UTC [3143] LOG:  restored log file "000000010000000000000039" from archive
2026-08-28 06:04:23.247 UTC [3143] LOG:  starting point-in-time recovery to 2026-08-28 06:04:03.779666+00
2026-08-28 06:04:23.255 UTC [3143] LOG:  restored log file "00000001000000000000003A" from archive
2026-08-28 06:04:23.269 UTC [3143] LOG:  consistent recovery state reached at 0/39000120
2026-08-28 06:04:23.274 UTC [3143] LOG:  recovery stopping before commit of transaction 780, time 2026-08-28 06:04:05.83324+00
2026-08-28 06:04:23.274 UTC [3143] LOG:  pausing at the end of recovery

Read the fifth line closely: “recovery stopping before commit of transaction 780, time 2026-08-28 06:04:05.83324”.

The target was 06:04:03.779666. Transaction 780 committed at 06:04:05.833 — after the target — so recovery stopped before applying it. The server names the exact transaction it declined to replay, which is the most precise possible statement of where you have landed.

consistent recovery state reached marks the point at which the data directory stopped being an inconsistent mixture of instants. Before that line the cluster cannot be queried at all; after it, read-only queries work while replay continues.

Task 5 — Look before you commit

docker exec -u postgres rbpg-pitr psql -X -p 5470 -c "
  SELECT pg_is_in_recovery() AS in_recovery,
         pg_get_wal_replay_pause_state() AS pause_state,
         pg_last_wal_replay_lsn() AS replayed_to;"

docker exec -u postgres rbpg-pitr psql -X -p 5470 -d lab20 -c "
  SELECT count(*) AS total,
         count(*) FILTER (WHERE note='before-backup')          AS before_backup,
         count(*) FILTER (WHERE note='after-backup')           AS after_backup,
         count(*) FILTER (WHERE note='MARKER-BEFORE-ACCIDENT') AS marker
  FROM ledger;" | tee "$LAB/verified.txt"

Read-only / Safethe dropped table is back, with the deleted rows in it— Captured from the lab run of 2026-08-28. The server is still in recovery and read-only.

$ check the pause state, then count the rows by category

 in_recovery | pause_state | replayed_to 
-------------+-------------+-------------
t           | paused      | 0/3A19A2B8
(1 row)

total | before_backup | after_backup | marker 
-------+---------------+--------------+--------
15001 |         10000 |         5000 |      1
(1 row)

All of it. The 10,000 rows that existed at backup time, the 5,000 that were inserted afterwards and then deleted, and the marker row that was the last thing written before the accident. 15,001 rows in a table that no longer exists on the original cluster.

This pause is the whole point of recovery_target_action

The default for recovery_target_action is pause, and it is the right default. The alternatives are promote, which ends recovery immediately and makes the cluster writable, and shutdown, which stops the server at the target.

pause gives you a read-only cluster sitting exactly at the target, for as long as you want, with the full WAL still available. If the target was wrong — a minute too early, a minute too late — you stop the server, change recovery_target_time, and start again. Nothing has been consumed.

Once you promote, that option closes. The cluster writes a new timeline and the WAL beyond the target is no longer part of its history. Choosing promote as the action means committing to a target you have not yet verified, and there is almost never a reason to do that.

Verify as the application would, not just by counting: run the query that prompted the recovery, check the row somebody said was missing, and confirm the thing that went wrong has not.

Task 6 — Promote

docker exec -u postgres rbpg-pitr psql -X -p 5470 -c "SELECT pg_promote();"
sleep 4
docker exec -u postgres rbpg-pitr psql -X -p 5470 -c "SELECT pg_is_in_recovery();"
docker exec -u postgres rbpg-pitr psql -X -p 5470 -d lab20 -c \
  "INSERT INTO ledger VALUES (100000, 1, 'written-after-promotion'); SELECT count(*) FROM ledger;"
docker exec rbpg-pitr grep -E "redo done|selected new timeline|archive recovery complete|ready to accept" /tmp/lab20.log

Service impact possiblerecovery ends, a new timeline begins, the cluster accepts writes— Captured from the lab run of 2026-08-28.

$ pg_promote(), then confirm writability and read the log

 pg_promote 
------------
t
(1 row)

pg_is_in_recovery 
-------------------
f
(1 row)

INSERT 0 1
count 
-------
15002
(1 row)

2026-08-28 06:04:23.269 UTC [3137] LOG:  database system is ready to accept read-only connections
2026-08-28 06:04:46.302 UTC [3143] LOG:  redo done at 0/3A19A2B8
2026-08-28 06:04:46.329 UTC [3143] LOG:  selected new timeline ID: 2
2026-08-28 06:04:46.352 UTC [3143] LOG:  archive recovery complete
2026-08-28 06:04:46.358 UTC [3137] LOG:  database system is ready to accept connections

selected new timeline ID: 2. This is the piece that makes PITR safe to do more than once.

Timelines exist so that a recovery cannot destroy its own history

Suppose recovery to 06:04:03 was wrong and you actually wanted 06:02:00. If the promoted cluster resumed writing WAL on timeline 1 at the LSN where recovery stopped, it would overwrite the archived WAL describing what originally happened after 06:04:03 — the history you might still need.

So a promotion always starts a new timeline. The recovered cluster writes timeline 2 from that LSN forward, the original timeline 1 archive is untouched, and a .history file records where the branch happened.

Two practical consequences:

You can recover repeatedly from the same archive. Ten recoveries to ten different targets produce ten timelines and destroy nothing.

recovery_target_timeline decides which branch you follow. Its default is latest, which follows the newest timeline the archive knows about. Recovering to a point on the original timeline after a promotion has already happened requires naming the timeline explicitly, and getting that wrong is how a recovery silently lands in the wrong history.

Task 7 — A target the archive cannot reach

docker exec -u postgres rbpg-pitr /usr/lib/postgresql/18/bin/pg_ctl -D /lab20/restore stop -m fast

docker exec rbpg-pitr bash -c "
  cp -a /lab20/base /lab20/restore2
  chown -R postgres:postgres /lab20/restore2
  chmod 0700 /lab20/restore2
"
docker exec rbpg-pitr bash -c "cat >> /lab20/restore2/postgresql.auto.conf <<'EOF'
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2030-01-01 00:00:00+00'
port = 5471
archive_mode = off
EOF"
docker exec -u postgres rbpg-pitr touch /lab20/restore2/recovery.signal

docker exec -u postgres rbpg-pitr /usr/lib/postgresql/18/bin/pg_ctl \
  -D /lab20/restore2 -l /tmp/lab20b.log start
echo "pg_ctl exit status: $?"
sleep 6
docker exec -u postgres rbpg-pitr psql -X -p 5471 -c "SELECT 1;"
docker exec rbpg-pitr grep -E "FATAL|shutting down|redo done" /tmp/lab20b.log | tee "$LAB/unreachable.txt"

Service impact possiblethe recovery refuses rather than guessing— Captured from the lab run of 2026-08-28, with a recovery target years in the future.

$ recover with recovery_target_time = '2030-01-01', then check whether the server is up

waiting for server to start...... stopped waiting
pg_ctl: could not start server
Examine the log output.
pg_ctl exit status: 1

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5471" failed: No such file or directory

2026-08-28 06:05:08.073 UTC [3255] LOG:  redo done at 0/3A1A7E58
2026-08-28 06:05:08.073 UTC [3255] FATAL:  recovery ended before configured recovery target was reached
2026-08-28 06:05:08.074 UTC [3249] LOG:  shutting down due to startup process failure

FATAL: recovery ended before configured recovery target was reached. The server replayed everything the archive contained, found it had not reached the requested time, and refused to open.

That is the correct behaviour and it is worth appreciating. A recovery that quietly promoted at “as far as I got” would hand you a database missing an unknown amount of data, presented as a success. Instead it fails loudly and the data directory is left ready for another attempt with a reachable target.

pg_ctl's word is not sufficient evidence

On this run pg_ctl reported could not start server and exited 1. On a slower recovery captured earlier in this course’s development, pg_ctl printed server started and exited 0 for the same class of failure — because pg_ctl stops waiting once the postmaster is listening, and the startup process failed after that point.

So the exit status of pg_ctl is timing-dependent for recovery failures. Confirm a recovery two other ways, always:

psql -p 5470 -c "SELECT pg_is_in_recovery();"   # can you connect at all?
grep -E "FATAL|recovery stopping|consistent" server.log

A recovery script that checks only pg_ctl’s exit status will sometimes report a successful recovery of a server that is not running.

Task 8 — The full set of targets

docker exec -u postgres rbpg-pitr psql -X -c "
  SELECT name, setting, boot_val FROM pg_settings
  WHERE name LIKE 'recovery_target%' ORDER BY name;"

Read-only / Safeeight settings, of which you set at most one target— Captured from the lab run of 2026-08-28.

$ psql -X -c "SELECT name, setting, boot_val FROM pg_settings WHERE name LIKE 'recovery_target%' ORDER BY name;"

           name            | setting | boot_val 
---------------------------+---------+----------
recovery_target           |         | 
recovery_target_action    | pause   | pause
recovery_target_inclusive | on      | on
recovery_target_lsn       |         | 
recovery_target_name      |         | 
recovery_target_time      |         | 
recovery_target_timeline  | latest  | latest
recovery_target_xid       |         | 
(8 rows)

Five of these specify where to stop, and only one may be set:

recovery_target_time — a timestamp. The usual choice, and the one you can derive from an incident report.
recovery_target_xid — a transaction id. Precise, when you know it from the log.
recovery_target_lsn — a WAL position. The most precise of all.
recovery_target_name — a label previously created with pg_create_restore_point(). Excellent before a risky migration: name a restore point, and recovery to it needs no timestamp arithmetic.
recovery_target = 'immediate' — stop as soon as the backup is consistent. This is how you verify a base backup without needing any archive beyond it.

recovery_target_inclusive decides whether the named transaction is itself replayed: on stops after it, off stops before it. It applies only to the xid and time targets.

Validation

test -s "$LAB/timeline.txt"     && echo "OK timeline"
test -s "$LAB/recovery-log.txt" && echo "OK recovery-log"
test -s "$LAB/verified.txt"     && echo "OK verified"
test -s "$LAB/unreachable.txt"  && echo "OK unreachable"

grep -q "starting point-in-time recovery" "$LAB/recovery-log.txt" && echo "OK PITR performed"
grep -q "recovery stopping before commit"  "$LAB/recovery-log.txt" && echo "OK target reached"
grep -q "15001"                            "$LAB/verified.txt"     && echo "OK data recovered"
grep -q "recovery ended before configured" "$LAB/unreachable.txt"  && echo "OK unreachable target refused"

Questions to answer without looking anything up:

What are the four things that turn a copied data directory into a recovery, and which one is an empty file?
Recovery stopped and the log says “before commit of transaction 780”. Was transaction 780 applied?
Why does promotion start a new timeline, and what would break if it did not?
Your recovery target is 30 minutes after the last archived WAL. What happens?
You have just been told a bad UPDATE ran ten minutes ago. What is the first SQL statement you run on the primary?

Expected Outcome

You have recovered a dropped table and 5,000 deleted rows to a chosen instant, verified the result while the server was still paused and read-only, and promoted it onto a new timeline.

The procedure, which belongs in a runbook rather than in anyone’s memory:

# 1. On the primary, immediately: get the WAL into the archive.
psql -c "SELECT pg_switch_wal();"
psql -c "SELECT last_archived_wal, failed_count FROM pg_stat_archiver;"

# 2. Restore the base backup BESIDE the original, never over it.
cp -a /backup/base /restore && chmod 0700 /restore

# 3. Configure the recovery.
cat >> /restore/postgresql.auto.conf <<EOF
restore_command = 'cp /archive/%f %p'
recovery_target_time = '<the moment before the mistake>'
recovery_target_action = 'pause'
port = 5470
archive_mode = off
EOF
touch /restore/recovery.signal

# 4. Start it, and verify with a connection and the log — not with pg_ctl.
pg_ctl -D /restore -l /tmp/recovery.log start
psql -p 5470 -c "SELECT pg_is_in_recovery(), pg_get_wal_replay_pause_state();"
grep -E "FATAL|recovery stopping|consistent" /tmp/recovery.log

# 5. Check the data as the application would. Only then:
psql -p 5470 -c "SELECT pg_promote();"

Troubleshooting

Recovery stops immediately with requested recovery stop point is before consistent recovery point. The target time is earlier than the base backup. Recovery can go forward from a backup, never backward — use an older backup.

Recovery runs past the target and the mistake is still there. The target was set after it, or recovery_target_inclusive included the transaction you meant to exclude. Recover again to an earlier target; this is why the base backup is copied rather than restored over the original.

The server starts and immediately promotes without pausing. Not the action setting — its default is pause, as Task 5 explains. Recovery promoted because it never reached a target: check the log for a recovery stopping before line, and if there is none, the target lies beyond the end of the archive. The commonest cause is a target written in local time against an archive recorded in UTC. A second cause is hot_standby = off, where pause degrades to shutdown.

FATAL: could not locate required checkpoint record. The backup_label was removed, or the WAL needed to reach consistency is missing from the archive. -Xstream at backup time avoids the second case.

restore_command fails for every segment. Run it by hand as the postgres OS user on the recovery host, substituting a real filename. The commonest causes are a path that does not exist from that user, and permissions.

pg_ctl start returned non-zero and the server is running. With recovery_target_action = pause, pg_ctl can time out waiting for the server to accept connections while recovery is paused. Check the log and pg_isready rather than trusting the exit status here.

The recovered cluster tried to archive into the original archive. Set archive_mode = off on the recovery instance before starting it, or it writes its own timeline’s segments into the archive the original is still using.

Cleanup

docker exec -u postgres rbpg-pitr /usr/lib/postgresql/18/bin/pg_ctl -D /lab20/restore stop -m fast
docker exec rbpg-pitr rm -rf /lab20
docker exec -u postgres rbpg-pitr psql -X -c "DROP DATABASE IF EXISTS lab20;"

Production notes

Restore beside the original, never over it. The original data directory is the only thing that lets you try a different target when the first one is wrong, and PITR targets are frequently wrong the first time.
recovery_target_action = 'pause' is not optional in a real recovery. It gives you a read-only server at the target where you can verify the data before the promotion makes it permanent.
Force a WAL switch on the primary the moment an incident is declared. The segment containing the last good data is not in the archive until it is complete or switched, and everything after the incident depends on it being there.
Set port and archive_mode = off on every recovery instance. The first stops it colliding with the original; the second stops it polluting the archive with its own timeline.
Rehearse this on a schedule and time it. The RTO you quote is the measured duration of this procedure, not an estimate, and it changes as the database grows.

What You Learned

Recovery goes forward from a backup, never backward. The target must be after the backup.
recovery_target_action = 'pause' gives you a read-only server at the target to verify before promoting. It is also the default — boot_val is pause, not promote — but set it explicitly anyway, because it degrades to shutdown when hot_standby is off and a template may override it.
Promotion creates a new timeline, and the .history file records the LSN at which it diverged.
A target beyond the archive stops where the WAL runs out, which is a different failure from a target before the backup.
The recovery instance must not archive. Its timeline is not the original’s, and mixing them in one archive is difficult to unpick.
pg_ctl’s exit status is unreliable while recovery is paused — read the log and pg_isready instead.

Objectives

Prerequisites