Versioning & Upgrade Policy
PostgreSQL ships major releases yearly with new features and minor releases with security and bug fixes. Plan upgrades before EOL. PostgreSQL 18 is current stable; 17 remains on maintenance. Treat minor upgrades as mandatory, majors as projects.
Recipe
SELECT version();
SHOW server_version_num;
-- After upgrade, analyze for planner stats
ANALYZE;# Minor upgrade (same data directory, new binaries)
pg_ctl stop -D $PGDATA
# install postgresql-18.x point release
pg_ctl start -D $PGDATAWhen to reach for this: You set org policy for supported versions, patching cadence, and major migration windows.
Working Example
-- Catalog compatibility check helpers post-major upgrade
SELECT extname, extversion FROM pg_extension ORDER BY 1;
SELECT count(*) AS invalid_indexes
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;What this demonstrates:
server_version_numis comparable as an integer in scripts- Extensions may need
ALTER EXTENSION ... UPDATEafter server upgrade - Invalid indexes flag rebuild work after pg_upgrade or restore
Deep Dive
Release Types
- Major (17 to 18): catalog changes, initdb not required if using pg_upgrade or logical replication.
- Minor (18.3 to 18.4): drop-in binary replacement, same PGDATA format.
- EOL: unsupported versions receive no security fixes.
Upgrade Paths
| Path | Downtime | Notes |
|---|---|---|
| pg_upgrade | Minutes to hours | Fast, same host or new data dir |
| Logical replication | Low | Version skew friendly, cutover step |
| Dump/restore | High | Simplest mental model, slowest at scale |
Policy Baseline
- Run latest minor on supported major within 30 days of release.
- Major upgrade within 6 months of previous major EOL announcement.
- Test extensions (pgvector, PostGIS) in staging before prod major.
Gotchas
- Skipping minors - Security CVEs land in minor releases only.. Fix: Automate apt/yum/dnf or image bumps monthly.
- Extension ABI lag - PostGIS and pgvector need matching builds per major.. Fix: Pin versions in runbook before upgrade weekend.
- Statistics reset - Major upgrades can invalidate plans until ANALYZE runs.. Fix: Schedule ANALYZE or rely on autovacuum analyze thresholds.
- Replication version mix - Subscribers can be newer; publishers older with care.. Fix: Read logical replication compatibility matrix.
- OS library drift - libpq vs server mismatch causes odd protocol errors.. Fix: Upgrade client libraries with server.
Alternatives
| Alternative | Use When | Don't Use When |
|---|---|---|
| Managed auto-minor | RDS/Cloud SQL handles patching | Less control on timing |
| Blue/green major | New cluster plus cutover | Double storage during migration |
| Stay one major behind | More community battle-testing | Shorter window on new features |
FAQs
Is PostgreSQL 18.4 production ready?
Yes for teams tracking stable releases; validate extensions and workload plans in staging.
How long is PostgreSQL 17 supported?
Five years from its initial release per community policy; confirm current EOL dates on postgresql.org.
Do I need REINDEX after upgrade?
Usually no. Reindex if pg_upgrade reports invalid indexes or after collation updates.
Can applications stay connected during minor upgrade?
Brief restart disconnects sessions; use pooler failover or maintenance window.
What about Postgres forks?
Treat version policy separately; this site documents community PostgreSQL.
Related
- PostgreSQL Basics - version() and cluster tour
- Installation Basics - packaging channels
- Governance Basics - upgrade calendar ownership
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.