How to restore one Postgres table to a point in time

The short answer

You can’t point-in-time-recover a single table in PostgreSQL. The docs are direct: continuous archiving “can only support restoration of an entire database cluster, not a subset.”¹ What you can do is recover a second copy of the cluster from your base backup and WAL archive to a moment just before the bad statement, pause it there, dump the one table you need, and merge those rows back into production. Production stays online the whole time.

We ran the full sequence on PostgreSQL 18.6. Every command and output below is from that run.

What you need before anything goes wrong

This only works if you already have:

• A base backup taken before the incident (we used pg_basebackup).
• A WAL archive covering the time from that backup to the incident (archive_mode = on with an archive_command).
• Somewhere to run a second server, with disk for a full copy of the cluster.

Managed services do this for you under other names: Neon’s history window and Supabase’s point-in-time recovery are covered in restore on Neon and restore on Supabase.

Step 1: find the bad moment

Our simulated agent ran this against a table of 1,200 invoices:

Code What the screen or file shows, copied as is.

prod=# delete from invoices where id > 100;
DELETE 1100

Just before that, we noted the time:

Code What the screen or file shows, copied as is.

2026-10-26 17:34:33.096546+05:30|before delete

In a real incident you get this from the agent’s transcript, your application logs, or log_statement output. A recovery target can be a timestamp, a named restore point or a transaction ID.¹ A timestamp is usually what you have.

Step 2: recover a side copy and pause

Copy the base backup to a new data directory (never the production one), and add the recovery settings. The one setting you must give is restore_command, which tells the server how to fetch archived WAL; recovery starts when the data directory holds a recovery.signal file.¹

Code What the screen or file shows, copied as is.

restore_command = 'cp /path/to/arch/%f %p'
recovery_target_time = '2026-10-26 17:34:33+05:30'
recovery_target_action = 'pause'
archive_mode = off

Code Commands typed in a terminal. The text around it says what happens.

touch /path/to/side/recovery.signal
pg_ctl -D /path/to/side -o "-p 55498" start

Three choices here matter:

The side server’s log showed exactly where it stopped:

Code What the screen or file shows, copied as is.

LOG:  starting point-in-time recovery to 2026-10-26 17:34:33+05:30
LOG:  recovery stopping before commit of transaction 801, time 2026-10-26 17:34:35.136696+05:30
LOG:  pausing at the end of recovery

Transaction 801 was the delete. Then we queried the paused copy:

Code What the screen or file shows, copied as is.

$ psql -p 55498 -d prod -Atc "select pg_is_in_recovery(), pg_get_wal_replay_pause_state(), count(*) from invoices"
t|paused|1200

All 1,200 rows, read-only, in a server that’s paused before the damage. If the target turns out to be wrong, the docs say to shut down, set a later target and restart.² Don’t promote the side copy unless you mean to make it a new primary; pausing is enough to read from it.

Step 3: copy the table back

Dump just that table’s data from the paused copy. --data-only skips the schema, and -t selects one table without the objects it depends on.⁴

Code Commands typed in a terminal. The text around it says what happens.

pg_dump -p 55498 -d prod --data-only -t invoices -f invoices.sql

On production, load it into a separate schema rather than straight into the live table, so you can inspect it first:

Code A database command in SQL. The text around it says what it does.

create schema recovery;
create table recovery.invoices (like public.invoices including all);

The dump names public.invoices, so we pointed it at the recovery table before loading (sed 's/public\.invoices/recovery.invoices/' invoices.sql > inv2.sql, then psql -f inv2.sql). Then the merge, in one transaction:

Code A database command in SQL. The text around it says what it does.

begin;
insert into public.invoices select * from recovery.invoices on conflict (id) do nothing;
select count(*) from public.invoices;
commit;

Code What the screen or file shows, copied as is.

INSERT 0 1100
 count
-------
  1200

The 1,100 deleted rows are back. The 100 that were never deleted were skipped by on conflict do nothing, and any rows written since the incident would have been left alone too. Drop the recovery schema when you’re done, and stop and delete the side cluster.

If the agent updated rows rather than deleting them, use on conflict (id) do update only for the columns it damaged, and only after comparing with what’s there now. Rows may have had legitimate changes since.

A faster option: a delayed standby

A delayed standby is a replica that deliberately lags. With recovery_min_apply_delay = '1h', the standby replays each commit only once its clock is an hour past the primary’s commit time. The docs describe it as offering “opportunities to correct data loss errors.”³ When an agent deletes something, you have up to an hour to pause the standby’s replay and copy the rows out, without restoring any backup.

Costs to know about:³

• The delay applies only to commit records, and WAL piles up on the standby while it waits, so it needs more disk.
• If commits are configured to wait for standbys to apply them (remote_apply), every COMMIT waits for the delayed apply. Never make the delayed standby one your commits wait on.
• It only helps if someone notices within the delay.

When not to use this

• The whole database is suspect. If the agent ran destructive DDL across many tables, recovering the cluster to the target and promoting it may be simpler than table-by-table copying. Recovery then starts a new timeline.¹
• You know exactly which rows changed. If you recorded each change at commit time, you can reverse it without any backup. That’s the approach in undo for agent sessions, built on change data capture.
• The damage left the database. Emails, webhooks and payments aren’t in any backup.

The checklist

• Base backups and a WAL archive exist, and you’ve restored from them once on purpose.
• You can start a second server with disk for a full copy.
• Recovery target taken from logs, set clearly before the bad commit.
• recovery_target_action = 'pause', hot_standby on, archive_mode off on the side copy.
• Data checked on the paused copy before anything touches production.
• Rows loaded into a separate schema, merged with on conflict in one transaction.
• Side copy and recovery schema removed afterwards.

To see whether any agent on your machine can reach a production database in the first place, paste the agent’s MCP config into the check page or run npx deedglass scan (how the grade works). To try the checks in your browser without installing anything, open the playground.

Written with AI assistance and checked against our claims register.