PostgreSQL: invalid page header in block

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 t开发者_JAVA技巧here a way to tell PSQL to skip this block?

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.

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.

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.

these are almost always hardware problems btw. Verify and test RAM, disk, CPU. Make sure your environment is good (bad power input can cause problems as can overheating). That's the best way to prevent it. Best way to address it is point in time recovery from a base backup.

If you have a slave, set hot_standby_feedback to 'on' on it if not already. Do pg_dump and write it to /dev/null so that you don't consume any space. nohup pg_dump db_name -v -Fc -f /dev/null & If the dump succeeds, then your slave is fine. Do a failover. There will be no data loss.

Another way to validate your slave is to do, explain select count(*) from table_name; If it succeeds and if it is using a sequence scan, then your slave is good. You may not have to consider this option if it is using index scan.

Note: This works only if your master is affected with storage level corruption.

I happened to face same issue just today and i was able to fix it.