Re: Data corruption zero a file - help!!

On Thu, Mar 09, 2006 at 03:57:46PM +1100, Noel Faux wrote:
> Given that this seems problem has occurred a number of times for a
> number I've written a small step by step procedure to address this
> issue. Is there any other comments you which to add. I was thinking
> that this should be added to the FAQ / troubleshooting in the docs.
>
> How to repair corrupted data due to "ERROR: invalid page header in block
> X of relation "Y":

In pgsql-novice Christopher Goodfellow mentioned the zero_damaged_pages
option that I had forgotten about. If you don't care about examining
the bad pages then you could set this option in a session and execute
a statement that hits every page in the file (Tom Lane mentioned
VACUUM and SELECT COUNT(*)). Here's the example I posted in reply:

test=# select count(*) from foo;
ERROR: invalid page header in block 10 of relation "foo"
test=# set zero_damaged_pages to on;
SET
test=# select count(*) from foo;
WARNING: invalid page header in block 10 of relation "foo"; zeroing out page
WARNING: invalid page header in block 20 of relation "foo"; zeroing out page
WARNING: invalid page header in block 30 of relation "foo"; zeroing out page
count
-------
9445
(1 row)

test=# set zero_damaged_pages to off;
SET

--
Michael Fuhr