How do we prevent a backup restore from bringing intentionally deleted records back?

Keep deletion intent outside the database state you might restore, and treat restore as incomplete until that intent is replayed. In practice, that means writing every intentional delete to a durable deletion ledger stored beyond the application database snapshot boundary, then restoring the database, replaying WAL or other database logs as usual, and only reopening the application after reconciling the ledger against the restored state and any derived copies.

Why can a restore resurrect deleted records?

A PostgreSQL base backup plus WAL replay restores a database to a chosen historical point. The documentation is explicit that you can “restore the file system backup and then replay” WAL, and that you can “stop the replay at any point” for point-in-time recovery in PostgreSQL continuous archiving and PITR documentation. That is exactly why deleted rows can come back: if the chosen recovery point is before the delete was recorded in the primary database, the restored cluster has no memory of that later business decision.

Commit deletion intent to a system that is not rolled back by the database restore, and keep normal application traffic blocked until that ledger has been replayed.

What should the deletion ledger contain?

Record the minimum durable fact needed to reapply the decision: object type, object identifier, deletion timestamp, legal or product reason code if needed, and a monotonically increasing ledger position. Make the delete operation idempotent. If the same tombstone is replayed twice, nothing should be recreated or double-deleted.

Store the ledger somewhere with retention independent of the application database backup set. That could be an append-only stream, a separate control database with its own recovery regime, or immutable object storage. If you use S3 for ledger segments, treat lifecycle policy as storage hygiene, not as your business-rule engine. AWS says S3 Lifecycle can transition or delete expired objects and that S3 “deletes expired objects on your behalf” in the Amazon S3 lifecycle documentation. Useful for retention, yes; not sufficient to prove that a user-initiated delete must win after restoring an older database.

How does the replay boundary work during recovery?

Worked example:

• T1: nightly database backup finishes.
• T2: customer account 847 is intentionally deleted; application writes a tombstone to the deletion ledger and removes live rows.
• T3: production corruption forces restore from T1 backup.
• T4: PostgreSQL recovery replays WAL up to the selected safe point.
• T5: before the app accepts reads or writes, recovery job reads deletion-ledger entries after T1 and re-applies delete 847 to the restored database, search index, cache, analytics sink, and object references.
• T6: app reopens.

The key control is at T5. Reconciliation is a startup gate, not a background best effort. If you cannot reconcile all stores that can expose the restored data, keep reads and writes unavailable for the affected data domain. Reopen only independently verified, reconciled data; read-only access can still disclose records that were intentionally deleted.

How do you handle derived copies and retention rules?

Your ledger should drive compensating deletes in every derived system that can surface the data: search, warehouse exports, blob metadata, read models, and caches. Do not claim success when only the primary table is fixed.

Organizational retention rules are separate. They decide how long backups, WAL archives, or ledger segments are kept. Engineering enforcement decides that, after restore, deletion intent is replayed before data becomes active again.

Two quick follow-ups:

What if the deletion was accidental? Keep delete authorization and undelete policy separate from the ledger. The ledger enforces the decision that was made; governance decides whether a reversing event is allowed.

Can WAL alone solve this? Only if you always recover to a point after every intended delete. In real incidents, the safe recovery point may be earlier, so WAL is not a substitute for an external deletion ledger.

Next step: document one restore runbook that names the ledger authority, the exact reopen gate, and the list of derived systems that must be reconciled before production traffic resumes.

Reviewed: 2026-09-05