Upgrading PostGIS Major Versions on Kubernetes

This guide upgrades a PostGIS deployment across a major database version and a major extension version, in the order that keeps geometry, indexes and client behaviour intact. It belongs to Kubernetes StatefulSets for PostGIS Databases, within the Infrastructure Orchestration & Configuration Management framework.

Prerequisites #

• A verified backup with a tested restore, per taking consistent PostGIS backups with pgBackRest.
• A staging copy at production data volume, because plan and duration both depend on size.
• The extension inventory: PostGIS and everything alongside it, with current and target versions.
• A control-point dataset for coordinate verification, as described in the parity guidance.

Three versions move, not one #

A PostGIS upgrade is at least three upgrades happening together, and the failure modes belong to different layers. The database engine changes, which affects plans and configuration. The PostGIS extension changes, which affects function behaviour and sometimes on-disk representation. And the geospatial libraries underneath — the projection, geometry and raster libraries — change with the image, which is the layer most likely to alter output silently.

The bottom row is the one that produces the incident nobody expects. A projection library update can change the resolution of a coordinate transformation by metres, which is invisible in every functional test and immediately visible to a surveyor comparing a parcel boundary against a fence.

Step-by-step implementation #

1. Inventory the extensions and their targets #

SELECT e.extname,
       e.extversion                             AS current,
       (SELECT max(version) FROM pg_available_extension_versions v
         WHERE v.name = e.extname)              AS available
FROM pg_extension e
ORDER BY e.extname;
--   record this before starting; it is the checklist for the new image

-- And the underlying library versions, which the image decides.
SELECT postgis_full_version();
--   note the GEOS, PROJ and GDAL versions specifically

2. Rehearse on a staging copy at real volume #

# Restore production-scale data into a staging StatefulSet running the NEW image,
# and time the whole thing — the duration is the maintenance window.
pgbackrest --stanza=geoportal --type=time --target="$LATEST" \
  --pg1-path=/var/lib/postgresql/upgrade-test restore

time pg_upgrade \
  --old-datadir=/var/lib/postgresql/16/main \
  --new-datadir=/var/lib/postgresql/17/main \
  --old-bindir=/usr/lib/postgresql/16/bin \
  --new-bindir=/usr/lib/postgresql/17/bin \
  --link                                  # far faster; requires same filesystem
#   record the elapsed time: this is what the window must accommodate

3. Upgrade the extension after the engine, in the right order #

-- Engine first (pg_upgrade above), then the extension, then anything that
-- depends on it. The reverse order produces functions bound to the old library.
ALTER EXTENSION postgis UPDATE;
SELECT postgis_extensions_upgrade();      -- upgrades postgis_raster, topology, etc.

-- Confirm no function is still bound to a previous library version.
SELECT postgis_full_version();
SELECT * FROM postgis_extensions_upgrade();

4. Rebuild what the upgrade invalidates #

-- Spatial indexes may need rebuilding across a major extension change, and
-- statistics are always stale after an upgrade — plans will be wrong until
-- ANALYZE has run, which is a common cause of "the upgrade made it slow".
REINDEX INDEX CONCURRENTLY parcels_geom_idx;
ANALYZE VERBOSE parcels;

-- Materialised views built on spatial functions should be refreshed, not trusted.
REFRESH MATERIALIZED VIEW CONCURRENTLY parcels_by_ward;

5. Verify coordinates, not only queries #

-- Run before and after; the results should match within the stated tolerance.
SELECT id,
       round(ST_X(ST_Transform(geom, 27700))::numeric, 4) AS easting,
       round(ST_Y(ST_Transform(geom, 27700))::numeric, 4) AS northing
FROM control_points
ORDER BY id;

Choosing the upgrade path #

Three paths exist and they trade window length against complexity. Choosing deliberately, and writing down why, is what makes the next upgrade shorter.

Whichever path is chosen, the extension and library steps are identical — the path only changes how the data arrives in the new cluster, not what has to be verified once it is there.

Verification #

# 1. Engine and extension versions are the expected ones
psql -Atc "SHOW server_version;"
psql -Atc "SELECT postgis_full_version();"

# 2. Nothing is left on an old extension version
psql -Atc "SELECT extname, extversion FROM pg_extension ORDER BY 1;"
#   expect: every extension at its target version

# 3. Spatial queries use the spatial index, and plans are not worse
psql -c "EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM parcels
         WHERE geom && ST_MakeEnvelope(-1.31,50.90,-1.28,50.92,4326);"
#   compare execution time and plan shape against the pre-upgrade baseline

# 4. Control points match within tolerance
diff <(psql -Atf control_points.sql -d geoportal_pre) \
     <(psql -Atf control_points.sql -d geoportal)
#   expect: no differences, or differences you have decided to accept

# 5. The portal serves a real tile and a real feature query
curl -s -o /dev/null -w '%{http_code}\n' "https://portal.example.gov/geoserver/wms?service=WMS&request=GetMap&layers=parcels&bbox=-1.31,50.90,-1.28,50.92&width=256&height=256&format=image/png&srs=EPSG:4326"
curl -s "https://portal.example.gov/geoserver/wfs?service=WFS&version=2.0.0&request=GetFeature&typeNames=parcels&count=1" | head -3

Troubleshooting matrix #

FAQ #

Is pg_upgrade --link safe? #

It is fast and it makes the old data directory unusable afterwards, because the new cluster hard-links its files. That is an acceptable trade when a verified backup exists and has been restored somewhere recently — and it removes the rollback you might otherwise have assumed you had. Confirm the backup before choosing link mode, not after.

Can a major PostGIS upgrade be done without downtime? #

Not the extension upgrade itself, which needs a restart, though the window can be short. Logical replication into a new cluster running the target versions gives a much smaller cutover for portals that need it, at the cost of a considerably more complex procedure and careful handling of sequences and large objects.

What about the rendering engine’s compatibility? #

Check it explicitly. A rendering engine pinned to a particular driver version may not support the new database version, and the failure appears as datastores that will not connect after the upgrade rather than as anything the database reports. Test the engine against the new version in the rehearsal, not only the SQL.

Should the extension be upgraded at the same time as the engine? #

In the same window, in the stated order, and not in the same step. Doing them together is efficient and makes attribution harder if something is wrong; doing them in separate windows doubles the disruption. The pragmatic middle is one window, sequential steps, with a verification pause between them.

How long should the old version remain available? #

Until the new one has served a full traffic cycle including whatever monthly job the portal runs, because that job is the most likely thing to meet a compatibility problem nobody tested. Keep the backup and the previous image available for that period rather than cleaning up immediately.

What should be recorded afterwards? #

The versions of all three layers, the measured upgrade duration, the control-point comparison and its tolerance, and the plan comparison for the representative query set. The next upgrade starts from that record, and — more immediately — it is what answers a question about coordinate differences six months later.

How should the maintenance window be communicated? #

With the expected duration from the rehearsal, the read-only period stated separately from the fully-unavailable period, and a named point at which the decision to roll back would be taken. Partner integrations in particular need the window in advance: a nightly harvest that runs into a stopped database produces a failed run and, on many partner systems, an alert to somebody who has no idea an upgrade was planned.

Is it worth upgrading the extension and the engine in separate releases? #

If the window allows two, yes — it makes attribution trivial when something behaves differently, because only one variable moved. Most teams cannot justify two windows and should instead run the two steps sequentially within one, with the verification pause between them, so that a problem is still attributable to a step even if not to a release.

What is the most commonly skipped step? #

Running ANALYZE afterwards. The upgrade discards planner statistics, so every query is planned from nothing until they are rebuilt, and the portal is measurably slower for as long as that takes. Teams then investigate the new version’s performance rather than running a command that takes minutes — and the investigation frequently ends with an unnecessary rollback.

Related #

• Deploying PostGIS on Kubernetes with Persistent Volumes — the deployment this upgrades.
• Taking Consistent PostGIS Backups with pgBackRest — the rollback path.
• Sizing PostGIS for Concurrent WFS Queries — re-measuring the ceiling afterwards.

Up one level: Kubernetes StatefulSets for PostGIS Databases.