postgresql: 'invalid page header' message even after drop and recreate

Question

My Ubuntu 14.04 LTS server running postgresql 8.4.11 crashed, and after a reboot the application server running a query against postgresql reported the following error

ERROR: invalid page header in block 40 of relation base/18038/22194 

I googled, tried to create a dump, and used the resulting error message to identify a broken table. I then executed

SET zero_damaged_pages = on; VACUUM FULL damaged_table; -- vacuum didn't report any errors here REINDEX TABLE damaged_table; 

others mentioned reindexdb, so I ran

$ reindexdb -s mydb 

as well, but the problem still stayed the same.

Finally - I got a working backup from the database - I dropped and recreated the whole database

DROP DATABASE mydb; CREATE DATABASE mydb; 

and restored the backup:

cat mydump| sudo -u postgres pgsql mydb 

But even now the problem is still the same. I don't know much about postgresql, but how can such an error survive a recreation of the database? Unfortunately I need to get the database working on the same machine again.

Is there anything else I can try to get the database working again?

Answer 1

This sounds like you have corruption of some sort on your filesystem or disk.

I'd recommend looking here: postgresql corruption

Following that, I'd look in dmesg or /var/log/messages for any hardware errors you might see.

If you got a working dump, I'd copy the current data directory away from /var/lib/postgresql/8.4/main, run pg_createcluster 8.4 main on a new directory, and try restoring there, and seeing if that helps fix any lingering issues.

If you're still having troubles after that, going onto #postgresql on Freenode IRC, or [email protected] would be my next step, if I had to get this up and running as fast as possible.

Hope that helps. Good luck. =)

Answer 2

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.