Re: Detecting database corruption

On Wed, 14 Jan 2004, Jack Orenstein wrote:

> My company is developing a PostgreSQL 7.4 application.  We don't want
> our customers to have to manage the database, so we're automating as
> much maintenance as possible. If the database ever becomes corrupt,
> we have procedures for restoring the database from backup data.
> The question we've been wondering about is how to detect a corrupt
> database. False positives are acceptable (database isn't really
> corrupt but we think it is); false negatives are not, (database
> is corrupt but we can't tell).
>
> - Are any cases of corruption detected in the normal operation of
> PostgreSQL?
>
> - If there are, then how are applications notified when corruption
> is detected?
>
> - What symptoms should prompt us to suspect and check for corruption?
>
> - Are there any tools we can run to determine whether a database is
> corrupt?

Just a couple of points:

99.9% of all the corruption I've ever seen on postgresql servers has been
bad hardware.  Test your hardware well, and you shouldn't have much, if
any, of a problem.

If postgresql gets corrupted, it's usually an individual table that it
will show up in, but could be a transaction log or something similar as
well.  Generally the errors say things about not being able to read a
block, etc...  I'd suggest looking through the source code for all the
error messages and harvesting the ones that look like they'd show up if a
table / index etc got corrupted.