Re: Data corruption zero a file - help!!

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":

CAUTION this will permanently remove the data defined in the bad block

1. To identify which file(s) the relation is in:
1. <THE SELECT STATEMENTS TO COLLECT THIS DATA> I can't
remember how I did it, but will keep hunting for my notes :)
2. so the file(s) are $PGDATA/<databaseDIR>/<relFile>.* The *
is any number which is defined below
2. To calculate the * value:
1. SELECT <block> / 131072 AS filenum, <block> % 131072 AS
blocknum;
filenum | blocknum
-----------+----------------
<filenum> | <blocknum>
1. 131072 comes from "each database file is 1G, or 131072
* 8k blocks"
2. The block size is determined when compiling postgres
3. Use "SHOW block_size in the database or use
pg_controldata from the shell." to confirm this. The default is 8k.
3. Now you need to re-zero this block using the following command:
1. dd bs=8k seek=<blocknum> conv=notrunc count=1 if=/dev/zero
of=$PGDATA/base/<databaseDIR>/<relFile>.<filenum>
1. Before you do this it is best to backup the block:
"dd bs=8k skip=<blocknum> count=1 if=/path/file | hd"
See this post:
http://archives.postgresql.org/pgsql-general/2006-03/msg00002.php

Your thoughts / comments...

Cheers
Noel

Michael Fuhr wrote:
> On Thu, Mar 09, 2006 at 12:37:52PM +1100, Noel Faux wrote:
>
>> I've been watching the post: Re: [GENERAL] Fixing up a corrupted toast table
>> In there they mention deletion of the bad rows from the table based on
>> the citid. If I could come up with a def of a back row, would this
>> work, or are there other issues?
>>
>
> If you have a corrupt tuple within an otherwise good block then you
> can try deleting that tuple, but if the block header is corrupt
> then you have no way of addressing any of that block's tuples.
> Errors implying a bad tuple include "missing chunk number" and
> "invalid memory alloc request size"; but "invalid page header in
> block" means the block itself is bad.
>
>