pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedERROR: XX001 (Critical and Urgent)
9+ messages / 3 participants
[nested] [flat]
* ERROR: XX001 (Critical and Urgent)
@ 2010-05-07 08:03 Siddharth Shah <siddharth.shah@elitecore.com>
0 siblings, 1 reply; 9+ messages in thread
From: Siddharth Shah @ 2010-05-07 08:03 UTC (permalink / raw)
To: pgsql-admin; pgsql-novice@postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ERROR: XX001 (Critical and Urgent)
@ 2010-05-07 13:35 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Siddharth Shah <siddharth.shah@elitecore.com>
0 siblings, 1 reply; 9+ messages in thread
From: Kevin Grittner @ 2010-05-07 13:35 UTC (permalink / raw)
To: Siddharth Shah <siddharth.shah@elitecore.com>; pgsql-admin; pgsql-novice@postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ERROR: XX001 (Critical and Urgent)
@ 2010-05-07 13:51 Siddharth Shah <siddharth.shah@elitecore.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 9+ messages in thread
From: Siddharth Shah @ 2010-05-07 13:51 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ERROR: XX001 (Critical and Urgent)
@ 2010-05-07 14:19 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Siddharth Shah <siddharth.shah@elitecore.com>
0 siblings, 1 reply; 9+ messages in thread
From: Kevin Grittner @ 2010-05-07 14:19 UTC (permalink / raw)
To: Siddharth Shah <siddharth.shah@elitecore.com>; +Cc: pgsql-admin
[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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ERROR: XX001 (Critical and Urgent)
@ 2010-05-07 15:00 Siddharth Shah <siddharth.shah@elitecore.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 2 replies; 9+ messages in thread
From: Siddharth Shah @ 2010-05-07 15:00 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ERROR: XX001 (Critical and Urgent)
@ 2010-05-07 15:09 Siddharth Shah <siddharth.shah@elitecore.com>
parent: Siddharth Shah <siddharth.shah@elitecore.com>
1 sibling, 0 replies; 9+ messages in thread
From: Siddharth Shah @ 2010-05-07 15:09 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-admin
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
>>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ERROR: XX001 (Critical and Urgent)
@ 2010-05-07 15:21 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Siddharth Shah <siddharth.shah@elitecore.com>
1 sibling, 1 reply; 9+ messages in thread
From: Kevin Grittner @ 2010-05-07 15:21 UTC (permalink / raw)
To: Siddharth Shah <siddharth.shah@elitecore.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ERROR: XX001 (Critical and Urgent)
@ 2010-05-07 15:42 Siddharth Shah <siddharth.shah@elitecore.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 9+ messages in thread
From: Siddharth Shah @ 2010-05-07 15:42 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: ERROR: XX001 (Critical and Urgent)
@ 2010-05-07 15:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: Siddharth Shah <siddharth.shah@elitecore.com>
0 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2010-05-07 15:54 UTC (permalink / raw)
To: Siddharth Shah <siddharth.shah@elitecore.com>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-admin
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2010-05-07 15:54 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-05-07 08:03 ERROR: XX001 (Critical and Urgent) Siddharth Shah <siddharth.shah@elitecore.com>
2010-05-07 13:35 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-05-07 13:51 ` Siddharth Shah <siddharth.shah@elitecore.com>
2010-05-07 14:19 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-05-07 15:00 ` Siddharth Shah <siddharth.shah@elitecore.com>
2010-05-07 15:09 ` Siddharth Shah <siddharth.shah@elitecore.com>
2010-05-07 15:21 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-05-07 15:42 ` Siddharth Shah <siddharth.shah@elitecore.com>
2010-05-07 15:54 ` Re: ERROR: XX001 (Critical and Urgent) Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox