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.