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

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?

Regards,
Jeff Davis