PG_DUMP/PG_RESTORE duplicate Rows?

From: "Larry Rosenman" <ler(at)lerctr(dot)org>
To: <pgsql-general(at)postgresql(dot)org>
Subject: PG_DUMP/PG_RESTORE duplicate Rows?
Date: 2009-12-01 18:31:00
Message-ID: 014601ca72b4$74570460$5d050d20$@org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Greetings,
I have a PostgreSQL 8.3.7 database that gets backed up each night with
pg_dump -Fc. When I restored it today I got the following:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2021; 2606 20719 CONSTRAINT
last_run_id_key drucker
pg_restore: [archiver (db)] could not execute query: ERROR: could not
create unique index "last_run_id_key"
DETAIL: Table contains duplicated values.
Command was: ALTER TABLE ONLY last_run
ADD CONSTRAINT last_run_id_key UNIQUE (id);

Looking at the table:
druckerdb=> select ctid,* from last_run;
ctid | id | last_run
--------+----+-------------------------
(0,10) | 2 | 2009-12-01 09:44:26.269
(0,11) | 2 | 2009-12-01 09:44:26.269
(0,60) | 1 | 2009-12-01 11:49:26.269
(0,61) | 1 | 2009-12-01 11:49:26.269
(4 rows)

My question is, how did this happen?

Yes, we're moving to 8.4.1 on our next release (12-12-2009).

I cleaned it up by:
druckerdb=> delete from last_run where ctid='(0,10)' or ctid='(0,60)';
DELETE 2

and putting the constraint back in place.

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 512-248-2683 E-Mail: ler(at)lerctr(dot)org
US Mail: 430 Valona Loop, Round Rock, TX 78681-3893

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Larry Rosenman 2009-12-01 18:36:37 Re: PG_DUMP/PG_RESTORE duplicate Rows?
Previous Message Robert Haas 2009-12-01 17:54:09 Re: [HACKERS] Fwd: psql+krb5