The retention job that deleted depot-3

Lab machine

A private Linux machine with the problem already set up. Sessions last up to 60 minutes.

Ana Costa opened INC-2541 at 07:35SEV-1

Overnight a retention migration was meant to purge old cancelled shipments. This morning every depot-3 parcel from before today is "not found" in tracking. There is a backup from 01:00.

Customer service has forty calls queued before 08:00, all about depot-3 parcels that tracking no longer knows. The depots have been scanning since 06:00, so the database has moved on since the backup.

"There's a full backup from 01:00. Can we just restore it?" (Ivo)

Before restoring anything, work out exactly what the migration removed, what it was supposed to remove, and what has been written since.

Your task

Bring back the shipments the migration deleted by mistake, with their original ids, without losing this morning's scans and without undoing the deletions the retention policy really wanted.

On the machine

migrations/0042_purge_cancelled.sql
backups/waybill-2026-10-04T0100Z.dump (pg_restore -l lists what it holds)
psql -d waybill -c "SELECT depot, status, count(*) FROM shipments GROUP BY 1, 2"

Timeline

01:00Nightly backup of the waybill database.

01:20Retention migration CHG-4475 runs in the deploy window.

07:20Customers read depot-3 tracking numbers that return "not found".

07:35"There's a full backup from 01:00. Can we just restore it?" (Ivo)

Done when

Every shipment the migration deleted by mistake is back, unchanged, with its original id.
The shipments recorded since the 01:00 backup are all still there, unchanged.
The shipments the retention policy meant to purge stay purged.

How it is checked:The checker verifies that the backup file is unchanged and compares this morning's shipments with a checksum taken at setup. It restores the backup into its own scratch database, applies the retention rule as written in the migration's comment, and compares every pre-backup shipment in the live table with that expected set by id and content.

Passed. Here is how others fixed it

The fix we expected. Restore the backup into a new database (`createdb recovery && pg_restore -d recovery backups/*.dump`), copy its shipments into the waybill database (for example `\copy` out and into a temporary table), then `INSERT INTO shipments SELECT * FROM restored WHERE id NOT IN (SELECT id FROM shipments) AND NOT (status = 'cancelled' AND (created_at < DATE '2026-07-06' OR depot = 'depot-3'))`.

Other fixes that also pass:

Use `pg_restore --data-only -t shipments -f restored.sql` to get the rows as SQL, load them into a temporary table, and insert the wrongly deleted ones.

Fixes that look right but are not:

Running `pg_restore --clean` against the live database.
Bringing back every row that is missing, including the old cancelled ones the policy meant to purge.
Re-inserting rows without their ids, which breaks every reference to them.

Why it broke. The migration meant "cancelled AND (older than 90 days OR from the depot-3 pilot)" but said "(cancelled AND older than 90 days) OR depot-3", so it deleted every depot-3 shipment. Restoring the whole 01:00 backup would bring them back and also erase this morning's scans and reset the id sequence. The safe recovery restores the backup somewhere else, works out exactly which rows were lost by mistake, and copies only those back with their original ids.

How Mara worked it

Read migrations/0042_purge_cancelled.sql aloud, with AND before OR
Work out what it really deleted and what it meant to delete.
pg_restore -l backups/*.dump | head
Know what the backup holds before restoring anything.
createdb recovery && pg_restore -d recovery backups/*.dump
Restore somewhere harmless. The live database has this morning's scans.
Copy the backup's shipments into a table in waybill (\copy out and in)
Both copies side by side, so a single INSERT can compare them.
Insert rows missing from live that do not match the intended purge rule
Bring back only the mistakes, with their original ids.
Count by depot and status, and compare with what you expected
Recovery is done when the numbers agree, not when the command succeeds.

Hints

Hint 1

Read the migration's WHERE clause the way PostgreSQL does. AND binds tighter than OR.

Hint 2

`pg_restore` can restore into a different, empty database. Nothing forces you to restore over waybill.

Hint 3

With the backup in its own database, compare it with the live table by id.

Hint 4

Write the condition the comment describes, and keep rows that match it out of what you copy back.

Show the solution

Restore the backup into a new database (`createdb recovery && pg_restore -d recovery backups/*.dump`), copy its shipments into the waybill database (for example `\copy` out and into a temporary table), then `INSERT INTO shipments SELECT * FROM restored WHERE id NOT IN (SELECT id FROM shipments) AND NOT (status = 'cancelled' AND (created_at < DATE '2026-07-06' OR depot = 'depot-3'))`.

Databases for Operators

Backups you can restore from

A backup is a copy of the past. Recovery means taking back exactly what was lost, without undoing what happened since.

What a dump is

pg_dump connects like any client and reads the database inside one transaction, so the dump is a consistent snapshot of that moment. The custom format (-Fc) is compressed and lets pg_restore choose what to restore and in which order. A dump says nothing about what happened after it was taken. If the nightly dump runs at 01:00 and the damage happens at 01:20, everything written between 01:20 and now exists only in the live database. Physical backups with archived WAL let you replay to any moment, but they restore a whole cluster, which is rarely what you want for one bad statement.

Restore somewhere else first

Restoring a dump over a live database with pg_restore --clean makes the database match the dump. That brings back the lost rows and also deletes every later write and resets sequences, so new inserts may collide with ids handed out since. The safer pattern:

createdb recovery and pg_restore -d recovery waybill.dump. Nothing live is touched.
Compare: which ids exist in the backup but not in production?
Decide which of those were lost by mistake, then copy only them back, keeping their original ids. Other tables, logs, and customers refer to those ids.

Read what the change meant

The hardest part is step 3. A bad migration usually deleted some rows on purpose and others by accident. SQL evaluates AND before OR, so status = 'cancelled' AND created_at < X OR depot = 'depot-3' means (cancelled and old) or depot-3, whatever the comment above it says. Write down the rule the change was supposed to apply, express it as a condition, and exclude rows that match it from what you restore. Then verify by counting: rows restored, rows still purged, rows written since the backup, all matching what you expected before you started.

A backup you have never restored from is a hope. Practise the restore, time it, and write it down, so the first time is not during an incident.

Key terms

Logical backup: A dump of the database as SQL or an archive of rows, taken from a consistent snapshot (pg_dump).
Point-in-time recovery: Restoring a physical base backup and replaying archived WAL up to a chosen moment.
RPO: Recovery point objective, the most data you can afford to lose, measured in time.
Selective restore: Recovering only some objects or rows from a backup into a live system.

Read further

PostgreSQL Documentation, version 17, Chapter "Backup and Restore", sections "SQL Dump" and "Continuous Archiving and Point-in-Time Recovery (PITR)" (Free online (PostgreSQL Licence))
What pg_dump guarantees (a consistent snapshot), and what PITR adds (recovery to any moment).
PostgreSQL Documentation, version 17, Reference pages for pg_dump and pg_restore (Free online (PostgreSQL Licence))
Custom-format archives, -l to list contents, -t to pick a table, --data-only, and why --clean against a live database is dangerous.
Database Reliability Engineering, Chapter "Backup and Recovery" (Paid (book or O'Reilly subscription))
Recovery as the goal of backups, recovery scenarios, and testing restores before you need them.
PostgreSQL Documentation, version 17, Chapter "SQL Syntax", section "Operator Precedence" (Free online (PostgreSQL Licence))
Why AND binds tighter than OR, the bug behind many bad WHERE clauses.

The retention job that deleted depot-3 INC-2541