Re: 8.1 index corruption woes

On Jul 7, 2008, at 7:39 PM, Tom Lane wrote:
> Another point to keep in mind, if you are trying to analyze files
> belonging to a live database, is that what you can see in the  
> filesystem
> may not be the "current" contents of every page.  For typical access
> patterns it'd be unsurprising for the visible index pages to lag  
> behind
> those of the heap, since they'd be "hotter" and tend to stay in shared
> buffers longer.


None of the tests were done on the production database. Most of the  
checks were not done on a PITR restore; they were done on a SAN-level  
snapshot that had been run through the recovery process (startup  
postmaster on snapshot, let it recover, shut down).

I hadn't thought about checkpointing; I'll make sure to do that next  
time we take a snapshot.

We also analyzed a single table from a completely different (much  
larger) database. In that case the analysis was done on a PITR- 
recovered "slave" that was up and running, but nothing should have  
been writing to the table at all, and it would have been up long  
enough that it would have checkpointed after exiting PITR recovery  
(though IIRC there's a manual checkpoint done at exit of PITR  
recovery anyway). That check didn't show as many questionable index  
pointers, but there were some (again, the bulk of them were index  
pointers that were using the first line pointer slot in the index page).
-- 
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828