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.