Upgrading

How to upgrade

An upgrade has two parts: install the new release on the server, then update the extension in every database that has it. Installing a package or running make install alone changes nothing inside a database.

  1. Install the new release the same way as the old one (package, PGXN or make install; see Installation).

  2. In each database:

     ALTER EXTENSION pg_integrity_checksums UPDATE;

 SELECT extversion
 FROM pg_extension
 WHERE extname = 'pg_integrity_checksums';

    UPDATE without TO goes to the newest installed version, applying the upgrade scripts in between. Upgrades never require dropping the extension.

  3. New sessions load the new library. Reconnect long-lived clients (connection poolers, monitoring agents) so they stop using the old one.

  4. Read the notes below for every release you skip. If an upgrade changes the checksum values you store, capture new baselines from data you trust.

The CLI is released separately (cli-v* tags) and can be upgraded on its own; see CLI installation.

Which upgrades change checksum values

A stored checksum or CLI baseline only stays comparable while the values it contains do not change. Releases not listed here kept every value.

Release Values that change What to do
2.7 pg_database_logical_checksum (every database: relations are now identified by schema.relname); physical checksums of mapped catalogs (pg_class, pg_type, pg_proc, pg_attribute, shared catalogs) and of indexes with metapages or non-B-tree page layouts, and the database physical checksums that include them; table and index logical checksums do not change Recompute stored database logical checksums; re-capture physical baselines
2.6 Physical checksums of indexes and TOAST relations Re-capture physical baselines (capture --kind physical)
2.4 Logical checksums of tables with dropped columns or composite types with dropped attributes; user types when the baseline was taken with a non-default search_path; physical tuple and table checksums with include_header = true Recompute the affected values and compare; recreate baselines that differ
2.1 Logical checksums on big-endian systems only (now equal to little-endian) Recreate logical baselines taken on big-endian servers
2.0.1 Logical checksums involving Hash indexes; database logical checksums with include_system = true Recreate the affected index and database baselines

Release notes

The complete history is in Changes. These notes cover what to check when upgrading.

2.7

2.7 is a fix and security release; it also makes logical checksums 2-5x faster without changing their values. Upgrade with:

ALTER EXTENSION pg_integrity_checksums UPDATE TO '2.7.0';

The upgrade script changes no SQL objects; everything is in the C library, so reconnect clients after installing the package.

What changes:

  • pg_database_logical_checksum values change for every database. The per-relation checksums are now combined with each relation’s qualified name, so moving rows between two tables of the same structure changes the result. Renaming a relation or moving it to another schema now changes it as well. Table and index logical checksums, and therefore logical CLI baselines, do not change.
  • Some physical checksums change. Mapped catalogs are read through their actual file number, and the LP_DEAD masking of index pages is limited to pages that hold index tuples. Re-capture physical baselines (pg_integrity_checksums capture --kind physical) after the upgrade.
  • Security. Tuple and cell checksums return NULL for a tuple the query cannot see (deleted, old or uncommitted versions) instead of a checksum of its data, and pg_tuple_physical_checksum returns NULL instead of 0 for a TID without a tuple. Index expressions and partial-index predicates are evaluated as the table owner in a security-restricted operation, as REINDEX does, so a user who can create an index can no longer run SQL as the role computing a checksum.
  • In SERIALIZABLE transactions the checksum functions now take predicate locks like a sequential scan, so write skew through a checksum is detected.
  • A relation dropped while a database checksum runs is left out with a NOTICE instead of failing the whole check, in strict mode too.

The CLI 0.5 that ships alongside records schema_hash_version 2 in new baselines: re-capture baselines (update --yes) to compare the schema again; until then verify compares data only and warns.

2.6

2.6 fixes physical index and TOAST checksums that changed without any change to the data (after a plain SELECT through an index, or after a server restart with data checksums enabled). Upgrade with:

ALTER EXTENSION pg_integrity_checksums UPDATE TO '2.6.0';

What changes:

  • Physical index and TOAST checksum values differ from 2.5. Re-capture physical baselines (pg_integrity_checksums capture --kind physical) after the upgrade; a verify against an older physical baseline reports every index as changed. Logical checksums and physical heap (table) checksums do not change, so logical baselines stay valid.
  • Logical checksums no longer run type I/O functions and index expressions under a buffer content lock, so long calls can be cancelled with statement_timeout or pg_cancel_backend().
  • The pure utility functions (pg_data_checksum, pg_text_checksum and their algorithm overloads, pg_checksum_bigint) are IMMUTABLE and PARALLEL SAFE, so they can be used in index expressions, generated columns and parallel queries. The ALTER EXTENSION step applies this.

2.5

2.5 changes one default: pg_integrity_checksums.strict is now on. A database checksum (pg_database_physical_checksum, pg_database_logical_checksum) fails when a relation cannot be checksummed, instead of skipping it with a warning. Checksum values do not change. Upgrade with:

ALTER EXTENSION pg_integrity_checksums UPDATE TO '2.5.0';

The new default is picked up when the new library is loaded (new sessions after the package upgrade). Jobs that relied on the old behavior, for example a scheduled check that ran with an invalid index left over from a failed CREATE INDEX CONCURRENTLY, with a third-party index access method, or on a standby that holds unlogged tables, will now fail. Fix the underlying problem (DROP or REINDEX the invalid index), or keep skipping explicitly:

ALTER ROLE monitor SET pg_integrity_checksums.strict = off;

See Strict mode.

2.4

2.4 fixes three cases where a logical or physical checksum depended on something other than the stored data. Upgrade with:

ALTER EXTENSION pg_integrity_checksums UPDATE TO '2.4.0';

Checksum values change, and old baselines no longer match, only in these cases:

  • Dropped columns. Tables with dropped columns, and composite types with dropped attributes, used to hash the physical attribute number. They now hash the position among the live columns, so the checksum equals that of an identical table without the dropped columns (for example after a dump/restore). Tables without dropped columns are unchanged.
  • search_path. A user-defined type used to be named with its schema when the schema was not on the session’s search_path. It is now always identified by its unqualified name. Baselines taken from a session with the default search_path (types in public) are unchanged; baselines taken with another search_path must be recreated.
  • include_header = true. Physical tuple and table checksums that include the tuple header now ignore the xmin/xmax hint bits, which are set lazily by whichever session reads a tuple first. Checksums with include_header = false (the default) are unchanged.

To check whether an existing baseline is affected, recompute it after the upgrade and compare. If it differs, recreate it from the current data; the earlier value reflected the old, less stable computation.

The 2.4 upgrade also revokes EXECUTE from PUBLIC on the generic pg_database_*_checksum(boolean, boolean, text) functions. They already required a superuser.

2.3

2.3 adds pg_page_checksum_status(regclass, integer), which verifies a page against the checksum stored in its header and reports data_checksums = off as checksums_disabled. Upgrade with:

ALTER EXTENSION pg_integrity_checksums UPDATE TO '2.3.0';

The 2.3 upgrade also refreshes the pg_cell_checksum and pg_page_checksum comments for installations coming from 2.2.0.

2.2

2.2 adds generic algorithm overloads for FNV-1a 32-bit and 64-bit digests. Existing SQL functions and their 32-bit values are unchanged; PostgreSQL page checksums remain 16-bit. Upgrade with:

ALTER EXTENSION pg_integrity_checksums UPDATE TO '2.2.0';

Older CLI releases cannot read v2 baselines created with --hash fnv64; the 0.4 CLI reads both v1 (fnv32) and v2 (fnv64).

From pg_checksums 1.x

The extension was renamed in 2.0.0 to avoid confusion with PostgreSQL’s core pg_checksums utility. PostgreSQL has no ALTER EXTENSION ... RENAME, so migrate by reinstalling the extension and updating the GUC prefix. See MIGRATION.md for the full checklist:

DROP EXTENSION pg_checksums;
CREATE EXTENSION pg_integrity_checksums;

-- old settings
SET pg_checksums.workers = 4;
SET pg_checksums.strict = on;

-- new settings
SET pg_integrity_checksums.workers = 4;
SET pg_integrity_checksums.strict = on;

SQL function names and checksum values are unchanged, so existing logical and physical baselines remain valid. If DROP EXTENSION fails because of dependent objects, remove or recreate those dependencies explicitly rather than using CASCADE without review.