From the objective of OSS-DB Exam Gold
- Troubleshooting - Recovering damaged cluster
Sample Question
4.04
The following error message was output when referencing a certain user table.
ERROR: invalid page header in block 0 of relation base/16408/16421
Choose the most appropriate response to this situation.
-
Perform a VACUUM on the entire database.
-
Delete the relevant table files and restart PostgreSQL.
-
Set zero_damaged_pages to on and attempt to access the table again.
-
Execute CLUSTER on the relevant system table.
-
Start PostgreSQL in single-user mode and execute REINDEX INDEX on any indexes defined on the relevant table.
※This sample exam is different from those that appear in the actual OSS-DB Exam.
2025/06/02
Answer and Explanation
The error message in the question indicates corruption in the header information of the affected table's file.
With a corrupted header, SQL commands like those in options A, D, and E (which require scanning all pages of the table) cannot be executed. Furthermore, directly deleting the file and restarting, as suggested in option B, will only worsen the problem. Direct manipulation of PostgreSQL data files is strongly discouraged unless absolutely necessary.
Therefore, options A, B, D, and E are incorrect, making C the correct answer.
Option C involves zero_damaged_pages, which is a developer-oriented setting. Under normal circumstances, it should remain disabled (off) and only be enabled using SET statement when necessary, such as during file corruption recovery (as in this scenario).
=# SET zero_damaged_pages TO on;
When enabled (on), zero_damaged_pages fills pages identified as corrupted with zeros. This means that the data on those pages will be permanently lost. When using this setting, understand that only data on undamaged pages can be recovered; data on corrupted pages will be lost.
https://www.postgresql.org/docs/10/runtime-config-developer.html#GUC-ZERO-DAMAGED-PAGES
The correct answer is C.