Extension Upgrade Path

Extension versions move independently from Postgres majors. Plan OS package upgrades, ALTER EXTENSION UPDATE, and regression tests as a single change window, especially for pgvector and PostGIS.

Recipe

-- Check current vs target version
SELECT extname, extversion
FROM pg_extension
WHERE extname = 'vector';
 
SELECT name, default_version
FROM pg_available_extensions
WHERE name = 'vector';
 
-- Apply available upgrade scripts
ALTER EXTENSION vector UPDATE TO '0.8.0';
 
-- Verify
SELECT extname, extversion FROM pg_extension WHERE extname = 'vector';

When to reach for this: After patching Postgres, deploying new extension packages, or before building HNSW indexes that require a minimum pgvector version.

Working Example

-- Safe upgrade runbook (run on staging first)
BEGIN;
 
-- 1) Snapshot inventory
CREATE TEMP TABLE ext_before AS
SELECT extname, extversion FROM pg_extension;
 
-- 2) Upgrade one extension
ALTER EXTENSION postgis UPDATE;
 
-- 3) Compare
SELECT b.extname,
       b.extversion AS before_version,
       e.extversion AS after_version
FROM ext_before b
JOIN pg_extension e ON e.extname = b.extname
WHERE b.extversion IS DISTINCT FROM e.extversion;
 
-- 4) Smoke test
SELECT PostGIS_Version();
SELECT ST_Distance(
  ST_MakePoint(-73.9857, 40.7484)::geography,
  ST_MakePoint(-118.2437, 34.0522)::geography
) AS nyc_to_la_meters;
 
COMMIT;

What this demonstrates:

• Inventory before and after prevents silent skips
• ALTER EXTENSION UPDATE without TO applies latest available script
• Functional smoke queries catch broken SONAME mismatches early
• Transaction wraps metadata change; OS libs must already match

Deep Dive

Upgrade Mechanics

1. OS / package layer - Install postgresql-18-postgis-3.5 (or vendor equivalent) on every node.
2. Database layer - ALTER EXTENSION name UPDATE [TO 'version'] runs SQL migration scripts in EXTENSION(name)/.
3. Index layer - Some upgrades require REINDEX (pgvector index opclass changes). Read release notes.

-- List upgrade path scripts shipped with the extension
SELECT version, relocatable
FROM pg_available_extension_versions
WHERE name = 'vector'
ORDER BY version;

Minor vs Major Extension Versions

Multi-Node Clusters

• Primary first - Apply packages and ALTER EXTENSION on primary.
• Replicas - Packages must exist before replication replays extension-dependent queries.
• Patroni - Rolling OS package sync, then controlled ALTER EXTENSION during maintenance window.
• Managed cloud - Vendor bumps extension catalog; you still run ALTER EXTENSION in each database.

Rollback Reality

There is no ALTER EXTENSION DOWNGRADE. Rollback means restore from backup or recreate indexes after reinstalling older packages (high risk). Test upgrades on a snapshot clone.

Gotchas

• Binary/SQL mismatch - New SQL script with old .so file causes runtime crashes. Fix: Upgrade packages on all nodes before ALTER EXTENSION.
• Long-running reindex - pgvector HNSW rebuild blocks or consumes I/O. Fix: REINDEX CONCURRENTLY where supported; schedule off-peak.
• Forgotten databases - Extension updated in appdb but not analytics. Fix: Loop all databases: \l + per-DB ALTER EXTENSION.
• Breaking opclass names - Client queries reference old operator classes. Fix: Search codebase for index definitions; migrate in expand-contract phases.
• PostGIS topology extras - Separate extensions (postgis_topology) need their own UPDATE. Fix: Inventory all postgis* rows in pg_extension.
• Logical replication drift - Subscriber missing extension version. Fix: Match extension versions before subscribing to DDL-heavy publications.

Alternatives

FAQs

Related

• Extensions Basics - install and preload
• Trusted vs Untrusted Extensions - security review
• IVFFlat vs HNSW Indexes - index rebuild implications
• Flyway and Liquibase - migration automation
• Extensions Best Practices - pin versions in IaC

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PostGIS 3.5+, pgbouncer 1.x, and Patroni 3.x.