postgres extension pg_checksums - ghdrako/doc_snipets GitHub Wiki

pg_checksum (Postgres 12+)

Only checksum the actual table and index data files used by Postgres, but there are other parts of the system that are not checked. Not the system catalogs, nor things like the free space map - only the data itself.

Enablechecksum when creating a new Postgres cluster.

When you invoke the initdb program, add the --data‑checksums flag (or -k if you prefer obscure arguments), and your shiny new Postgres cluster will have data checksums enabled.

Enable checksum on existing luster using pg_checksum

Data checksums are a mechanism to ensure the integrity of data stored in PostgreSQL. When enabled, PostgreSQL calculates and stores checksums for each block of data in the database. During reads, the checksums are recalculated, and any discrepancy indicates possible data corruption. This helps in early detection of issues, providing a higher level of data reliability. Enabling data checksums is especially valuable in critical production environments where data integrity is paramount, offering an additional layer of protection against silent data corruption.

The tool is able to do three modes now in total:

  • check, the default if nothing is specified and what pg_verify_checksums was already able to do. This mode scans all the relation file blocks, reporting any mismatch.

  • enable, which enables data checksums. This rewrites all the relation file blocks, and finishes the operation by updating the control file. Note that this can be take time depending on the size of the instance, and that the tool has no parallel mode.

  • disables which disables data checksums by only updating the control file.

  • verify if is instaled

$ psql -Axtc 'show data_checksums'
data_checksums|off

$ pg_controldata | grep -E 'state|checksum'
Database cluster state: in production
Data page checksum version: 0

systemctl stop postgresql-15      
pg_checksums --check -D /path/to/data_directory
systemctl start postgresql-15

Or

SHOW data_checksums;
  • Enable data checksums
systemctl stop postgresql-15 # or pg_ctl stop --mode=fast
pg_checksums --enable -D /var/lib/pgsql/15/data/  
# to report progres use
$ pg_checksums --enable --progress
systemctl start postgresql-15. # pg_ctl start

  • Once checksums are enabled, subsequent calls to pg_checksums --check can successfully verify the integrity of data pages. The cluster musy be shut down

  • Disable data checksum

time pg_checksums --disable