zero_damaged_pages — PostgreSQL configuration parameter

Detection of a damaged page header normally causes PostgreSQL to report an error, aborting the current transaction.

At a glance

What it does

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. Only superusers and users with the appropriate SET privilege can change this setting.

(Description quoted from the official PostgreSQL documentation.)

How to apply a change

Can be set per session with SET, per role/database with ALTER ROLE/DATABASE ... SET, or globally in postgresql.conf.

Inspect the current value and source with SHOW zero_damaged_pages; or SELECT name, setting, unit, context, source FROM pg_settings WHERE name = 'zero_damaged_pages';.

Tuning guidance

This is a developer and debugging aid, not a performance knob. Leave it at the default in production; enable it only temporarily, on a non-production or carefully controlled system, while diagnosing a specific problem. Several options in this group add overhead, generate large volumes of log output, or can damage data if misused — turn them off again as soon as the investigation is done.

Reference

PostgreSQL documentation — zero_damaged_pages.