postgres index contains "unexpected zero page at block" exceptions

Question

I have following errors in pg_log file several thousand times. How to resolve them.

index "meeting_pkey" contains unexpected zero page at block 410.Please REINDEX it.
index "faultevent_props_pkey" contains unexpected zero page at block 37290.
index "faultevent_pkey" contains unexpected zero page at block 1704

Boilermaker

so far only indexes - viable, just rebuild them. next much more important identify how you killed those pages. do youhave backups?.. – Hamfurd

I don't have any backup. I am new to postgres. In which scenario we will get this kind of issue.This we are facing on of the perfomance check environment. – Boilermaker

pages get corrupted on hardware failure mostly. – Hamfurd

Answer 1

The cause of the issue is due to bad index pages and its unable to read it.

Reindex the problematic index to overcome the issue.

 Reindex index <schema_name>.<index_name>;

Here you have some hits.

Charlsiecharlton

Answer 2

Your database is corrupt.

Try to run pg_dumpall to get a logical dump of the database.

If that fails, buy support from somebody who can salvage data from corrupted databases.

If it succeeds:

• Check the hardware, particularly storage and RAM.
• Once you are certain the hardware is ok, install the latest patch update for your PostgreSQL version.
• Create a new database cluster with initdb.
• Restore the dump into the new cluster.

Did you have crashes recently?
Did you test if your storage handles fsync requests properly?
Do you have any dangerous settings like fsync = off?

Jail

Did you have crashes recently? No Did you test if your storage handles fsync requests properly? How to test it? Do you have any dangerous settings like fsync = off? its on. – Boilermaker

Testing reliable storage is not trivial, but if there were no crashes it cannot be the cause of the problem. – Jail

Answer 3

I ran into this issue and after a lot of reading I decided to do a complete DB reindex:

reindex DATABASE <DATABASE NAME>

and it solved the issue for me. Hope this helps you.

Reck