pg_integrity_checksums 2.9.0

This Release
pg_integrity_checksums 2.9.0
Date
Status
Stable
Other Releases
Abstract
Multi-level data integrity checksums for PostgreSQL
Description
pg_integrity_checksums provides checksum functions for PostgreSQL at six granularity levels (page, cell, tuple, table, index and database), each in a physical and a logical variant (cell level is logical only). Physical checksums detect storage corruption and change after VACUUM FULL, CLUSTER or REINDEX. Logical checksums depend only on data content: they exclude relation OIDs, heap TIDs and type OIDs, canonicalize enum, numeric, array, composite and range values, are endian-independent, and do not require a primary key. Logical index checksums are derived from table data and the index definition, so they work for every access method. The implementation uses FNV-1a 32-bit by default and adds generic FNV-1a 32/64-bit overloads, supports parallel scans through the pg_integrity_checksums.workers GUC, and transparently aggregates partitioned tables and partitioned indexes. pg_integrity_checksums.strict (on by default) makes database scans fail instead of skipping relations without a usable checksum.
Released By
VictorNagibator
License
MIT
Resources
Special Files
Tags

Extensions

pg_integrity_checksums 2.9.0
Checksum functions at page, cell, tuple, table, index and database level

Documentation

pull_request_template
pull_request_template
testing
Testing and releases
README
pg_integrity_checksums CLI
README
Vendored third-party code
SECURITY
Security Policy
functions
Functions and algorithms
MIGRATION
Migrating from pg_checksums 1.x
cli
pg_integrity_checksums CLI
installation
Installation
index
PostgreSQL integrity checks you can run online
upgrading
Upgrading
getting-started
Getting started
architecture
Architecture and internals
CODE_OF_CONDUCT
Contributor Covenant Code of Conduct
concepts
Concepts
CONTRIBUTING
Contributing
troubleshooting
Troubleshooting
CLAUDE
CLAUDE.md
requirements-docs
requirements-docs
operations
Operations

README

pg_integrity_checksums - Multi-Level Data Integrity Extension for PostgreSQL

CI Quality CodeQL codecov OpenSSF Scorecard OpenSSF Best Practices

Release CLI release PGXN PGEXT.CLOUD Docker image PostgreSQL 12-19 Docs License: MIT

Overview

pg_integrity_checksums provides cell, tuple, table, index and database checksums. Cell checksums are logical; tuple, table, index and database checksums support both physical and logical modes.

Logical checksums are canonical across endianness, VACUUM/REINDEX/physical movement and database OIDs. Physical checksums are for on-disk corruption in one specific cluster.

The default SQL API uses FNV-1a 32-bit and returns ordinary SQL integers:

SELECT pg_table_logical_checksum('public.orders'::regclass);
SELECT pg_table_physical_checksum('public.orders'::regclass, false);

When two copies of a table differ, the logical checksum can be split by key bucket (or, on a physical standby, by block range) to find the rows that differ; see Finding differing rows.

A generic API with an explicit algorithm (FNV-1a 32/64-bit, CRC-32C or XXH3-64) returns raw bytea digests, for a wider or faster checksum. It is described in the function reference.

Errors and skipped relations

Database-level checksums are strict by default (since 2.5): if a relation cannot be checksummed (an invalid index, a corrupt page, an unlogged table on a standby), the call fails and names the relation, instead of returning a value that silently leaves it out. SET pg_integrity_checksums.strict = off skips such relations with a warning; do not use that value for monitoring. See Strict mode.

Quick start

CREATE EXTENSION pg_integrity_checksums;
SELECT pg_table_logical_checksum('public.orders'::regclass);

Install it with any of these (details):

pgxn install pg_integrity_checksums                     # PGXN
sudo apt install postgresql-16-pg-integrity-checksums   # PIGSTY APT repository
docker run -e POSTGRES_PASSWORD=secret \
  ghcr.io/victornagibator/pg_integrity_checksums:pg16   # image with the extension

git clone https://github.com/VictorNagibator/pg_integrity_checksums.git
cd pg_integrity_checksums && make && sudo make install  # from source (PGXS)

The companion CLI captures baselines and verifies a database against them, with text, JSON and JUnit reports for CI. Download a release binary or use the ghcr.io/victornagibator/pg_integrity_checksums-cli image (see CLI installation), then:

pg_integrity_checksums capture --db "$DATABASE_URL" --output baseline.json
pg_integrity_checksums verify --db "$DATABASE_URL" --baseline baseline.json

The CLI uses the 32-bit v1 baseline format by default. Add --hash fnv64 to capture when you explicitly need a 64-bit v2 baseline.

Documentation

Compatibility

The 32-bit numeric SQL functions are the default and remain fully supported, as are v1 baselines. The optional generic API adds explicit fnv32/fnv64/crc32c/xxh3 digests and v2 baselines; see Functions and algorithms and Concepts for selection guidance.

Support