· Development

What PostgreSQL data checksums catch, and what they don't

PostgreSQL data checksums detect corrupted pages on read. Covers when checks run, enabling them offline with pg_checksums, pg_stat_database counters, and what amcheck and backups still cover.

DevelopmentDatabasesPostgreSQL

A disk, controller, or file system can hand back different bytes from the ones PostgreSQL wrote, without reporting any error. The initdb docs describe data checksums as a way “to help detect corruption by the I/O system that would otherwise be silent.” This article covers what is checked, when the check runs, how to turn checksums on for an existing cluster, where failures show up, and what checksums don’t protect.

It follows the PostgreSQL 18 docs (minor release 18.6), the newest stable release as of October 18, 2026. PostgreSQL 19 was at release candidate 1 (announced October 15, 2026) and is not covered here.

What gets checksummed

Tables and indexes are stored in fixed-size pages (the layout is covered in the article on HOT updates). The second field of each page header, pd_checksum, is 2 bytes, and it holds the page checksum when checksums are enabled. According to the docs, the checksum is updated when the page is written and verified each time the page is read.

Only data pages are covered. The docs say directly that internal data structures and temporary files are not:

  • pg_xact, pg_subtrans, pg_multixact, pg_serial, pg_notify, pg_stat, and pg_snapshots are not directly checksummed. Where these structures are persistent, the WAL records that let recent changes be rebuilt at crash recovery are protected instead.
  • Temporary files that large queries use for sorts, materializations, and intermediate results are not checksummed, and no WAL is written for them.

WAL has its own protection. Each WAL record carries a CRC-32C, which is set when the record is written and checked during crash recovery, archive recovery, and replication. Full page images stored in WAL are always checksum protected, whether or not data checksums are on.

Checksums are a cluster-wide setting. You can’t turn them on for a single database or table.

Is it on?

SHOW data_checksums;

data_checksums is a read-only parameter that reports whether checksums are enabled for the cluster.

The default depends on which version created the cluster. Starting with PostgreSQL 18, initdb enables checksums by default, and the new --no-data-checksums option turns them off. Since the 18 release notes describe this as a change in the default, a cluster created by an older initdb without -k may have checksums off. The release notes also say pg_upgrade requires matching checksum settings in the old and new clusters. To upgrade a cluster without checksums to 18, either create the new cluster with --no-data-checksums, or enable checksums on the old cluster first with pg_checksums, covered below.

On cost, the initdb docs say only that enabling checksums “might incur a small performance penalty.” They give no figure. One cost you can measure is WAL volume. With checksums on, hint bit updates are always WAL-logged and the wal_log_hints setting is ignored. The docs say you can turn on wal_log_hints in a cluster without checksums to test how much extra WAL-logging checksums would cause. wal_log_hints can only be set at server start.

When the check runs

The verification happens on read. The docs for ignore_checksum_failure say that detecting a checksum failure during a read normally makes PostgreSQL report an error and abort the current transaction.

This leads to an inference the docs don’t spell out: a page that is never read is never checked. Damage to a rarely queried old partition can go unnoticed until someone reads it. Two tools provide broader checksum verification than ordinary workload reads, one online and one offline:

  • pg_basebackup (online): when the source server has checksums enabled, it verifies them by default while taking the base backup, so the check covers the data the backup transfers. A failure produces a non-zero exit status, but the backup is not removed, as if --no-clean had been given. The failure is also reported in pg_stat_database. --no-verify-checksums turns this verification off.
  • pg_checksums --check (offline): the docs say every file in the cluster is scanned. It runs only after the server has been shut down cleanly. The exit status is zero if there are no checksum errors and nonzero if at least one failure is found.

A checksum detects damage. Nothing in the docs describes it repairing a page, so recovery depends on another copy of the data, such as a backup.

Turning on checksums for an existing cluster

In PostgreSQL 18, pg_checksums is the only way to enable or disable checksums after initdb, and it requires the server to be stopped. The command below is illustrative: it was not run for this article, and the path is a placeholder.

# After a clean shutdown of the server
pg_checksums --enable --progress -D /path/to/data

Things to know before running it:

  • The server must have been shut down cleanly.
  • Enabling rewrites in place every relation file block whose checksum changes, so it can take a long time on a large cluster. While it runs, neither the server nor anything else that writes to the data directory may be started, or data loss may occur.
  • Disabling (--disable) only updates the pg_control file.
  • --no-sync returns without waiting for files to reach disk, so a later operating system crash can leave the data directory corrupt. The docs say it is for testing, not production.
  • If pg_checksums is aborted or killed while enabling or disabling, the cluster’s checksum configuration stays as it was, and you can run the same operation again.

Replication needs extra care. If a tool such as pg_rewind copies relation file blocks directly between nodes and the nodes don’t share the same checksum setting, pages can end up with incorrect checksums. The docs recommend stopping all the clusters and switching them all consistently. Another option they describe as safe is to destroy the standbys, change the primary, and rebuild the standbys from scratch.

For a check that doesn’t change anything, use --check, the default mode. It needs the same clean shutdown.

pg_checksums --check --progress -D /path/to/data

Where failures show up

Checksum failures detected by the running server, and those found by pg_basebackup, are reported in the pg_stat_database view.

SELECT coalesce(datname, '(shared objects)') AS db,
       checksum_failures,
       checksum_last_failure
FROM pg_stat_database
WHERE checksum_failures > 0;
  • checksum_failures: the number of data page checksum failures detected in the database (or on a shared object).
  • checksum_last_failure: when the last such failure was detected.
  • Both are NULL when checksums are disabled. If these columns are NULL on every row, the cluster has checksums off.
  • The view has one row per database plus one row for shared objects. For that row, datid is 0 and datname is NULL, which is why the query uses coalesce.

Alerting when checksum_failures goes up gives you a signal that doesn’t depend on someone noticing an error in the application logs. That is a suggestion, not something the docs prescribe. Don’t look here for the result of an offline pg_checksums --check run. That tool runs with the server stopped, and its docs describe reporting only through its command output and exit status (nonzero if at least one failure is found), with no mention of pg_stat_database.

Settings for after a failure

The docs mention two settings for recovering from page corruption. Both are off by default, and only superusers or users with the appropriate SET privilege can change them.

ignore_checksum_failure works only when checksums are enabled. It turns the error into a warning and keeps processing, so you may be able to retrieve undamaged rows if the block header is still sane. If the header itself is corrupt, an error is still reported. The docs warn that this setting may cause crashes, propagate or hide corruption, or lead to other serious problems.

zero_damaged_pages applies when a damaged page header is detected. It zeroes out the damaged page in memory and continues, which destroys all the rows on that page. The docs advise not turning it on until you have given up hope of recovering data from the damaged pages. Zeroed pages are not forced to disk, so the docs recommend recreating the table or index before turning the setting off again.

Both settings are for getting data out after damage has happened. Neither is meant to be left on in normal operation.

What checksums don’t replace

A page can be correctly formatted, internally consistent, and match its own checksum while still holding logically wrong data. The amcheck docs give two examples: a toasted value in the main table with no matching entry in the TOAST table, and a tuple whose transaction ID is older than the oldest valid transaction ID. The same docs list failures that amcheck can catch and checksums can’t:

  • structural inconsistencies caused by an incorrect operator class implementation
  • mismatches between an index and its table (when heapallindexed verification is performed)
  • corruption from undiscovered bugs in PostgreSQL’s access method, sort, or transaction management code
  • corruption from faulty RAM or the broader memory subsystem

On memory, the reliability chapter says PostgreSQL does not protect against correctable memory errors and assumes you run on ECC or better RAM. A checksum is computed when the page is written, so if the bytes were already wrong in memory at that moment, the checksum matches the wrong bytes.

So checksums are good at one thing: noticing that bytes changed on the way to storage and back. Logical consistency inside and between relations is amcheck’s job (verify_heapam, bt_index_check, bt_index_parent_check, gin_index_check). Restoring data after damage is the job of a backup. For what the operating system promises when a program writes to a file, see what write() and fsync guarantee.

Docs used

Links and content checked on October 18, 2026.

Back to the blog