Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 14EE9B5DBBF
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 26 Aug 2011 00:52:03 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 32132-05 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 26 Aug 2011 03:51:55 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail-vw0-f46.google.com (mail-vw0-f46.google.com
	[209.85.212.46])
	by mail.postgresql.org (Postfix) with ESMTP id BCE83B5D803
	for <pgsql-sql@postgresql.org>; Fri, 26 Aug 2011 00:51:53 -0300 (ADT)
Received: by vws1 with SMTP id 1so2591757vws.19
	for <pgsql-sql@postgresql.org>; Thu, 25 Aug 2011 20:51:52 -0700 (PDT)
Received: by 10.52.24.162 with SMTP id v2mr681206vdf.153.1314330712742;
	Thu, 25 Aug 2011 20:51:52 -0700 (PDT)
Received: from [10.1.1.7] (124-169-228-164.dyn.iinet.net.au [124.169.228.164])
	by mx.google.com with ESMTPS id z9sm546851vco.14.2011.08.25.20.51.49
	(version=SSLv3 cipher=OTHER); Thu, 25 Aug 2011 20:51:51 -0700 (PDT)
Message-ID: <4E571848.9010504@ringerc.id.au>
Date: Fri, 26 Aug 2011 11:51:36 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:6.0) Gecko/20110812 Thunderbird/6.0
MIME-Version: 1.0
To: Mik Rose <mrose@power-soft.com>
CC: Scott Marlowe <scott.marlowe@gmail.com>,
	"pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: (pgsql8.4) DATA Corruption
References: 
 <F9528C0916A72145B548F5A758993ACC0EEFB41A@powerexchange.power-soft.net>
	<CAOR=d=05dcTFyV2zv1hPjUu4iTXT1w+sTySErF-PrmmB9jT=9g@mail.gmail.com>
	<0F87A7E6-6BF1-43BF-9299-BD94E28547FB@power-soft.com>
In-Reply-To: <0F87A7E6-6BF1-43BF-9299-BD94E28547FB@power-soft.com>
Content-Type: multipart/alternative;
	boundary="------------030802070708070307040402"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201108/115
X-Sequence-Number: 35783

This is a multi-part message in MIME format.
--------------030802070708070307040402
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 20/08/2011 4:27 AM, Mik Rose wrote:
> Thanks for the suggestions Scott.
>
> I was looking at the zero_damage_page option and enabled it in my 
> postgres.conf.  I saw that it detected bad pages and zero'd them out 
> during a number of reindex and a vacuum's.
>
> Now when i reindex though,  I am getting this error.
>
> NOTICE:  table "pg_foreign_server" was reindexedERROR:  could not 
> create unique index "pg_toast_57366_index"
> DETAIL:  Table contains duplicated values.
>
> ERROR:  could not create unique index "pg_toast_57366_index"
> DETAIL:  Table contains duplicated values.

You'll have to figure out what what values are duplicates within each 
table and DELETE one of them after determining which is the correct one 
- if you can. Then you should be able to re-create the index. If the 
DELETE fails you might find you have to DROP the index, do the 
DELETE(s), then CREATE the index again.

If the index is a partial index (ie it has a WHERE clause) you need to 
find duplicates that match that WHERE clause, you can ignore ones that 
don't match.

I hope you took a file-system-level backup of your database before 
starting to mess with it. People here will normally advise you to do 
that first and I'm kind of surprised nobody did.

--
Craig Ringer

--------------030802070708070307040402
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    On 20/08/2011 4:27 AM, Mik Rose wrote:
    <blockquote
      cite="mid:0F87A7E6-6BF1-43BF-9299-BD94E28547FB@power-soft.com"
      type="cite">Thanks for the suggestions Scott.
      <div><br>
      </div>
      <div>I was looking at the zero_damage_page option and enabled it
        in my postgres.conf. &nbsp;I saw that it detected bad pages and
        zero'd them out during a number of reindex and a vacuum's.</div>
      <div><br>
      </div>
      <div>Now when i reindex though, &nbsp;I am getting this error.</div>
      <div><br>
      </div>
      <div>
        <div style="margin-top: 0px; margin-right: 0px; margin-bottom:
          0px; margin-left: 0px; font: normal normal normal 13px/normal
          'Lucida Grande'; ">NOTICE:&nbsp; table "pg_foreign_server" was
          reindexedERROR:&nbsp; could not create unique index
          "pg_toast_57366_index"</div>
        <div style="margin-top: 0px; margin-right: 0px; margin-bottom:
          0px; margin-left: 0px; font: normal normal normal 13px/normal
          'Lucida Grande'; ">DETAIL:&nbsp; Table contains duplicated values.</div>
        <div style="margin-top: 0px; margin-right: 0px; margin-bottom:
          0px; margin-left: 0px; font: normal normal normal 13px/normal
          'Lucida Grande'; min-height: 16px; "><br>
        </div>
        <div style="margin-top: 0px; margin-right: 0px; margin-bottom:
          0px; margin-left: 0px; font: normal normal normal 13px/normal
          'Lucida Grande'; ">ERROR:&nbsp; could not create unique index
          "pg_toast_57366_index"</div>
        <div style="margin-top: 0px; margin-right: 0px; margin-bottom:
          0px; margin-left: 0px; font: normal normal normal 13px/normal
          'Lucida Grande'; ">DETAIL:&nbsp; Table contains duplicated values.<br>
        </div>
      </div>
    </blockquote>
    <br>
    You'll have to figure out what what values are duplicates within
    each table and DELETE one of them after determining which is the
    correct one - if you can. Then you should be able to re-create the
    index. If the DELETE fails you might find you have to DROP the
    index, do the DELETE(s), then CREATE the index again.<br>
    <br>
    If the index is a partial index (ie it has a WHERE clause) you need
    to find duplicates that match that WHERE clause, you can ignore ones
    that don't match.<br>
    <br>
    I hope you took a file-system-level backup of your database before
    starting to mess with it. People here will normally advise you to do
    that first and I'm kind of surprised nobody did.<br>
    <br>
    --<br>
    Craig Ringer<br>
  </body>
</html>

--------------030802070708070307040402--