Search Postgresql Archives

Duplicate Key violation on dump&reload using pg_restore

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxx  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@xxxxxxx  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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux