Data File Corruption

Scenario

After an unexpected server shutdown during heavy writes, queries against the orders table return ERROR: invalid page header in block 4523 of relation base/12345/67890. Other tables work fine. pg_dump fails when it reaches this table. The server had fsync = on (correct) and data checksums enabled, which is how the corruption was detected. The corruption appears to be in a few pages of one heap file.

How to Identify

Conditions:

• ERROR: invalid page header in block N when querying a specific table
• ERROR: wrong page type or ERROR: checksum mismatch (if checksums enabled)
• pg_dump fails at the corrupted table
• pg_filedump shows bad page headers for specific blocks
• Only specific blocks affected (not entire table)

Analysis Steps

-- Find which table is corrupted (if you only have the error's block/relation info)
SELECT relname, relfilenode
FROM pg_class
WHERE relfilenode = 67890;   -- from the error message

-- Check if data checksums are enabled
SELECT current_setting('data_checksums');
-- 'on' = checksums enabled; corruption detected immediately on read
-- 'off' = corruption may go undetected for a long time (silent corruption)

-- Try to read pages from the table to find the extent of corruption:
SET zero_damaged_pages = off;  -- default: raises ERROR on bad pages
SELECT count(*) FROM orders;   -- will error at the first bad page

-- Try with zero_damaged_pages=on (zero out bad pages, continue reading):
SET zero_damaged_pages = on;
SELECT count(*) FROM orders;   -- returns count, bad pages silently zeroed
RESET zero_damaged_pages;

-- Check pg_catalog.pg_relation_filepath to find the file on disk:
SELECT pg_relation_filepath('orders');
-- Returns: 'base/12345/67890' (relative to $PGDATA)

Pitfalls

• zero_damaged_pages = on allows PostgreSQL to continue past corrupted pages but silently discards all data in those pages. Use only to recover what’s salvageable — do not leave this enabled.
• Data checksums catch corruption at read time. Without checksums, corrupted pages are silently used until the data is actually wrong.
• Trying to VACUUM or REINDEX a table with corruption will fail or produce a broken index. Fix the table first.
• pg_filedump (external tool) can inspect raw page contents without going through PostgreSQL — useful for forensics.
• After recovering from corruption, verify the recovered data against a backup or secondary — rows from corrupted pages are lost even with zero_damaged_pages = on.

Resolution Approach

Restore the corrupted table from the most recent backup using PITR or pg_dump restore. If a backup isn’t available: use zero_damaged_pages = on to extract surviving rows into a new table, then rebuild the original. Always enable data checksums (initdb --data-checksums) to detect future corruption early.