Recovering data from table show corruption with "invalid page header in block X"
Greetings,
We've recently had database server crash due to a heavy duty disk failure and upon rebooting we now have a table showing corruption via the "invalid page header in block X" message when querying one table in particular, the rest have all checked 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 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.
* 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
On Feb 9, 2010, at 3:28 PM, Erik Jones wrote:
Greetings,
We've recently had database server crash due to a heavy duty disk failure and upon rebooting we now have a table showing corruption via the "invalid page header in block X" message when querying one table in particular, the rest have all checked 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 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.
* 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...
Scratch that about the first damaged page going away, after a server restart it popped up again.
Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k
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
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
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