TOC errors

On Aug 2, 2013, at 12:29 PM, Perry Smith <pedzsan@gmail.com> wrote:

I am doing a restore:

pg_restore --no-owner -L /tmp/db.list --single-transaction --dbname=condor3_production $DBFILE

and I get this error:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2056; 0 35202 TABLE DATA adv_ptf_release_maps pedzan
pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key value violates unique constraint "adv_ptf_release_maps_apar_defect_version_map_id_ptf_id_rele_key"
DETAIL: Key (apar_defect_version_map_id, ptf_id, release_id)=(72022, 16678, 436) already exists.
CONTEXT: COPY adv_ptf_release_maps, line 651290: "651290 72022 16678 436 2009-06-07 14:08:55.627762 2009-06-07 14:08:55.627762"

I've used the custom format. The error tells me a line number but I can't look at it. Is there a way that I can convert the custom format to something I can view?

Also, I have turned off all the foreign key checking while I was doing the restore but I guess this is the unique checking that is causing the error. Can I turn that off (without doing a table modify)?

The other confusing part about this is "its not true" :-)... The tuple shows up only once in the original database that was dumped. So, its like it is getting a read error or something while doing the restore.

psql --version
psql (PostgreSQL) 9.0.4
contains support for command-line editing

Thank you for any help and suggestions.
Perry

Perry Smith escribi�:

On Aug 2, 2013, at 12:29 PM, Perry Smith <pedzsan@gmail.com> wrote:

I've used the custom format. The error tells me a line number but I
can't look at it. Is there a way that I can convert the custom
format to something I can view?

Sure, pg_restore can save its output to a file instead of to a database.

Also, I have turned off all the foreign key checking while I was
doing the restore but I guess this is the unique checking that is
causing the error. Can I turn that off (without doing a table
modify)?

No.

The other confusing part about this is "its not true" :-)... The tuple
shows up only once in the original database that was dumped. So, its
like it is getting a read error or something while doing the restore.

Probably one of the copies is not visible to the index, which would
explain why the other one was allowed in in the first place. To verify
this, do a select for that value, but first set enable_indexscan and
enable_bitmapscan to off.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Perry Smith <pedzsan@gmail.com> writes:

I am doing a restore:

pg_restore --no-owner -L /tmp/db.list --single-transaction --dbname=condor3_production $DBFILE

and I get this error:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2056; 0 35202 TABLE DATA adv_ptf_release_maps pedzan
pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key value violates unique constraint "adv_ptf_release_maps_apar_defect_version_map_id_ptf_id_rele_key"
DETAIL: Key (apar_defect_version_map_id, ptf_id, release_id)=(72022, 16678, 436) already exists.
CONTEXT: COPY adv_ptf_release_maps, line 651290: "651290 72022 16678 436 2009-06-07 14:08:55.627762 2009-06-07 14:08:55.627762"

I've used the custom format. The error tells me a line number but I can't look at it. Is there a way that I can convert the custom format to something I can view?

Also, I have turned off all the foreign key checking while I was
doing the restore but I guess this is the unique checking that is
causing the error. Can I turn that off (without doing a table
modify)?

I think you are restoring into a DB with non-empty tables.

Your target DB/tables needs either...

a. to be initially empty
b. to be populated with non-conflicting data
c. dump and/or restore invoked using 'clean' options

Or even possibly you are restoring a data-only dump from a
non-constrained origin system into one with unique constraints.

HTH

Thank you,
Perry

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general