Re: Recovering data from table show corruption with "invalid page header in block X"

On Feb 9, 2010, at 5:00 PM, Jeff Davis wrote:

> On Tue, 2010-02-09 at 15:28 -0800, Erik Jones wrote:
>> * Set zero_damaged_pages=on, run query that originally showed the
>> corruption. This reports 3 different blocks with invalid page headers
>> and reports that they are being zero'd out. Unfortunately,
>> subsequently querying the table the same blocks show as corrupt.
>> Well, after running the query twice with zero_damaged_pages=on the
>> first one did go away but the other two remain.
>
> You probably already did this, but remember to back up your $PGDATA
> directory.
>
> The only thing that I can think of is that the pages are not being
> marked as dirty after being zeroed, so it evicts the zeroed page without
> actually writing it to disk. That combined with the ring buffer for
> sequential scans (which eliminates cache pollution by only using a few
> blocks for a sequential scan) would explain why even subsequent queries
> encounter the damaged page again.
>
> VACUUM with zero_damaged_pages on would probably do the trick.
>
> It's possible that this is a bug. What version are you on?

Not sure if it's a bug. Version is 8.3.5 the issue sticks when starting a copy of the data directory with 8.3.8.

Anyways, I realized that the dump run with zero_damaged_pages does actually finish. Also, I found that I can actually select all of the data by doing per-day queries to cause data access to be done via index scans since there is a date column indexed; I'm guessing that's because that avoids having to read the data pages' headers? Regardless, I now have two different ways to view the data and decide which works best if there are differences.

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k