Contents

• pg_integrity_checksums CLI
  • Installation
    • Release binaries (recommended)
    • Container image
    • go install
    • From source
  • Quick start
  • Status
  • Commands
    • init
    • config
    • Configuration profiles
    • capture
    • verify
    • diff
    • show
    • update
    • doctor
    • completion
    • Docker
    • GitHub Action
  • Exit codes
  • Baseline formats
    • v1 — FNV-1a 32-bit
    • v2 — explicit checksum algorithm
  • Semantics
    • Snapshot consistency
    • Object scope
    • Logical vs physical
    • --changed-since
    • No automatic baseline updates
    • Permissions
  • Examples
  • Release artifacts
  • Versioning
  • Building and testing

pg_integrity_checksums CLI

Companion command-line tool for the pg_integrity_checksums PostgreSQL extension. The extension provides checksum functions; this tool adds baselines, verification, diffs, filters and CI-friendly exit codes.

The CLI is released independently from the extension. It talks to the database through SQL and does not install or modify extension objects. Release notes live in Changes and the release workflow reads the section for the pushed cli-v* tag automatically.

Installation

The CLI is a single static binary with no dependencies. It needs the extension installed in the database it connects to.

Release binaries (recommended)

Each cli-v* release has binaries for Linux (amd64, arm64), macOS (amd64, arm64), FreeBSD (amd64) and Windows (amd64):

VERSION=cli-v0.5.0
ASSET=pg_integrity_checksums-$VERSION-linux-amd64
BASE=https://github.com/VictorNagibator/pg_integrity_checksums/releases/download/$VERSION

curl -LO "$BASE/$ASSET"
curl -LO "$BASE/SHA256SUMS"
sha256sum --check --ignore-missing SHA256SUMS

install -m 0755 "$ASSET" ~/.local/bin/pg_integrity_checksums
pg_integrity_checksums version

To also check that SHA256SUMS was produced by the release workflow, see Release artifacts.

Container image

A signed multi-arch image (amd64, arm64) is published to GHCR. It contains only the CLI, not PostgreSQL:

docker run --rm -e DATABASE_URL -v "$PWD:/work" -w /work \
  ghcr.io/victornagibator/pg_integrity_checksums-cli:latest \
  verify --baseline baseline.json

Tags: latest, the release tag (for example cli-v0.5.0) and the plain version (0.5.0). The image runs as an unprivileged user, so the mounted directory must be writable by it if the command writes a baseline or a report.

go install

With Go 1.25 or newer:

go install github.com/VictorNagibator/pg_integrity_checksums/cli/cmd/pg_integrity_checksums@cli-v0.5.0

The binary works the same, but version reports a -dev suffix because go install does not stamp the release version. Use a release binary when the version string matters (it is recorded in baselines as tool_version).

From source

git clone https://github.com/VictorNagibator/pg_integrity_checksums.git
cd pg_integrity_checksums/cli
go build -o pg_integrity_checksums ./cmd/pg_integrity_checksums

Quick start

The default 32-bit baseline works with any supported extension version; the optional fnv64 format requires extension 2.2 or newer.

# Capture a logical baseline (32-bit v1 by default)
pg_integrity_checksums capture \
  --db "$DATABASE_URL" \
  --schema public \
  --output baseline.json

# Verify the database against it
pg_integrity_checksums verify \
  --db "$DATABASE_URL" \
  --baseline baseline.json

Add --hash fnv64 to capture to create a 64-bit v2 baseline. A clean verify prints no changes and exits 0. See Getting started for the extension side and the command reference below for every flag.

Status

The CLI is currently 0.5.0. It is versioned separately from the extension. It writes v1 (FNV-1a 32-bit) by default and v2 with capture --hash fnv64.

Commands

pg_integrity_checksums init      [options]
pg_integrity_checksums config    validate|show [options]
pg_integrity_checksums capture   [--config FILE] [--profile NAME] [options]
pg_integrity_checksums verify    [--config FILE] [--profile NAME] [options]
pg_integrity_checksums diff      OLD.json NEW.json [options]
pg_integrity_checksums show      FILE [options]
pg_integrity_checksums update    [--config FILE] [--profile NAME] [options]
pg_integrity_checksums doctor    [--config FILE] [--profile NAME] [options]
pg_integrity_checksums completion bash|zsh|fish|powershell
pg_integrity_checksums version

init

Creates a starter .pgics.yaml without connecting to PostgreSQL:

pg_integrity_checksums init \
  --profile prod \
  --dsn env:DATABASE_URL \
  --baseline baselines/prod.json \
  --schema public

The command refuses to overwrite an existing file unless --force is used. Relative baseline paths are written into the profile; directories are created as needed.

config

pg_integrity_checksums config validate
pg_integrity_checksums config validate --config .pgics.yaml --profile prod
pg_integrity_checksums config show --profile prod

validate checks the YAML, profiles, durations, DSN environment references and known format/kind/fail-on values. show prints the effective profile with the DSN password masked.

Configuration profiles

.pgics.yaml profiles provide defaults for repeated commands:

default: prod

profiles:
  prod:
    dsn: env:DATABASE_URL
    schema: [public]
    exclude_table: [audit.*]
    baseline: baselines/prod.json
    output: baselines/prod.json
    workers: 4
    hash: fnv64
    timeout: 5m
    connect_timeout: 10s
    application_name: pg_integrity_checksums
    fail_on: changed

pg_integrity_checksums verify --profile prod
PGICS_PROFILE=prod pg_integrity_checksums verify

Precedence: command-line flags, environment, profile, built-in defaults. PGICS_CONFIG and --config select the file. Relative baseline and output paths resolve against the config file directory. env:NAME DSNs read a connection string from the environment.

capture

Discovers objects in scope, computes checksums in one repeatable-read read-only transaction, and writes a versioned JSON baseline.

pg_integrity_checksums capture \
  --db "$DATABASE_URL" \
  --output baseline.json \
  --schema public

Options:

Flag	Default	Meaning
--db	DATABASE_URL/PG env	PostgreSQL connection string
--dsn		Alias for --db
--config	.pgics.yaml	Config file
--profile	config default	Profile name
--connect-timeout	15s	Connection timeout
--application-name		Value for pg_stat_activity.application_name
--output	baseline.json	Output file; - means stdout; .gz is compressed
--kind	logical	logical or physical
--hash	fnv32	fnv32 (v1) or fnv64 (v2)
--schema	all non-system	Schemas to include; globs allowed
--exclude-schema		Schemas to exclude; globs allowed
--table	all	schema.table or table glob to include
--exclude-table		schema.table or table glob to exclude
--no-indexes	false	Do not discover indexes
--include-system	false	Include system schemas
--workers	0	Extension parallel workers (GUC)
--timeout	0	statement_timeout, e.g. 5m
--strict	false	Fail if an object cannot be checksummed (the CLI’s own preflight; unrelated to the server setting pg_integrity_checksums.strict)
--quiet	false	No progress on stderr

--kind physical is opt-in. A physical baseline is tied to one cluster; it records system_identifier and cannot be used against another cluster.

--hash fnv64 writes a v2 baseline. The default --hash fnv32 keeps writing the historical v1 format. verify and update inherit the hash algorithm from the baseline they read; there is no separate verify flag.

verify

Re-discovers the objects using the scope recorded in the baseline and compares their current checksums with the baseline.

pg_integrity_checksums verify \
  --db "$DATABASE_URL" \
  --baseline baseline.json

Options:

Flag	Default	Meaning
--db	DATABASE_URL/PG env	PostgreSQL connection string
--dsn		Alias for --db
--config	.pgics.yaml	Config file
--profile	config default	Profile name
--connect-timeout	15s	Connection timeout
--application-name		Value for pg_stat_activity.application_name
--format	text	text, json or junit
--output	-	Report file; - means stdout
--fail-on	any	changed, schema-changed, new, missing, any, none
--show-ok	false	Include unchanged objects in text output
--allow-version-mismatch	false	Allow a different PostgreSQL major
--changed-since	false	Reuse unchanged tables/indexes (logical only, heuristic)
--workers	0	Extension parallel workers
--timeout	0	statement_timeout
--quiet	false	No progress on stderr

diff

Compares two baseline files without connecting to PostgreSQL.

pg_integrity_checksums diff before.json after.json

Flags and the two files may appear in any order. --format accepts text, json or junit. --output writes a report file. Exit code is 1 when changes are found and 0 when the object maps match. --no-fail makes a change-only diff exit 0.

show

Prints baseline metadata and a compact object summary. With --format json it prints the baseline document as JSON. --objects lists every object, including skipped ones. --output writes the report to a file.

Flags and the file may appear in any order, for example:

pg_integrity_checksums show baseline.json --objects

update

Captures a new baseline using the old baseline’s scope. It prints the diff and requires --yes before replacing the file. There is no automatic update on DML or DDL. The file is replaced atomically. --format accepts text, json and junit; --output writes the diff report to a file. --config, --profile, --dsn, --connect-timeout and --application-name work as in capture.

pg_integrity_checksums update \
  --db "$DATABASE_URL" \
  --baseline baseline.json \
  --yes

# Preview the diff without writing
pg_integrity_checksums update \
  --db "$DATABASE_URL" \
  --baseline baseline.json \
  --dry-run

doctor

Checks that the extension is installed, reports versions, the current database, and the number of objects that would be captured. --verbose lists every object with its preflight status; --strict exits 1 when a problem is found. --format json and --output are supported. The preflight checks at least SELECT privileges and row-level security. --timeout sets statement_timeout on the diagnostic connection.

completion

Prints a shell completion script:

pg_integrity_checksums completion bash > /etc/bash_completion.d/pg_integrity_checksums
pg_integrity_checksums completion zsh
pg_integrity_checksums completion fish
pg_integrity_checksums completion powershell

Docker

See Container image. To build the image yourself:

docker build -t pgics-cli ./cli
docker run --rm -e DATABASE_URL -v "$PWD:/work" -w /work pgics-cli verify --baseline baseline.json

GitHub Action

The composite action in cli/action.yml builds the CLI from the referenced tag and runs verify. The job needs a reachable database with the extension (for example a service container) and Go is set up by the action itself:

- uses: VictorNagibator/pg_integrity_checksums/cli@cli-v0.5.0
  with:
    baseline: baselines/prod.json
    dsn: ${{ secrets.DATABASE_URL }}
    fail_on: changed
    format: junit
    output: pgics-report.xml

Input	Default	Meaning
baseline	(required)	Path to the JSON baseline
dsn	DATABASE_URL from the environment	Connection string
format	json	Report format: text, json or junit
fail_on	any	What fails the step: changed, schema-changed, new, missing, any or none
changed_since	false	true passes --changed-since
output	(stdout)	Write the report to this file
quiet	true	Suppress progress output

The step fails with the CLI’s exit code. Pin the action to a release tag; main may contain unreleased changes.

Exit codes

Code	Meaning
0	Success / no blocking differences
1	Differences found (according to --fail-on), or diff found changes
2	Operational error: connection, SQL, permissions, corrupt page
3	Invalid baseline: format, JSON, kind or incompatible version
4	update/destructive operation refused without --yes

Baseline formats

v1 — FNV-1a 32-bit

The v1 format identifier is:

pg_integrity_checksums.baseline/v1

v1 has no hash field and uses the default numeric FNV-1a 32-bit functions. The CLI continues to read and write it by default.

Example:

{
  "format": "pg_integrity_checksums.baseline/v1",
  "baseline_id": "sha256:aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa",
  "created_at": "2026-11-06T16:18:40Z",
  "tool_version": "0.2.0-dev",
  "kind": "logical",
  "extension_version": "2.1.0",
  "postgres_version": 160014,
  "database": "app",
  "scope": {
    "schemas": ["public"],
    "exclude_schemas": [],
    "tables": [],
    "exclude_tables": [],
    "include_system": false,
    "include_indexes": true
  },
  "objects": [
    {
      "kind": "table",
      "schema": "public",
      "name": "orders",
      "status": "ok",
      "checksum": "161531096",
      "schema_hash": "sha256:aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa",
      "stats": {
        "relfilenode": 16399,
        "inserted": 100,
        "updated": 2,
        "deleted": 1
      }
    },
    {
      "kind": "index",
      "schema": "public",
      "name": "orders_pkey",
      "table_schema": "public",
      "table_name": "orders",
      "status": "ok",
      "checksum": "-1867570346",
      "schema_hash": "sha256:aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"
    }
  ]
}

v2 — explicit checksum algorithm

v2 adds a required hash field and stores the algorithm explicitly:

pg_integrity_checksums.baseline/v2

{
  "format": "pg_integrity_checksums.baseline/v2",
  "hash": "fnv64",
  "kind": "logical",
  "extension_version": "2.2.0"
}

The supported values are fnv32 and fnv64. The CLI writes fnv64 v2 baselines when capture --hash fnv64 is used. Older CLI releases reject v2 instead of silently comparing incompatible digests. The current CLI reads both v1 and v2 baselines.

Rules:

• Checksums are JSON strings, because SQL bigint values do not fit safely into JavaScript numbers.
• kind is logical or physical.
• Object kind is table or index.
• OIDs are never stored; identity is schema-qualified names.
• schema_hash is a SHA-256 of the portable object definition (columns, constraints, partition key, reloptions and index definition). It lets verify/diff report schema_changed separately from a data change. It is computed with search_path = pg_catalog and quote_all_identifiers = off and with constraints ordered by name, so it does not depend on the connection’s settings and survives a dump/restore.
• schema_hash_version records how schema_hash was computed (absent means 1, the scheme of earlier CLI versions, which depended on search_path and on constraint creation order). Hashes of different versions are not compared: verify and diff then report data changes only and print a warning; update --yes re-captures the baseline with current hashes.
• stats is table-only change-detection data from pg_stat_all_tables plus relfilenode; it is used only by --changed-since.
• Objects are sorted by kind, schema, name.
• status is ok or skipped; skipped objects may carry reason.
• A comparison adds schema_changed when the stored and current schema_hash differ; --fail-on changed counts it as a change.
• baseline_id is sha256: plus the SHA-256 of the canonical JSON encoding of kind, hash (when present), extension_version, postgres_version, database and the sorted object list.
• Physical baselines additionally contain system_identifier (string) and database_oid (number).
• Unknown fields are rejected only by schema validation tooling; the CLI ignores unknown fields when reading, for forward compatibility.

The full JSON Schemas are in docs/baseline-v1.schema.json and docs/baseline-v2.schema.json.

Semantics

Snapshot consistency

Capture and verify run in one transaction:

BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;

All objects in a baseline therefore describe the same database snapshot. Different tables are never read from separate snapshots or separate connections. Parallelism is provided by the extension’s pg_integrity_checksums.workers GUC inside that transaction.

A long repeatable-read transaction holds an xmin horizon and can delay VACUUM. Keep capture/verify windows bounded and monitor long-running sessions on busy databases.

Object scope

By default the CLI discovers:

• ordinary tables (r), partitioned tables (p) and materialized views (m);
• top-level relations only: partition leaves are not duplicated;
• indexes (i) and partitioned indexes (I) when indexes are enabled;
• non-system schemas (pg_catalog, information_schema, pg_toast and temporary schemas are excluded).

A partitioned table/index is checksummed through its parent, which aggregates its leaves. --include-system includes system schemas. Foreign tables, views, sequences and TOAST relations are not captured as separate objects.

Logical vs physical

logical is the default:

• stable across VACUUM/REINDEX/physical movement;
• suitable for migrations and cross-database comparison;
• excludes relation OIDs, heap TIDs and type OIDs.

physical is opt-in:

• depends on storage, tuple locations, relfilenodes and architecture;
• only valid for the same cluster and storage state;
• the baseline records system_identifier and is rejected on another cluster.

--changed-since

--changed-since is a logical-only optimization for verify. It reuses a baseline checksum when all of the following are true:

• the object’s schema_hash is unchanged;
• for a table, relfilenode and the n_tup_ins/n_tup_upd/n_tup_del counters are unchanged and non-zero;
• for an index, the owning table is unchanged and the index definition hash is unchanged.

The statistics counters are a heuristic: a reset, overflow or missing stats row forces a full checksum. Use a normal verify when exactness matters. Physical baselines do not support --changed-since.

The counters are read just before the snapshot transaction starts, so the counters stored with a checksum never include a change that the checksum does not. PostgreSQL publishes the counters with a short delay (about a second on PostgreSQL 15 and later), so a change committed in the last moments before a --changed-since verify may only be noticed by the next one; it is never missed for good.

On a standby (pg_is_in_recovery()), --changed-since is ignored with a warning and every object is checksummed: WAL replay does not update the table statistics, so unchanged counters there say nothing about the data.

No automatic baseline updates

The CLI does not install triggers, start background workers or update baselines after DML. update is explicit and requires --yes. This prevents a corrupted database from silently replacing a known-good baseline.

Permissions

Capture requires the same privileges as the checksum functions: SELECT on each object and superuser for database-level checks. RLS-protected relations are rejected by the extension and are reported as errors unless skipped.

Examples

# Capture public schema, logical
pg_integrity_checksums capture --db "$DATABASE_URL" \
  --schema public --output prod-baseline.json

# CI verification
pg_integrity_checksums verify --db "$DATABASE_URL" \
  --baseline prod-baseline.json --format json --fail-on changed

# Compare two snapshots from different releases
pg_integrity_checksums diff before.json after.json

# Physical snapshot of one cluster, after maintenance
pg_integrity_checksums capture --db "$DATABASE_URL" \
  --kind physical --output physical.json

Release artifacts

Each cli-v* release contains the platform binaries, SHA256SUMS, SHA256SUMS.bundle and sbom.spdx.json. Verify a download:

sha256sum -c SHA256SUMS
cosign verify-blob \
  --bundle SHA256SUMS.bundle \
  --certificate-identity-regexp 'https://github.com/VictorNagibator/pg_integrity_checksums/.github/workflows/cli-release.yml@refs/tags/cli-v.*' \
  --certificate-oidc-issuer https://token.actions.githubusercontent.com \
  SHA256SUMS

The SBOM can be inspected with any SPDX tool. Release jobs use keyless Sigstore signing through the GitHub OIDC token.

Versioning

• Baseline formats: v1 (FNV-1a 32-bit) and v2 (explicit hash).
• CLI version: independent 0.x.y / 1.x.y semver.
• Extension compatibility is checked at runtime and recorded in the baseline.
• New optional fields may be added without a format bump; breaking changes require a new format identifier.

Building and testing

cd cli
go test ./...
go vet ./...
go build ./cmd/pg_integrity_checksums

The integration test is opt-in and needs an installed extension:

PGICS_TEST_DSN="postgresql:///app?host=/tmp" \
  go test ./internal/cli -run TestIntegration -v

It creates and drops a temporary schema, so use a test database.