Обсуждение: ERROR invalid page header in block xxx of relation base/xxxxx/xxxxx/
Hi ,I am getting invalid page header error and what I could observe is when I select the table I get this error , where as if I select table and order by primary key I can retrieve the rows from table.And I don't see any dataloss ( based on total number records) after fixing the blocks using zero_damaged_pages=on and then vacuum full on the tablePlease note I have renamed few tables to avoid giving actual table namesWe run Postgres 9.2 version on Windows and Dell Optiplex XE2 server and we suspect that block corruption would have happen due to hardware/memory/power failures reasons and I have gone through wikik https://wiki.postgresql.org/wiki/Reliable_Writes. I want to understand why we can read the table through where there exists index and explain plan shows Index scan and with high cost compare to seq scan .I assume that since there no rows/data present in these corrupted blocks index scan skips these blocks and hence it is not throwing the error .Also , I want to know what would have caused the postgres to create these corrupted blocks andcan I reproduce this error ? appreciate if you share any pointers to blogs/mailing lists if this type of issue is already discussed ?create table a.parametertable_bak as select * from a.parametertable order by id;labs=# select count(*) from a.parametertable_bak ;count-------31415(1 row)labs=#labs=# checkpoint;CHECKPOINTlabs=# set zero_damaged_pages=on;SETlabs=# vacuum full a.parametertable;WARNING: invalid page header in block 204 of relation base/16413/16900; zeroing out pageWARNING: invalid page header in block 205 of relation base/16413/16900; zeroing out pageVACUUMlabs=# select count(*) from a.parametertable ;count-------31415(1 row)labs=#-Sreekanth
Jorge Torralba
----------------------------
Note: This communication may contain privileged or other confidential information. If you are not the intended recipient, please do not print, copy, retransmit, disseminate or otherwise use the information. Please indicate to the sender that you have received this email in error and delete the copy you received. Thank You.
Fwd: ERROR invalid page header in block xxx of relation base/xxxxx/xxxxx/
- zero_damaged_pages (boolean)
Detection of a damaged page header normally causes PostgreSQL to report an error, aborting the current transaction. Setting zero_damaged_pages to on causes the system to instead report a warning, zero out the damaged page in memory, and continue processing. This behavior will destroy data, namely all the rows on the damaged page. However, it does allow you to get past the error and retrieve rows from any undamaged pages that might be present in the table. It is useful for recovering data if corruption has occurred due to a hardware or software error. You should generally not set this on until you have given up hope of recovering data from the damaged pages of a table. Zeroed-out pages are not forced to disk so it is recommended to recreate the table or the index before turning this parameter off again. The default setting is off, and it can only be changed by a superuser.
Look into this setting.zero_damaged_pages = on;The docs should explain it.--On Wed, Dec 7, 2016 at 2:47 PM, sreekanth Palluru <sree4pg@gmail.com> wrote:Hi ,I am getting invalid page header error and what I could observe is when I select the table I get this error , where as if I select table and order by primary key I can retrieve the rows from table.And I don't see any dataloss ( based on total number records) after fixing the blocks using zero_damaged_pages=on and then vacuum full on the tablePlease note I have renamed few tables to avoid giving actual table namesWe run Postgres 9.2 version on Windows and Dell Optiplex XE2 server and we suspect that block corruption would have happen due to hardware/memory/power failures reasons and I have gone through wikik https://wiki.postgresql.org/wiki/Reliable_Writes. I want to understand why we can read the table through where there exists index and explain plan shows Index scan and with high cost compare to seq scan .I assume that since there no rows/data present in these corrupted blocks index scan skips these blocks and hence it is not throwing the error .Also , I want to know what would have caused the postgres to create these corrupted blocks andcan I reproduce this error ? appreciate if you share any pointers to blogs/mailing lists if this type of issue is already discussed ?create table a.parametertable_bak as select * from a.parametertable order by id;labs=# select count(*) from a.parametertable_bak ;count-------31415(1 row)labs=#labs=# checkpoint;CHECKPOINTlabs=# set zero_damaged_pages=on;SETlabs=# vacuum full a.parametertable;WARNING: invalid page header in block 204 of relation base/16413/16900; zeroing out pageWARNING: invalid page header in block 205 of relation base/16413/16900; zeroing out pageVACUUMlabs=# select count(*) from a.parametertable ;count-------31415(1 row)labs=#-SreekanthThanks,
Jorge Torralba
----------------------------
Note: This communication may contain privileged or other confidential information. If you are not the intended recipient, please do not print, copy, retransmit, disseminate or otherwise use the information. Please indicate to the sender that you have received this email in error and delete the copy you received. Thank You.
Forgot to loop communityHi Jorge/All,Thanks for the reply.As per the documentation , I think backend reads the this page header and reports that it is damaged.I am looking at ways we re-create this scenario of creation this blank corrupted page ? do I have any control over Backend and after it initializes a new page using function PageInit(Page page, Size pageSize, Size specialSize) and I want to halt/crash the backend by stopping postgres database or through Hardware/system crash ?Is this possible ?Also I see that based on below comments from Source code, having such pages in database is normal . is my understanding correct ?/*
- zero_damaged_pages (boolean)
Detection of a damaged page header normally causes PostgreSQL to report an error, aborting the current transaction. Setting zero_damaged_pages to on causes the system to instead report a warning, zero out the damaged page in memory, and continue processing. This behavior will destroy data, namely all the rows on the damaged page. However, it does allow you to get past the error and retrieve rows from any undamaged pages that might be present in the table. It is useful for recovering data if corruption has occurred due to a hardware or software error. You should generally not set this on until you have given up hope of recovering data from the damaged pages of a table. Zeroed-out pages are not forced to disk so it is recommended to recreate the table or the index before turning this parameter off again. The default setting is off, and it can only be changed by a superuser.
--On Thu, Dec 8, 2016 at 9:50 AM, Jorge Torralba <jorge.torralba@gmail.com> wrote:Look into this setting.zero_damaged_pages = on;The docs should explain it.--On Wed, Dec 7, 2016 at 2:47 PM, sreekanth Palluru <sree4pg@gmail.com> wrote:Hi ,I am getting invalid page header error and what I could observe is when I select the table I get this error , where as if I select table and order by primary key I can retrieve the rows from table.And I don't see any dataloss ( based on total number records) after fixing the blocks using zero_damaged_pages=on and then vacuum full on the tablePlease note I have renamed few tables to avoid giving actual table namesWe run Postgres 9.2 version on Windows and Dell Optiplex XE2 server and we suspect that block corruption would have happen due to hardware/memory/power failures reasons and I have gone through wikik https://wiki.postgresql.org/wiki/Reliable_Writes. I want to understand why we can read the table through where there exists index and explain plan shows Index scan and with high cost compare to seq scan .I assume that since there no rows/data present in these corrupted blocks index scan skips these blocks and hence it is not throwing the error .Also , I want to know what would have caused the postgres to create these corrupted blocks andcan I reproduce this error ? appreciate if you share any pointers to blogs/mailing lists if this type of issue is already discussed ?create table a.parametertable_bak as select * from a.parametertable order by id;labs=# select count(*) from a.parametertable_bak ;count-------31415(1 row)labs=#labs=# checkpoint;CHECKPOINTlabs=# set zero_damaged_pages=on;SETlabs=# vacuum full a.parametertable;WARNING: invalid page header in block 204 of relation base/16413/16900; zeroing out pageWARNING: invalid page header in block 205 of relation base/16413/16900; zeroing out pageVACUUMlabs=# select count(*) from a.parametertable ;count-------31415(1 row)labs=#-SreekanthThanks,
Jorge Torralba
----------------------------
Note: This communication may contain privileged or other confidential information. If you are not the intended recipient, please do not print, copy, retransmit, disseminate or otherwise use the information. Please indicate to the sender that you have received this email in error and delete the copy you received. Thank You.RegardsSreekanth--RegardsSreekanth