pg_restore certain tables only

I'm looking for a way to use pg_restore to restore a newly-created database from a dump file, but only certain tables from that file. (There are a lot of extra tables in the database that are slow to restore and that I don't care about.)

I first tried pg_restore with the -t/--table flag, but it didn't enable the extensions I need for those tables. Not a big deal; I can manually run psql -c "CREATE EXTENSION ..." before the pg_restore command. My bigger issue is that the -t command seems to skip other things related to the tables, like constraints and indexes.

Indexes are annoying but I could run pg_restore --list and use awk/grep/etc. to get a list of indexes and pass them in to pg_restore with the -I/--index flag, so the process would be (I think):

• pg_restore -t table1 -t table2 --schema-only ...

• pg_restore -I index1 -I index2 ...

• pg_restore -t table1 -t table2 --data-only ...

But (a) that still won't set up the constraints for me, and (b) it feels like I'm going down a rabbit hole with this roundabout solution for something I thought would be straightforward.

I know you can tell pg_dump to only dump certain tables, but I was hoping to avoid that due to the long time it takes to make dumps of my database.

if you're using the "custom" format , you can use the -l (ell) and -L options to do this.

• create your dump, using the "custom" format

• run pg_restore -l /path/to/dump/file > table_of_contents.txt

• edit table_of_contents.txt and just remove references to the tables you don't want anymore. (Make sure you get all of them. You can comment out a line with a semicolon.)

• restore your database with pg_restore ... -L table_of_contents.txt /path/to/dump/file

Profit!

StackExchange Database Administrators Q#90574, answer score: 14