Fast Access to Data from a Massive PostgreSQL Backup: How to Extract Just the Tables You Need Without Waiting 2-4 Hours for a Full Restore

Short Use Case Scenario

We frequently encounter situations where we need to back up only specific tables, but lack the disk space for a full backup. For example, you may have a backup file of approximately 40 GB. Restoring it in its entirety to a test database on standard hardware can take two to four hours or longer if the storage is slow or there are many indexes. Yet you might only need to inspect a couple of tables, such as TableName1 and TableName2, to verify or extract data before a migration. How can you reduce those hours to minutes?

This article presents two approaches:

Method A (Fast and Simple):

Use pg_restore -t schema.table when you know the exact table names and the schema is straightforward.

Method B (Controlled):

Generate a list of objects via pg_restore -l, manually filter that list, and restore using -L. This is useful when you need to account for dependencies, include or exclude comments and triggers, or when table names are not obvious.

Use Method A when:

• The tables are known by their exact names (including schema).

• I need to quickly restore both schema and data (both are included by default).

• There is no need to control the restoration order.

Recently used Method B when:

• Exact names or letter casing were uncertain.

• A set of related objects needed to be restored (table, table data, sequence, comments, and foreign key constraints).

• A precise restoration sequence was required for dependency handling.

• I wanted to create a reusable “ration” list for colleagues-saving the filtered object list for repeated use.

Brief Reference: Contents of a Custom Backup (.backup)

A custom-format backup (pg_dump -Fc) is an archive with a table of contents (TOC). Each object in the TOC includes:

• A sequential TOC entry ID

• The object type (TABLE, TABLE DATA, SEQUENCE SET, COMMENT, ACL, etc.)

• The schema and object name

• Internal OIDs

pg_restore reads the TOC and restores objects in order, respecting dependencies. By exporting that list, editing it, and supplying it again with -L, we can restore only the required portions.

Method A – Fast: by table names (-t)

Applicable when you simply need to extract those two tables.

Option 1: restore directly into a temporary database.

pg_restore -U postgres -d scratchdb \
  -t "TableName1" \
  -t "TableName2" \
  "C:/Backups/mydb-20250714.backup"        

Option 2: Generate an SQL script and review or edit it

pg_restore -U postgres -f partial_TableName1.sql \
  -t "TableName1" \
  -t "TableName2" \
  "C:/Backups/mydb-20250714.backup"
#then
psql -U postgres -d scratchdb -f partial_TableName1.sql        

Advantages: quick and requires minimal manual effort.

Disadvantages: if the table depends on other objects (for example, a foreign key to a table you are not restoring), you may encounter errors when loading the data or need to disable constraint checks (see tips below).

Method B – Controlled: Using a List (-l + Filtering + -L)

Use this method when you need full control over what will be restored.

Step 1. Export the complete list of objects from the backup

On Windows/Linux:

pg_restore -l "backup_directory/mydb.backup" > full.list        

The file full.list is plain text. The first section contains comments, thereafter you’ll see lines like:

1234; 1259 56789 TABLE public TableName1
1235; 1259 56790 TABLE DATA public TableName1        

Step 2. Filter the required lines

On Windows (example: searching for both names at once):

type full.list | findstr /i "TableName2 TableName1" > filtered.list        

On Linux:

grep -Ei 'TableName1(Property)?' full.list > filtered.list        

You can also open filtered.list in any editor and remove all entries you don’t need. Leave only the lines for the objects you want to restore. If you need comments or constraint definitions, include the COMMENT or ALTER TABLE entries as well.

Example filtered.list:

; partial restore for TableName1* objects
1234; 1259 56789 TABLE public TableName1
1235; 1259 56790 TABLE DATA public TableName1
1240; 1259 56800 TABLE public TableName2
1241; 1259 56801 TABLE DATA public TableName2        

Lines beginning with “;” are treated as comments and ignored by pg_restore.

Step 3. Restore only the selected objects

On Windows/Linux:

pg_restore -U postgres -d scratchdb -L filtered.list /full/path/to/file/mydb-20250714.backup        

Step 4. Verify the result

Run:

SELECT COUNT(*) FROM "TableName1";
SELECT COUNT(*) FROM "TableName2";        

Compare the row counts against your expectations. If you see zero rows, you may have omitted the TABLE DATA entries.

How much time can be saved?

At typical I/O speeds on mid-range VMs, a full 40 GB restore can take two to four hours (indexes, foreign keys, transactions, validation). A partial restore of just two tables takes minutes: limited I/O, minimal DDL, and no full catalog rebuild. In urgent situations, that difference can mean resolving the issue immediately rather than escalating it the next day.