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?
1 Answer 1
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.