Parameter · Developer Options
zero_damaged_pages
Continues processing past damaged page headers. Detection of a damaged page header normally causes PostgreSQL to report an error, aborting the current transaction. Setting "zero_damaged_pages" to true causes the system to instead report a warning, zero out the damaged page, and continue processing. This behavior will destroy data, namely all the rows on the damaged page.
- Default:
off - Type: bool
- Applies: Session (superuser)
In plain words
Normally, when PostgreSQL reads a data page that fails its sanity checks (a garbage header, or a checksum mismatch), the query stops with an error, so you notice the damage. With this switch on, PostgreSQL instead warns, replaces the page in memory with an empty one, and carries on, as if the rows on that page never existed.
It is a last-resort tool for salvaging the rest of a damaged table. It destroys data on purpose: every row on a zeroed page is gone.
For DBAs and operators
Captured on 18.6 after overwriting the header of block 0 of a test table:
ERROR: invalid page in block 0 of relation "base/5/24695"SET zero_damaged_pages = on; SELECT count(*) …WARNING: invalid page in block 0 of relation "base/5/24695"; zeroing out pageThe count went from "error" to 0: the 50 rows on that page were silently dropped from the result. The zeroed page is only in shared buffers; it is not forced to disk, so on a later eviction the original bytes may come back (or be overwritten if the page is modified). The docs therefore recommend recreating the table or index before turning it off again. A careful procedure: copy the data directory, enable it in one session, copy the readable rows out (CREATE TABLE … AS SELECT or pg_dump -t), rebuild or truncate the damaged table, then turn it off.
It acts on every page that fails verification ("invalid page"): a broken header, and also, with data checksums on, a checksum mismatch (source: PageIsVerified(), bufmgr.c). For a page with a sane header but a wrong checksum, ignore_checksum_failure is the gentler alternative: it keeps the rows instead of zeroing them. In 18, with io_method = worker, the first report comes from the I/O worker that did the read (CONTEXT: I/O worker executing I/O on behalf of process 1986), and multi-block reads can report zeroing out N invalid pages among blocks ….
Danger. Rows on zeroed pages are gone for good, and indexes may still point at them. Find the root cause (storage, RAM, a filesystem bug) first; in support cases, ask for the exact error and a copy of the file before anyone enables this. Take a file-level copy of the data directory (or of the affected files) before touching it, and turn it off again as soon as you are done.
How to change it
A superuser (or a role granted SET) can change it per session with SET.
See it in context
Related parameters
ignore_checksum_failure data_checksums full_page_writes ignore_system_indexes