Upgrades and routine maintenance

conceptThe concept

Minor upgrades: install new binaries and restart; no data conversion. Read the release notes for occasional "reindex these indexes" instructions. Each major version is supported for five years.

Major upgrades with pg_upgrade: initialise a new cluster, run pg_upgrade --check, then upgrade with --link (hard links, minutes even for terabytes, but the old cluster cannot be started afterwards) or --clone / --swap (18). Afterwards, update extensions and run vacuumdb --analyze-in-stages (statistics are carried over since 18, but extended statistics are not). Logical replication upgrades trade complexity for near-zero downtime and an easy rollback path.

Routine maintenance: REINDEX CONCURRENTLY for bloated or corrupted indexes, ALTER EXTENSION ... UPDATE after upgrades, and watching for collation changes: an OS glibc upgrade can change text sort order and silently corrupt B-tree indexes on text columns. Since 17, the builtin collation provider avoids that dependency.

whyWhy it matters

Running unsupported versions means missing security fixes, and upgrades put off for years become multi-version jumps with more surprises. A rehearsed upgrade runbook turns a scary weekend into a routine task.

Use caseReal-world scenario

The shop moves from 14 to 18: rehearsal on a restored copy with pg_upgrade --check and timing, application tests against 18, then a production run with --link during a 15-minute window, followed by analyze-in-stages and extension updates.

Try itExamples

Run and edit these in the interactive lesson.

SELECT current_setting('server_version') AS version,
       current_setting('server_version_num')::int / 10000 AS major;
SELECT name, default_version, installed_version,
       default_version IS DISTINCT FROM installed_version AS needs_update
FROM pg_available_extensions
WHERE installed_version IS NOT NULL
ORDER BY name;

# 1. new binaries installed, new cluster initialised with matching options
/usr/lib/postgresql/18/bin/initdb -D /data/pg18 --data-checksums --locale-provider=builtin --builtin-locale=C.UTF-8

# 2. dry run while the old cluster is still up
/usr/lib/postgresql/18/bin/pg_upgrade -b /usr/lib/postgresql/14/bin -B /usr/lib/postgresql/18/bin \
  -d /data/pg14 -D /data/pg18 --link --check

# 3. stop the old cluster, upgrade for real, start the new one
/usr/lib/postgresql/18/bin/pg_upgrade ... --link --jobs=8

# 4. after start
psql -c "ALTER EXTENSION pg_stat_statements UPDATE"
vacuumdb --all --analyze-in-stages --missing-stats-only
# 5. rebuild standbys (rsync or new base backups), re-run backups

SELECT indexrelid::regclass AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       idx_scan
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 5;
REINDEX INDEX CONCURRENTLY order_items_pkey;

SELECT datname, datlocprovider, datcollate, datcollversion,
       pg_database_collation_actual_version(oid) AS actual_version
FROM pg_database
WHERE datname = current_database();

Avoid / preferDo this, not that

-- pg_upgrade on production first, extensions checked afterwards

-- rehearse on a restored copy: extensions, pg_upgrade --check, application tests

-- OS upgrade changes glibc; indexes on text are left as they are

SELECT datname, datcollversion, pg_database_collation_actual_version(oid) FROM pg_database;
-- REINDEX affected indexes, then ALTER DATABASE ... REFRESH COLLATION VERSION

PitfallsCommon mistakes

• Skipping minor releases for months. They contain security and data-corruption fixes and only need a restart.
• Running pg_upgrade --link without a tested backup. After the new cluster starts, the old one is no longer usable.
• Upgrading the operating system under a running PostgreSQL without checking glibc collation changes.

LimitsWhen not to use it

Do not postpone minor releases: they contain only bug and security fixes and need just a restart. Major upgrades deserve planning; minor ones deserve a schedule.

SecuritySecurity notes

Minor releases fix CVEs. Track the PostgreSQL security announcements and apply minor releases within weeks, and stay on a major version that is still supported (five years from release).

ChallengeTry it yourself

SELECT name, installed_version, default_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL AND installed_version <> default_version;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Upgrading a cluster
• pg_upgrade
• REINDEX