Upon restarting the postgresql database, it failed with the following error.
invalid page header in block xxxxx of relation pg_tblspc/16385/pg_9.0_201106101/16386/17694
Hint: This probably means that some data is corrupted and you will have to use the last backup for recovery
Turn on the zero_damaged_pages flag in postgresql.conf or within the psql prompt.
Add the following or change it from OFF to ON within postgresql.conf
zero_damaged_pages = on
show zero_damaged_pages;
(this probably state OFF)
SET zero_damaged_pages = on;
vacuum full <TABLE>;
reindex table <TABLE>;
This typically triggered after disk filled up where the postgresql WAL and database mounted together.. I am really not sure what caused this. Perhaps, unplanned power outage. It basically just mean corruption happened within the table and the flag simply telling the db engine to skip it. This seems happened quite often. If it can be restored with good backup, that would be more ideal as the workaround still consist of corruption data within. It has been reported that by simply turning the zero_damaged_pages to ON, it simply work again even without the VACUUM FULL and REINDEX. I have also encountered a situation where executing reindex prior to vacuum full, it did not work but reversing the order as the example shown above worked.
No comments:
Post a Comment