From mrose@power-soft.com Fri Aug 19 19:08:18 2011 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 78993B5D91F for ; Fri, 19 Aug 2011 16:08:18 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 31589-02 for ; Fri, 19 Aug 2011 19:08:11 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from powerexchange.power-soft.net (rx0cv-power-soft.gv.bigpipeinc.com [64.251.72.74]) by mail.postgresql.org (Postfix) with ESMTP id 63518B5D803 for ; Fri, 19 Aug 2011 16:08:11 -0300 (ADT) Received: from POWEREXCHANGE.power-soft.net ([fe80::85ae:21c1:689e:2e09]) by powerexchange.power-soft.net ([fe80::85ae:21c1:689e:2e09%13]) with mapi; Fri, 19 Aug 2011 12:08:10 -0700 From: Mikola Rose To: "pgsql-sql@postgresql.org" Subject: (pgsql8.4) DATA Corruption Thread-Topic: (pgsql8.4) DATA Corruption Thread-Index: Acxeo1WEN1KO2lOBQyePOOR6qwG25w== Date: Fri, 19 Aug 2011 19:08:09 +0000 Message-ID: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: Content-Type: multipart/alternative; boundary="_000_F9528C0916A72145B548F5A758993ACC0EEFB41Apowerexchangepo_" MIME-Version: 1.0 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/75 X-Sequence-Number: 35743 --_000_F9528C0916A72145B548F5A758993ACC0EEFB41Apowerexchangepo_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Happy Friday people! I was wondering if anyone had any suggestions on how to resolve this issue.= .. I am moving otrs to another server and during the backup process I am r= unning into this error pg_dump: dumping contents of table article_attachment pg_dump: SQL command failed pg_dump: Error message from server: ERROR: unexpected chunk number 7 (expe= cted 6) for toast value 77281 in pg_toast_57366 pg_dump: The command was: COPY public.article_attachment (id, article_id, f= ilename, content_size, content_type, content, create_time, create_by, chang= e_time, change_by, content_id, content_alternative) TO stdout; pg_dump: *** aborted because of error I have tried a vacuum and reindex with no successes. Mik Rose --_000_F9528C0916A72145B548F5A758993ACC0EEFB41Apowerexchangepo_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Happy Friday peo= ple!

I was wondering if anyone had any suggestions on how= to resolve this issue... I am moving otrs to another server and duri= ng the backup process I am running into this error

pg_dump: dumping contents of table article_attachment=

pg_dump: SQL command failed

pg_dump: Err=
or message from server: ERROR:  unexpected chunk number 7 (expected 6)=
 for toast value 77281 in pg_toast_57366

&n=
bsp;

pg_dump: The command =
was: COPY public.article_attachment (id, article_id, filename, content_size=
, content_type, content, create_time, create_by, change_time, change_by, co=
ntent_id, content_alternative) TO stdout;

&=
nbsp;

pg_dump: *** aborted=
 because of error

&nbs= p;

I have tried a vacuum and reindex with no successes.<= o:p>

Mik Rose

= --_000_F9528C0916A72145B548F5A758993ACC0EEFB41Apowerexchangepo_-- From scott.marlowe@gmail.com Fri Aug 19 20:02:24 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id A9A66B5DC3E for ; Fri, 19 Aug 2011 17:02:24 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 32139-06 for ; Fri, 19 Aug 2011 20:02:18 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f46.google.com (mail-bw0-f46.google.com [209.85.214.46]) by mail.postgresql.org (Postfix) with ESMTP id 9ECA1B5DBC1 for ; Fri, 19 Aug 2011 17:02:17 -0300 (ADT) Received: by bke11 with SMTP id 11so2458754bke.19 for ; Fri, 19 Aug 2011 13:02:16 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; bh=HdUN5/CEPJYcUpaw+71yTsXrAmoXoWl35W15XqXH60A=; b=pWdaCGoZq8mGwbjGfiR328ZFRXYmTKPBmaFTH+Pzet/D61uh2AlsYsDhuzDTBDAP+t sDk75SwM6TQcdlc1uOLx9DhXbfqztvaUjxXVtgc5IiiRhVXoExDOa66GhXyPQi40wF/z 8Si0ZjkH8w1ypOC3qu+cFHMOC3tPFPCMJ2J4s= MIME-Version: 1.0 Received: by 10.204.129.217 with SMTP id p25mr40383bks.282.1313784136849; Fri, 19 Aug 2011 13:02:16 -0700 (PDT) Received: by 10.204.181.69 with HTTP; Fri, 19 Aug 2011 13:02:16 -0700 (PDT) In-Reply-To: References: Date: Fri, 19 Aug 2011 14:02:16 -0600 Message-ID: Subject: Re: (pgsql8.4) DATA Corruption From: Scott Marlowe To: Mikola Rose Cc: "pgsql-sql@postgresql.org" Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable 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, FREEMAIL_FROM=0.001 X-Spam-Level: X-Archive-Number: 201108/76 X-Sequence-Number: 35744 On Fri, Aug 19, 2011 at 1:08 PM, Mikola Rose wrote: > Happy Friday people! > > > > I was wondering if anyone had any suggestions on how to resolve this > issue... =A0I am moving otrs to another server and during the backup proc= ess I > am running into this error > > pg_dump: dumping contents of table article_attachment > > > > > > pg_dump: SQL command failed > > pg_dump: Error message from server: ERROR:=A0 unexpected chunk number 7 > (expected 6) for toast value 77281 in pg_toast_57366 > > > > > > pg_dump: The command was: COPY public.article_attachment (id, article_id, > filename, content_size, content_type, content, create_time, create_by, > change_time, change_by, content_id, content_alternative) TO stdout; > > > > > > pg_dump: *** aborted because of error > > > > > > I have tried a vacuum and reindex with no successes. You'll likely have to figure which blocks are corrupted, and copy out the good data using a where clause the excludes it, then get what you can out of it, truncate the table, and reinsert the data. Then figure out what part of your hardware is / might be dodgy. mem test, disk check, etc. From mrose@power-soft.com Fri Aug 19 20:28:06 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 660ABB5DBF6 for ; Fri, 19 Aug 2011 17:28:06 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 15775-01 for ; Fri, 19 Aug 2011 20:27:59 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from powerexchange.power-soft.net (rx0cv-power-soft.gv.bigpipeinc.com [64.251.72.74]) by mail.postgresql.org (Postfix) with ESMTP id 7DBACB5DBF7 for ; Fri, 19 Aug 2011 17:27:58 -0300 (ADT) Received: from [192.168.0.66] (192.168.0.66) by powerexchange.power-soft.net (192.168.0.24) with Microsoft SMTP Server (TLS) id 14.0.722.0; Fri, 19 Aug 2011 13:27:57 -0700 Subject: Re: (pgsql8.4) DATA Corruption MIME-Version: 1.0 (Apple Message framework v1244.3) Content-Type: multipart/alternative; boundary="Apple-Mail=_2C4AD8AC-5813-4728-88C2-7EC43C30F0A0" From: Mik Rose In-Reply-To: Date: Fri, 19 Aug 2011 13:27:57 -0700 CC: "pgsql-sql@postgresql.org" Message-ID: <0F87A7E6-6BF1-43BF-9299-BD94E28547FB@power-soft.com> References:

To: Scott Marlowe X-Mailer: Apple Mail (2.1244.3) 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/77 X-Sequence-Number: 35745 --Apple-Mail=_2C4AD8AC-5813-4728-88C2-7EC43C30F0A0 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset="iso-8859-1" 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. On 2011-08-19, at 1:02 PM, Scott Marlowe wrote: > On Fri, Aug 19, 2011 at 1:08 PM, Mikola Rose = wrote: >> Happy Friday people! >>=20 >>=20 >>=20 >> I was wondering if anyone had any suggestions on how to resolve this >> issue... I am moving otrs to another server and during the backup = process I >> am running into this error >>=20 >> pg_dump: dumping contents of table article_attachment >>=20 >>=20 >>=20 >>=20 >>=20 >> pg_dump: SQL command failed >>=20 >> pg_dump: Error message from server: ERROR: unexpected chunk number 7 >> (expected 6) for toast value 77281 in pg_toast_57366 >>=20 >>=20 >>=20 >>=20 >>=20 >> pg_dump: The command was: COPY public.article_attachment (id, = article_id, >> filename, content_size, content_type, content, create_time, = create_by, >> change_time, change_by, content_id, content_alternative) TO stdout; >>=20 >>=20 >>=20 >>=20 >>=20 >> pg_dump: *** aborted because of error >>=20 >>=20 >>=20 >>=20 >>=20 >> I have tried a vacuum and reindex with no successes. >=20 > You'll likely have to figure which blocks are corrupted, and copy out > the good data using a where clause the excludes it, then get what you > can out of it, truncate the table, and reinsert the data. >=20 > Then figure out what part of your hardware is / might be dodgy. mem > test, disk check, etc. --Apple-Mail=_2C4AD8AC-5813-4728-88C2-7EC43C30F0A0 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="iso-8859-1" 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.

On 2011-08-19, at = 1:02 PM, Scott Marlowe wrote:

On = Fri, Aug 19, 2011 at 1:08 PM, Mikola Rose <mrose@power-soft.com> = wrote:
Happy Friday = people!

I was wondering = if anyone had any suggestions on how to resolve = this
issue... I am = moving otrs to another server and during the backup process = I
am running into this = error

pg_dump: = dumping contents of table article_attachment

pg_dump: SQL = command failed

pg_dump: Error = message from server: ERROR: unexpected chunk number = 7
(expected 6) for toast value = 77281 in pg_toast_57366

pg_dump: The = command was: COPY public.article_attachment (id, = article_id,
filename, = content_size, content_type, content, create_time, = create_by,
change_time, = change_by, content_id, content_alternative) TO = stdout;

pg_dump: *** = aborted because of error

I have tried a = vacuum and reindex with no successes.

You'll likely = have to figure which blocks are corrupted, and copy out
the good data = using a where clause the excludes it, then get what you
can out of = it, truncate the table, and reinsert the data.

Then figure out = what part of your hardware is / might be dodgy. mem
test, disk = check, etc.

= --Apple-Mail=_2C4AD8AC-5813-4728-88C2-7EC43C30F0A0-- From ringerc@ringerc.id.au Fri Aug 26 03:52:03 2011 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 14EE9B5DBBF for ; 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 ; 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 ; Fri, 26 Aug 2011 00:51:53 -0300 (ADT) Received: by vws1 with SMTP id 1so2591757vws.19 for ; 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 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 CC: Scott Marlowe , "pgsql-sql@postgresql.org" Subject: Re: (pgsql8.4) DATA Corruption References:

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