How to Restore One Table From a Full Cluster Backup

Somebody truncated one table at 14:40. Orders are still arriving, the rest of the schema is untouched, and the only thing missing is the contents of that one table as it stood this morning.

Restoring the whole backup over production would take the business offline and discard every write since the dump was taken, to repair damage that lives in a single table. The right shape is a side restore. Bring the backup up somewhere harmless, take the one table out of it, and put the rows back into the running database while it keeps serving.

The format your dump was written in decides everything

Look at what the nightly job actually writes before you do anything else, because that one choice decides whether the next twenty minutes are easy or awful.

pg_restore reads archives created by pg_dump "in one of the non-plain-text formats". A plain SQL dump, which is what pg_dump produces by default, cannot be fed to it at all. The archive formats exist for exactly this moment, and the PostgreSQL manual is direct about why. The custom and directory formats "allow for selection and reordering of all archived items, support parallel restoration, and are compressed by default".

# nightly, and the -Fc is the part that matters
pg_dump -Fc -d app -f /backups/app-$(date +%F).dump

# read the table of contents without restoring anything
pg_restore -l /backups/app-2026-08-24.dump | grep 'TABLE DATA'

If your backups are physical rather than logical, a copy of the data directory plus write-ahead log, there is no selective restore at any level. You restore the whole cluster into a scratch data directory on a spare host, start it on a port nothing else uses, and dump the single table out of that. It is slower and it is the only route.

Pull the table into a scratch database

createdb app_scratch
pg_restore -d app_scratch -n public -t order_items --no-owner \
  /backups/app-2026-08-24.dump

Three things about -t in pg_restore surprise people who know the pg_dump flag of the same name.

• There is no pattern matching. The manual states that "there is not currently any provision for wild-card matching in pg_restore, nor can you include a schema name within its -t". Name the table exactly and put the schema in -n.
• It does not bring the subsidiary objects along. Where pg_dump would also dump the indexes of a selected table, "pg_restore's -t flag does not include such subsidiary objects".
• It does not chase dependencies. "When -t is specified, pg_restore makes no attempt to restore any other database objects that the selected table(s) might depend upon."

None of that matters in a scratch database where you only want the rows. All of it matters if you were thinking of restoring straight into production with -t, which is the thing not to do.

Two more flags earn their place. -j runs the slow parts in parallel, works only with the custom and directory formats, and cannot be combined with --single-transaction. And pg_restore -f - writes the SQL to standard output instead of executing it, so you can read exactly what a restore would do first.

Check that the damage really is one table

Worth five minutes before you copy anything back. TRUNCATE "cannot be used on a table that has foreign-key references from other tables, unless all such tables are also truncated in the same command". So if a plain truncate succeeded, either nothing references that table, or somebody reached for CASCADE, which "can be used to automatically include all dependent tables" and emptied those too. Compare the child tables against the scratch copy as well as the one everybody is talking about.

Move the rows across

psql -d app_scratch -c "\copy (SELECT * FROM public.order_items) TO STDOUT" \
  | psql -d app -c "\copy public.order_items FROM STDIN"

That works when the table is empty and you are refilling it. When the loss was partial, a bad update or a delete that took more rows than intended, do not copy the recovered table over live data. Load it into a staging table and insert only what is missing, so rows that were never damaged are never touched.

CREATE TABLE order_items_recovered (LIKE public.order_items);

-- load the recovered rows into order_items_recovered, then

INSERT INTO public.order_items
SELECT r.* FROM order_items_recovered r
LEFT JOIN public.order_items o ON o.id = r.id
WHERE o.id IS NULL;

Foreign keys

Insert parents before children and most of the problem disappears, because a foreign key is checked on the child side.

If a load genuinely will not go in without turning the checks off, know the cost of each option. ALTER TABLE ... DISABLE TRIGGER ALL covers "internally generated constraint triggers, such as those that are used to implement foreign key constraints", and doing that "requires superuser privileges". The session-wide equivalent, session_replication_role = replica, can only be changed by "superusers and users with the appropriate SET privilege", and PostgreSQL spells out the consequence, that it "also disables all foreign key checks, which can leave data in an inconsistent state if improperly used". The --disable-triggers option of pg_restore is the same mechanism during a data-only restore, and "the commands emitted for --disable-triggers must be done as superuser".

Prefer leaving the checks on. Data that was consistent when the backup was taken will validate on the way back in, and a load that fails a constraint has told you something true about the copy you are holding. If you did disable them, prove the result before closing the incident.

SELECT count(*) FROM order_items oi
LEFT JOIN orders o ON o.id = oi.order_id
WHERE oi.order_id IS NOT NULL AND o.id IS NULL;

Sequences, the step that bites an hour later

The rows are back, the application looks healthy, and the next insert fails on a duplicate key. Setting the sequence belongs to the restore, not to the follow-up ticket.

SELECT setval(
  pg_get_serial_sequence('public.order_items', 'id'),
  (SELECT max(id) FROM public.order_items)
);

pg_get_serial_sequence "returns the name of the sequence associated with a column", already formatted for passing to the sequence functions. The two-argument setval "sets the sequence's last_value field to the specified value and sets its is_called field to true, meaning that the next nextval will advance the sequence before returning a value", so the next row gets the maximum plus one. The call needs UPDATE privilege on the sequence.

Whether you need it at all depends on how the table was emptied. A plain truncate leaves sequences alone, because CONTINUE IDENTITY "is the default", and the sequence is simply ahead of the restored rows, which harms nothing. TRUNCATE ... RESTART IDENTITY resets them, and then the call above is mandatory.

Afterwards

Drop the scratch database and write down how long the whole thing took from the moment somebody noticed. That number is the only honest input to a recovery time objective for this class of incident, and it is better to have it before the next one.

The other half of this is the job that proves the backup is restorable at all, which we covered in how to build a backup you have actually restored. If you would rather have both built and rehearsed on your own estate, that is what our disaster recovery and backup work does, and our infrastructure management engagements keep it running afterwards.