Hi Shaun,
We reindexed all the primary and unique keys of all the tables, But we
did not reindex the tables. You think we should do that also ?
Yes, you need to reindex. Part of the problem with this kind of table corruption, is that PostgreSQL has applied data and index page modifications to the binary files themselves. Due to this, there are probably index rows pointing to incorrect or invalid data pages.
Reindexing the primary and unique indexes is a very good start, but any queries that use regular indexes can still return invalid or mismatched rows when compared to the primary/unique results. Those kind of mismatches can result in unexpected behavior from applications.
Thanks, we will reindex the tables too.
Also, do you think we should do a clean dump restore to eliminate all data inconsistencies.
Any inputs here? You think a pgdump and restore would help more ?
TIA,
- Karthik