Constraints missing after pg_restore

Question

After dumping a table and importing it to another postgres db constraints are missing.

I'm using this to dump:

    pg_dump --host=local --username=user -W --encoding=UTF-8 -j 10 --file=dump_test --format=d -s --dbname=mydb -t addendum

This to import:

    pg_restore -d myOtherdb --host=local -n public --username=user -W --exit-on-error --format=d -j 10 -t addendum dump_test/

What I can see in the resulting toc.dat is something like this:

    ADD CONSTRAINT pk_addendum PRIMARY KEY (addendum_id);
    >   ALTER TABLE ONLY public.addendum DROP CONSTRAINT pk_addendum;

That looks like its creating and destroying the PK, but I'm not sure if my interpretation is correct as the file is binary.

edit: I'm using PostgreSQL 9.3

Answer 1

From the documentation:

Note: 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.

You thus have some admittedly unattractive choices:

1. You can rebuild the constraints manually, especially if you still have the DDL which created them.
2. You can do a database-wide pg_dump to text, obtain the constraint DDL from there, see step 1.
3. You can do a database-wide pg_dump, and restore it fully.

Answer 2

I had the situation where the table already exists but using pg_restore deleted the constraints of the table. There is an accepted answer already but I will try to provide an answer for those cases where the table to be restored is already available. In such cases, the constraints are deleted, only if you are trying to drop and recreate the table (-c or -C). Whereas if you only want the data from the dump you can perform delete all records on the table (DELETE FROM tableName) and then use pg_restore with -a flag. You can thus exclude -c or -C flag from you pg_restore command.

Answer 3

A little late to the party but here's something that may help.

If you're restoring a single table from a large dump file and having trouble getting the indexes with pg_restore (-t doesn't do indexes and constraints)

 pg_restore db_dump_file.dump | awk '/table_name/{nr[NR]; nr[NR+1]}; NR in nr' > table_name_indexes_tmp.psql

You also need the subsequent line after a match for indexes and constraints. The awk command above gets line + 1 after every match.

This output file should contain your indexes (assuming the dump file actually contains them, plus data). Then you can apply them back to the table you restored as individual commands.

Not a perfect solution but better than trying to re-create them manually.