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
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);
(
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
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
