pg_integrity_checksums

Overview

Former pg_checksums SQL extension, unrelated to the core/PGDG utility. Old/new extensions cannot share a schema; follow MIGRATION.md. Superuser install; relation checks enforce SELECT and reject RLS; database checks require superuser. Logical checksums compare content; physical checksums depend on storage. PG14-18.

Version

Build

You can build the RPM / DEB packages for pg_integrity_checksums using pig build:

pig build pkg pg_integrity_checksums         # build RPM / DEB packages

Install

You can install pg_integrity_checksums directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

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

Install the extension using pig or apt/yum/dnf:

pig install pg_integrity_checksums;          # Install for current active PG version

pig ext install -y pg_integrity_checksums -v 18  # PG 18
pig ext install -y pg_integrity_checksums -v 17  # PG 17
pig ext install -y pg_integrity_checksums -v 16  # PG 16
pig ext install -y pg_integrity_checksums -v 15  # PG 15
pig ext install -y pg_integrity_checksums -v 14  # PG 14

dnf install -y pg_integrity_checksums_18       # PG 18
dnf install -y pg_integrity_checksums_17       # PG 17
dnf install -y pg_integrity_checksums_16       # PG 16
dnf install -y pg_integrity_checksums_15       # PG 15
dnf install -y pg_integrity_checksums_14       # PG 14

apt install -y postgresql-18-pg-integrity-checksums   # PG 18
apt install -y postgresql-17-pg-integrity-checksums   # PG 17
apt install -y postgresql-16-pg-integrity-checksums   # PG 16
apt install -y postgresql-15-pg-integrity-checksums   # PG 15
apt install -y postgresql-14-pg-integrity-checksums   # PG 14

Create Extension:

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.