Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 89CD56323FA
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon,  9 Feb 2009 13:20:27 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 92389-04 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon,  9 Feb 2009 13:19:55 -0400 (AST)
X-Greylist: delayed 00:42:40.859634 by SQLgrey-1.7.6
Received: from mail.kickstone.com (smtp.kickstone.com [87.194.183.70])
	by mail.postgresql.org (Postfix) with ESMTP id D62C5632D80
	for <pgsql-admin@postgresql.org>; Mon,  9 Feb 2009 13:19:54 -0400 (AST)
Received: from madras2.square-pi.com ([192.168.2.131] helo=MADRAS2)
	by mail.kickstone.com with esmtpa (Exim 4.69)
	(envelope-from <john.lister-ps@kickstone.com>) id 1LWZ8B-0002YA-Bd
	for pgsql-admin@postgresql.org; Mon, 09 Feb 2009 16:37:11 +0000
Message-ID: <507152DDF8AB4DF093BD01A8BEC7C659@squarepi.com>
From: "John Lister" <john.lister-ps@kickstone.com>
To: <pgsql-admin@postgresql.org>
Subject: database corruption help
Date: Mon, 9 Feb 2009 16:37:11 -0000
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0010_01C98AD4.A8017F20"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200902/59
X-Sequence-Number: 31280

This is a multi-part message in MIME format.

------=_NextPart_000_0010_01C98AD4.A8017F20
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi, my wal archiving broke and postgresql filled up the local disk with =
transaction logs, which i foolishly deleted in a moment of madness, =
after resetting the transaction log a few of my tables are damaged but =
repairable. However the system tables also seemed to have suffered. My =
main problem is duplicate rows which violated the primary key and if i =
do a reindex system i get similar errors, for example:

ERROR:  could not create unique index "pg_class_oid_index"

a quick inspection of the pg_class table doesn't show any duplicates, is =
there anyway i can find out which row(s) are duplicated and remove them =
without a full db restore?

also doing something like this doesn't return anything which worked for =
my other tables

select oid from pg_class where oid in(select oid from pg_class group by =
oid having count(oid)>1 )



Many thanks
--

Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/

------=_NextPart_000_0010_01C98AD4.A8017F20
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.6000.16674" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi, my wal archiving broke and =
postgresql filled up=20
the local disk with transaction logs, which i foolishly deleted in a =
moment of=20
madness, after resetting the transaction log a few of my tables are =
damaged but=20
repairable. However the system tables also seemed to have suffered. My =
main=20
problem is duplicate rows which violated the primary key and if i do a=20
</FONT><FONT face=3DArial size=3D2>reindex system i get similar errors, =
for=20
example:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>ERROR:&nbsp; could not create unique =
index=20
"pg_class_oid_index"<BR></FONT></DIV>
<DIV><FONT face=3DArial size=3D2>a quick inspection of the pg_class =
table doesn't=20
show any duplicates, is there anyway i can find out which row(s) are =
duplicated=20
and remove them without a full db restore?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>also doing something like this doesn't =
return=20
anything which worked for my other tables</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>select oid from pg_class where oid =
in(select oid=20
from pg_class group by oid having count(oid)&gt;1 )<BR></DIV></FONT>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Many thanks</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>--</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Got needs? Get Goblin'! - <A=20
href=3D"http://www.pricegoblin.co.uk/">http://www.pricegoblin.co.uk/</A><=
BR></FONT></DIV></BODY></HTML>

------=_NextPart_000_0010_01C98AD4.A8017F20--