Restore a Postgres backup without bringing back deleted users
You restore Monday's backup on Wednesday. Two things come back that you did not ask for:
every row deleted on Tuesday, including the account a user asked you to erase, and every
scheduled job, which starts running against the restored data. This guide restores
without either, first with pg_dump and pg_restore, then with
SafeGrd.
1. What comes back
| What | Why |
|---|---|
| Rows deleted after the backup | They are in the backup. For an account erased at its owner's request, the restore undoes the erasure, and you have to delete it again. |
pg_cron jobs | pg_dump includes the rows of cron.job, because pg_cron marks that table as configuration. pg_restore puts every job back as it was, and each runs at its next scheduled time. |
| Triggers that call out | A database webhook is a trigger. It is restored with its table and fires on the next write. The requests that were queued when the backup ran are not in it. |
2. Keep a record of erasures outside the database
When your app erases an account, have it also write the account's id and the time to a table in another database, on another server:
Not in the same database: it would be lost with it, and each backup would hold only the erasures made before it, which are the ones you do not need. After a restore, and before your app writes to the restored database, delete again every account erased since the backup was taken. Here the backup ran at 02:00 UTC on 12 October 2026, and the hour before it covers transactions that were still open:
This is safe when an id is never reused, as with serial or UUID keys. If a
table is keyed by something people can reuse, such as an email, only replay the erasures
made after the backup, or you delete someone who signed up again. Foreign keys with
ON DELETE CASCADE take the account's other rows with it; a table that copies
personal data without a foreign key needs its own DELETE.
3. Restore without starting the jobs
The first command lists what the dump holds, without the rows of cron.job. The
second restores everything on that list in one transaction, so the restored database has
pg_cron and no jobs. The third writes the jobs to jobs.sql for you to read.
Once you have checked the restored data, schedule again the ones you want with
SELECT cron.schedule('name', 'schedule', 'command');.
pg_cron creates only in the database that cron.database_name names
(postgres by default, and on Supabase). Restoring into another database fails
at CREATE EXTENSION pg_cron with “can only create extension in database
postgres”. Restore into that database, or drop the extension's line from
restore.list as well.
4. With SafeGrd
Name the tables whose deleted rows are erasures on the database's surface:
The next backup puts a trigger on public.users that records the primary key
of each deleted row, never the row. The daemon moves those records out of the database on
every check-in, encrypted to your key, beside your backups. A restore of an older backup
deletes every row erased after it, before it commits, and says so:
The pg_cron jobs come back turned off, whether or not you name any table. An erasure the
backup already saw is not replayed, so a key reused since is safe. If the record cannot be
read, nothing is restored until you choose --without-erasures. Every Fire
Drill reads the record, so you learn it is unreadable before you need it. The details are
on the PostgreSQL page.
Before you need it
- Decide which tables hold erasures. Accounts and profiles, not sessions or carts: every delete writes a record.
- Put your pg_cron jobs in your migrations, so a restore that leaves them out loses nothing you cannot recreate.
- Rehearse. Restore last week's backup into a scratch database, run section 2 or let SafeGrd do it, and check that an account you deleted since is gone.