Re: PG_DUMP/PG_RESTORE duplicate Rows?

Never mind. I had my appserver up, and it probably screwed up.

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

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Larry Rosenman
Sent: Tuesday, December 01, 2009 12:31 PM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] PG_DUMP/PG_RESTORE duplicate Rows?

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