Detecting DB corruption

From: Raj Gandhi <raj01gandhi(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Detecting DB corruption
Date: 2012-11-01 00:01:39
Message-ID: CALU_HCPC7Vq60cq7=U7KDUdNGWwBbAhNvHb05EF5iOfvAc16Mw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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.

I would like to detect some of the following DB corruptions:
- corruption in DB indexes
- detect duplicate primary keys in a table (noticed in couple of
instances where duplciates keys were found becuase of corrupted indexes)
- any page or block is corrupted

Planning to do the following on every restart of Postgres DB. Can someone
suggest if this is the write approach? Or, suggest better and faster
approach to detect the corruption.
- reindex database <dbname>
- for each table run : select count(*) from <table name> //to ensure
no rows are corrupted
- for each table run update: begin; update <table name> set
<col_name> = "value" ; rollback; //to update whole table and then
rollback the transactions
- run "vacuum analyze"

If indexes are corrupted then it will be re-built. For other types of
corruption, pg_dump will be restored from last known good backup.

How do I write a generic SQL script to detect the corruption, remove
duplicate rows etc.?

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

Thanks in advance for your help.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Craig Ringer 2012-11-01 02:50:31 Re: Detecting DB corruption
Previous Message Baptiste LHOSTE 2012-10-31 15:05:13 Autoanalyze of the autovacuum daemon ...