How can I do this when the table has a constraint and already exists in both databases? I need to rewrite the data in the table on my server with the data in the table on my development computer.

I have tried various forms of pg_dump, using both plain-text and binary formats. I have also tried using the --clean and --data-only options, but what always gets me when I try to restore is the constraint on the table.

pg_restore -O -d database_name dump.pg

Results in this error:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2409; 0 56001 TABLE DATA attendees
pg_restore: [archiver (db)] COPY failed for table "attendees": ERROR:  duplicate key value violates unique constraint "attendees_pkey"
DETAIL:  Key (id)=(237) already exists.
CONTEXT:  COPY attendees, line 1
WARNING: errors ignored on restore: 1

I'm trying to do this using just pg_dump/pg_restore with no editing of the server db between running the two commands. In other words, I don't want to have to go in and manually drop table attendees cascade before running the restore.

Is this possible? If so, how?


No, you probably can't do that automatically. If you are trying to dump and restore only fragments of the dependency chains, pg_dump and pg_restore have no way of knowing what your intentions are. You are most likely going to need to spell them out for it, by supplying your own supplementary pre/post commands.

It is possible you could make a custom file to supply to pg_restore's -L command which correctly drops and recreates the constraints you need.

| improve this answer | |

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.