After pg_upgrade moves a PostgreSQL 17 cluster to 18, why does amcheck still lack gin_index_check, and how should extension versions be managed across upgrades?
answer
- two versions: SQL objects and files
- binary upgrade keeps extversion
- update_extensions.sql
- installed_version versus default_version
basics
~20 spg_upgrade recreates each extension at its old SQL version, so amcheck stays at 1.4 while the 18 files offer 1.5 with gin_index_check. Running pg_upgrade's update_extensions.sql, or ALTER EXTENSION amcheck UPDATE in each database, brings the SQL objects forward.
solid answer
~40 sAn extension has two versions that can drift apart: `pg_extension.extversion` (the SQL objects in this database) and the control file's `default_version` (what the installed files offer). The shared library is always the one on disk. `pg_upgrade` runs in binary-upgrade mode: it creates each extension empty at its **old** `extversion` and restores the members one by one. So after upgrading from 17, `amcheck` is still 1.4, even though 18's files are 1.5 and add `gin_index_check()`. `pg_upgrade` notices and writes `update_extensions.sql` with an `ALTER EXTENSION ... UPDATE` for each lagging extension in each database. Run it afterwards. A plain dump and restore is different: it writes `CREATE EXTENSION` with no version, so the target's default is installed. Routine package upgrades need the same step, and `installed_version <> default_version` in `pg_available_extensions` finds what lags.
code
sql · 11 lines-- in each database of the upgraded 18 cluster
SELECT name, installed_version, default_version
FROM pg_available_extensions
WHERE installed_version <> default_version;
-- amcheck | 1.4 | 1.5
SELECT path FROM pg_extension_update_paths('amcheck')
WHERE source = '1.4' AND target = '1.5'; -- 1.4--1.5
ALTER EXTENSION amcheck UPDATE; -- runs amcheck--1.4--1.5.sql
SELECT extversion FROM pg_extension WHERE extname = 'amcheck'; -- 1.5go deeper
Know that an extension has a version, that upgrades of the package do not change the objects in your database by themselves, and that ALTER EXTENSION ... UPDATE does.
Explain extversion versus default_version, update scripts named name--old--new.sql, the shortest-path rule, and why the update is run per database.
Show the major-upgrade flow: packages for the new major on every node, pg_upgrade --check's library probe, update_extensions.sql afterwards, and the dump-and-restore difference.
Own the policy: pin extension versions per environment, test update scripts like migrations, and decide when lagging versions are acceptable.
## Two versions, not one An installed extension has two version numbers that can drift apart: - the **SQL version**, recorded in `pg_extension.extversion`. It describes which objects exist in this database and how they are defined; - the **files' version**: `default_version` in the control file on disk, together with whatever shared library the package installed. `pg_available_extensions` shows both side by side (`installed_version`, `default_version`). The shared library in use is always the one on disk. The SQL objects change only when someone runs `ALTER EXTENSION ... UPDATE`. ## Why amcheck has no gin_index_check after pg_upgrade `amcheck` is version `1.4` in PostgreSQL 17. PostgreSQL 18 ships `1.5`, whose only change is the new `gin_index_check()` function (script `amcheck--1.4--1.5.sql`). `pg_upgrade` dumps the schema in **binary-upgrade mode**. For each extension that dump creates an *empty* extension at exactly the old `extversion`, then restores the member objects one by one. The new cluster therefore runs 18's library with 1.4's SQL objects, and `gin_index_check()` does not exist. `pg_upgrade` knows this happens. It compares `installed_version` with `default_version` and, for every database where they differ, writes a `\connect` plus `ALTER EXTENSION ... UPDATE;` into **`update_extensions.sql`**. It then reports that the installation contains extensions that should be updated with `ALTER EXTENSION`. Run that file with psql as a superuser once the new cluster is up. ## Contrast: dump and restore A regular `pg_dump` writes `CREATE EXTENSION IF NOT EXISTS amcheck WITH SCHEMA public;`, and leaves out the version on purpose, so a restore into 18 creates the target's default version directly. The catch: if the source database ran an older SQL version, the restored objects now have the newer shape, and anything that relied on the old definitions has to cope with that. | | Plain `pg_dump` and restore | `pg_upgrade` | |---|---|---| | Extension statement | `CREATE EXTENSION IF NOT EXISTS ... WITH SCHEMA ...`, no version | an empty extension created at the old `extversion` | | SQL version afterwards | the target's `default_version` | the old version, until updated | | Follow-up for versions | none | run `update_extensions.sql` | | Extension files on the target | required | required, and probed by `--check` | ## Before a major upgrade: the libraries `pg_upgrade --check` takes every library the old cluster's C functions reference and probes it in the new cluster with `LOAD` (*Checking for presence of required libraries*). If packages built for 18, such as PostGIS or Citus, are missing, the check fails and lists them in `loadable_libraries.txt` in its output directory. The fix is to install the matching packages for the new major on the primary and every standby. Do **not** run `CREATE EXTENSION` in the new cluster: the schema definitions come over from the old one. How `pg_upgrade` itself is run (modes, checks, statistics) is covered by the major-upgrade topic. ## Routine package upgrades Minor package updates follow the same model without any major upgrade: 1. The OS package replaces the `.so` and the control file, and adds new update scripts. 2. Sessions that already loaded the old library keep it, because PostgreSQL never unloads a library, while new sessions load the new file. For preloaded libraries, nothing changes until a restart. The new library has to keep serving the old SQL definitions until the update runs. Well-maintained extensions make sure of this; a mismatch shows up as *could not find function ... in file ...*. 3. **Find what lags**, in **every database**: `installed_version <> default_version` in `pg_available_extensions`. This is the same query `pg_upgrade` uses. 4. **Check the path**: `pg_extension_update_paths('name')` shows the script chain for each `source` → `target` pair. `ALTER EXTENSION name UPDATE` (no `TO`) moves to `default_version` along the shortest chain of `name--old--new.sql` scripts, all in one transaction. PostgreSQL does not interpret version strings, so a careless downgrade script can create a shorter but unwanted path. 5. **Schedule it**: update scripts are DDL and take whatever locks their statements need on the member objects. ## Rules that keep this boring - Treat `ALTER EXTENSION ... UPDATE` as a schema migration. Test it on a restored copy, run it in each database, and record `extversion` before and after. - Install packages on standbys **before** the primary, so a replica never replays catalog changes for a library it does not have. - External extensions such as PostGIS can have their own release-specific upgrade steps. Read the project's notes before a major PostgreSQL upgrade, because the binaries for the new major must exist before `pg_upgrade` runs. - Only the extension's owner or a superuser can update it, and a non-superuser owner can do so only for a trusted extension.
- Why does a PostgreSQL logical dump and restore into 18 give amcheck 1.5 straight away while pg_upgrade keeps 1.4?A regular `pg_dump` writes `CREATE EXTENSION IF NOT EXISTS amcheck WITH SCHEMA public` with no version, so the restore installs the target's `default_version`. `pg_upgrade` dumps in binary-upgrade mode, where the goal is to reproduce the old catalog exactly. It creates an empty extension at the old `extversion` and attaches the restored members, leaving the version bump to `update_extensions.sql`.
- In PostgreSQL, what makes ALTER EXTENSION ... UPDATE pick an unexpected chain of scripts, and how do you check beforehand?PostgreSQL treats version names as opaque strings and applies the path with the fewest scripts. If a package ships a fast-path script plus a downgrade script to that fast path's starting point, the shortest route may include the downgrade and drop objects. `SELECT * FROM pg_extension_update_paths('name')` shows the planned path for each `source`/`target` pair before you run the update.
- pg_upgrade automatically runs ALTER EXTENSION UPDATE for every extension it carries over.
- Installing the new OS package upgrades the extension's SQL objects in every database.
- After pg_upgrade you should run CREATE EXTENSION again in the new cluster.
- ALTER EXTENSION UPDATE in one database updates the extension cluster-wide.
- PostgreSQL compares version strings numerically to choose the newest update path.
Assumes PostgreSQL 18 as the target: amcheck's default version there is 1.5, which adds gin_index_check(); 17 shipped 1.4.