Restore a Single Table from a Full Database Dump

Extract one table from a pg_dump archive with pg_restore, land it in staging, verify counts and checksums, then move only the needed rows.

~40 mindatabasepostgresoperations

Task

Recover one accidentally damaged table from an existing full backup without touching anything else in the database.

When to use this

A bad migration zeroed a column on the invoices table at 14:03. The fix must restore that one table without rolling back everything else that changed since.

Prerequisites

A recent pg_dump custom-format archive covering the needed point in time
Staging or scratch space to land the restore
The list of rows or the row range that actually needs recovery

Steps

1. Confirm the table exists in the archive and is recoverable.

pg_restore --list backup.dump | grep "TABLE DATA" | grep invoices

Expected output: The archive lists the invoices table data entry with its dump id, confirming it is present and extractable.

2. Restore only that table into a scratch database.

createdb scratch_restore && pg_restore -d scratch_restore --table=invoices backup.dump

Expected output: Only the invoices table and its dependencies are created and loaded; every other object in the archive is skipped.

3. Verify the restored copy before touching anything.

psql scratch_restore -c "SELECT count(*), md5(string_agg(amount, ':'::text)) FROM invoices;"

Expected output: A row count and checksum comparable with expectations proves the extracted table is complete and uncorrupted.

4. Compare against the damaged production data.

SELECT count(*) AS total,
       count(*) FILTER (WHERE amount = 0) AS zeroed
FROM invoices;

Expected output: The comparison quantifies the blast radius: how many rows the bad migration actually touched, and therefore what must move back.

5. Move only the damaged rows back.

BEGIN;

UPDATE invoices prod
SET amount = restored.amount,
    currency = restored.currency
FROM scratch_restore.public.invoices restored
WHERE prod.id = restored.id
  AND prod.amount = 0;

COMMIT;

Expected output: Only the zeroed rows regain their values in one transaction; untouched rows keep their newer production state.

Troubleshooting

Problem

Table is missing from the archive

Solution

The dump was filtered when taken; find an earlier archive that includes it, or accept a point-in-time restore of the whole cluster into scratch and extract from there.

Problem

pg_restore fails on dependent objects

Solution

Add the dependencies to the selection with --table for referenced tables, or restore schema first with --schema-only and data afterwards.

Problem

Foreign keys reject the repair

Solution

Fix in dependency order or defer the constraints inside the repair transaction with SET CONSTRAINTS ALL DEFERRED.

Limitations

A logical dump restores to its dump time only; later changes to other tables are preserved but later changes to the same table are lost.
The single-table path assumes row-level surgery is possible; wholesale table replacement loses any writes made after the dump.

Surgery, not resurrection

Restoring the whole cluster to fix one table costs you every write made since the backup. The surgical path is cheaper and safer: pg_restore can extract a single table from a custom-format archive into a scratch database, where it can be verified, compared against the damage, and then only the affected rows moved back. Everything else in production continues as if nothing happened, because for them, nothing did.

# What is in the archive?
pg_restore --list backup.dump | grep "TABLE DATA" | grep invoices

# Extract only that table
createdb scratch_restore
pg_restore -d scratch_restore --table=invoices backup.dump

Verify in scratch, act in production

The scratch database exists so that verification happens somewhere harmless. Count rows, checksum the critical columns, compare against the damaged production values, and only when the numbers agree does a single bounded UPDATE move the recovered values back. The production transaction touches exclusively the rows the incident actually harmed.

Read the archive before you need it

pg_restore –list against your newest backup belongs in monitoring, not in an incident. Knowing nightly that the archive contains the tables you would need, and how old it is, converts restore-time surprises into routine checklist items. An archive that silently stopped including a table is discovered on a Tuesday, not mid-outage.