Recovering from a logical mistake

A point-in-time recovery performed calmly, against a target you chose in advance, is a procedure. The same procedure run forty minutes after somebody pasted a DELETE without its WHERE clause is a different exercise entirely, because now the target is unknown, the business is still transacting on top of the damage, and every instinct in the room is pointing at the newest backup. This lesson is about the decisions that surround the recovery rather than the recovery itself, since the mechanics were settled in the previous lesson and the mechanics are not what goes wrong.

A destructive statement is a correct instruction

Most of the protection in a production database estate is aimed at things going wrong: a disk failing, a page checksum mismatching, a node dropping out of a cluster. A logical mistake is not one of those. DELETE FROM orders with no predicate is a valid statement issued by an authorised session, it acquires its locks in the ordinary way, it writes ordinary write-ahead log records, and it commits. There is nothing anywhere in the stack for which it is an error.

That is why the mechanism people reach for first is the one that cannot help. A streaming replica exists to reproduce the primary’s write-ahead log faithfully and as quickly as possible, and it does exactly that. Within the round trip of the network the replica has applied the same records and has the same empty table. A synchronous replica is worse in this respect, not better, because it confirmed the transaction before the commit returned. The same is true of every mirroring arrangement below the database: a storage-layer replica, a stretched volume, a filesystem-level sync all carry the blocks the mistake produced.

Only a copy that predates the statement can undo it, and there are exactly two families of those: a base backup plus archived write-ahead log, replayed to a moment before the commit, and an independent logical export taken earlier. Everything else in the estate is a copy of the present, and the present is the problem.

The first decision is to stop making the recovery larger

Before anything is restored, two things need to happen, and both of them are about limiting how much work the recovery will have to reconcile later.

The first is to stop the application writing, if it is still writing. Every transaction that lands after the mistake is a transaction that will have to be preserved through whatever recovery follows, and one that may itself be built on the damaged state — an order-total recalculation that read an empty table, a nightly job that inserted corrective rows, an integration that decided the customer had no history. Pausing writes is disruptive and nearly always correct: the alternative is a growing set of records whose correctness you must reason about individually. Stopping is reversible; the rows created in the next ten minutes are not.

The second is to leave the damaged cluster exactly where it is. The urge to restore over production is strong precisely because it feels like the fastest route back, and it destroys the only complete record of what happened: the current data directory, its log, and the write-ahead log segments that have not yet aged out of the archive. If the recovery target turns out to be wrong, or the extraction turns out to need a table nobody mentioned in the first ten minutes, a production cluster that has already been overwritten offers no second attempt.

Establishing the moment from the log, not from memory

A recovery target is a timestamp, and the quality of the recovery is bounded by the quality of that timestamp. Asking the operator when they ran the statement produces an answer accurate to a few minutes at best, and a target a few minutes late replays the mistake into the recovered copy while looking entirely successful.

The timestamp has to come from a record. Depending on how the estate is configured, the candidates are the server log where the statement itself was logged, the audit extension’s output, the connection-level log entries that bracket the session, a deployment marker if the damage came from a migration that a pipeline applied, or the archived write-ahead log itself. What follows is the state of a cluster in exactly this position, captured for this course on PostgreSQL 18.6.

$ pg_basebackup -D /work/base -X stream -c fast

  >>> exit code: 0
rows contained in the base backup: 45000

--- business continues after the backup: 5,000 more orders arrive ---
rows now                      : 50000
checksum of the business data : sum(amount)=825025000
recovery target time          : 2026-08-28 13:34:40.077562+00

--- and then somebody runs an unqualified DELETE ---
rows after the mistake        : 0
pg_stat_archiver:
  archived=6 failed=0 last=000000010000000000000005
WAL segments in the archive   : 5
  000000010000000000000001
  000000010000000000000002
  000000010000000000000003
  000000010000000000000003.00000028.backup
  000000010000000000000004
  000000010000000000000005

Three facts in that block decide everything that follows. The base backup holds 45000 rows, which is the floor any recovery starts from. The business had 50000 rows immediately before the incident, so 5000 rows of legitimate work exist only in the archive. And pg_stat_archiver reports archived=6 failed=0, which is the only evidence available at this moment that the archive is continuous enough to carry the recovery past the base backup. A single failed archive attempt in that counter would change the plan, because recovery stops at the first missing segment regardless of what comes after it.

The target chosen here was 2026-08-28 13:34:40.077562+00. What recovery actually did with it is worth reading closely, because the achieved point is not the requested one:

  2026-08-28 13:35:12.735 UTC [631] LOG:  recovery stopping before commit of transaction 836, time 2026-08-28 13:34:42.096745+00
  2026-08-28 13:35:12.735 UTC [631] LOG:  redo done at 0/52EBC90 system usage: CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.02 s
  2026-08-28 13:35:12.735 UTC [631] LOG:  last completed transaction was at log time 2026-08-28 13:34:38.041366+00

Recovery stopped before the first commit it met after the target, and the state it left behind corresponds to the last commit at or before it, at 13:34:38.041366+00. Roughly two seconds separate the target from the commit that was rejected. In a busy system that gap can hold a great deal of work, and on the wrong side of the mistake it holds the mistake itself. The practical consequence is to aim the target deliberately early rather than exactly, and to recover the difference by extraction rather than by trying to land the target on the instant.

The side-by-side recovery on port 5434

The recovered copy is a second cluster. It has its own data directory, its own port, and no relationship to the production cluster beyond the archive both read. Nothing in this sequence touches production.

set -euo pipefail

RECOVER_DIR=/work/pitr-copy
ARCHIVE_DIR=/work/wal-archive
TARGET_TIME='2026-08-28 13:34:40.077562+00'
RECOVER_PORT=5434

install -d -m 0700 -o postgres -g postgres "$RECOVER_DIR"
tar -xf /backups/base-2026-08-28.tar -C "$RECOVER_DIR"

cat >> "$RECOVER_DIR/postgresql.conf" <<EOF
restore_command = 'cp $ARCHIVE_DIR/%f %p'
recovery_target_time = '$TARGET_TIME'
recovery_target_action = 'pause'
port = $RECOVER_PORT
EOF

touch "$RECOVER_DIR/recovery.signal"
pg_ctl -D "$RECOVER_DIR" -l "$RECOVER_DIR/recover.log" start

recovery_target_action is the part that matters under pressure. Its boot value in the Write Ahead Log configuration reference is pause, which stops the cluster at the target and waits so the copy can be inspected before anyone decides it is the right one. A copy that promotes on arrival removes the chance to look first, and looking first is the point of recovering beside production rather than on top of it. The capture’s copy did promote — archive recovery complete at 13:35:12.740, connections accepted at 13:35:12.745 — which is why its log ends on a new timeline:

  2026-08-28 13:35:12.691 UTC [625] LOG:  listening on IPv4 address "0.0.0.0", port 5434
  2026-08-28 13:35:12.708 UTC [631] LOG:  starting point-in-time recovery to 2026-08-28 13:34:40.077562+00
  2026-08-28 13:35:12.713 UTC [631] LOG:  consistent recovery state reached at 0/3000120
  2026-08-28 13:35:12.713 UTC [625] LOG:  database system is ready to accept read-only connections
  2026-08-28 13:35:12.736 UTC [631] LOG:  selected new timeline ID: 2

Once the copy is up, it is verified before it is trusted. The capture’s verification did not check that the server started; it compared the data against a business property recorded before the incident:

  rows recovered  : 50000   (expected 50000)
  sum(amount)     : 825025000   (expected 825025000)
  RECOVERED - row count and business checksum both match the pre-DELETE state

A row count alone would have been satisfied by a target that landed anywhere in a quiet period. The checksum over the business column is what distinguishes a copy recovered to the right instant from one recovered to a plausible-looking wrong instant, and it only exists because somebody recorded it while the system was healthy. That recording is cheap and it is the difference between asserting a recovery is correct and demonstrating it.

Rewinding everything against extracting what was lost

With a verified copy running on port 5434 there are two ways to finish, and the choice is not a matter of taste.

Rewinding means promoting the recovered copy, or repeating the recovery over the production data directory, and running the business on the result. It is simple, it is quick, and it discards every transaction committed between the target and now. If the mistake was noticed in ninety seconds on a quiet system, that cost may be zero. If it was noticed at the end of the working day, the cost is the working day, and it lands on customers who did nothing wrong.

Extraction means reading the affected rows out of the recovered copy and putting them back into a production cluster that has never stopped being authoritative for everything else. It preserves all the work that continued after the incident and it confines the change to the data the mistake touched.

set -euo pipefail

RECOVER_PORT=5434
CUTOFF='2026-08-28 13:34:40.077562+00'
EXPORT_FILE=/work/orders-recovered.csv

EXTRACT="\\copy (SELECT * FROM orders WHERE created_at < '$CUTOFF')"
EXTRACT="$EXTRACT TO '$EXPORT_FILE' WITH CSV"
psql -p "$RECOVER_PORT" -d shop -c "$EXTRACT"

psql -p 5432 -d shop -c 'CREATE TABLE orders_staged (LIKE orders INCLUDING ALL)'
psql -p 5432 -d shop -c "\\copy orders_staged FROM '$EXPORT_FILE' WITH CSV"

The rows land in a staging table inside the production database, where they can be counted and compared against what production currently holds before one row is merged into the live table. That is what makes extraction reversible: a staging table that turns out to be wrong is dropped, whereas an INSERT straight into orders has to be identified and undone.

Prefer extraction whenever the rest of the database has moved on, which in a transacting system means almost always. Prefer the rewind when the mistake was estate-wide rather than confined to a few tables, when the referential structure makes a partial reinsertion genuinely unsafe, or when nothing of value committed after the target — and in that case, say so with a number, not an impression.

Where a 24-hour RPO is felt most sharply

A logical mistake is where a recovery point objective stops being an abstraction on a slide, because the loss window applies to the whole recovery unit and not to the damage.

Consider the same incident against an estate whose only copy is a nightly logical dump at 02:00 — a 24-hour recovery point objective by construction. The mistake happens at 16:00 and affects one table. The recovery available is the dump, so the choice is to restore a fourteen-hour-old copy of everything, discarding fourteen hours of legitimate work in every other table, or to extract one table from that dump and accept that the table is fourteen hours stale while the rest of the database is current. Both options are bad, and neither is made better by the fact that the mistake itself touched one table.

Now consider the estate in the capture. A base backup plus a continuous archive made the loss window the distance between a commit and its segment reaching the archive, and the recovery was aimed at an arbitrary instant of the operator’s choosing rather than at whatever the last scheduled job happened to leave behind. That is the difference an architecture buys: not merely a smaller number, but the ability to place the recovery point relative to the incident instead of relative to the schedule.

The trap that closes this loop is the reflex to reach for the newest backup. It is the newest copy, so it feels like the least loss, and for a logical mistake it is the copy most likely to contain the mistake already. Recovery point selection for this failure mode runs backwards from the incident, not forwards from recency, and the target is defended with a log line rather than with a timestamp on a file.

What to take from this

• The cluster in the capture went from 50000 rows to 0 rows after the unqualified DELETE, and every replica of it held the same empty table, because the statement was valid and replication reproduced it faithfully.
• The base backup contained 45000 rows. The other 5000 existed only in the archived write-ahead log, and pg_stat_archiver reporting archived=6 failed=0 was the evidence that the archive could carry them.
• The requested target was 2026-08-28 13:34:40.077562+00, but recovery stopped before the commit of transaction 836 at 2026-08-28 13:34:42.096745+00 and settled on the last commit, at 2026-08-28 13:34:38.041366+00. Aim the target early rather than exactly.
• The recovery ran on port 5434 and reported selected new timeline ID: 2. The recovered copy is a permanently divergent branch that production will never merge on its own, which is precisely why it is safe to run beside it.
• Verification compared the recovered 50000 rows and the checksum sum(amount)=825025000 against figures recorded before the incident. A row count alone would not have distinguished the right instant from a plausible wrong one.
• A recovery point objective applies to the whole recovery unit. Restoring an estate whose only copy is a 02:00 dump because of a 16:00 mistake discards fourteen hours of work in every table, not just the damaged one.

Cross-course references

• PostgreSQL for Production Sysadmins — Part XIV (Replication, Slots and Read Replicas) covers how faithfully a standby reproduces the primary’s write-ahead log, which is the property that makes replication excellent against node loss and useless against the destructive statement recovered in this lesson.
• Observability for Production Sysadmins — Part CIX (Incident Investigation Workflows) covers deriving an incident’s timeline from recorded signals rather than from recollection, which is the same discipline this lesson applies when it insists the recovery target comes from a log line rather than from the operator who ran the statement.
• Git, CI/CD & GitOps for Infrastructure Engineers — Part LIX (Rollback) covers reverting a deployment, and reading it beside this lesson makes the boundary explicit: rolling the migration’s code back restores the schema the application expects, and does nothing whatsoever about the rows the migration destroyed, which still require the recovery described here.

Quiz