Re: ERROR: XX001 (Critical and Urgent)

[Siddharth Shah <siddharth.shah@xxxxxxxxxxxxx>]

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