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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
