skip to content

In PostgreSQL 18, what do data checksums detect and miss, what does a checksum failure look like, and how do you enable them on an existing cluster?

level: seniorimportance: nice to knowfreq 28%

answer

  1. computed on write, verified on read
  2. data pages only
  3. XX001 and pg_stat_database
  4. offline pg_checksums --enable

basics

~20 s

Data checksums catch storage corruption of data pages when they are read back, failing the query with 'invalid page in block' and SQLSTATE XX001. They miss corruption before a write and outside data pages. Enable them offline with pg_checksums --enable.

solid answer

~40 s

With data checksums on, PostgreSQL stores a checksum in every data page when it is written and verifies it when the page is read. A mismatch logs `page verification failed, calculated checksum ... but expected ...` and fails the query with `invalid page in block N of relation ...`, SQLSTATE `XX001` (`data_corrupted`); `pg_stat_database.checksum_failures` counts them. They detect corruption introduced by disks, controllers or the file system, not bad data written by memory errors or bugs, and not WAL, `pg_xact` or temporary files. In **18**, `initdb` enables them by default; older or upgraded clusters may lack them (`SHOW data_checksums`). To enable, stop the cluster cleanly and run `pg_checksums --enable`, which rewrites every page; do it consistently on all nodes. Cost: hint-bit changes become WAL-logged, adding full-page images.

code

bash · 4 lines
bash
pg_ctl stop -D "$PGDATA" -m fast
pg_checksums --enable -D "$PGDATA" --progress
pg_ctl start -D "$PGDATA"
psql -c 'SHOW data_checksums;'

go deeper

for a junior

Recall that checksums are per data page, computed on write and checked on read, and that PostgreSQL 18 turns them on at initdb by default.

for a middle

Explain what a failure looks like (XX001, pg_stat_database.checksum_failures), what checksums cannot see, and that enabling later is an offline pg_checksums run.

for a senior

Plan enabling on an existing replicated cluster - downtime, all nodes consistent or rebuilt standbys - and respond to a failure without leaving ignore_checksum_failure on.

for a principal

Decide whether the extra WAL and the enabling downtime are worth earlier detection of storage corruption across the estate, and how failures reach on-call.

## What a data checksum is A **data checksum** is a 16-bit value stored in the header of every 8 kB data page. PostgreSQL computes it when the page is **written** to disk and verifies it each time the page is **read** back into shared buffers. Checksums are a cluster-wide property: there is no per-database or per-table switch, and the read-only setting `data_checksums` reports whether they are on (`SHOW data_checksums;`). In **PostgreSQL 18**, `initdb` enables checksums by default (`--no-data-checksums` opts out). Clusters created on 17 or earlier without `-k`/`--data-checksums`, and upgraded with `pg_upgrade`, still run without them. Two quick ways to see a cluster's state: - on a running server, `SHOW data_checksums;` returns `on` or `off`; - on a stopped one, `pg_controldata` prints `Data page checksum version:`, where `0` means checksums are off. Audit this across an estate after upgrades: a fleet in which new 18 clusters have checksums and upgraded ones silently do not is common, and it changes what a storage fault will look like on each. ## What they detect and what they miss | Case | Caught? | |---|---| | A disk, controller or file system returns a page with flipped or stale bytes | yes, when the page is next read | | A page that is never read again | no, until something reads it (or an offline check) | | Memory corruption or a software bug that changed the page before it was written | no: the checksum is computed over the already-wrong page | | WAL records | not by data checksums; each WAL record carries its own CRC | | `pg_xact`, other internal structures, temporary files | no: only data pages are covered | | A logically inconsistent index that still has valid page checksums | no: that needs `amcheck` | So checksums turn **silent** storage corruption into a **loud** error at the moment of reading, which is what makes them worth having on any cluster whose storage you do not fully trust. ## What a failure looks like ```console LOG: page verification failed, calculated checksum 20919 but expected 15254 ERROR: invalid page in block 4127 of relation "base/16384/24576" ``` In 18 the `page verification failed` line goes to the server log, and the client receives the `invalid page` error. The error carries SQLSTATE **`XX001`** (`data_corrupted`) and aborts the transaction. The failures are also counted per database: ```sql SELECT datname, checksum_failures, checksum_last_failure FROM pg_stat_database WHERE checksum_failures > 0; ``` To salvage what is left of a damaged table, a superuser can temporarily set `ignore_checksum_failure = on` in one session: the failure becomes a warning and undamaged tuples can be read out, as long as the page header is still sane. It may propagate or hide corruption, so it is a recovery tool, never a permanent setting. The real fix is restoring the affected data from a backup or a healthy standby, and checking the storage. ## Enabling checksums on an existing cluster `pg_checksums` (a server binary) enables, disables or verifies checksums, but only on a cluster that has been **shut down cleanly**: 1. Stop the server with a clean shutdown (`pg_ctl stop -m fast`). 2. Run `pg_checksums --enable -D "$PGDATA" --progress`. It rewrites every block whose checksum changes, so the time grows with database size; nothing may start the cluster meanwhile. 3. Start the server and confirm `SHOW data_checksums;` returns `on`. 4. `pg_checksums --check` verifies every page of an offline cluster; it exits non-zero if any failure is found. In a replication setup, enabling on the primary alone leaves standbys with mismatched pages. The manual's options are to stop every node and switch them all consistently, or to enable on the primary and rebuild the standbys from scratch. If the tool is interrupted, the cluster's checksum state is unchanged and it can be re-run. ## What they cost - **More WAL.** With checksums on, PostgreSQL WAL-logs hint-bit-only page changes, the same as `wal_log_hints = on` (which is then ignored). The first such change after a checkpoint writes a full-page image, so read-heavy tables whose rows are freshly committed can generate WAL just by being read. - **A little CPU** to compute and verify on each write and read. - **Downtime to enable** on an existing cluster, proportional to its size. For a payments ledger, those costs are usually small next to learning about bad storage from a query error instead of from wrong balances.

  • pg_stat_database shows checksum_failures = 3 on the ledger database. What do you do first?
    Find the relation and block from the server log's `invalid page in block ... of relation ...` lines, check the storage for errors, and restore the damaged data from a backup or a healthy standby. Use `ignore_checksum_failure` only briefly to read out surviving rows; do not leave it on.
  • Why does enabling checksums make some SELECT queries generate WAL?
    Reading freshly committed rows sets hint bits on their pages. With checksums on, those hint-bit changes are WAL-logged, and the first one after a checkpoint writes a full-page image, so WAL volume rises even on read-mostly tables.

  • Checksums are a per-table option set with ALTER TABLE.
  • You can turn data_checksums on in postgresql.conf and restart.
  • Checksums catch memory corruption that happened before the page was written.
  • Data checksums also verify WAL, pg_xact and temporary files.
  • Leave ignore_checksum_failure on so queries stop failing.

Assumes PostgreSQL 18, where initdb enables data checksums by default; clusters initialised on 17 or earlier without --data-checksums, then upgraded, still have them off.