Skip to main content
RunBook Academy

← All break/fix scenarios in PostgreSQL

advancedpg-corruption~50 min

One query started failing with invalid page in block 5, and somebody found a setting that made the error go away

Reported symptoms

  • ●A nightly reconciliation query began failing with ERROR: invalid page in block 5 of relation "base/5/16384"
  • ●Most queries against the same table continue to work normally, including primary-key lookups
  • ●The application is otherwise unaffected and no other table produces errors
  • ●An engineer found ignore_checksum_failure, set it, and the reconciliation query now completes
  • ●The reconciliation totals it produces have been used for two nights
  • ●The storage team reports a firmware update on the array eleven days ago and no reported errors since
  • ●A second cluster in the same estate, built with checksums disabled, shows no errors at all

Evidence

  • · The server log records LOG: page verification failed, calculated checksum 23084 but expected 18652 immediately before each ERROR
  • · pg_checksums --check against the stopped cluster reports checksum verification failed in file base/5/16384, block 5: calculated checksum 5A2C but block contains 48DC, with 1 bad checksum out of 3212 blocks scanned
  • · SELECT count(*) FROM victim fails, while SELECT count(*) FROM victim WHERE id = 1 succeeds, so the damage is confined to one block
  • · With ignore_checksum_failure on, the same count query returns 20000 and logs WARNING: ignoring checksum failure in block 5
  • · On the cluster built without checksums, the identical byte-level damage produced no error, no warning and no log entry
  • · On that cluster a row was returned containing the literal string CORRUPTX in the middle of its payload, at the correct length, from a query that reported success
  • · Repeating a query immediately after the first error sometimes succeeded, because the page was already resident in shared buffers
  • · data_checksums is on for the affected cluster and off for the second one
Diagnosis and resolutionclick to reveal

Root cause

A single 8 KB page was damaged below PostgreSQL. Data checksums detected it and PostgreSQL refused to serve it, which is the entire purpose of the feature. The block was corrupted outside the database's control — the storage firmware update eleven days earlier is the obvious candidate, though establishing that is the storage team's work, not the database team's. What matters for the database is that the page read back differs from the page written, and the checksum stored in the page header no longer matches its contents. Corruption is localised. `pg_checksums` found exactly one bad block out of 3,212 scanned. Queries that do not read block 5 succeed, which is why most of the application never noticed and why the damage was invisible for days. It is also why "the database is fine except for one report" is a dangerous reading: the damage is not smaller because fewer queries touch it. Setting `ignore_checksum_failure` did not repair anything. It converted a detected error into a warning nobody reads and an answer derived from a page known to be damaged. The reconciliation totals produced over the last two nights came from that page. They are not trustworthy and they must be withdrawn. That setting exists to extract what can be extracted from a cluster you have already decided is lost — a last resort during a salvage operation, with the results treated as suspect. It is not a way to keep working. The second cluster is the most important piece of evidence in this incident. Built with checksums disabled, it received identical byte-level damage and reported **nothing**: no error, no warning, no log entry, and no statistic. A query returned a row containing the literal string `CORRUPTX` in the middle of its payload, at the correct length, and reported success. Without checksums the database is a confident liar. With them it refuses to answer. That is the whole argument for the feature, and it is measurable. One further behaviour explains the intermittency: checksums are verified when a page is read **from disk**, not when it is read from shared buffers. A damaged page already resident in cache is served without a check, so the same query can succeed and then fail depending on what is cached. Verification work must be done against a restored copy, after a restart, or with `pg_checksums` against the files — not against a warm running cluster.

Remediation

Turn `ignore_checksum_failure` off immediately, and treat every result produced while it was on as unreliable: ```sql ALTER SYSTEM SET ignore_checksum_failure = off; SELECT pg_reload_conf(); ``` Withdraw the two nights of reconciliation totals. That is a communication task and it is more urgent than the technical repair, because those numbers are already in use elsewhere. Establish the extent of the damage. Do this against the files, not against a warm cluster, because a cached page is never re-verified: ```bash pg_ctl -D /var/lib/postgresql/18/main -m fast stop pg_checksums --check -D /var/lib/postgresql/18/main ``` This scans every block and names every bad one. One bad block is a different incident from four hundred, and you cannot tell which you have from the errors alone. Identify what the damaged block holds: ```sql SELECT relname, relkind FROM pg_class WHERE relfilenode = 16384; ``` Then check the rest of the cluster's structures, which checksums do not cover: ```sql CREATE EXTENSION IF NOT EXISTS amcheck; SELECT bt_index_check(index => c.oid, heapallindexed => true) FROM pg_class c JOIN pg_index i ON i.indexrelid = c.oid WHERE c.relkind = 'i' AND i.indisvalid; ``` `bt_index_check` returns void on success and raises an error describing the problem otherwise. Now choose the repair, in this order of preference: 1. **Restore from backup and replay.** This is the only option that produces a cluster you can trust completely. If the damage predates your oldest backup, a point-in-time recovery to before the corruption is the same answer. 2. **Rebuild the affected object**, if it is an index. An index is derived data; `REINDEX` regenerates it from the heap and the result is correct by construction. 3. **Recover the readable rows and rebuild the table**, if the damage is in a heap block and no backup can help. This is where `ignore_checksum_failure` legitimately appears — as part of a salvage, with the extracted data treated as suspect and reconciled against another source. Do not put the cluster back into service on the damaged storage until the storage team has an answer. A single bad block is evidence of a fault, not a one-off event, and the fault has had eleven days to produce others.

Verification

`pg_checksums --check` reports zero bad checksums across the whole cluster, run against a stopped cluster or a restored copy. `amcheck` passes on every index of the affected table, with `heapallindexed => true` so the heap is compared against the index rather than only the index's own structure being validated. The failing query completes and returns a value that reconciles against an independent source. A query that stops erroring is not the same as a query that is correct. `ignore_checksum_failure` is off, everywhere, verified rather than assumed: ```sql SELECT name, setting, source FROM pg_settings WHERE name = 'ignore_checksum_failure'; ``` `pg_stat_database.checksum_failures` is zero and stays zero. Note that this counter cannot increment on a cluster without checksums, so a zero there means nothing at all: ```sql SELECT datname, checksum_failures, checksum_last_failure FROM pg_stat_database; ``` The storage team has identified a cause. "No errors reported since" is not a cause; the array reported no errors while it was returning a damaged block. The withdrawn reconciliation totals have been recomputed from a trusted copy and the difference is known.

Prevention

**Run with data checksums on.** They are enabled by default from PostgreSQL 18 onward. On an existing cluster, `pg_checksums --enable` can turn them on offline. The cost is a few percent of CPU; the benefit is measured in the difference between these two outcomes on identical damage: ```text with checksums: ERROR: invalid page in block 5 of relation "base/5/16384" without checksums: 20000 <- no error, no warning, nothing ``` **Never leave `ignore_checksum_failure` on.** It exists for salvage. Anything it returns is suspect by definition, and setting it during an incident is how suspect data enters reports that other people rely on. **Alert on `checksum_failures` in `pg_stat_database`**, and on `page verification failed` in the log. Either would have caught this on the first night. **Alert on `invalid page in block` too.** It is the error the application sees, and it names the relation. **Run `pg_checksums --check` on a schedule**, against a restored backup rather than production — which verifies the backup and the checksums in one pass, and costs the production cluster nothing. **Run `amcheck` periodically on important indexes.** Checksums cover pages; `amcheck` covers structure, and a logically corrupt index passes every checksum. **Know that checksums are verified on read from disk, not from shared buffers.** A cached damaged page is served without a check. Verification belongs on a stopped cluster or a restored copy. **Treat one bad block as a storage incident.** It is evidence of a fault that has been active for some period and may have produced more. The database's job here is detection; the repair belongs upstream. **Test restores.** Every remedy above that produces a trustworthy cluster begins with a backup you can restore, and this is the incident in which you discover whether you have one.

Reported symptoms

A nightly reconciliation query began failing with ERROR: invalid page in block 5 of relation "base/5/16384".

Most queries against the same table work normally, including primary-key lookups. The application is otherwise unaffected and no other table produces errors.

An engineer found ignore_checksum_failure, set it, and the reconciliation query now completes. Its totals have been used for two nights.

The storage team reports a firmware update on the array eleven days ago and no reported errors since.

A second cluster in the same estate, built with checksums disabled, shows no errors at all.

Evidence provided

Read-only / Safewhat the server log records alongside the error
$ grep -E 'page verification|invalid page' /var/log/postgresql/postgresql-18-main.log
2026-08-27 23:47:33.213 UTC [78] LOG:  page verification failed, calculated checksum 23084 but expected 18652
2026-08-27 23:47:33.213 UTC [89] ERROR:  invalid page in block 5 of relation "base/5/16384"
Read-only / Safepg_checksums naming the block, on a stopped cluster
$ pg_checksums --check -D /var/lib/postgresql/cor
pg_checksums: error: checksum verification failed in file
"/var/lib/postgresql/cor/base/5/16384", block 5:
calculated checksum 5A2C but block contains 48DC
Checksum operation completed
Files scanned:   955
Blocks scanned:  3212
Bad checksums:  1
Data checksum version: 1

The damage is confined: SELECT count(*) FROM victim fails, and SELECT count(*) FROM victim WHERE id = 1 succeeds.

Data-loss riskwhat ignore_checksum_failure actually does
$ psql -c "SET ignore_checksum_failure = on; SELECT count(*) FROM victim;"
WARNING:  ignoring checksum failure in block 5 of relation "base/5/16384"
count
-------
20000

And on the cluster built without checksums, with the identical damage:

Data-loss riskno error, no warning, nothing — and a corrupted row returned as fact
$ psql -c "SELECT count(*) FROM victim;" -c "SELECT id, payload FROM victim WHERE payload !~ '^row-[0-9]+-z+$';"
 count
-------
20000

id  |                               payload                                | len
-----+----------------------------------------------------------------------+-----
423 | row-423-zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzCORRUPTXzzzzzzzzzzzzzzzzz |  68

Work the evidence before reading on

  1. Most queries succeed. Is the damage smaller than it looks?
  2. ignore_checksum_failure made the query work. What did it repair?
  3. The second cluster reports nothing. Is it healthier?
  4. Two nights of reconciliation totals came from block 5. What do you do about that first?

Root cause

A checksum caught a damaged page and PostgreSQL refused to serve it

That is the entire purpose of the feature, working. The block was corrupted below the database — the firmware update eleven days ago is the obvious candidate, though establishing that is the storage team’s work. The page read back differs from the page written.

pg_checksums found one bad block out of 3,212. Queries that do not read block 5 succeed, which is why most of the application never noticed.

The setting repaired nothing

The cluster without checksums is the important evidence

Why the error came and went

Resolution

Turn the setting off, and treat everything produced while it was on as unreliable:

ALTER SYSTEM SET ignore_checksum_failure = off;
SELECT pg_reload_conf();

Withdraw the two nights of totals. Communication task, and the most urgent thing in this incident.

Establish the extent of the damage against the files:

pg_ctl -D /var/lib/postgresql/18/main -m fast stop
pg_checksums --check -D /var/lib/postgresql/18/main

One bad block is a different incident from four hundred, and the errors alone cannot tell you which you have.

Identify what the block holds, then check the structures checksums do not cover:

SELECT relname, relkind FROM pg_class WHERE relfilenode = 16384;

CREATE EXTENSION IF NOT EXISTS amcheck;
SELECT bt_index_check(index => c.oid, heapallindexed => true)
FROM pg_class c JOIN pg_index i ON i.indexrelid = c.oid
WHERE c.relkind = 'i' AND i.indisvalid;

bt_index_check returns void on success and raises an error describing the problem otherwise.

Then choose the repair, in this order:

  1. Restore from backup and replay. The only option producing a cluster you can trust completely. If the damage predates your oldest backup, a point-in-time recovery to before the corruption is the same answer.
  2. Rebuild the object, if it is an index. An index is derived data; REINDEX regenerates it from the heap and is correct by construction.
  3. Salvage the readable rows and rebuild the table, if the damage is in a heap block and no backup helps. This is where ignore_checksum_failure legitimately appears — as part of a salvage, with the extracted data reconciled against another source.

Verification

pg_checksums --check reports zero bad checksums across the whole cluster, run against a stopped cluster or a restored copy.

amcheck passes on every index of the affected table, with heapallindexed => true so the heap is compared against the index rather than only the index’s own structure.

The failing query completes and returns a value that reconciles against an independent source. A query that stops erroring is not the same as a query that is correct.

ignore_checksum_failure is off everywhere, verified rather than assumed:

SELECT name, setting, source FROM pg_settings WHERE name = 'ignore_checksum_failure';

checksum_failures is zero and stays zero — remembering that this counter means nothing on a cluster without checksums:

SELECT datname, checksum_failures, checksum_last_failure FROM pg_stat_database;

The storage team has identified a cause, and the withdrawn totals have been recomputed from a trusted copy with the difference known.

Prevention

Run with data checksums on. Enabled by default from PostgreSQL 18 onward; pg_checksums --enable turns them on offline for an existing cluster. The cost is a few percent of CPU. The benefit is the difference between these two outcomes on identical damage:

with checksums:     ERROR:  invalid page in block 5 of relation "base/5/16384"
without checksums:  20000        <- no error, no warning, nothing

Never leave ignore_checksum_failure on.

Alert on checksum_failures and on page verification failed. Either would have caught this on the first night.

Alert on invalid page in block — the error the application sees, and it names the relation.

Run pg_checksums --check on a schedule against a restored backup, which verifies the backup and the checksums in one pass and costs production nothing.

Run amcheck periodically on important indexes. Checksums cover pages; amcheck covers structure, and a logically corrupt index passes every checksum.

Know that checksums are verified on read from disk, not from shared buffers.

Treat one bad block as a storage incident. The database’s job here is detection; the repair belongs upstream.

Test restores. Every remedy that produces a trustworthy cluster begins with a backup you can restore, and this is the incident in which you find out whether you have one.