Restore a Single Table from a Dump with pg_restore
Restoring one table with pg_restore -t brings back the rows only, not its indexes, constraints or defaults. A list file made with -l and used with -L restores them all.
A table was dropped by mistake and you want it back from a custom-format dump without restoring the whole database.
docker run -d --name pgrest -e POSTGRES_PASSWORD=pw postgres:17-alpine > /dev/null
until [ "$(docker logs pgrest 2>&1 | grep -c 'ready to accept connections')" -ge 2 ]; do sleep 1; done
docker exec -i pgrest psql -U postgres -q <<'SQL'
create table customers (id serial primary key, name text);
create table orders (id serial primary key, customer_id int references customers(id), total int);
create index orders_total on orders (total);
insert into customers (name) values ('ann'), ('bob');
insert into orders (customer_id, total) values (1, 10), (2, 20);
SQL
check() {
docker exec pgrest psql -U postgres -tA -c "select (select count(*) from orders) as rows, (select count(*) from pg_indexes where tablename = 'orders') as indexes, (select count(*) from pg_constraint where conrelid = 'orders'::regclass) as constraints, (select column_default is not null from information_schema.columns where table_name = 'orders' and column_name = 'id') as id_default"
}
docker exec pgrest pg_dump -U postgres -Fc -f /tmp/db.dump postgres
docker exec pgrest psql -U postgres -qc 'drop table orders'
docker exec pgrest pg_restore -U postgres -d postgres -t orders /tmp/db.dump
echo "after -t orders: $(check)"
docker exec pgrest psql -U postgres -qc 'drop table orders'
docker exec pgrest sh -c 'pg_restore -l /tmp/db.dump | grep orders > /tmp/orders.list; pg_restore -U postgres -d postgres -L /tmp/orders.list /tmp/db.dump'
echo "after -L list file: $(check)"
docker rm -f pgrest > /dev/nullafter -t orders: 2|0|0|f
after -L list file: 2|2|2|tpg_restore -t orders recreates the table and its data, and the first check shows what it leaves out: 2 rows, 0 indexes, 0 constraints and no default on the id column. Indexes, keys, defaults and sequences are separate entries in the dump, and -t does not select them. The list file route takes every entry that mentions the table, and -L restores exactly those, giving 2 rows, 2 indexes, 2 constraints and the default. See pg_restore.
Edit the list file by hand when the filter catches too much or too little, and check the foreign key targets exist first, since orders points at customers. pg_restore -l shows the entries, and a semicolon comments one out. The sequence value is set from the dump, which may be older than the live data. This run used PostgreSQL 17.