pg_integrity_checksums

pg_integrity_checksums : Physical and logical checksums for PostgreSQL pages, rows, tables, indexes and databases.

Overview

Packages

Source

pig build pkg pg_integrity_checksums;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install pg_integrity_checksums;		# install via package name, for the active PG version

pig install pg_integrity_checksums -v 18;   # install for PG 18
pig install pg_integrity_checksums -v 17;   # install for PG 17
pig install pg_integrity_checksums -v 16;   # install for PG 16
pig install pg_integrity_checksums -v 15;   # install for PG 15
pig install pg_integrity_checksums -v 14;   # install for PG 14

Create this extension with:

CREATE EXTENSION pg_integrity_checksums;

Usage

Sources:

• PGXN metadata
• Official README
• Control file
• Migration guide

pg_integrity_checksums computes physical and logical checksums for PostgreSQL pages, cells, tuples, tables, indexes and databases. It is a C extension for PostgreSQL 12–18 and reads relation storage directly, so installation and database-wide checks require a superuser.

Install and migrate

CREATE EXTENSION pg_integrity_checksums;

Version 2.0.0 is the renamed continuation of the earlier pg_checksums extension. PostgreSQL has no ALTER EXTENSION ... RENAME; review dependent objects, drop the old extension, create the new one, and change the GUC prefix before using the new name:

DROP EXTENSION pg_checksums;
CREATE EXTENSION pg_integrity_checksums;
SET pg_integrity_checksums.workers = 4;
SET pg_integrity_checksums.strict = on;

The old and new names cannot coexist because they define the same SQL functions. The unrelated core pg_checksums utility is a separate program.

Checksum levels

Use logical checksums for content comparison and physical checksums for storage integrity. Logical table checksums do not require a primary key, preserve duplicate rows, and exclude relation and tuple identifiers. Logical index checksums are derived from visible table rows and the index definition, so they are stable across VACUUM, CLUSTER and REINDEX.

SELECT pg_table_logical_checksum('public.orders'::regclass);
SELECT pg_index_logical_checksum('public.orders_pkey'::regclass);
SELECT pg_database_logical_checksum(false, true);

Physical checksums include storage details and can change after VACUUM FULL, CLUSTER, REINDEX or other physical rewrites. Use the page, tuple and table physical functions when those changes are relevant to the integrity baseline.

Privileges and maintenance

Relation-level functions require SELECT on the relation and reject row-level-security protected relations because direct storage scans cannot apply RLS. Database-level functions are superuser-only. pg_integrity_checksums.workers controls parallel scans; pg_integrity_checksums.strict makes database scans fail when a relation cannot be checked instead of skipping it. The extension does not require shared preload, but physical baselines must be recomputed after the changes described in the release notes.