Thread: Recovering data from table show corruption with "invalid page header in block X"
Recovering data from table show corruption with "invalid page header in block X"
From
Erik Jones
Date:
Greetings, We've recently had database server crash due to a heavy duty disk failure and upon rebooting we now have a table showingcorruption via the "invalid page header in block X" message when querying one table in particular, the rest have allchecked out via full table dumps with no errors. Here is what we've done so far to try to fix the table and/or recover the good data from it: * REINDEX -- no good. * Set zero_damaged_pages=on, run query that originally showed the corruption. This reports 3 different blocks with invalidpage headers and reports that they are being zero'd out. Unfortunately, subsequently querying the table the sameblocks show as corrupt. Well, after running the query twice with zero_damaged_pages=on the first one did go away butthe other two remain. * Set zero_damaged_pages=on for pg_dump. Same results as above. I'm unsure what to do next to get those damaged pages cleared out... Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k
Re: Recovering data from table show corruption with "invalid page header in block X"
From
Jeff Davis
Date:
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
Re: Recovering data from table show corruption with "invalid page header in block X"
From
Erik Jones
Date:
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 selectall of the data by doing per-day queries to cause data access to be done via index scans since there is a date columnindexed; I'm guessing that's because that avoids having to read the data pages' headers? Regardless, I now have twodifferent 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
Re: Recovering data from table show corruption with "invalid page header in block X"
From
Jeff Davis
Date:
On Tue, 2010-02-09 at 17:14 -0800, Erik Jones wrote:
> Anyways, I realized that the dump run with zero_damaged_pages does
> actually finish.
Yeah, it should finish, it's just a question of whether the warnings
continue, and if you need to keep zero_damaged_pages on to keep reading.
> 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?
Hmm... I don't think that will actually avoid the issue. My guess is
that those pages happened to be cached from an earlier read with
zero_damaged_pages on. An index scan does not use the ring buffer I was
talking about, so the pages are more likely to stay in cache much
longer. I believe that's what's happening, and the issue is just more
hidden than before.
Regards,
Jeff Davis