How to undo an accidental deletion in Postgres: getting deleted rows back

Stop writes to the affected tables before anything else, and do not run VACUUM. How to undo an accidental deletion in Postgres depends on where a copy still lives: a committed DELETE has no undo command, but the rows can survive in a backup, in point-in-time recovery, in the app’s own history, or briefly on disk.

How to undo an accidental deletion: what to do in the first ten minutes

An accidental deletion in Postgres is undone in five moves: stop the writes, write down when the delete ran, count what is missing, find the newest copy that still holds the rows, and restore that copy somewhere other than production. Restoring over production comes last, if at all.

Recovering data that was accidentally deleted from a database is a different job from getting back a deleted file: the copy you need is a database copy. This page covers rows that vanished from tables that still exist. If the whole database or project is gone, the first hour is different, and the steps are written up for when the whole production database is deleted.

For a set of missing rows, these five steps come before any restore. Each one leaves a note you will need later.

1. 01 Stop the writes. Put the app in maintenance mode, or turn off the route, background job or AI agent that ran the delete, so the damage does not grow. Note the time you did it.
2. 02 Do not run VACUUM, VACUUM FULL or CLUSTER, and do not click anything that offers to clean up or reclaim space. Note anyone else who has database access, and ask them to hold off too.
3. 03 Write down when the delete ran, to the second if you can, from the database logs, the app logs or the agent's transcript. A point-in-time restore needs a target just before that moment.
4. 04 Count what is missing: the row count the DELETE reported, or a count against the newest backup. Then check the tables that point at it, because a cascade may have taken their rows too.
5. 05 Check what your plan keeps before you pick a way back: which backups exist, how far back they reach, and whether point-in-time recovery is on.

Step 2 matters because of how Postgres deletes. PostgreSQL’s routine vacuuming docs say that “an UPDATE or DELETE of a row does not immediately remove the old version of the row”, and that the standard form of VACUUM “removes dead row versions in tables and indexes and marks the space available for future reuse.” Those dead versions are the last place deleted rows can be read from, and a manual vacuum ends that.

For step 4, Postgres returns a command tag of the form DELETE count when a delete succeeds, and that count is the number of rows deleted. A foreign key declared with ON DELETE CASCADE deletes the rows that reference a deleted row, so orders, invoices or comments in other tables can be gone as well.

For step 5 on Supabase, the Free plan has no automated backups, and the docs tell you to export with the CLI yourself. Pro keeps 7 days of daily backups, Team 14 days and Enterprise up to 30; point-in-time recovery is a paid add-on on Pro, Team and Enterprise with 7, 14 or 28 day retention that replaces daily backups.

Why the rows are gone, and where a copy may still be

Deleted rows leave Postgres in two stages: once the DELETE commits, ROLLBACK can no longer reach them, and later vacuum, which autovacuum runs by default, frees the space their dead versions used. What survives is any copy made before the delete, in a backup, a point-in-time log, the app’s history, or dead rows not yet vacuumed.

The mechanism is short. The ROLLBACK reference says ROLLBACK “rolls back the current transaction and causes all the updates made by the transaction to be discarded.” That only helps while the transaction is open. A DELETE typed on its own, without BEGIN, runs inside an implicit BEGIN and, if it succeeds, an implicit COMMIT, so it is final the moment it finishes. After the commit, the old row versions stay in the table’s pages as dead rows until vacuum reclaims the space, and in the default configuration autovacuum is enabled.

How the rows got deleted changes nothing about where they can come back from, but it tells you what to stop. The usual causes:

• An AI agent or a script ran a DELETE whose WHERE clause matched more rows than intended, or had no WHERE clause at all, which deletes every row in the table.
• An ON DELETE CASCADE foreign key reached tables nobody had in mind when the parent row went.
• A customer deleted the wrong record in the app, and the app removed it for real.

Every copy that may still hold the rows sits in one of these places, from the most common to the last resort:

Supabase’s backups docs list what each plan keeps and where backups live in the dashboard: Database > Backups. Work down the table and use the first place that still holds the rows; the later entries are for when the earlier ones are closed.

How to reverse a delete in Postgres, one rung at a time

To reverse a delete, start at the top of the table above and stop at the first rung that still holds the rows. Every procedure below comes from the PostgreSQL and Supabase documentation and the pg_dirtyread README; none of it was run against a live database for this page.

Restore a backup into a copy, then move only the missing rows back

Restore the backup into a separate database or a new project, never over production. An in-place restore takes the whole database back to the backup’s moment, so every other customer’s changes since then go with it, and on Supabase the project is inaccessible while the restore runs.

On Supabase, the browser path is Database > Backups, then the Restore to a New Project tab. That flow is for paid plans with physical backups enabled, and it creates a database-only copy. It is a binary restore: enabled extensions such as pg_cron and pg_net come across, and their jobs start running as soon as the restore completes. If a scheduled job in your app emails customers or calls a webhook, Supabase’s docs point to a logical restore with the CLI instead, so you can remove those jobs first. Supabase’s backup-restore guide describes that manual path as a logical restore using pg_dump and psql, and names Restore to a new project and Branching as separate flows.

With the copy running, move only the rows production lacks. In this example the table is orders with an id primary key; the two \copy lines are psql commands, and nothing else goes on those lines.

-- Connected to the restored copy, in psql:
\copy (SELECT * FROM orders) TO 'orders.csv' WITH (FORMAT csv)

-- Connected to production, in psql:
CREATE TEMP TABLE recovered_orders (LIKE orders);
\copy recovered_orders FROM 'orders.csv' WITH (FORMAT csv)
INSERT INTO orders
SELECT r.* FROM recovered_orders r
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.id = r.id);

The syntax is in PostgreSQL’s INSERT reference: an INSERT can take its rows from a SELECT, and ON CONFLICT (id) DO NOTHING in place of the WHERE NOT EXISTS line skips rows whose id already exists. If the id column is GENERATED ALWAYS, inserting an explicit id is an error unless the statement says OVERRIDING SYSTEM VALUE. LIKE copies column names, types and not-null constraints, so the holding table lines up with the export as long as no migration changed the table since the backup; if one did, list the columns on both sides. The holding table is temporary: Postgres drops it at the end of the session, so run the production lines in one psql session, and no extra copy of the rows stays behind. If you know the condition the bad DELETE used, add it to the final WHERE, so rows someone removed on purpose after the backup stay removed.

Without a terminal, Supabase’s Table Editor can load a CSV that holds only the missing rows into an existing table through Insert, then Import Data from CSV, with a 100MB size limit. Go table by table, parents before children, including the child tables a cascade emptied, so every foreign key finds its parent. Count the rows after each table before moving to the next.

What is point in time recovery, and when it can reach your rows

Point-in-time recovery replays the database to a chosen moment, so a target a few seconds before the delete reaches the rows as they were, inside the recovery window. On Supabase it is a paid add-on, and an in-place recovery makes the project inaccessible while it runs, so a copy in a new project is the safer target.

Postgres keeps a write-ahead log that “records every change made to the database’s data files.” PostgreSQL’s point-in-time recovery chapter describes recovery as restoring a base backup and replaying the archived log, and notes “We could stop the replay at any point and have a consistent snapshot of the database as it was at that time.” The stop point is called the recovery target, and it can be a date and time. The same chapter says the method “can only support restoration of an entire database cluster, not a subset”, so you get a whole database back and still carry the rows across yourself.

On Supabase, PITR is a paid add-on on the Pro, Team and Enterprise plans and requires at least a Small compute add-on. A restore can reach any point with up to seconds of granularity. In-place PITR makes the project inaccessible during the restore, the same downside as an in-place backup restore. So pick Restore to a New Project, as above, and set its date and time selector a little before the delete you noted in step 3, and then move the missing rows as in the section above. What the add-on costs is covered in Supabase backups and point-in-time recovery.

When the app kept its own copy

Look inside the app before restoring anything. If the table has a deleted_at column and the delete went through the app, the rows may still be there, only hidden, and an UPDATE that clears the column brings them back. A DELETE run straight against the table removes them all the same. How that pattern works is set out under what soft delete is.

Other copies can rebuild the rows that matter most: a history or audit table, a recent CSV export, the confirmation emails the app sent, and the payment provider’s records of customers and invoices. What a history table records, and how to add one, is covered in audit logging best practices. When the app keeps no history, the database’s own copies are the only way back.

pg_dirtyread: reading deleted rows off disk before vacuum

pg_dirtyread is a Postgres extension that reads row versions a DELETE left dead but VACUUM has not yet removed. It is the last rung: it needs the extension installed by a superuser and a table that vacuum has not processed since the delete.

The pg_dirtyread README says the extension “provides the ability to read dead but unvacuumed rows from a relation”, supports PostgreSQL 9.2 and later, and is loaded by connecting to the database as a superuser and running CREATE EXTENSION pg_dirtyread. It is built and installed on the server with make and make install, and a table is read with SELECT * FROM pg_dirtyread('tablename') AS t(col1 type1, col2 type2, ...), where the alias describes the table’s columns. On Supabase, pg_dirtyread is not on the list of available extensions, so for Supabase readers this rung is closed: go back to backups or point-in-time recovery.

On self-hosted Postgres, the README’s own example turns autovacuum off for its table with ALTER TABLE foo SET (autovacuum_enabled = false, toast.autovacuum_enabled = false). With that setting false, the CREATE TABLE reference says the table “will not be autovacuumed, except to prevent transaction ID wraparound.” The risks are on record from the maintainer, Christoph Berg, who warns on his blog that pg_dirtyread and pg_surgery “operate outside the normal PostgreSQL MVCC machinery, it’s easy to create corrupt data using them.” He also writes that none of these methods work once VACUUM or autovacuum has purged the rows, and that pg_dirtyread does not yet work if the deleted rows contain any toasted values. Read from a copy of the database files, never first on production.

pg_surgery, the other extension in Berg’s post, is a repair kit. The pg_surgery docs say its functions perform surgery on a damaged relation and that they “are unsafe by design and using them may corrupt (or further corrupt) your database.” Berg shows that one of them, heap_force_freeze(), can also make deleted rows reappear as a side effect, under his caveat that he is “not promising any of the ideas quoted below will actually work in practice.” We print no call to it here: the rungs above come first.

Confirm the rows are back

Four checks, each with evidence to keep:

• For each table, the number of rows inserted matches the number that went missing in step 4, checked before writes reopen. Keep both counts.
• Every restored child row finds its parent: a query joining the child table to its parent on the foreign key returns every restored row. Keep the query and its result.
• The app shows a restored record to the user who owns it, and a second account in another tenant cannot see it. Keep screenshots of both.
• A short note: when the delete ran, which rung you used, which tables and how many rows moved. The customer may ask, and the next incident starts from it.

How to stop it happening again

An accidental deletion stops being permanent when three things exist before it happens: soft delete on the records people delete by mistake, a backup someone has restored at least once, and point-in-time recovery on the production database.

Soft delete marks a row with a timestamp instead of removing it, so putting a mistaken delete back takes one UPDATE rather than a restore. It belongs on the records a customer can delete from the app, such as projects, documents and accounts.

A backup only counts once it has been restored into an isolated database and checked, with how long it is kept written down where the team can find it. The retention and the restore drill are both in the database backup checklist for startups.

Point-in-time recovery belongs on the production database once the app takes payments, because, in Supabase’s words, “Even with daily backups, you could still lose a day’s worth of data.” On Supabase it is the paid add-on described above, with at least a Small compute add-on.

The habit that prevents the next one: scripts and AI agents get no write access to production, and a bulk delete first runs as a dry run that counts the rows it would touch, as in a dry run before a data cleanup. These controls sit with the other database checks a SaaS needs in the data consistency checklist for SaaS.

Common questions about getting deleted rows back

Is ROLLBACK possible after commit?

No. ROLLBACK discards the changes of the current transaction only, and once COMMIT has run the changes are permanently recorded. After a commit, deleted rows come back only from a copy: a backup, point-in-time recovery, the app’s own history, or dead rows that vacuum has not yet removed.

Can permanently deleted data be recovered?

Yes, in a database, when a copy made before the delete still exists: a backup taken before it, point-in-time recovery inside its window, or dead rows not yet vacuumed. A read replica does not help: replication copies every change from the primary, the delete included.

What does it mean to vacuum a database?

Vacuuming reclaims the storage held by dead row versions, the rows that a DELETE or UPDATE left behind, so the space can be reused. Plain VACUUM marks the space for reuse; VACUUM FULL rewrites the whole table into a new file and takes an ACCESS EXCLUSIVE lock while it runs. Once vacuum has processed a table, its deleted rows can no longer be read back from it.

How to perform point-in-time recovery?

Restore a base backup and replay the write-ahead log up to a recovery target, a date and time just before the delete. On Supabase, the PITR add-on does this from the dashboard with a date and time selector, and Restore to a New Project builds a separate, independent project, so production is not the one taken offline.