Major vs Minor Upgrades, Binary Compatibility, Extension Readiness, and Release Notes

Learning outcomes

ServiceHub runs a PostgreSQL 18 cluster. A security advisory and a new maintenance release arrive during the same quarter that the platform team is planning its next major-version migration. One engineer proposes replacing binaries immediately because “PostgreSQL upgrades are compatible”; another proposes copying PGDATA into the new major because “the table files are still PostgreSQL.” Both statements mix two very different change classes.

1. Record the actual server and client baseline

SELECT version();SELECT current_setting('server_version') AS server_version,       current_setting('server_version_num') AS server_version_num,       current_setting('data_directory') AS data_directory;SELECT pg_postmaster_start_time() AS server_started_at;

psql --versionpg_config --versionpg_config --bindirpg_config --pkglibdirpg_config --sharedir

The server and client tools do not have to be exactly the same minor version, but upgrade tools have directional/version constraints. In particular, a cross-version dump should normally use the newer pg_dump; pg_dump refuses to dump a server newer than its own major.

2. Minor updates: compatible data format, new binaries

For PostgreSQL 10 and later, the first number is the major version and the second is the minor maintenance release. PostgreSQL guarantees that minor releases within one major do not change the internal storage format. The normal upstream pattern is: read the minor release notes, stop the server cleanly, install the new same-major binaries/packages, restart, then perform any release-note follow-up.

# 1. Confirm backup/restore readiness and read exact minor release notes.# 2. Stop PostgreSQL with the platform's service mechanism.# 3. Upgrade ONLY to the current package in the same major line.# 4. Restart and verify.psql -X -d servicehub_lab -c "SELECT version();" 

No dump/reload or pg_upgrade is required simply to move from one 18.x minor to another 18.x minor. That does not mean a minor update is “zero-risk”: release notes can require reindexing, extension/package changes, or other remediation for specific bugs.

Before approving a major-version maintenance window, run the new major's pg_upgrade --check against the exact old/new binaries, data directories, extension libraries, checksum mode, and intended transfer mode. A successful check is necessary compatibility evidence, not proof that application behavior or performance is unchanged.

3. Major upgrades: data-directory compatibility ends at the major boundary

Major releases can change system catalogs, on-disk metadata assumptions, planner behavior, defaults, syntax and extension application binary interfaces (ABIs). PostgreSQL intentionally refuses to use an incompatible data directory with the wrong server major.

# WRONG: point the PostgreSQL 18 server binary at an older-major PGDATA.# Example shape only:postgres -D /srv/postgresql/17/data# Expected class of failure:# FATAL: database files are incompatible with server# ... data directory was initialized by PostgreSQL version 17 ...# ... which is not compatible with this version 18 ...

The repair is a supported migration path: logical dump/restore, pg_upgrade, or logical replication. File-level copies and physical backups preserve the old major's physical cluster and cannot convert it to a new major.

4. Release notes are executable requirements

A major release note has a migration section precisely because defaults and semantics can change. PostgreSQL 18, for example, changed initdb to enable data checksums by default, while pg_upgrade requires old/new checksum settings to match. It also changed some full-text-search behavior around collation providers and recommends reindexing affected full-text/pg_trgm indexes in relevant upgrades.

SHOW data_checksums;SHOW default_text_search_config;SELECT name, setting, sourceFROM pg_settingsWHERE name IN (  'password_encryption',  'compute_query_id',  'jit',  'io_method')ORDER BY name;

Read every intervening major release's migration section even when pg_upgrade can jump directly across multiple majors. “The upgrade tool passed” cannot prove the application still has identical semantics under new defaults.

5. Extension readiness is a new-major binary/package question

SELECT e.extname,       e.extversion,       n.nspname AS schema_name,       pg_get_userbyid(e.extowner) AS owner,       a.default_version AS packaged_defaultFROM pg_extension AS eJOIN pg_namespace AS n ON n.oid = e.extnamespaceLEFT JOIN pg_available_extensions AS a ON a.name = e.extnameORDER BY e.extname;SELECT name, version, superuser, trusted, relocatable, requiresFROM pg_available_extension_versionsWHERE installedORDER BY name, version;

For extensions with native shared objects/DLLs, the new PostgreSQL major needs extension files built/packaged for that new server. During pg_upgrade, install those shared libraries in the new installation before the upgrade; do not manually run CREATE EXTENSION to duplicate schema objects already present in the old cluster.

6. Collation/ICU changes can invalidate ordering assumptions

Indexes and constraints that depend on textual ordering assume a collation definition. An operating-system or ICU library update can change that definition even when table bytes are unchanged.

SELECT datname,       pg_encoding_to_char(encoding) AS encoding,       datlocprovider,       datcollate,       datctype,       datlocale,       datcollversionFROM pg_databaseWHERE datallowconnORDER BY datname;

SELECT c.oid::regcollation AS collation,       c.collprovider,       c.collversion AS recorded_version,       pg_collation_actual_version(c.oid) AS actual_versionFROM pg_collation AS cWHERE c.collversion IS DISTINCT FROM pg_collation_actual_version(c.oid)ORDER BY 1;

A version mismatch is evidence that dependent objects need assessment/rebuild. Running ALTER COLLATION ... REFRESH VERSION only updates catalog metadata; it does not rebuild or verify dependent indexes for you.

7. Inventory collated dependencies before refreshing versions

SELECT pg_describe_object(d.refclassid,d.refobjid,d.refobjsubid) AS collation,       pg_describe_object(d.classid,d.objid,d.objsubid) AS dependent_objectFROM pg_depend AS dJOIN pg_collation AS c  ON d.refclassid = 'pg_collation'::regclass AND d.refobjid = c.oidWHERE c.collversion IS DISTINCT FROM pg_collation_actual_version(c.oid)ORDER BY 1,2;

Use dependency evidence to decide which indexes/materialized expressions need rebuilding before refreshing version metadata. Collation changes are correctness issues, not just planner-performance details.

8. Application/client compatibility belongs in the same inventory

Server success is necessary but not sufficient. A major upgrade can expose a driver assumption, reserved-word conflict, authentication deprecation, or planner behavior change that no storage compatibility check can detect.

9. Supported-version policy

PostgreSQL supports each major for roughly five years. The operational policy should say: production majors must be supported, patch within each supported major to the current official minor after testing, plan major migration well before end-of-life (EOL), and never defer a security/data-corruption fix merely to avoid maintenance risk.

# Capture these URLs/versions in the change ticket at execution time:# - PostgreSQL Versioning Policy# - exact current-major minor release notes# - target-major Migration section# - PostgreSQL security advisories# - OS/vendor package release/changelogpsql --versionpg_config --version

Authoritative references

Upgrade, compatibility, locking, and migration behavior is version-sensitive. These PostgreSQL primary sources define the mechanisms used here; always read the exact source/target release notes during a real change.

• PostgreSQL — Versioning Policy
• PostgreSQL 18 — Upgrading a PostgreSQL Cluster
• PostgreSQL 18 — Release 18 / Migration Notes
• PostgreSQL 18 — pg_upgrade
• PostgreSQL 18 — ALTER COLLATION