DEV Community

Franck Pachot
Franck Pachot

Posted on

Online enabling checksums in PostgreSQL 19

#postgres #sql #database #online

Enabling data checksums is strongly recommended to detect corruption originating in the storage or I/O layer, which can silently lead to incorrect query results. Although PostgreSQL performs basic sanity checks on the page header without checksums, it does not cryptographically verify page contents. As a result, many types of silent corruption could go unnoticed and produce inaccurate query outcomes.

In PostgreSQL 18, data pages across database clusters include a checksum by default, which is verified each time a page is read from disk and recalculated when written. Since checksums are enabled for all databases in a cluster, this applies broadly. However, if you've upgraded from earlier versions, your databases likely lack checksums. You can add checksums using pg_checksums, but the database must be shut down, and it can be a time-consuming process.

PostgreSQL 19 will support enabling checksums online, allowing them to operate in the background while the application remains active, possibly with throttling to lessen workload impact. Here's an example.

Corruption with checksums

To set up a demonstration database without checksums, I initialized it using the --no-data-checksums option with initdb:

podman run -d --replace                        \
 -e POSTGRES_PASSWORD=xxxxxxx                  \
 -e POSTGRES_INITDB_ARGS="--no-data-checksums" \
postgres:19beta3
Enter fullscreen mode Exit fullscreen mode

I created a table with one row containing the 'Hello World!' text:

postgres=# create table hackme as select 'Hello World!' as value
;
CREATE TABLE

postgres=# select distinct value from hackme
;
    value
--------------
 Hello World!

(1 row)
Enter fullscreen mode Exit fullscreen mode

I ensure the dirty page is written to and flushed from the shared buffers:

postgres=# checkpoint
;
CHECKPOINT

postgres=# create extension if not exists pg_buffercache
;
CREATE EXTENSION

postgres=# select * from pg_buffercache_evict_all()
;
 buffers_evicted | buffers_flushed | buffers_skipped
-----------------+-----------------+-----------------
            1200 |               0 |               0

(1 row)
Enter fullscreen mode Exit fullscreen mode

There's no encryption in PostgreSQL, so the data is visible in the file:

postgres=# select current_setting('data_directory')||'/'||pg_relation_filepath('hackme'::regclass) as file
;
                    file
--------------------------------------------
 /var/lib/postgresql/20/docker/base/5/16454

(1 row)

postgres=# \gset

postgres=# \setenv file :file

postgres=# \! cat -v $file | tail -c 42

@^@^@^@^A^@^A^@^B       ^X^@^[Hello World!^@^@^@

postgres=#
Enter fullscreen mode Exit fullscreen mode

With filesystem access, I can modify the data to simulate storage corruption:


postgres=# \! LC_ALL=C sed 's/World!/Hacker/g' $file > /tmp/corrupted.file && cat /tmp/corrupted.file > $file

postgres=# \! cat -v $file | tail -c 42

@^@^@^@^A^@^A^@^B       ^X^@^[Hello Hacker^@^@^@

postgres=#
Enter fullscreen mode Exit fullscreen mode

When PostgreSQL reads the file again, it doesn't detect that the page was modified outside the instance and displays corrupted data:

postgres=# select distinct value from hackme
;
    value
--------------
 Hello Hacker

(1 row)
Enter fullscreen mode Exit fullscreen mode

This is a major problem. Data can be corrupted at any layer below the PostgreSQL instance, and this corruption goes undetected.

Enabling checksums online

Without stopping the instance, I enable checksums:


postgres=# show data_checksums
;
 data_checksums
----------------
 off

(1 row)

postgres=# select pg_enable_data_checksums()
;
 pg_enable_data_checksums
--------------------------

(1 row)
Enter fullscreen mode Exit fullscreen mode

The checksum process is in progress and you can track the updates:

postgres=# select * from pg_stat_progress_data_checksums
;
 pid | datid | datname |  phase   | databases_total | databases_done | relations_total | relations_done | blocks_total | blocks_done
-----+-------+---------+----------+-----------------+----------------+-----------------+----------------+--------------+-------------
 101 |     0 |         | enabling |                 |                |                 |                |              |

(1 row)

postgres=# show data_checksums
;
 data_checksums
----------------
 inprogress-on

(1 row)
Enter fullscreen mode Exit fullscreen mode

Over time, the database is protected by checksums:

postgres=# select * from pg_stat_progress_data_checksums
;
 pid | datid | datname | phase | databases_total | databases_done | relations_total | relations_done | blocks_total | blocks_done
-----+-------+---------+-------+-----------------+----------------+-----------------+----------------+--------------+-------------

(0 rows)

postgres=# show data_checksums
;
 data_checksums
----------------
 on

(1 row)
Enter fullscreen mode Exit fullscreen mode

Checksums are enabled without any downtime.

Corruption with checksums

I do the same as before, flushing the shared buffers and modifying the file directly:


postgres=# select * from pg_buffercache_evict_all()
;
 buffers_evicted | buffers_flushed | buffers_skipped
-----------------+-----------------+-----------------
             991 |               0 |               0

(1 row)

postgres=# \! cat -v $file | tail -c 42

@^@^@^@^A^@^A^@^B       ^X^@^[Hello Hacker^@^@^@

postgres=# \! LC_ALL=C sed 's/Hacker/Franck/g' $file > /tmp/corrupted.file && cat /tmp/corrupted.file > $file

postgres=# \! cat -v $file | tail -c 42

@^@^@^@^A^@^A^@^B       ^X^@^[Hello Franck^@^@^@
Enter fullscreen mode Exit fullscreen mode

Now that each page has a checksum, a read detects any corruption:

postgres=# select distinct value from hackme
;
ERROR:  invalid page in block 0 of relation "base/5/16454"
Enter fullscreen mode Exit fullscreen mode

You can now switch to a standby node or restore from a backup.

Detecting corruption early is crucial for successful recovery. pg_basebackup performs checksum verification:

\! pg_basebackup  -zFtar -D  /var/tmp/backup

WARNING:  checksum verification failed in file "./base/5/16388", block 0: calculated 764A but expected 163F
WARNING:  file "./base/5/16388" has a total of 1 checksum verification failure
WARNING:  1 total checksum verification failure
pg_basebackup: error: checksum error occurred
Enter fullscreen mode Exit fullscreen mode

If you back up your database with a tool that doesn't check backups, validate the restore test with pg_checksums --check.

Conclusion

Data checksums are vital for PostgreSQL, transforming silent storage issues into detectable errors for investigation and recovery. Without them, corrupted data may go unnoticed, as page-header checks can't detect arbitrary page changes.

Enable checksums during cluster setup. For existing clusters, online activation avoids maintenance but isn't zero-impact:

  • pg_enable_data_checksums() and pg_disable_data_checksums() need superuser access and must run on the primary. Changes are propagated to standbys via WAL.
  • The operation uses two background-worker slots. Ensure max_worker_processes has enough headroom.
  • It waits for open transactions and temporary tables in all databases. Long sessions or tables can delay indefinitely.
  • Checksums are applied when enabling, but reads verify them only after final transition to on.
  • A crash or restart during inprogress-on requires restarting from scratch.
  • Standbys may need forced restart points, blocking WAL replay and causing lag, possibly stalling a primary. Reducing max_wal_size beforehand can mitigate this.

Checksums are best seen as part of a larger corruption-detection strategy: enable early, monitor transition, validate backups and replicas, and plan around transaction lifetimes and workload.

Top comments (0)