Re: database corruption - Mailing list pgsql-general

From Jeff Brenton
Subject Re: database corruption
Date
Msg-id F489AB573A749146B33461ECE080913A09DF6863@EXCHANGE-1.sandvine.com
Whole thread Raw
In response to Re: database corruption  (Chris <rfusca@gmail.com>)
List pgsql-general

I was looking at dropping the index and recreating.  Part of the reason that I restarted postgres was to enable WAL archiving so that I can migrate to a larger filesystem next week.  I’ve got a system with a 1.3T array that I will be migrating to.  This DB has been neglected and I am trying to fix it but things are arguing with me.

 

Will dropping the index have any negative consequences if the indexed table has a primary key associated with it?  I think not but want to be certain.   

 


From: Chris [mailto:rfusca@gmail.com]
Sent: Wednesday, April 08, 2009 10:08 PM
To: Jeff Brenton
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] database corruption

 

I would imagine you would have better luck dropping the index and recreating.  But considering you're 98% full on that drive, it looks like you're about to have other problems...

 

On Wed, Apr 8, 2009 at 8:32 PM, Jeff Brenton <jbrenton@sandvine.com> wrote:

I’ve encountered some db corruption after restarting postgres on my database server running 8.2.4.  I think that postgres did not shut down cleanly.  Postgres started appropriately but crashed 45 minutes later.  I used pg_resetxlog after the crash to get the db to start again but it appears that the database is not running properly now.  When users try to access some of the tables in the db they get the error below;

 

ERROR:  index "testrun_log_pkey" contains unexpected zero page at block 3155408

HINT:  Please REINDEX it.}> <SQL environment diagnostic: no diagnostic record

SQL connection is null

SQL statement diagnostic: XX002 7 {Error while executing the query;

ERROR:  index "testrun_log_pkey" contains unexpected zero page at block 3155408

HINT:  Please REINDEX it.}

 

I’ve attempted to re-index the pkey listed but after an hour it fails with

 

REINDEX INDEX testrun_log_pkey;

 

ERROR:  could not write block 1832079 of temporary file: No space left on device

HINT:  Perhaps out of disk space?

 

There is currently 14GB free on the disk that postgres is installed on.  Does anyone know what I can do to get the db up and running again? 

 

/dev/amrd0s1a    3.9G    2.7G    898M    75%    /

/dev/amrd0s1e    115G     43G     63G    40%    /backup

/dev/amrd1s1d    133G    748M    121G     1%    /wal

/dev/amrd2s1d    663G    596G     14G    98%    /db

/dev/amrd0s1d    3.9G    184M    3.4G     5%    /var

 




--
Chris Spotts
rfusca@gmail.com

pgsql-general by date:

Previous
From: "Jeff Brenton"
Date:
Subject: Re: database corruption
Next
From: "Joshua D. Drake"
Date:
Subject: Re: database corruption