Thread: Duplicate Key violation on dump&reload using pg_restore

Duplicate Key violation on dump&reload using pg_restore

From
"Markus Wollny"
Date:
Hello!
 
I'm currently trying to migrate one of our databases from PostgreSQL 8.2.4 to PostgreSQL 8.3.1. I have worked around the Tsearch2 migration (we used the contrib module) without too much hassle, but find myself stuck at an unexpected point - I get a duplicate key violation for the primary key on one of my tables:
 
pg_restore -U postgres -d community -a --disable-triggers -t ct_com_user -v ct_com_user.backup
pg_restore: connecting to database for restore
pg_restore: disabling triggers for ct_com_user
pg_restore: restoring data for table "ct_com_user"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4256; 0 106035908 TABLE DATA ct_com_user postgres
pg_restore: [archiver (db)] COPY failed: ERROR:  duplicate key value violates unique constraint "pk_ct_com_user"
CONTEXT:  COPY ct_com_user, line 357214: "2463013       Xxxxx   xxxxxxxx        5       \N      \N      0       \N      0       \N      0       \N      0   \N       1       \N      1       \N      1       \N      0       \N      0       \N      0       \N      0      
xxxxxx@foo.foo  0       ..."
pg_restore: *** aborted because of error
 
This is the table definition (I left out the non-relevant bits):
CREATE TABLE ct_com_user
(
  user_id integer NOT NULL,
  "login" text,
  "password" text,
  [...]
  CONSTRAINT pk_ct_com_user PRIMARY KEY (user_id)
)
WITH (OIDS=TRUE);
I did not change the table definition after the dump. I used pgdump of 8.3.1 to create a dump of schema and data separately like this:
 
/opt/pgsql/bin/pg_dump -hxxxxxxxxxxxx-U postgres -N tsearch2 -s community > community.schema.sql
/opt/pgsql/bin/pg_dump -hxxxxxxxxxxxx -U postgres -N tsearch2 -a community -Fc > community.data.pg
 
Then I created a new database (same encoding UTF-8, no issues there) on my 8.3.1 machine and installed the 8.3.1-contrib-tsearch2-module for backwards compatibility. After that I fed the schema.sql into that new DB - no errors so far. Then I tried to restore the data using
 
/opt/pgsql/bin/pg_restore --disable-triggers -v -U postgres -v -Fc -d community community.data.pg
 
During restore of that complete data dump, I get a warning like the one above:
 
pg_restore: restoring data for table "ct_com_user"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 9116; 0 106035908 TABLE DATA ct_com_user postgres
pg_restore: [archiver (db)] COPY failed: ERROR:  duplicate key value violates unique constraint "pk_ct_com_user"
CONTEXT:  COPY ct_com_user, line 356811: "2463013       Xxxxx   xxxxxxxx        5       \N      \N      0       \N      0       \N      0       \N      0   \N       1       \N      1       \N      1       \N      0       \N      0       \N      0       \N      0       xxxxxx@foo.foo  0       ..."
pg_restore: enabling triggers for ct_com_user
[...]
WARNING: errors ignored on restore: 1
 
Checking the restored database, everything is where it should be (i.e. even the TSearch2-enabled tabled), with the exception of that ct_com_user-table, which remains empty. I therefore tried and dumped that table alone again and tried to restore - with the exact same result (see above). Before restoring again, I made sure that the target table doesn't contain any entries (count(*) still is 0).
 
I'll try and delete that single line in the 8.2.1 production system now (this user has not logged in for nearly three months now, so not much loss there - but even if that happens to work out (not so sure if it will), I'd still like to know what's going on here. Any ideas?
 
Kind regards
 
   Markus
 
 
 
 
 


Computec Media AG

Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818)

Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch

Vorsitzender des Aufsichtsrates: Jürg Marquard

Umsatzsteuer-Identifikationsnummer: DE 812 575 276

Re: Duplicate Key violation on dump&reload using pg_restore

From
Tom Lane
Date:
"Markus Wollny" <Markus.Wollny@computec.de> writes:
> I'm currently trying to migrate one of our databases from PostgreSQL 8.2.4 to PostgreSQL 8.3.1. I have worked around
theTsearch2 migration (we used the contrib module) without too much hassle, but find myself stuck at an unexpected
point- I get a duplicate key violation for the primary key on one of my tables: 

Maybe there actually is a duplicate key in the source DB --- have you
checked?  There were some bugs in early 8.2.x releases that could
possibly allow that to happen.

            regards, tom lane

Re: Duplicate Key violation on dump&reload using pg_restore

From
"Markus Wollny"
Date:
Quick update: Seems like removing that tuple has solved the issue, dump and import of that table went fine, everything is where is should be - but there shouldn't have been an issue there in the first place however, with the primary key constraint present in the source database. I'm still curious, even though I've now got less to worry about the upcoming migration :)


Computec Media AG

Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818)

Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch

Vorsitzender des Aufsichtsrates: Jürg Marquard

Umsatzsteuer-Identifikationsnummer: DE 812 575 276

Re: Duplicate Key violation on dump&reload using pg_restore

From
"Markus Wollny"
Date:
Tom Lane wrote:
> Maybe there actually is a duplicate key in the source DB --- have you
> checked?  There were some bugs in early 8.2.x releases that could
> possibly allow that to happen.

Thanks, I was hoping there would be an easy explanation like that. I guess I'll have to do a little reading up on the change logs of post-8.2.4-releases :)


Computec Media AG

Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818)

Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch

Vorsitzender des Aufsichtsrates: Jürg Marquard

Umsatzsteuer-Identifikationsnummer: DE 812 575 276