2

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):

  1. pg_restore -t table1 -t table2 --schema-only ...
  2. pg_restore -I index1 -I index2 ...
  3. 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.

  • How about restoring the entire schema in a 1st step (--schema-only), which will create all indexes (empty) and constraints, and the data for certain tables in a 2nd step? – Daniel Vérité Jan 30 '15 at 15:19
  • Ah, of course! This worked beautifully (with --data-only for the second step). If you make that an official answer I'll accept it. – JacobEvelyn Jan 30 '15 at 22:31
  • Actually, I spoke too soon. It looks like the -t flag prevents sequences from being restored, even when I explicitly call pg_restore -t table_id_seq .... So after the restore all of my auto-incrementing IDs reset to 1. Do you know of any workarounds? – JacobEvelyn Jan 30 '15 at 22:46
6

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

  1. create your dump, using the "custom" format
  2. run pg_restore -l /path/to/dump/file > table_of_contents.txt
  3. 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.)
  4. restore your database with pg_restore ... -L table_of_contents.txt /path/to/dump/file

Profit!

  • While kind of clunky, I suspect this works (I can no longer test to confirm that sequences work, but oh well) and I suppose one could script this to make it automatic. – JacobEvelyn Nov 7 '16 at 21:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.