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(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828