Message-ID: <5091E377.2030505@ringerc.id.au>
Date: Thu, 01 Nov 2012 10:50:31 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:16.0) Gecko/20121016 Thunderbird/16.0.1
MIME-Version: 1.0
To: Raj Gandhi <raj01gandhi@gmail.com>
CC: pgsql-admin@postgresql.org
Subject: Re: Detecting DB corruption
References: 
 <CALU_HCPC7Vq60cq7=U7KDUdNGWwBbAhNvHb05EF5iOfvAc16Mw@mail.gmail.com>
In-Reply-To: 
 <CALU_HCPC7Vq60cq7=U7KDUdNGWwBbAhNvHb05EF5iOfvAc16Mw@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

On 11/01/2012 08:01 AM, Raj Gandhi wrote:
> 
> I'm looking for ways to detect DB index and any other type of corruption
> in DB.  It looks like there is no tool to verify if Postgres DB is
> corrupted or not.

There is no database verifier tool. One would be quite nice to have for
testing and development purposes, though I question whether corruption
should be a concern in production. If you face the realistic risk of
database corruption, you need to urgently address the problems in your
setup that make that possible.

I wrote a bit about that a while ago:

http://blog.ringerc.id.au/2012/10/avoiding-postgresql-database-corruption.html

Seriously, if you're facing DB corruption then something is already
horribly wrong with your setup.

PostgreSQL isn't like MySQL with MyISAM; corruption is not routine and
part of life. It's a sign of a very bad problem, one you should diagnose
and fix not paper over. Do you expect ext3 file system corruption
routinely? No? PostgreSQL should be the same.

> I would like to detect some of the following DB corruptions:
>   - corruption in DB indexes

A query that scans the whole index (say, to sort on it) should generally
find damaged pages in indexes. "Corruption" can cover many different
things, though, and some damage would not be detected by simply using
the index.

>   - detect duplicate primary keys in a table (noticed in couple of
> instances where duplciates keys were found becuase of  corrupted indexes)

A REINDEX will find that. Alternately, you might be able to formulate
queries that ignore the indexes and do duplicate searches by grouping by
the primary key with `enable_indexscan = off`, `enable_indexonlyscan =
off`, etc etc.

>   - any page or block is corrupted

I'd want to use the `pageinspect' extension to scan the table manually.
Create some artificially damaged blocks in a just-for-testing table and
make sure that doing so actually finds them.

> Using Postgres 8.3.18 on Linux. Database has around 100 tables with
> average rows in a table are 500.

Well, you're on an old version, but not one with any known serious
issues AFAIK.

--
Craig Ringer