jeudi 29 janvier 2015

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



  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.





Aucun commentaire:

Enregistrer un commentaire