1

I started using the -t argument of pg_restore to only restore some tables, which has worked well for read-only workflows.

The problem is that the -t seems to not restore the FK constraints nor sequences applied to the table/column.

Regarding the former (sequence), it seems that the tables restored with -t which had an id column with a modifier of not null default nextval('report_id_seq'::regclass) was restored with just not null, so the sequence isn't applied, which is an obvious.

Is there a way to use pg_restore with explicit tables using -t that also restore foreign-key constraints and sequences?

1

What you probably want is to restore all things that are not data. So try -s, from man pg_restore

-s
--schema-only
    Restore only the schema (data definitions), not data, to the extent
    that schema entries are present in the archive.

    This option is the inverse of --data-only. It is similar to, but
    for historical reasons not identical to, specifying
    --section=pre-data --section=post-data.

    (Do not confuse this with the --schema option, which uses the word
    “schema” in a different meaning.)

Note that schema is everything. Foreign key constraints mean you may not be able to load just the data for a table. From pg_dump

when -t is specified, pg_dump makes no attempt to dump any other database objects that the selected table(s) might depend upon. Therefore, there is no guarantee that the results of a specific-table dump can be successfully restored by themselves into a clean database.

So if you've got a clean database, and you try to restore with -t you're potentially attempting to insert data the conflicts keys and constraints specified in the schema -s.

  • If I first run the command with -s, and then run it with -t to get the data, I get a lot of errors violating the FK constraints. – leonsas Dec 19 '16 at 21:26
  • 1
    Sure you do -- to be expected. If you need the data too, you need the insertion order that pg_restore and pg_dump provides. How else could it possibly work... If table foo references bar. bar has to be loaded first. If you -t foo later, and bar isn't loaded -- what do want to happen? – Evan Carroll Dec 19 '16 at 23:08
  • Right, I hear that edge case. In what I'm trying to do, I'm trying to exclude some tables that don't have any other tables referencing to them as FK. – leonsas Dec 20 '16 at 4:10

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.