Hi,
This is sort of a continuation of = problems I was working on last week
with selective restorations of an archive file at the schema or table level. ( V9.3)
Given that I dumped the entire database ( pg_dump –Fc my_db –f archive_file )
When I pg_restore an entire schema ( -n ) everything is wonderful.
If I try to attempt two tables in one of the schemas I encounter problems.
I get a success of sort with these option variations:
pg_restore -c -t tbl1 –t tbl2 –U <username> -d my_db archive_file
In this case the tables are recreated with data but all the original constraints for these tables are missing
As are triggers that are associated with the tables. I guess I can understand this.
This variation seems encouranging but ultimately fails:
pg_restore –a –v -c -t translator_sys –t translator_sys_mbr –U <username> -d my_db archive_file
pg_restore: connecting to database for restore
pg_restore: dropping TABLE DATA translator_sys
pg_restore: dropping TABLE DATA translator_sys_mbr
pg_restore: dropping TABLE DATA translator_sys
pg_restore: processing data for table "translator_sys"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4247; 0 332255 TABLE DATA translator_sys redcom
pg_restore: [archiver (db)] COPY failed for table "translator_sys": ERROR: duplicate key value violates unique constraint "translator_sys_pkey"
DETAIL: Key (translator_id)=(1) already exists.
CONTEXT: COPY translator_sys, line 1
pg_restore: processing data for table "translator_sys_mbr"
pg_restore: [archiver (db)] Error from TOC entry 4248; 0 332262 TABLE DATA translator_sys_mbr redcom
pg_restore: [archiver (db)] COPY failed for table "translator_sys_mbr": ERROR: duplicate key value violates unique constraint "translator_sys_mbr_pkey"
DETAIL: Key (translator_id, tid_seq)=(1, 1) already exists.
CONTEXT: COPY translator_sys_mbr, line 1
pg_restore: processing data for table "translator_sys"
It seems like it is geared to TRUNCATE or DELETE the specified table data in this case based on the verbose output.
However I see no SQL commands in the std_out to support this verbose message so it ultimately fails because of the
Duplication of PK associated with the table..
Is this a bug or a mis-understanding on my part?
Regards
Dave Day