From siddharth.shah@elitecore.com Fri May  7 08:04:13 2010
Received: from maia.hub.org (maia-4.hub.org [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 4870E633877
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri,  7 May 2010 05:04:13 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 30958-05 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri,  7 May 2010 08:04:05 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from elitecore.com (mailhost.elitecore.com [203.88.135.194])
	by mail.postgresql.org (Postfix) with ESMTP id E56CE632AA7
	for <pgsql-admin@postgresql.org>; Fri,  7 May 2010 05:04:04 -0300 (ADT)
Received: (qmail 2098 invoked from network); 7 May 2010 08:04:02 -0000
Received: from router.elitecore.com (HELO [192.168.1.136])
	(siddharth.shah@[203.88.135.193])
	(envelope-sender <siddharth.shah@elitecore.com>)
	by elitecore.com (qmail-ldap-1.03) with SMTP
	for <pgsql-admin@postgresql.org>; 7 May 2010 08:04:02 -0000
Message-ID: <4BE3C93C.70800@elitecore.com>
Date: Fri, 07 May 2010 13:33:08 +0530
From: Siddharth Shah <siddharth.shah@elitecore.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
To: pgsql-admin@postgresql.org,  pgsql-novice@postgresql.org
Subject: ERROR:  XX001 (Critical and Urgent)
Content-Type: multipart/alternative;
	boundary="------------080709060004000102080909"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.909 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201005/89
X-Sequence-Number: 34562

This is a multi-part message in MIME format.
--------------080709060004000102080909
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Hello All,
   
    Getting *ERROR:  XX001: could not read block 17 of relation 
base/16386/2619: read only 0 of 8192 bytes*, While vacuuming database
    Manual vacuuming and Auto vacuuming process *constantly* taking high 
CPU, not able to skip corrupted table for vacuuming and dump this 
message at regular interval. * fsync is off* , From strace, found that 
semop call was in infinite loop.
     
    I have tried with making fsync on, Now manual vacuum process is 
taking high CPU, Strace unable to show any results (may be dead lock 
situation)
    and not any error / warning from postgres daemon

    Postgres Version : 8.4.3 (Migrated data from 8.4.1)

What can be issue ? Is it issue coming after database  table corruption, 
Can fsync on can prevent such (corruption) scenarios ?

Thanks,
Siddharth

--------------080709060004000102080909
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hello All,<br>
    <br>
    Getting <b>ERROR:  XX001: could not read block 17 of relation
base/16386/2619: read only 0 of 8192 bytes</b>, While vacuuming database<br>
    Manual vacuuming and Auto vacuuming process <b>constantly</b>
taking high CPU, not able to skip corrupted table for vacuuming and
dump this message at regular interval. <b> fsync is off</b> , From
strace, found that semop call was in infinite loop.<br>
      <br>
    I have tried with making fsync on, Now manual vacuum process is
taking high CPU, Strace unable to show any results (may be dead lock
situation)<br>
    and not any error / warning from postgres daemon<br>
<br>
    Postgres Version : 8.4.3 (Migrated data from 8.4.1)<br>
<br>
What can be issue ? Is it issue coming after database  table
corruption, Can fsync on can prevent such (corruption) scenarios ?<br>
<br>
Thanks,<br>
Siddharth
</body>
</html>

--------------080709060004000102080909--



From Kevin.Grittner@wicourts.gov Fri May  7 13:36:26 2010
Received: from maia.hub.org (maia-4.hub.org [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id EE9E863340A;
	Fri,  7 May 2010 10:36:26 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024)
	with ESMTP id 42645-05; Fri,  7 May 2010 13:36:19 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by mail.postgresql.org (Postfix) with ESMTP id 658EB632F5F;
	Fri,  7 May 2010 10:36:19 -0300 (ADT)
Received: from Courts-MTA by gw.wicourts.gov
	with Novell_GroupWise; Fri, 07 May 2010 08:36:06 -0500
Message-Id: <4BE3D0EF020000250003137D@gw.wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 8.0.1 
Date: Fri, 07 May 2010 08:35:58 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Siddharth Shah" <siddharth.shah@elitecore.com>,
	<pgsql-admin@postgresql.org>,<pgsql-novice@postgresql.org>
Subject: Re: ERROR:  XX001 (Critical and Urgent)
References: <4BE3C93C.70800@elitecore.com>
In-Reply-To: <4BE3C93C.70800@elitecore.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-4.21 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201005/90
X-Sequence-Number: 34563

Siddharth Shah <siddharth.shah@elitecore.com> wrote:
 
> * fsync is off*
 
If you are running the database with fsync off and there is any sort
of unusual termination, your database will probably be corrupted.  I
recommend restoring from your last good backup.  If you don't have
one, recovery is going to be painful; I recommend contracting with
one of the many companies which off PostgreSQL support.  (I'm not
affiliated with any of them.)
 
> I have tried with making fsync on
 
That may help prevent further corruption, but will do nothing to
help recover from the damage already done.
 
> Postgres Version : 8.4.3 (Migrated data from 8.4.1)
 
What do you mean by that?  You installed 8.4.3 and reindexed hash
indexes?
 
> What can be issue ? Is it issue coming after database table
> corruption
 
Yes.
 
> Can fsync on can prevent such (corruption) scenarios ?
 
Yes.
 
-Kevin



From siddharth.shah@elitecore.com Fri May  7 13:52:35 2010
Received: from maia.hub.org (maia-4.hub.org [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 1CFEF6334CB
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri,  7 May 2010 10:52:35 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 97591-10 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri,  7 May 2010 13:52:27 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from elitecore.com (mailhost.elitecore.com [203.88.135.194])
	by mail.postgresql.org (Postfix) with ESMTP id 677FE633332
	for <pgsql-admin@postgresql.org>; Fri,  7 May 2010 10:52:25 -0300 (ADT)
Received: (qmail 26277 invoked from network); 7 May 2010 13:52:23 -0000
Received: from router.elitecore.com (HELO [192.168.1.136])
	(siddharth.shah@[203.88.135.193])
	(envelope-sender <siddharth.shah@elitecore.com>)
	by elitecore.com (qmail-ldap-1.03) with SMTP
	for <Kevin.Grittner@wicourts.gov>; 7 May 2010 13:52:23 -0000
Message-ID: <4BE41AE1.4010804@elitecore.com>
Date: Fri, 07 May 2010 19:21:29 +0530
From: Siddharth Shah <siddharth.shah@elitecore.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
CC: pgsql-admin@postgresql.org
Subject: Re: ERROR:  XX001 (Critical and Urgent)
References: <4BE3C93C.70800@elitecore.com>
	<4BE3D0EF020000250003137D@gw.wicourts.gov>
In-Reply-To: <4BE3D0EF020000250003137D@gw.wicourts.gov>
Content-Type: multipart/alternative;
	boundary="------------010600040400040301070806"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.909 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201005/91
X-Sequence-Number: 34564

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

Thanks Kevin.

Yes, I installed 8.4.3 then I have found that DDL and DML statements 
were getting failed to execute in some distributions
So that's why taken call for reindexing

What can be the method to verify that it's a database corruption ?  

xdb=# \dt;
ERROR:  index "pg_class_relname_nsp_index" contains unexpected zero page 
at block 33
HINT:  Please REINDEX it.
xdb=# analyse verbose pg_class_relname_nsp_index;
ANALYZE
xdb=# \dt;   
ERROR:  index "pg_class_relname_nsp_index" contains unexpected zero page 
at block 33
HINT:  Please REINDEX it.
xdb=# reindex index pg_class_relname_nsp_index;

Now INDEXing taking High CPU and postgres baffled.


I don't have any backup available, Is there any way to fix this ?


Kevin Grittner wrote:
> Siddharth Shah <siddharth.shah@elitecore.com> wrote:
>  
>   
>> * fsync is off*
>>     
>  
> If you are running the database with fsync off and there is any sort
> of unusual termination, your database will probably be corrupted.  I
> recommend restoring from your last good backup.  If you don't have
> one, recovery is going to be painful; I recommend contracting with
> one of the many companies which off PostgreSQL support.  (I'm not
> affiliated with any of them.)
>  
>   
>> I have tried with making fsync on
>>     
>  
> That may help prevent further corruption, but will do nothing to
> help recover from the damage already done.
>  
>   
>> Postgres Version : 8.4.3 (Migrated data from 8.4.1)
>>     
>  
> What do you mean by that?  You installed 8.4.3 and reindexed hash
> indexes?
>  
>   
>> What can be issue ? Is it issue coming after database table
>> corruption
>>     
>  
> Yes.
>  
>   
>> Can fsync on can prevent such (corruption) scenarios ?
>>     
>  
> Yes.
>  
> -Kevin
>   


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

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Thanks Kevin.<br>
<br>
Yes, I installed 8.4.3 then I have found that DDL and DML statements
were getting failed to execute in some distributions<br>
So that's why taken call for reindexing <br>
<br>
What can be the method to verify that it's a database corruption ?&nbsp;&nbsp; <br>
<br>
xdb=# \dt;<br>
ERROR:&nbsp; index "pg_class_relname_nsp_index" contains unexpected zero
page at block 33<br>
HINT:&nbsp; Please REINDEX it.<br>
xdb=# analyse verbose pg_class_relname_nsp_index;<br>
ANALYZE<br>
xdb=# \dt;&nbsp;&nbsp;&nbsp; <br>
ERROR:&nbsp; index "pg_class_relname_nsp_index" contains unexpected zero
page at block 33<br>
HINT:&nbsp; Please REINDEX it.<br>
xdb=# reindex index pg_class_relname_nsp_index;<br>
<br>
Now INDEXing taking High CPU and postgres baffled.<br>
<br>
<br>
I don't have any backup available, Is there any way to fix this ?<br>
<br>
<br>
Kevin Grittner wrote:
<blockquote cite="mid4BE3D0EF020000250003137D@gw.wicourts.gov"
 type="cite">
  <pre wrap="">Siddharth Shah <a class="moz-txt-link-rfc2396E" href="mailto:siddharth.shah@elitecore.com">&lt;siddharth.shah@elitecore.com&gt;</a> wrote:
 
  </pre>
  <blockquote type="cite">
    <pre wrap="">* fsync is off*
    </pre>
  </blockquote>
  <pre wrap=""><!----> 
If you are running the database with fsync off and there is any sort
of unusual termination, your database will probably be corrupted.  I
recommend restoring from your last good backup.  If you don't have
one, recovery is going to be painful; I recommend contracting with
one of the many companies which off PostgreSQL support.  (I'm not
affiliated with any of them.)
 
  </pre>
  <blockquote type="cite">
    <pre wrap="">I have tried with making fsync on
    </pre>
  </blockquote>
  <pre wrap=""><!----> 
That may help prevent further corruption, but will do nothing to
help recover from the damage already done.
 
  </pre>
  <blockquote type="cite">
    <pre wrap="">Postgres Version : 8.4.3 (Migrated data from 8.4.1)
    </pre>
  </blockquote>
  <pre wrap=""><!----> 
What do you mean by that?  You installed 8.4.3 and reindexed hash
indexes?
 
  </pre>
  <blockquote type="cite">
    <pre wrap="">What can be issue ? Is it issue coming after database table
corruption
    </pre>
  </blockquote>
  <pre wrap=""><!----> 
Yes.
 
  </pre>
  <blockquote type="cite">
    <pre wrap="">Can fsync on can prevent such (corruption) scenarios ?
    </pre>
  </blockquote>
  <pre wrap=""><!----> 
Yes.
 
-Kevin
  </pre>
</blockquote>
<br>
</body>
</html>

--------------010600040400040301070806--



From Kevin.Grittner@wicourts.gov Fri May  7 14:19:42 2010
Received: from maia.hub.org (maia-4.hub.org [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id CB718633CE1
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri,  7 May 2010 11:19:42 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 34934-09 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri,  7 May 2010 14:19:35 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by mail.postgresql.org (Postfix) with ESMTP id 54CF063340A
	for <pgsql-admin@postgresql.org>; Fri,  7 May 2010 11:19:35 -0300 (ADT)
Received: from Courts-MTA by gw.wicourts.gov
	with Novell_GroupWise; Fri, 07 May 2010 09:19:33 -0500
Message-Id: <4BE3DB1D020000250003138A@gw.wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 8.0.1 
Date: Fri, 07 May 2010 09:19:23 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Siddharth Shah" <siddharth.shah@elitecore.com>
Cc: <pgsql-admin@postgresql.org>
Subject: Re: ERROR:  XX001 (Critical and Urgent)
References: <4BE3C93C.70800@elitecore.com>
	<4BE3D0EF020000250003137D@gw.wicourts.gov>
	<4BE41AE1.4010804@elitecore.com>
In-Reply-To: <4BE41AE1.4010804@elitecore.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-4.21 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201005/92
X-Sequence-Number: 34565

[rearranged to put the most critical point first]
 
Siddharth Shah <siddharth.shah@elitecore.com> wrote:
 
> I don't have any backup available, Is there any way to fix this ?
 
I *strongly* recommend that you shut down the database and take a
file copy of the whole data tree (everything under what -D points to
on the server startup) which you should keep until long after you
think everything is working OK again.  Before you do anything else. 
You are at risk of losing everything in the database, and one
misstep could put you over the edge.  If this is a production
database, tell the users that it is down until further notice.
 
> What can be the method to verify that it's a database corruption ?
 
> ERROR:  index "pg_class_relname_nsp_index" contains unexpected
> zero page at block 33
 
Getting an error like that indicates database corruption.
 
> HINT:  Please REINDEX it.
> xdb=# reindex index pg_class_relname_nsp_index;
> 
> Now INDEXing taking High CPU and postgres baffled.
 
That is an index on the table which describes all your tables and
indexes.  It normally doesn't take a long time to reindex.  You
should consider doing your recovery in single-user mode (*AFTER* you
make that copy):
 
http://www.postgresql.org/docs/8.4/interactive/app-postgres.html
 
After trying reindex in that context, please post again.
 
-Kevin



From siddharth.shah@elitecore.com Fri May  7 15:01:42 2010
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id D441863263B
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri,  7 May 2010 12:01:42 -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 98709-08 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri,  7 May 2010 15:01:35 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from elitecore.com (mailhost.elitecore.com [203.88.135.194])
	by mail.postgresql.org (Postfix) with ESMTP id A4718632A8D
	for <pgsql-admin@postgresql.org>; Fri,  7 May 2010 12:01:34 -0300 (ADT)
Received: (qmail 29789 invoked from network); 7 May 2010 15:01:32 -0000
Received: from router.elitecore.com (HELO [192.168.1.136])
	(siddharth.shah@[203.88.135.193])
	(envelope-sender <siddharth.shah@elitecore.com>)
	by elitecore.com (qmail-ldap-1.03) with SMTP
	for <Kevin.Grittner@wicourts.gov>; 7 May 2010 15:01:32 -0000
Message-ID: <4BE42B16.8060804@elitecore.com>
Date: Fri, 07 May 2010 20:30:38 +0530
From: Siddharth Shah <siddharth.shah@elitecore.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
CC: pgsql-admin@postgresql.org
Subject: Re: ERROR:  XX001 (Critical and Urgent)
References: <4BE3C93C.70800@elitecore.com>
	<4BE3D0EF020000250003137D@gw.wicourts.gov>
	<4BE41AE1.4010804@elitecore.com>
	<4BE3DB1D020000250003138A@gw.wicourts.gov>
In-Reply-To: <4BE3DB1D020000250003138A@gw.wicourts.gov>
Content-Type: multipart/alternative;
	boundary="------------060208070909060906040800"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.909 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201005/95
X-Sequence-Number: 34568

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

Kevin Grittner wrote:
> [rearranged to put the most critical point first]
>  
> Siddharth Shah <siddharth.shah@elitecore.com> wrote:
>  
>   
>> I don't have any backup available, Is there any way to fix this ?
>>     
>  
> I *strongly* recommend that you shut down the database and take a
> file copy of the whole data tree (everything under what -D points to
> on the server startup) which you should keep until long after you
> think everything is working OK again.  Before you do anything else. 
> You are at risk of losing everything in the database, and one
> misstep could put you over the edge.  If this is a production
> database, tell the users that it is down until further notice.
>   
    Yes Kevin, I have taken backup of DATADIR.
>  
>   
>> What can be the method to verify that it's a database corruption ?
>>     
>  
>   
>> ERROR:  index "pg_class_relname_nsp_index" contains unexpected
>> zero page at block 33
>>     
>  
> Getting an error like that indicates database corruption.
>  
>   
>> HINT:  Please REINDEX it.
>> xdb=# reindex index pg_class_relname_nsp_index;
>>
>> Now INDEXing taking High CPU and postgres baffled.
>>     
>  
> That is an index on the table which describes all your tables and
> indexes.  It normally doesn't take a long time to reindex.  You
> should consider doing your recovery in single-user mode (*AFTER* you
> make that copy):
>  
> http://www.postgresql.org/docs/8.4/interactive/app-postgres.html
>  
> After trying reindex in that context, please post again.
>   
    postgres --single -P -D $DATADIR -p 5433 xdb
    Same behavior in single mode.
>  
> -Kevin
>   



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

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Kevin Grittner wrote:
<blockquote cite="mid4BE3DB1D020000250003138A@gw.wicourts.gov"
 type="cite">
  <pre wrap="">[rearranged to put the most critical point first]
 
Siddharth Shah <a class="moz-txt-link-rfc2396E" href="mailto:siddharth.shah@elitecore.com">&lt;siddharth.shah@elitecore.com&gt;</a> wrote:
 
  </pre>
  <blockquote type="cite">
    <pre wrap="">I don't have any backup available, Is there any way to fix this ?
    </pre>
  </blockquote>
  <pre wrap=""><!----> 
I *strongly* recommend that you shut down the database and take a
file copy of the whole data tree (everything under what -D points to
on the server startup) which you should keep until long after you
think everything is working OK again.  Before you do anything else. 
You are at risk of losing everything in the database, and one
misstep could put you over the edge.  If this is a production
database, tell the users that it is down until further notice.
  </pre>
</blockquote>
&nbsp;&nbsp;&nbsp; Yes Kevin, I have taken backup of DATADIR.<br>
<blockquote cite="mid4BE3DB1D020000250003138A@gw.wicourts.gov"
 type="cite">
  <pre wrap=""> 
  </pre>
  <blockquote type="cite">
    <pre wrap="">What can be the method to verify that it's a database corruption ?
    </pre>
  </blockquote>
  <pre wrap=""><!----> 
  </pre>
  <blockquote type="cite">
    <pre wrap="">ERROR:  index "pg_class_relname_nsp_index" contains unexpected
zero page at block 33
    </pre>
  </blockquote>
  <pre wrap=""><!----> 
Getting an error like that indicates database corruption.
 
  </pre>
  <blockquote type="cite">
    <pre wrap="">HINT:  Please REINDEX it.
xdb=# reindex index pg_class_relname_nsp_index;

Now INDEXing taking High CPU and postgres baffled.
    </pre>
  </blockquote>
  <pre wrap=""><!----> 
That is an index on the table which describes all your tables and
indexes.  It normally doesn't take a long time to reindex.  You
should consider doing your recovery in single-user mode (*AFTER* you
make that copy):
 
<a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/8.4/interactive/app-postgres.html">http://www.postgresql.org/docs/8.4/interactive/app-postgres.html</a>
 
After trying reindex in that context, please post again.
  </pre>
</blockquote>
&nbsp;&nbsp;&nbsp; postgres --single -P -D $DATADIR -p 5433 xdb<br>
&nbsp;&nbsp;&nbsp; Same behavior in single mode.<br>
<blockquote cite="mid4BE3DB1D020000250003138A@gw.wicourts.gov"
 type="cite">
  <pre wrap=""> 
-Kevin
  </pre>
</blockquote>
<br>
<br>
</body>
</html>

--------------060208070909060906040800--



From siddharth.shah@elitecore.com Fri May  7 15:10:48 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id E19B06328C6
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri,  7 May 2010 12:10:48 -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 38671-10 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri,  7 May 2010 15:10:41 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from elitecore.com (mailhost.elitecore.com [203.88.135.194])
	by mail.postgresql.org (Postfix) with ESMTP id 41E5F6325FC
	for <pgsql-admin@postgresql.org>; Fri,  7 May 2010 12:10:39 -0300 (ADT)
Received: (qmail 1422 invoked from network); 7 May 2010 15:10:38 -0000
Received: from router.elitecore.com (HELO [192.168.1.136])
	(siddharth.shah@[203.88.135.193])
	(envelope-sender <siddharth.shah@elitecore.com>)
	by elitecore.com (qmail-ldap-1.03) with SMTP
	for <Kevin.Grittner@wicourts.gov>; 7 May 2010 15:10:38 -0000
Message-ID: <4BE42D38.6000606@elitecore.com>
Date: Fri, 07 May 2010 20:39:44 +0530
From: Siddharth Shah <siddharth.shah@elitecore.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
CC: pgsql-admin@postgresql.org
Subject: Re: ERROR:  XX001 (Critical and Urgent)
References: <4BE3C93C.70800@elitecore.com>
	<4BE3D0EF020000250003137D@gw.wicourts.gov>
	<4BE41AE1.4010804@elitecore.com>
	<4BE3DB1D020000250003138A@gw.wicourts.gov>
	<4BE42B16.8060804@elitecore.com>
In-Reply-To: <4BE42B16.8060804@elitecore.com>
Content-Type: multipart/alternative;
	boundary="------------040206000603000100040307"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.909 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201005/96
X-Sequence-Number: 34569

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


One more point, This is observed two times while product firmware 
updates which updates Postgres 8.4.3 from 8.4.1
Abruptly shutdown never leads to this type of corruption while fsync is 
always off

Thanks,
Siddharth


Siddharth Shah wrote:
> Kevin Grittner wrote:
>> [rearranged to put the most critical point first]
>>  
>> Siddharth Shah <siddharth.shah@elitecore.com> wrote:
>>  
>>   
>>> I don't have any backup available, Is there any way to fix this ?
>>>     
>>  
>> I *strongly* recommend that you shut down the database and take a
>> file copy of the whole data tree (everything under what -D points to
>> on the server startup) which you should keep until long after you
>> think everything is working OK again.  Before you do anything else. 
>> You are at risk of losing everything in the database, and one
>> misstep could put you over the edge.  If this is a production
>> database, tell the users that it is down until further notice.
>>   
>     Yes Kevin, I have taken backup of DATADIR.
>>  
>>   
>>> What can be the method to verify that it's a database corruption ?
>>>     
>>  
>>   
>>> ERROR:  index "pg_class_relname_nsp_index" contains unexpected
>>> zero page at block 33
>>>     
>>  
>> Getting an error like that indicates database corruption.
>>  
>>   
>>> HINT:  Please REINDEX it.
>>> xdb=# reindex index pg_class_relname_nsp_index;
>>>
>>> Now INDEXing taking High CPU and postgres baffled.
>>>     
>>  
>> That is an index on the table which describes all your tables and
>> indexes.  It normally doesn't take a long time to reindex.  You
>> should consider doing your recovery in single-user mode (*AFTER* you
>> make that copy):
>>  
>> http://www.postgresql.org/docs/8.4/interactive/app-postgres.html
>>  
>> After trying reindex in that context, please post again.
>>   
>     postgres --single -P -D $DATADIR -p 5433 xdb
>     Same behavior in single mode.
>>  
>> -Kevin
>>   
>
>


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

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<br>
One more point, This is observed two times while product firmware
updates which updates Postgres 8.4.3 from 8.4.1<br>
Abruptly shutdown never leads to this type of corruption while fsync is
always off<br>
<br>
Thanks,<br>
Siddharth<br>
<br>
<br>
Siddharth Shah wrote:
<blockquote cite="mid4BE42B16.8060804@elitecore.com" type="cite">
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
Kevin Grittner wrote:
  <blockquote cite="mid4BE3DB1D020000250003138A@gw.wicourts.gov"
 type="cite">
    <pre wrap="">[rearranged to put the most critical point first]
 
Siddharth Shah <a class="moz-txt-link-rfc2396E"
 href="mailto:siddharth.shah@elitecore.com">&lt;siddharth.shah@elitecore.com&gt;</a> wrote:
 
  </pre>
    <blockquote type="cite">
      <pre wrap="">I don't have any backup available, Is there any way to fix this ?
    </pre>
    </blockquote>
    <pre wrap=""><!----> 
I *strongly* recommend that you shut down the database and take a
file copy of the whole data tree (everything under what -D points to
on the server startup) which you should keep until long after you
think everything is working OK again.  Before you do anything else. 
You are at risk of losing everything in the database, and one
misstep could put you over the edge.  If this is a production
database, tell the users that it is down until further notice.
  </pre>
  </blockquote>
&nbsp;&nbsp;&nbsp; Yes Kevin, I have taken backup of DATADIR.<br>
  <blockquote cite="mid4BE3DB1D020000250003138A@gw.wicourts.gov"
 type="cite">
    <pre wrap=""> 
  </pre>
    <blockquote type="cite">
      <pre wrap="">What can be the method to verify that it's a database corruption ?
    </pre>
    </blockquote>
    <pre wrap=""><!----> 
  </pre>
    <blockquote type="cite">
      <pre wrap="">ERROR:  index "pg_class_relname_nsp_index" contains unexpected
zero page at block 33
    </pre>
    </blockquote>
    <pre wrap=""><!----> 
Getting an error like that indicates database corruption.
 
  </pre>
    <blockquote type="cite">
      <pre wrap="">HINT:  Please REINDEX it.
xdb=# reindex index pg_class_relname_nsp_index;

Now INDEXing taking High CPU and postgres baffled.
    </pre>
    </blockquote>
    <pre wrap=""><!----> 
That is an index on the table which describes all your tables and
indexes.  It normally doesn't take a long time to reindex.  You
should consider doing your recovery in single-user mode (*AFTER* you
make that copy):
 
<a class="moz-txt-link-freetext"
 href="http://www.postgresql.org/docs/8.4/interactive/app-postgres.html">http://www.postgresql.org/docs/8.4/interactive/app-postgres.html</a>
 
After trying reindex in that context, please post again.
  </pre>
  </blockquote>
&nbsp;&nbsp;&nbsp; postgres --single -P -D $DATADIR -p 5433 xdb<br>
&nbsp;&nbsp;&nbsp; Same behavior in single mode.<br>
  <blockquote cite="mid4BE3DB1D020000250003138A@gw.wicourts.gov"
 type="cite">
    <pre wrap=""> 
-Kevin
  </pre>
  </blockquote>
  <br>
  <br>
</blockquote>
<br>
</body>
</html>

--------------040206000603000100040307--



From Kevin.Grittner@wicourts.gov Fri May  7 15:21:58 2010
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 5BA5A632A8D
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri,  7 May 2010 12:21:58 -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 10663-06 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri,  7 May 2010 15:21:51 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by mail.postgresql.org (Postfix) with ESMTP id 788B5632545
	for <pgsql-admin@postgresql.org>; Fri,  7 May 2010 12:21:51 -0300 (ADT)
Received: from Courts-MTA by gw.wicourts.gov
	with Novell_GroupWise; Fri, 07 May 2010 10:21:50 -0500
Message-Id: <4BE3E9BA0200002500031394@gw.wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 8.0.1 
Date: Fri, 07 May 2010 10:21:46 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Siddharth Shah" <siddharth.shah@elitecore.com>
Cc: <pgsql-admin@postgresql.org>
Subject: Re: ERROR:  XX001 (Critical and Urgent)
References: <4BE3C93C.70800@elitecore.com>
	<4BE3D0EF020000250003137D@gw.wicourts.gov>
	<4BE41AE1.4010804@elitecore.com>
	<4BE3DB1D020000250003138A@gw.wicourts.gov>
	<4BE42B16.8060804@elitecore.com>
In-Reply-To: <4BE42B16.8060804@elitecore.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-4.21 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201005/98
X-Sequence-Number: 34571

Siddharth Shah <siddharth.shah@elitecore.com> wrote:
 
>>> xdb=# reindex index pg_class_relname_nsp_index;
>>>
>>> Now INDEXing taking High CPU and postgres baffled.
 
>> consider doing your recovery in single-user mode
 
>     postgres --single -P -D $DATADIR -p 5433 xdb
>     Same behavior in single mode.
 
How long did you leave it running?  Did you get any messages?  Is
there anything in the log?  What do CPU usage and disk usage look
like during the attempt?
 
-Kevin



From siddharth.shah@elitecore.com Fri May  7 15:43:42 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 4A71863381B
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri,  7 May 2010 12:43:42 -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 49067-08 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri,  7 May 2010 15:43:34 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from elitecore.com (mailhost.elitecore.com [203.88.135.194])
	by mail.postgresql.org (Postfix) with ESMTP id E77E163381D
	for <pgsql-admin@postgresql.org>; Fri,  7 May 2010 12:43:33 -0300 (ADT)
Received: (qmail 14410 invoked from network); 7 May 2010 15:43:31 -0000
Received: from router.elitecore.com (HELO [192.168.1.136])
	(siddharth.shah@[203.88.135.193])
	(envelope-sender <siddharth.shah@elitecore.com>)
	by elitecore.com (qmail-ldap-1.03) with SMTP
	for <Kevin.Grittner@wicourts.gov>; 7 May 2010 15:43:31 -0000
Message-ID: <4BE434D7.4050205@elitecore.com>
Date: Fri, 07 May 2010 21:12:15 +0530
From: Siddharth Shah <siddharth.shah@elitecore.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
CC: pgsql-admin@postgresql.org
Subject: Re: ERROR:  XX001 (Critical and Urgent)
References: <4BE3C93C.70800@elitecore.com>
	<4BE3D0EF020000250003137D@gw.wicourts.gov>
	<4BE41AE1.4010804@elitecore.com>
	<4BE3DB1D020000250003138A@gw.wicourts.gov>
	<4BE42B16.8060804@elitecore.com>
	<4BE3E9BA0200002500031394@gw.wicourts.gov>
In-Reply-To: <4BE3E9BA0200002500031394@gw.wicourts.gov>
Content-Type: multipart/alternative;
	boundary="------------000706090009010603040401"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.909 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201005/101
X-Sequence-Number: 34574

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

Kevin Grittner wrote:
> Siddharth Shah <siddharth.shah@elitecore.com> wrote:
>  
>   
>>>> xdb=# reindex index pg_class_relname_nsp_index;
>>>>
>>>> Now INDEXing taking High CPU and postgres baffled.
>>>>         
>  
>   
>>> consider doing your recovery in single-user mode
>>>       
>  
>   
>>     postgres --single -P -D $DATADIR -p 5433 xdb
>>     Same behavior in single mode.
>>     
>  
> How long did you leave it running?  Did you get any messages?  Is
> there anything in the log?  What do CPU usage and disk usage look
> like during the attempt?
>  
> -Kevin
>   

Kevin, It start normally , I have successfully retrieved data from few 
tables
But I am not able to do dS / dT or dt. As you said this is index file 
for Postgres tables and indexes
Now when I taken call for reindex   pg_class_relname_nsp_index it takes 
99% CPU

  PID  PPID USER     STAT   VSZ %MEM %CPU COMMAND
13419 13418 nobody   R    39172   8%  99% postgres --single -P -D 
/var/db -p 5433 xdb
It's been running from 10 minutes still there is no output or logs.


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

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Kevin Grittner wrote:
<blockquote cite="mid4BE3E9BA0200002500031394@gw.wicourts.gov"
 type="cite">
  <pre wrap="">Siddharth Shah <a class="moz-txt-link-rfc2396E" href="mailto:siddharth.shah@elitecore.com">&lt;siddharth.shah@elitecore.com&gt;</a> wrote:
 
  </pre>
  <blockquote type="cite">
    <blockquote type="cite">
      <blockquote type="cite">
        <pre wrap="">xdb=# reindex index pg_class_relname_nsp_index;

Now INDEXing taking High CPU and postgres baffled.
        </pre>
      </blockquote>
    </blockquote>
  </blockquote>
  <pre wrap=""><!----> 
  </pre>
  <blockquote type="cite">
    <blockquote type="cite">
      <pre wrap="">consider doing your recovery in single-user mode
      </pre>
    </blockquote>
  </blockquote>
  <pre wrap=""><!----> 
  </pre>
  <blockquote type="cite">
    <pre wrap="">    postgres --single -P -D $DATADIR -p 5433 xdb
    Same behavior in single mode.
    </pre>
  </blockquote>
  <pre wrap=""><!----> 
How long did you leave it running?  Did you get any messages?  Is
there anything in the log?  What do CPU usage and disk usage look
like during the attempt?
 
-Kevin
  </pre>
</blockquote>
<br>
Kevin, It start normally , I have successfully retrieved data from few
tables<br>
But I am not able to do dS / dT or dt. As you said this is index file
for Postgres tables and indexes<br>
Now when I taken call for reindex&nbsp;&nbsp; pg_class_relname_nsp_index it takes
99% CPU<br>
<br>
&nbsp; PID&nbsp; PPID USER&nbsp;&nbsp;&nbsp;&nbsp; STAT&nbsp;&nbsp; VSZ %MEM %CPU COMMAND<br>
13419 13418 nobody&nbsp;&nbsp; R&nbsp;&nbsp;&nbsp; 39172&nbsp;&nbsp; 8%&nbsp; 99% postgres --single -P -D
/var/db -p 5433 xdb <br>
It's been running from 10 minutes still there is no output or logs.<br>
<br>
<pre wrap="">
</pre>
</body>
</html>

--------------000706090009010603040401--



From tgl@sss.pgh.pa.us Fri May  7 15:54:40 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 5CF60632EA2
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri,  7 May 2010 12:54:40 -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 53531-02 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri,  7 May 2010 15:54:33 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id 3D5FA632436
	for <pgsql-admin@postgresql.org>; Fri,  7 May 2010 12:54:33 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o47FsPij014083;
	Fri, 7 May 2010 11:54:25 -0400 (EDT)
To: Siddharth Shah <siddharth.shah@elitecore.com>
cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>, pgsql-admin@postgresql.org
Subject: Re: ERROR: XX001 (Critical and Urgent) 
In-reply-to: <4BE434D7.4050205@elitecore.com> 
References: <4BE3C93C.70800@elitecore.com>
	<4BE3D0EF020000250003137D@gw.wicourts.gov>
	<4BE41AE1.4010804@elitecore.com>
	<4BE3DB1D020000250003138A@gw.wicourts.gov>
	<4BE42B16.8060804@elitecore.com>
	<4BE3E9BA0200002500031394@gw.wicourts.gov>
	<4BE434D7.4050205@elitecore.com>
Comments: In-reply-to Siddharth Shah <siddharth.shah@elitecore.com>
	message dated "Fri, 07 May 2010 21:12:15 +0530"
Date: Fri, 07 May 2010 11:54:25 -0400
Message-ID: <14082.1273247665@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201005/105
X-Sequence-Number: 34578

Siddharth Shah <siddharth.shah@elitecore.com> writes:
>   PID  PPID USER     STAT   VSZ %MEM %CPU COMMAND
> 13419 13418 nobody   R    39172   8%  99% postgres --single -P -D 
> /var/db -p 5433 xdb
> It's been running from 10 minutes still there is no output or logs.

What does "strace" show that process is doing?

			regards, tom lane