pg_restore Strategies

pg_restore --table

Restricts a restore from a custom- or directory-format dump to one specific table — both its definition and its data. Every other object captured in the dump is skipped entirely, even if it lives in the same schema or the same dump file.

--use-list

Feeds pg_restore an edited copy of its own --list output, restoring only the entries that remain (commented-out lines, prefixed with a semicolon, are skipped) and in the exact order they appear in the file. This is the mechanism for controlling restore order precisely when the default dependency-based ordering is not what a specific recovery needs.

💥 Simulate the Disaster

Drop the products table in beer_db, just like the developer in the scenario did.

psql -U postgres -d beer_db -c "DROP TABLE inventory.products CASCADE;"

student@lab:~$ psql -U postgres -d beer_db -c "DROP TABLE inventory.products CASCADE;" SET NOTICE: drop cascades to constraint product_reviews_product_id_fkey on table inventory.product_reviews DROP TABLE

📇 List the Dump's Contents

Before restoring anything, list the dump's table of contents to confirm products is actually in there.

pg_restore --list /tmp/inventory.dump

student@lab:~$ pg_restore --list /tmp/inventory.dump ; ; Archive created at 2026-06-26 16:03:56 UTC ; dbname: beer_db ; TOC Entries: 20 ; Compression: gzip ; Dump Version: 1.16-0 ; Format: CUSTOM ; Integer: 4 bytes ; Offset: 8 bytes ; Dumped from database version: 18.4 ; Dumped by pg_dump version: 18.4 ; ; ; Selected TOC Entries: ; 12; 2615 19011 SCHEMA - inventory postgres 237; 1259 19024 TABLE inventory product_reviews postgres 236; 1259 19023 SEQUENCE inventory product_reviews_id_seq postgres 3025; 0 0 SEQUENCE OWNED BY inventory product_reviews_id_seq postgres 235; 1259 19013 TABLE inventory products postgres 234; 1259 19012 SEQUENCE inventory products_id_seq postgres 3026; 0 0 SEQUENCE OWNED BY inventory products_id_seq postgres 2861; 2604 19027 DEFAULT inventory product_reviews id postgres 2859; 2604 19016 DEFAULT inventory products id postgres 3018; 0 19024 TABLE DATA inventory product_reviews postgres 3016; 0 19013 TABLE DATA inventory products postgres 3027; 0 0 SEQUENCE SET inventory product_reviews_id_seq postgres 3028; 0 0 SEQUENCE SET inventory products_id_seq postgres 2866; 2606 19033 CONSTRAINT inventory product_reviews product_reviews_pkey postgres 2864; 2606 19022 CONSTRAINT inventory products products_pkey postgres 2867; 2606 19034 FK CONSTRAINT inventory product_reviews product_reviews_product_id_fkey postgres

🎯 Restore Only products, Into recovery_db

Restore just the products table into the pre-created recovery_db, leaving beer_db untouched for now.

pg_restore -U postgres -d recovery_db --schema=inventory --table=products /tmp/inventory.dump

student@lab:~$ pg_restore -U postgres -d recovery_db --schema=inventory --table=products /tmp/inventory.dump

🔢 Verify the Row Count Matches

Confirm recovery_db.inventory.products has exactly the row count the original had — 20,000 rows.

psql -U postgres -d recovery_db -c "SELECT count(*) FROM inventory.products;"

student@lab:~$ psql -U postgres -d recovery_db -c "SELECT count(*) FROM inventory.products;" SET count ------- 20000 (1 row)

✂️ Build a Filtered use-list

Write the dump's table of contents to a file, remove the product_reviews-related entries, then restore using only what remains.

pg_restore --list /tmp/inventory.dump > /tmp/inventory.list

sed -i '/product_reviews/d' /tmp/inventory.list

pg_restore -U postgres -d recovery_db --use-list=/tmp/inventory.list /tmp/inventory.dump

student@lab:~$ pg_restore --list /tmp/inventory.dump > /tmp/inventory.list student@lab:~$ sed -i '/product_reviews/d' /tmp/inventory.list student@lab:~$ pg_restore -U postgres -d recovery_db --use-list=/tmp/inventory.list /tmp/inventory.dump pg_restore: error: could not execute query: ERROR: schema "inventory" already exists Command was: CREATE SCHEMA inventory; pg_restore: error: could not execute query: ERROR: relation "products" already exists Command was: CREATE TABLE inventory.products ( id integer NOT NULL, sku text, name text, unit_price numeric(10,2), created_at timestamp with time zone DEFAULT now() ); pg_restore: error: could not execute query: ERROR: could not create unique index "products_pkey" DETAIL: Key (id)=(5001) is duplicated. Command was: ALTER TABLE ONLY inventory.products ADD CONSTRAINT products_pkey PRIMARY KEY (id); pg_restore: warning: errors ignored on restore: 3

🐌 Full Restore, Serial

Restore the entire dump into a fresh database, single-threaded, timed as a baseline.

createdb -U postgres recovery_serial

time pg_restore -U postgres -d recovery_serial -j 1 /tmp/inventory.dump

student@lab:~$ createdb -U postgres recovery_serial student@lab:~$ time pg_restore -U postgres -d recovery_serial -j 1 /tmp/inventory.dump real 0m 6.82s user 0m 0.12s sys 0m 0.01s

⚡ Full Restore, Parallel

Restore the same dump into another fresh database, this time with 4 parallel jobs.

createdb -U postgres recovery_parallel

time pg_restore -U postgres -d recovery_parallel -j 4 /tmp/inventory.dump

student@lab:~$ createdb -U postgres recovery_parallel student@lab:~$ time pg_restore -U postgres -d recovery_parallel -j 4 /tmp/inventory.dump real 0m 4.20s user 0m 0.13s sys 0m 0.04s