Enable data checksums online

Data checksums store a checksum value in every data page, written when the page is flushed and verified when it is read back — the primary mechanism for detecting storage and file-system corruption. Since PostgreSQL 18, initdb enables them by default for new clusters. Clusters initialized on older major versions often still run without them, which is exactly the gap this page covers.

Before PostgreSQL 19: pg_checksums required downtime

Through PostgreSQL 18, changing the checksum state of an existing cluster meant pg_checksums, and pg_checksums requires the server to be shut down cleanly. Enabling checksums rewrites every relation block in place, so on a multi-terabyte cluster the maintenance window is long, and the cluster must not be started mid-operation. For always-on systems this made retroactively enabling checksums nearly impossible to schedule.

Enabling checksums online

PostgreSQL 19 adds SQL functions that flip checksums on a running cluster while clients keep working, described in Section 28.2, Data Checksums:

-- check current state: off / inprogress-on / on / inprogress-off
SHOW data_checksums;

-- start enabling; cost_delay / cost_limit throttle the rewrite
SELECT pg_enable_data_checksums(cost_delay => 10, cost_limit => 1000);

What happens after the call:

1. The cluster state becomes inprogress-on. From this point checksums are written on page flush but not yet verified on read.
2. A launcher starts one background worker per database, which walks every relation and marks each page dirty so it is rewritten with a checksum.
3. When all databases finish, the state automatically switches to on and read-time verification begins.

Prerequisites and stalls to know about:

• The process consumes two background worker slots — make sure max_worker_processes has headroom.
• It waits for all open transactions to finish before starting, and for each database it waits for pre-existing temporary tables to be dropped. Applications with long-lived temp tables can block completion indefinitely; terminating those connections may be necessary.
• If the cluster stops while in inprogress-on, there is no resume: after restart, re-run pg_enable_data_checksums() and the rewrite starts over. Plan it for a window without reboots or failovers.

I/O cost and progress monitoring

Enabling checksums rewrites every page in the cluster — dirty page flushes plus WAL for the rewrites — so the I/O impact is substantial. The cost_delay and cost_limit arguments of pg_enable_data_checksums() apply the same cost-based throttling model as vacuum; on production systems, start with conservative values and watch latency.

Progress is visible in pg_stat_progress_data_checksums: one row for the launcher (tracking databases_total / databases_done) and one row per worker (tracking relations_total / relations_done and blocks_total / blocks_done for the current relation):

SELECT pid, datname, phase,
       databases_total, databases_done,
       relations_total, relations_done,
       blocks_total, blocks_done
FROM pg_stat_progress_data_checksums;

The phase column distinguishes enabling, disabling, waiting on barrier (backends acknowledging the state change), and waiting on temporary tables — the latter two explain most "stuck" appearances. The processes also show up in pg_stat_activity with backend_type of datachecksums launcher / datachecksums worker.

Replication note from the documentation: when a standby receives the checksum state change in the WAL stream it forces a restartpoint, which blocks redo until finished and can induce replication lag — on synchronous standbys this also blocks the primary. Reducing max_wal_size before starting shortens that restartpoint.

Disabling checksums online

SELECT pg_disable_data_checksums();

The state moves to inprogress-off (checksums still written, no longer verified) and settles to off once all backends acknowledge the change. No pages are rewritten, so there is no I/O surge, though checkpoints are still required. Disabling while an enable is in progress aborts the enable. If the cluster stops during inprogress-off, it comes back up with checksums off.

Hardware-accelerated checksum computation

The checksum stored in data pages is PostgreSQL's own FNV-1a-based algorithm rather than CRC — on x86-64 an AVX2-vectorized implementation is selected at runtime when available. CRC-32C protects WAL records instead, with runtime dispatch to SSE4.2 or AVX-512 instructions on x86 and the CRC32/PMULL extensions on ARMv8 (background on the implementation: digoal's commit walkthrough; WAL's use of CRC-32C is documented in Section 28.1, Reliability). On modern CPUs the steady-state CPU overhead of keeping checksums enabled is small; the dominant cost is the one-time rewrite when enabling, not day-to-day operation.

Interaction with backup verification

Checksums compose with the backup toolchain at two different layers:

• pg_basebackup verifies page checksums while reading the cluster (unless --no-verify-checksums is given); a checksum failure produces a non-zero exit status and is counted in pg_stat_database.checksum_failures. Enabling checksums therefore makes every base backup a full-cluster corruption scan for free.
• pg_verifybackup validates a finished backup against the backup_manifest file-level hashes. That catches corruption introduced in backup storage or transfer — a different failure domain from page checksums, which guard the live cluster.

Both belong in the verification routine described in backup, recovery, and PITR; surface checksum_failures in the dashboards from monitoring and logging.

When it is worth enabling

• Clusters initialized before PostgreSQL 18's default change that never had checksums on — now there is no downtime excuse.
• Compliance or integrity requirements that demand detection of silent storage corruption.
• Any system where pg_basebackup's built-in verification doubles as a periodic corruption sweep.

Reasons to hold off are narrow: throwaway clusters, or storage stacks that already checksum end-to-end (e.g. ZFS) combined with a high risk tolerance. Note that only PostgreSQL-level checksums are verified by PostgreSQL's own tools. For more on PostgreSQL 19, see the release overview.

AI prompt: plan a checksum rollout

Help me plan enabling data checksums online on a PostgreSQL 19 production cluster.

1. Cluster facts: (fill in — data size, number of databases, max_worker_processes, synchronous standbys yes/no, typical write throughput)
2. Please:
 - Give exact commands: SHOW data_checksums, the pg_enable_data_checksums call with sensible cost_delay/cost_limit starting values for my size
 - List what can stall the process (long transactions, long-lived temp tables) and how to detect each with SQL
 - Write the pg_stat_progress_data_checksums monitoring query and explain launcher vs worker rows
 - Cover the standby restartpoint lag risk and whether I should lower max_wal_size first
 - Give the abort/rollback path, including what happens if the cluster restarts mid-way (inprogress-on does not resume)
3. Finish with a verification checklist: final SHOW data_checksums, pg_stat_database.checksum_failures baseline, and a pg_basebackup test run.