To clarify, when set on, every time it hits this error, postgres will
rezero that block?
Michael Fuhr wrote:
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
|
begin:vcard
fn:Noel Faux
n:Faux;Noel
org:Monash University;Biochemistry and Molecular Biology
adr:;;;Clayton;Vic;3800;Australia
email;internet:noel.faux@xxxxxxxxxxxxxxxxx
tel;work:+61 03 9905 1418
url:http://vbc.med.monash.edu.au/~fauxn
version:2.1
end:vcard
