Data corruption: signals and careful response · PostgreSQL

Everything in this lesson was produced by writing eight bytes — the string CORRUPTX — into byte offset 4000 of block 5 of a heap file, with the cluster stopped.

The signals

ERROR:  invalid page in block 5 of relation "base/5/16384"
LOG:    page verification failed, calculated checksum 23084 but expected 18652
ERROR:  could not read block N in file "...": read only 0 of 8192 bytes
ERROR:  missing chunk number 0 for toast value ... in pg_toast_...
WARNING: concurrent insert in progress within table "..."
ERROR:  index "..." contains unexpected zero page at block N
ERROR:  found xmin ... from before relfrozenxid ...

Softer signals, which is where it usually starts:

A query returns different results on the primary and a standby.
A query returns different results with and without an index.
checksum_failures non-zero in pg_stat_database — lesson XVI-03 said alert on this at any value.
Rows that violate a constraint the database is supposedly enforcing.
The operating system logging I/O errors.

What was measured

Data-loss riskthe query, after eight bytes were changed— EXECUTION VALIDATED on 18.6, cluster built with --data-checksums.

$ SELECT count(*) FROM victim;

ERROR:  invalid page in block 5 of relation "base/5/16384"

-- server log:
LOG:  page verification failed, calculated checksum 23084 but expected 18652

Read-only / Safecorruption is localised, and therefore partly invisible— EXECUTION VALIDATED on 18.6. A query not touching block 5.

$ SELECT count(*) FROM victim WHERE id = 1;

 count
-------
   1

Most of the table still reads fine. The damage surfaces only when something touches the affected block, which is why corruption is frequently discovered by an unrelated query weeks after it happened.

A corruption check can pass on a corrupt table

This was discovered by accident and is the most operationally useful result in this part.

With the corrupt page still resident in shared buffers:

Read-only / Safeamcheck passing on a table that is corrupt— EXECUTION VALIDATED on 18.6. 267 blocks of the table were cached at the time.

$ SELECT count(*) FROM pg_buffercache b JOIN pg_class c
ON c.relfilenode = b.relfilenode WHERE c.relname = 'victim';
SELECT bt_index_parent_check('victim_pkey', heapallindexed => true);

 victim_blocks_cached
----------------------
                267

bt_index_parent_check
-----------------------
                        <- PASSED

The cluster was then restarted, emptying shared buffers, and the same check was run again:

Data-loss riskthe identical check, after a restart— EXECUTION VALIDATED on 18.6.

$ SELECT bt_index_parent_check('victim_pkey', heapallindexed => true);

ERROR:  invalid page in block 5 of relation "base/5/16384"

Checksums are verified when a page is read from disk, not when it is read from shared buffers.

Two consequences that change how you investigate:

A check that passes on a warm running cluster may simply have been reading memory. It is not evidence of health.
A corrupt page already resident is served without complaint until it is evicted and re-read.

Verify against a restored copy, or after a restart, or with pg_checksums against the files on a stopped cluster. Not against a warm production instance.

Responding

The instinct is to fix it. Almost everything that feels like fixing it destroys evidence or makes recovery harder.

First: stop making it worse

Do not restart unless you must. A restart may evict the corrupt page and turn a working system into a failing one — and it destroys the shared-memory state.
Do not VACUUM FULL or REINDEX the affected object. Both rewrite, and a rewrite from corrupt input produces corrupt output with the original gone.
Do not zero_damaged_pages. It discards data by design.
Do not fail over. If the corruption came from PostgreSQL or replicated, the standby has it too.

Second: capture

# take a physical copy before anything else
pg_ctl stop -m fast     # only if you are certain, and only after deciding
cp -a $PGDATA /safe/place/pgdata-$(date +%s)

A copy taken now is the only thing that lets you try more than one recovery approach.

Third: establish the extent

-- which relation is base/5/16384?
SELECT c.relname, n.nspname FROM pg_class c
  JOIN pg_namespace n ON n.oid = c.relnamespace
 WHERE c.relfilenode = 16384;

-- has this happened before?
SELECT datname, checksum_failures, checksum_last_failure
  FROM pg_stat_database WHERE checksum_failures > 0;

And on a stopped cluster or a copy:

pg_checksums --check -D $PGDATA

Read-only / Safepg_checksums finding exactly one bad block— EXECUTION VALIDATED on 18.6 against the stopped cluster.

$ pg_checksums --check -D /var/lib/postgresql/cor

pg_checksums: error: checksum verification failed in file
".../base/5/16384", block 5:
calculated checksum 5A2C but block contains 48DC
Files scanned:   955
Blocks scanned:  3212
Bad checksums:  1

One block out of 3,212, named exactly. That is the number that decides what to do next.

Fourth: decide

Situation	Action
A good backup exists, and the RPO is acceptable	Restore. The clean answer
No usable backup	Extract what you can, below
Corruption is spreading	Hardware. Move off it first
One index affected	`REINDEX` — indexes are derived, so this is safe
Heap affected	Restore. The heap is the data

ignore_checksum_failure, and why the measurement matters

Data-loss riskwhat the setting actually does— EXECUTION VALIDATED on 18.6 on the corrupt table.

$ SET ignore_checksum_failure = on; SELECT count(*) FROM victim;

WARNING:  ignoring checksum failure in block 5 of relation "base/5/16384"
count
-------
20000

The query now succeeds and returns a number computed from a page known to be damaged.

The setting converts a detected error into a warning nobody reads and an answer nobody can trust. It exists for exactly one purpose: extracting what can be extracted from a cluster you have already decided is lost, into a new cluster, knowing the result is suspect.

It is not a way to keep working, and leaving it on in a production configuration means the next corruption is silent.

Extracting, when there is no backup

Last resort, on a copy, accepting that the result is not trustworthy:

-- 1. find which rows are unreadable, by ctid, block by block
--    and dump everything around them
-- 2. only if that fails:
SET zero_damaged_pages = on;   -- DISCARDS the damaged pages entirely

zero_damaged_pages replaces unreadable pages with empty ones. The table becomes readable and the rows in those pages are gone, with no record of what they were. It is a way to rescue 99% of a table at the cost of silently losing 1%, and every downstream consumer of that data now has a hole nobody can characterise.

Where corruption comes from, and why it matters for the response

The source determines the response, and it is worth establishing before acting.

Storage. A failing disk, a controller with a dead battery, a firmware bug, cosmic-ray bit flips. Signature: corruption appears in unrelated objects over time, and the operating system usually logs I/O errors. The hardware must be replaced before restoring, or the restore lands on the same problem.

A lying fsync. Lesson XVIII-04’s subject. A crash then leaves pages that never reached disk while WAL says they did. Signature: corruption appears after a crash or power loss, in objects being written at the time.

A PostgreSQL bug. Rare and real. Signature: the minor release notes describe it. Lesson XVII-05 covered why the “Migration to Version X.Y” section must be read — the fix stops it recurring and does not repair what happened.

Operator error. Copying a data directory from a running cluster without the backup API (lesson XIII-03), running two postmasters against one directory, restoring a partial file copy. Signature: it appeared immediately after someone did something.

A collation change. Not corruption of bytes at all, and it behaves like corruption: an operating system upgrade changes text ordering and existing indexes no longer match the collation, so index scans and sequential scans return different results. Signature: wrong answers with no error anywhere, and it is the one this list most often misses because nothing is damaged. PostgreSQL 18 records collation version information and warns when it differs, which is what turns it from invisible into detectable.

That last case is worth holding onto because the response is completely different: nothing needs restoring, the affected indexes need rebuilding. Restoring from a backup taken before the OS upgrade reintroduces indexes built under the old collation, which does not help and looks like the corruption “coming back”.

What to take from this

Corruption is localised. Most of the object still reads, which is why it is found late.
A check that passes on a warm cluster may have been reading shared buffers. Measured: amcheck passed, then failed after a restart.
Verify against a restored copy, after a restart, or with pg_checksums on a stopped cluster.
Do not restart, rewrite, reindex the heap, zero pages, or fail over before understanding it.
Copy the data directory first. It is what lets you try twice.
pg_checksums --check names the exact bad blocks. Measured: 1 of 3,212.
ignore_checksum_failure makes the database return answers from damaged pages. Measured.
Establish the source: storage, a lying fsync, a bug, operator error, or a collation change — the last needs reindexing, not restoring.

Cross-course references

Ceph & Distributed Storage — Part CXVIII (Data integrity incident) covers the same discipline in a storage system: take a copy first, and do not let a repair destroy the evidence.
Linux for Production Sysadmins — Part LXXXI (Incident command) covers the authorisation this class of response needs, and Part LXXXII (Root cause analysis) covers establishing the source rather than stopping at the symptom.
Observability for Production Sysadmins — Part LIX (Database observability) covers alerting on checksum_failures, which should never be non-zero.