Since PostgreSQL 18 was released last year checksums are enabled by default when a new cluster is initialized. This also means, that you either need to explicitly disable that when you upgrade from a previous version of PostgreSQL or you need to enable this in the old version of PostgreSQL you want to upgrade from. The reason is, that pg_upgrade will complain if the old and new version of PostgreSQL do not have the same setting for this.
Enabling and disabling checksums in offline mode can be done since several versions of PostgreSQL using pg_checksums, but as mentioned: This will not work if the cluster is running:
postgres@:/home/postgres/ [181] pg_checksums --version
pg_checksums (PostgreSQL) 18.1
postgres@:/home/postgres/ [181] pg_checksums --pgdata=$PGDATA
Checksum operation completed
Files scanned: 966
Blocks scanned: 2969
Bad checksums: 0
Data checksum version: 1 -> This means "enabled"
postgres@:/home/postgres/ [181] pg_checksums --pgdata=$PGDATA --disable
pg_checksums: error: cluster must be shut down
Even in PostgreSQL 19 this is still same: You cannot use pg_checksum to enable or disable checksums while the cluster is running.
What will change in version 19 is that two new functions have been added, one for enabling checksums and one for disabling checksums:
postgres=# \dfS *checksums*
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+---------------------------+------------------+--------------------------------------------------------------+------
pg_catalog | pg_disable_data_checksums | void | | func
pg_catalog | pg_enable_data_checksums | void | cost_delay integer DEFAULT 0, cost_limit integer DEFAULT 100 | func
(2 rows)
As mentioned in the commit message this is implemented by background workers and to actually see those processes on the operating system lets create some data so the workers really have something to do:
postgres=# create table t ( a int, b text, c timestamptz );
CREATE TABLE
postgres=# insert into t select i, md5(i::text), now() from generate_series(1,10000000) i;
INSERT 0 10000000
As this is version 19 of PostgreSQL currently checksum are enabled:
Questions about your own setup? A dbi services consultant can look at your case.
Talk to an expert (opens in a new tab)
postgres=# show data_checksums;
data_checksums
----------------
on
(1 row)
To disable that online, pg_disable_data_checksums is the function to use:
postgres=# select * from pg_disable_data_checksums();
pg_disable_data_checksums
---------------------------
(1 row)
postgres=# show data_checksums;
data_checksums
----------------
off
(1 row)
To enable checksums online pg_enable_data_checksums is the function to use. If you want to see the background workers you might grep for that in a second session on the operating system:
-- first session, connected to PostgreSQL
postgres=# select pg_enable_data_checksums();
pg_enable_data_checksums
--------------------------
(1 row)
postgres=# show data_checksums ;
data_checksums
----------------
on
(1 row)
-- second session, on the OS
postgres@:/home/postgres/postgresql/ [pgdev] watch "ps -ef | grep checksum | grep -v watch"
Every 2.0s: ps -ef | grep checksum | grep -v watch pgbox.it.dbi-services.com: 09:49:20 AM
in 0.006s (0)
postgres 4931 2510 0 09:49 ? 00:00:00 postgres: pgdev: datachecksum launcher
postgres 4932 2510 25 09:49 ? 00:00:00 postgres: pgdev: datachecksum worker
postgres 4964 4962 0 09:49 pts/2 00:00:00 grep checksum
Because enabling the checksum comes with some overhead there is throttling control as it is already the case for autovacuum:
postgres=# select pg_enable_data_checksums(cost_delay=>1,cost_limit=>3000);
pg_enable_data_checksums
--------------------------
(1 row)
Very nice, thanks to all involved.