PostgreSQL: invalid page header in block

Question

I'm getting an Error

ERROR:  invalid page header in block 411 of relation "t_value_time"

in my PostgreSQL database. This keeps happening on different machines. Is there a way to prevent it from happening, or at least telling PSQL to ignore the data on the invalid block and move on?

I'd rather lose the data from the block and have him skip over it, reading the rest of the data. Is there a way to tell PSQL to skip this block?

Answer 1

WARNING: You will lose some data!

We managed to get over it (crashed DEV VM) by issuing:

database=# SET zero_damaged_pages = on;
SET
database=# VACUUM FULL damaged_table;
WARNING: invalid page header in block xxx of relation base/yyy/zzz; zeroing out page
[..]
REINDEX TABLE damaged_table;

Fix via pwkg.ork.

Answer 2

Same block every time?

From what I've read, the most common cause of invalid blocks is hardware. Red Hat has a utility, pg_filedump, that formats "PostgreSQL heap, index, and control files into a human-readable form". I don't think they support any PostgreSQL version greater than 8.4.0, but I could be wrong.

You want to prove your hardware is good by using tough, thorough disk, RAM, and NIC diagnostics.

Answer 3

There's no simple way to do it, but it's reasonably easy to do just by editing the data file directly (relfilenode of the pg_class entry gives the filename).

Just copy a block from elsewhere in the file over the bad block. Ideally, synthesise an empty block or update the one you're overwriting to have no valid tuples in it.

Once you've got something that doesn't produce that error, dump the table and reload it for safety.