How to Undo a DELETE in Postgres: Getting Deleted Rows Back

Postgres has no undo for a delete that has already committed. What you can get back depends on one thing: whether a copy of those rows survives somewhere else. Stop writing to the table first, then work down the list of copies from safest to riskiest.

This page is written from documentation rather than from a database. The commands, limits and warnings below are taken from the PostgreSQL documentation, the pg_dirtyread README, the Supabase documentation and the pg_dirtyread maintainer’s own write-up, all checked 2026-08-17. Nothing was run against a live database to write it, and no managed database account was opened.

Three nearby situations are not this page. If the whole database, schema or project is gone rather than a set of rows, the first hour looks different. If the rows are still there but hold the wrong value after a bad update or a bad import, the repair is different. And if nothing has actually gone wrong and you want to know whether your backup works, that is a drill rather than an incident. The last section sorts those out.

Do these three things before you try to recover anything

Two of these three steps exist to stop you making the situation worse, and the clock on the third one is running whether you know it or not.

1. Stop writing to the affected table. Pause the app if you can, or just the one screen that writes to it. Every new write makes the later job of separating recovered rows from live rows harder, and on the bottom rung of the ladder new writes can physically overwrite what you are trying to read.
2. Do not run VACUUM, VACUUM FULL or CLUSTER, and do not press any button offering to clean up, optimize or reclaim space. PostgreSQL’s routine vacuuming documentation explains why: “In PostgreSQL, an UPDATE or DELETE of a row does not immediately remove the old version of the row.” The standard form of VACUUM is what removes those dead row versions and marks the space available for reuse, and the same page states that autovacuuming is enabled in the default configuration. So the process that closes your last rung is already running on a schedule you did not set.
3. Take a copy of the table as it stands right now, before you change anything. Export it, dump it, copy it into a second table, whatever your platform lets you do without a terminal. A failed recovery attempt that has a snapshot behind it is an inconvenience. The same attempt without one can turn a partial loss into a total one.

That is the whole containment step, and it is worth doing even when you are fairly sure a good backup exists. It costs five minutes.

Work out how many rows actually disappeared

The number of rows that vanished can be larger than the number your delete named. Foreign keys with a cascading delete action remove child rows in other tables automatically, and those tables are often ones you were not looking at. One deleted customer can take orders, invoices, payments and audit rows with it.

Row-level security will not have stopped any of that. PostgreSQL’s row security page says referential integrity checks, meaning unique and primary key constraints and foreign key references, always bypass row security so that data integrity is maintained. A policy that stops your app’s roles from deleting a billing row does nothing about the same row leaving through a cascade declared on its parent.

So the first job is to establish which rows are actually missing, before anyone tries to bring any of them back. List the tables that have a foreign key pointing at the one you deleted from, then check the ones that hold anything you would need to answer a customer or an accountant. Once the data is back, the delete actions that caused it are worth reviewing on their own: which of those keys cascade, and which of those cascade paths reach a table you would have to explain in a chargeback.

If your platform gives you a SQL editor in the browser, the information_schema views and the pg_constraint system catalog will list those relationships without a terminal.

The recovery ladder: where a copy of your deleted rows might still be

Recovery in Postgres is a search for a surviving copy. There are five places a copy can be, and they are not equally reachable, equally complete, or equally safe. This ladder is ordered by what you can physically get to, which is also, usefully, roughly the order from safest to riskiest.

Work down that table from the top, skipping any rung your setup does not have. Rung 2 is the one that is sometimes missing on purpose: on some managed platforms, turning point-in-time recovery on stops the daily backups, so a project on rung 3 has no rung 2 underneath it. Rungs 1 to 4 are all read operations against a copy: if they fail, you have lost time and nothing else. Rung 5 is the only one where the attempt itself can damage the database further, which is why it goes last even though it is the rung the internet talks about most.

One honest caveat before you start: rung 4 is the one people forget and the one that most often ends the incident. Before you touch anything technical, ask whether a report was emailed, a CSV was exported for an accountant, a spreadsheet was kept, or a customer has their own copy of the record you lost.

What you can do from the dashboard, without touching SQL

The browser rungs are 2 and 3, and on a managed platform both live behind a small number of screens. This section uses Supabase as the worked example, because its backup behavior is documented publicly and precisely enough to quote. If you are on something else, the next section tells you what to look for.

Supabase: where deleted rows can be recovered from the browser

Supabase’s database backups documentation, checked 2026-08-17, sets out the gating plainly. Daily backups are taken automatically for Pro, Team and Enterprise projects: Pro can reach the last 7 days of them, Team the last 14, and Enterprise up to 30. Point-in-time recovery is sold separately, as an add-on those same three plans can switch on, and the page attaches a compute floor to it: nothing smaller than the Small compute add-on. The same page states what enabling it costs you: “If you enable PITR, we will no longer take Daily Backups.” So point-in-time recovery does not sit on top of the daily backups. It replaces them. Free projects are not in that automatic list at all, and the page’s recommendation to them is to export regularly with the Supabase CLI’s db dump command and hold those exports off-site. Those figures move; read the page before you rely on them.

The documentation names two places in the Dashboard and no others. Daily backups live in the Database > Backups section. Point-in-time recovery is reached through the Point in Time settings. Look for those two and do not guess at any other menu label.

Two warnings on that page are worth reading before you press anything. The first is about files rather than rows. A database backup captures what the database knows about anything uploaded through the Storage API, which is metadata, and not the uploaded file itself, and the page adds the consequence: “Restoring an old backup does not restore objects you deleted after that backup.” If the incident took uploads with it, restoring the database will not bring them back.

The second is about time. The page’s own restoration advice is to pick the closest backup taken before the point you want, and it adds that you can always choose an earlier one but should weigh how many days of data that costs. That is the whole trouble with restoring in place: the restore returns the database to a moment in the past and takes every good change since along with it. It is the reason the next section restores into a copy rather than over the top of production.

Supabase’s backup and restore guide describes its manual path as a logical restore using pg_dump and psql, and names Restore to a new project and Branching as separate flows alongside it. So a non-terminal path does exist, though the manual one documented in detail is the command-line one.

On any other platform, open your provider’s own documentation and look for exactly two things: whether a daily backup exists and how long it is kept, and whether a point-in-time option exists on your plan. If the documentation does not answer one of those questions, that is your answer for now, and the export in rung 4 becomes your best rung.

Check whether your app already kept a copy

Rung 1 is the one most owners do not know they might have, because someone else made that decision months ago. Plenty of apps never issue a real delete at all. They set a timestamp on the row and hide it from the interface, so your rows are sitting in the table right now, flagged rather than gone.

Supabase’s troubleshooting page on soft deletes documents the common shape of this: a deleted_at column of type timestamptz, plus a view that shows only the rows where that column is null. Under that pattern, restoring a record means setting the timestamp back to null, which is a single update rather than a recovery operation. The page carried no publication date when it was read on 2026-08-17, so that is the only date its details can be pinned to.

This check is read-only and safe to paste into any SQL editor, including the one in a hosted dashboard:

-- Read-only. Runs as any role that can read information_schema.
-- Asks one question: does this table keep its own history?
-- Replace 'orders' with your table name.
SELECT column_name
FROM information_schema.columns
WHERE table_name = 'orders'
  AND column_name IN ('deleted_at', 'is_deleted', 'archived_at', 'deleted');

If a row comes back, look for your data with a filter on that column before doing anything else. If nothing comes back, that only rules out this table: an audit or history table under a name like orders_history, orders_audit or activity_log is a separate thing to look for, and so is any trigger the app uses to write into one.

Designing soft deletes properly is a separate job with its own tradeoffs, and doing it in the middle of an incident is the wrong time to start.

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

Never restore a backup over the top of a production database that is still partly correct. The restore does not merge and does not skip the good parts. It replaces the database with its state at the backup timestamp, undoing every order and payment recorded since. Restoring over production trades a small loss for a large one.

The pattern that works is: restore into a separate database, read the missing rows out of it, and insert only those rows back into production. Everything that is still fine in production stays untouched.

The sequence below is composed from the PostgreSQL documentation pages cited on this page. It was not run for this article, and the table and column names are placeholders. Before step 2, compare the real source and target schemas, list only the columns you intend to restore, and confirm the primary key, foreign keys and triggers that apply. Read every line before running any of it.

-- 1. Run on the RESTORED COPY, not production. Read-only.
--    Needs SELECT on the table. Tells you what the copy actually holds.
SELECT count(*) FROM orders;

-- 2. Run on PRODUCTION inside a transaction, after loading the rows you
--    recovered into a scratch table called orders_recovered (for example:
--    pg_dump -t orders from the restored copy, then psql into production
--    with the table renamed, or a CSV export and \copy). Replace this
--    example list with verified, matching source and target columns.
BEGIN;

INSERT INTO orders (
  id,
  customer_id,
  amount_cents,
  status,
  created_at
)
SELECT
  id,
  customer_id,
  amount_cents,
  status,
  created_at
FROM orders_recovered
ON CONFLICT (id) DO NOTHING
RETURNING id, customer_id, amount_cents, status, created_at;

-- 3. Still inside the transaction, inspect the rows and dependent tables.
--    Keep ROLLBACK as the default ending. Run the transaction again with
--    COMMIT only after the values, counts and constraints are correct.
SELECT count(*) FROM orders;
ROLLBACK;

Do not rely on schema drift to produce an error. PostgreSQL’s INSERT documentation says values are associated with an explicit or implicit target list from left to right and that automatic type conversion may be attempted. Without matching source and target lists, reordered but compatible columns can succeed with values in the wrong fields. Explicit names remove that positional ambiguity, and the transaction gives you a boundary for checking the affected rows before they become permanent. ON CONFLICT (id) still needs a unique index or constraint on id, and it skips an existing key rather than repairing a row whose values are wrong. Confirm the constraint, returned row set and dependent-table checks before replacing ROLLBACK with COMMIT.

If the restore itself will not complete, that is a different problem from this one, and proving the backup restores before you need it is a job that belongs on a calm day rather than this one.

What point-in-time recovery can and cannot reach

Point-in-time recovery restores a whole database cluster, never a single table. It is often described as rewinding the database to just before the mistake, which is close enough to true to be useful and wrong in the one way that decides how the rest of your recovery goes.

PostgreSQL’s continuous archiving documentation states the limit directly: “As with the plain file-system-backup technique, this method can only support restoration of an entire database cluster, not a subset.” In practical terms, you do not restore the deleted rows. You produce a second, complete copy of the database as it existed at a chosen second, and then you carry the rows across yourself, which is the previous section. Anyone who tells you point-in-time recovery can bring back one table is describing a two-step job as though it were one.

The same page sets out what the mechanism needs: a base backup, plus a continuous sequence of archived write-ahead log files reaching back at least as far as the start time of that backup. If either half is missing or has a gap, the recovery target you want may sit outside what the archive can actually reproduce. On a managed platform you do not assemble those pieces yourself, but you do inherit their consequence: the window is finite and set by your plan, not by you, which is what the dated figures earlier on this page are about.

The last rung: reading deleted rows off disk with pg_dirtyread

This rung exists because of the sentence quoted at the top of this page. A deleted row is not erased at the moment of deletion; its old version stays in the table’s disk pages until vacuum comes for it. Two extensions can read or resurrect those dead versions, and both are genuinely last resorts.

pg_dirtyread reads dead but unvacuumed rows out of a relation. Its README records that Christoph Berg took over further maintenance, documents support for PostgreSQL 9.2 and later, and tells you to connect to the database as a superuser to create the extension, which on most managed platforms means checking your provider’s list of available extensions before you plan around it. The usage shape is two statements:

-- From the pg_dirtyread README. Requires superuser to create the extension.
-- On a managed platform, check the provider's list of available extensions
-- FIRST; many do not offer this one at all.
-- The function returns RECORD, so the column list and types are required.
CREATE EXTENSION pg_dirtyread;
SELECT * FROM pg_dirtyread('tablename') AS t(col1 type1, col2 type2, ...);

Two limits are documented rather than incidental. Berg’s write-up states that pg_dirtyread does not yet work if the deleted rows contain any toasted values, which in practice means long text and large columns. And the README notes that the content of dropped columns survives only as long as the table has not been rewritten by VACUUM FULL or CLUSTER.

The second extension, pg_surgery, is not getting a copy-pasteable call on this page, on purpose. It provides heap_force_kill and heap_force_freeze, and its documentation opens with the warning: “These functions are unsafe by design and using them may corrupt (or further corrupt) your database.” The PostgreSQL documentation reads as version 18 as of 2026-08-17, and pg_surgery is documented from PostgreSQL 14 onward. The page does not state which version introduced it.

Berg wrote up both extensions on his own blog in November 2021 and arrived at the same place from the other direction. His explanation is that both work outside PostgreSQL’s normal MVCC machinery, which makes it easy to create corrupt data with them. His instruction to the reader is blunter than the documentation’s: “Don’t try this at work.”

Then there is the sentence that closes the rung entirely: “Most importantly, none of the above methods will work if the data you just deleted has already been purged by VACUUM or autovacuum.” Autovacuum is on by default. If it has already run on that table, no amount of persistence reopens this door, and continuing to try only adds risk to a database that has already lost data.

For a non-developer reading this page, the honest advice about rung 5 is to stop. Rungs 1 through 4 are things an owner can carry out with a dashboard, a support ticket and some patience. This one is the point where most owners hand the problem to someone who does this for a living, and there is no shame in that being the answer.

Which of these actually happened to you

Four situations get searched with almost the same words, and only one of them is the subject of this page. Read the left column, then follow the row that matches.

The line between the first two rows is worth being precise about, because it changes which rung you start on. If the database still exists and a table is missing rows, rung 1 is live and rung 5 is at least possible. If the whole database is gone, not just some rows, rungs 1 and 5 are gone with it, and everything depends on rung 2, rung 3 or a copy you already had. The third row is not a recovery job in the first place: rows that still exist but hold the wrong value were never removed, so there is nothing on this ladder to bring back.

Common questions about recovering deleted rows in Postgres

Does ROLLBACK work after the transaction has already committed?

No. ROLLBACK only undoes work inside a transaction that is still open. Once COMMIT has returned, there is nothing left to roll back, and the delete is as permanent as any other committed change. Recovery from that point means finding a surviving copy of the rows.

This is also the argument for the habit that prevents the next one. Running a delete inside an explicit transaction, checking the affected count, and only then committing costs a few seconds and converts a disaster into a rollback.

Will running VACUUM destroy my chance of recovering the rows?

For the last rung, yes. Vacuuming clears out dead row versions and frees their space for reuse, and once that has happened the extensions that read dead rows have nothing left to find. Autovacuum does the same job on a schedule, enabled by default.

It changes nothing about rungs 1 through 4. A backup, a point-in-time restore, an export or a soft-delete column is unaffected by vacuuming, which is another reason to work down the ladder in order.

Can I recover deleted rows on the Supabase free plan?

Only from a copy you already made. Supabase’s backup documentation, checked 2026-08-17, limits automatic daily backups to the Pro, Team and Enterprise plans, and its advice to free projects is to run regular exports of their own and store them off-site. There is no free-plan backup waiting in the Dashboard to restore from.

That is the exact situation behind most of the community threads on this question, and it is worth being blunt about: if no export exists and the app kept no history of its own, rung 5 is the only rung left, and it is the one you should not attempt yourself.

Is pg_dirtyread safe to use on a production database?

Treat it as unsafe. Its README documents that creating the extension requires a superuser connection, its maintainer writes that it operates outside PostgreSQL’s normal MVCC machinery and that it is easy to create corrupt data with it, and the related pg_surgery documentation calls its own functions unsafe by design.

There is also a plain practical limit: it does not yet work when the deleted rows contain toasted values, and it reads nothing at all once vacuum has been through. A method that can corrupt the database and may return nothing belongs after every read-only option has been tried.

Can point-in-time recovery restore just one table?

No. The PostgreSQL documentation states that the method can restore a whole database cluster and not a subset of one. You produce a full copy of the database as it was at a chosen moment, then move the rows you need out of that copy and into production yourself.

How do I find out how many rows the delete actually removed?

If the statement is already gone, count instead of guess: restore or export a copy from before the delete and compare row counts table by table, starting with the tables that hold a foreign key pointing at the one you deleted from.

Before the fact, the DELETE statement has a built-in answer. PostgreSQL’s DELETE documentation describes an optional RETURNING clause that makes the statement compute and return values based on each row actually deleted, so you see the rows themselves rather than a count you have to trust.

Does TRUNCATE behave the same way as DELETE for recovery?

Not quite, and the difference cuts both ways. The PostgreSQL documentation describes TRUNCATE as transaction-safe with respect to the data, so a truncate inside an open transaction rolls back like anything else. After a commit it is worse than a plain delete for the last rung.

PostgreSQL’s TRUNCATE documentation says it reclaims disk space immediately rather than waiting for a later VACUUM, so the dead row versions that rung 5 depends on are not left sitting there to be read. The same page notes that TRUNCATE is not MVCC-safe, so concurrent transactions using a snapshot taken before the truncation see an empty table.

What if the rows were deleted weeks ago and nobody noticed?

Assume rung 5 is closed and check the retention windows first. Daily backups are kept for a fixed number of days that depends on your plan, and point-in-time windows are shorter than most people expect, so a weeks-old deletion is frequently outside both.

That makes rung 4 the best rung. Old exports, emailed reports, accounting spreadsheets, another system that received the same data, and customers’ own copies of their records are all real sources, and unlike every other rung on the ladder they do not expire.

Do I need a developer for this?

For rungs 1 through 4, usually not. Checking for a soft-delete column, restoring a backup into a new project, and loading an old export are all things an owner can do from a browser and a support ticket, with care and a written-down plan.

Rung 5 is different. It needs superuser access, it is documented as unsafe, and getting it wrong corrupts data that is currently only missing. That is the point where the right move is to bring in someone who has done it before.