2

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?

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
3
  • 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
    Commented Dec 19, 2016 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
    Commented Dec 19, 2016 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
    Commented Dec 20, 2016 at 4:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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