Spatial IaC Terraform & Pulumi for GIS

Zero-Downtime PostGIS Major Version Upgrades

A major version upgrade of a spatial database is two upgrades wearing one name. The PostgreSQL engine moves — 15 to 16, say — and the PostGIS extension moves with it, and the extension carries its own compatibility surface: function signatures, the raster module’s packaging, and index operator classes that may require rebuilding. An in-place upgrade will do both, take the database offline for the duration, and leave you with indexes the planner distrusts. Achieving it without downtime means running both versions simultaneously and moving traffic, which is a different procedure with a different set of hazards. This guide extends PostGIS Cluster Provisioning within Geospatial Resource Provisioning.

Choosing the upgrade path

Two paths exist and the choice is determined by how much downtime the tile tier can absorb, not by preference.

In-place upgrade. The managed service upgrades the instance, taking it offline for a window that scales with database size — typically minutes for tens of gigabytes, longer for terabytes of geometry. Simple, one command, fully supported, and unavailable to you if the platform serves interactive maps during business hours across time zones.

Logical replication cut-over. Provision a new cluster at the target version, replicate the data into it logically, let it catch up, then switch the application’s endpoint. Downtime shrinks to the length of the switch, seconds rather than minutes, at the cost of a substantially more involved procedure and a period where two clusters both hold the data.

The spatial complications apply to both. PostGIS must be upgraded within the database after the engine moves, with ALTER EXTENSION postgis UPDATE, and until that runs the extension’s function definitions may not match the installed library. Spatial indexes should be reindexed after a major upgrade because operator-class internals can change between versions, and statistics must be regenerated because the planner starts with none — a freshly upgraded cluster whose statistics have not been rebuilt will choose sequential scans over your GiST indexes and look dramatically slower than the cluster it replaced.

In-place upgrade compared with a logical replication cut-over The in-place path applies the engine upgrade directly to the running instance, which is offline for a window proportional to database size, then requires the PostGIS extension update, a reindex of spatial indexes and a statistics rebuild before performance returns. The cut-over path provisions a second cluster already at the target version, establishes logical replication into it, waits until replication lag reaches zero, then switches the application endpoint — so the outage is only the length of the switch. Both paths require the same PostGIS extension update, spatial reindex and statistics rebuild afterwards. In-place modify engine version offline window scales with data size extension update · reindex · analyze Cut-over new cluster, new version logical replication wait for zero lag switch endpoint seconds reindex · analyze A freshly upgraded cluster has no statistics: until ANALYZE runs it will prefer sequential scans over your GiST indexes and appear dramatically slower than the cluster it replaced. This is expected, and it is not a reason to roll back.

Prerequisites and environment assumptions

Terraform 1.6 or later with hashicorp/aws pinned at ~> 5.60. A recent snapshot, verified by an actual restore rather than by its existence in a console list. Confirmation that the target engine version ships a PostGIS version compatible with your schema — a check that has to be done against the release notes for both, since a PostGIS major move can deprecate function signatures your views depend on.

Before anything else, inventory what depends on PostGIS behaviour. Materialised views over spatial functions, functions using deprecated signatures, and any application code pinning a PostGIS version string all need review. SELECT * FROM postgis_extensions_upgrade() and the deprecation notes are the reference; the audit is unglamorous and it is where the surprises are.

Step-by-step cut-over upgrade

  1. Provision the target cluster from code. Same instance class, same subnet group, the tuned parameter group regenerated for the new engine family — a parameter group is family-specific, so postgres15 settings do not carry to postgres16 and the new group must be created deliberately rather than inherited.

  2. Create the publication and subscription. Logical replication copies table data; it does not copy schema, sequences’ current values, or large objects. Create the schema on the target first, including the PostGIS extension at the target version, then subscribe.

  3. Let it catch up and watch the lag. Replication lag must reach and hold near zero before you consider switching. On a large geometry table the initial copy can take hours, and the copy is the part that surprises teams who scheduled a one-hour window.

  4. Freeze writes briefly and confirm zero lag. Stop write traffic — a read-only period of seconds — and confirm the subscription has consumed everything. This is the only true downtime in the procedure.

  5. Switch the endpoint. Update the DNS record or the connection secret that the renderers and pipelines read, so consumers reconnect to the new cluster. Managing this through the secret rather than through hardcoded hostnames is what makes it a one-line change, which is the pattern in Rotating PostGIS Credentials in Terraform Without Downtime.

  6. Reset sequences, reindex spatially, and analyze. Logical replication does not advance sequences on the target; a missed reset produces duplicate key errors on the first insert. Then REINDEX the spatial indexes and run ANALYZE across the spatial tables before releasing traffic, or the first minutes on the new cluster will look like a performance regression.

-- On the SOURCE cluster.
CREATE PUBLICATION spatial_pub FOR ALL TABLES;

-- On the TARGET cluster, after creating the schema and the extension.
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE SUBSCRIPTION spatial_sub
  CONNECTION 'host=old.cluster.internal dbname=gis user=replicator sslmode=verify-full'
  PUBLICATION spatial_pub;

-- Watch until this is empty or near zero before switching.
SELECT slot_name,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag
FROM pg_replication_slots;

-- After the switch, on the TARGET. Sequences are NOT replicated: skipping this
-- produces duplicate key errors on the first insert.
SELECT setval('parcels_id_seq', (SELECT max(id) FROM parcels));

-- Operator-class internals can change across a major version. Rebuild spatial
-- indexes concurrently so reads continue while it runs.
REINDEX INDEX CONCURRENTLY parcels_geom_idx;

-- The new cluster starts with no statistics at all.
ANALYZE parcels;
ANALYZE roads;
# The parameter group is family-specific: postgres15 settings do not carry
# forward, so the tuned group is regenerated for the new family rather than
# reused. See the tuning guide for how these values are derived.
resource "aws_db_parameter_group" "postgis_16" {
  name   = "postgis-tuned-16"
  family = "postgres16"

  parameter {
    name         = "shared_buffers"
    value        = tostring(local.shared_buffers_blocks) # 8 kB blocks
    apply_method = "pending-reboot"
  }
  parameter {
    name         = "random_page_cost"
    value        = "1.1"
    apply_method = "immediate"
  }
}

resource "aws_db_instance" "postgis_16" {
  identifier             = "gis-postgis-16"
  engine                 = "postgres"
  engine_version         = "16.4"
  parameter_group_name   = aws_db_parameter_group.postgis_16.name
  # Logical replication requires the WAL to carry enough detail; on managed
  # PostgreSQL this is enabled through the parameter group on the SOURCE.
  skip_final_snapshot    = false
  lifecycle { prevent_destroy = true }
}
Where the downtime actually falls in a cut-over A timeline in three parts. During the initial copy and catch-up, which can run for hours on a large geometry table, the old cluster continues to serve all traffic normally. The only true downtime is a brief write freeze of seconds while the subscription drains the last changes and the endpoint is switched. After the switch, the sequence reset, spatial reindex and analyze run on the new cluster while it is already serving traffic, though performance is below normal until analyze completes. Copy and catch-up — hours old cluster serves everything, normally Freeze seconds New cluster serving reindex and analyze in progress The hours are not downtime. The seconds are. Sequences do not replicate — reset them before the first write, or the first insert fails on a duplicate key. Reindex spatial indexes concurrently so reads continue while operator-class internals are rebuilt. Hold the old cluster, stopped but not destroyed, until the new one has survived a full business cycle.

Verification

Confirm both versions moved, not just one: SELECT version() for the engine and SELECT postgis_full_version() for the extension, and check that the reported GEOS and PROJ versions are the ones you expected — a PostGIS built against a different PROJ can change reprojection results at the last decimal places, which matters for anything that compares geometries for equality.

Then confirm the data and the plans. Row counts on the largest spatial tables must match the source. A representative tile query must produce an index scan, not a sequential scan; if it does not, ANALYZE has not finished. Fetch a tile through the real endpoint and compare it byte-for-byte with one captured before the upgrade — identical bytes prove the whole chain, from geometry through the encoder, is unchanged. Finally confirm writes work, which is the check that catches the unset sequence.

What to compare against the cluster you just left Five comparisons close out the cut-over. Engine and PostGIS versions must be the ones intended, and the GEOS and PROJ versions beneath them must be checked as well, because a PostGIS built against a different PROJ can shift reprojection results in the last decimal places, which matters wherever geometries are compared for equality. Row counts on the largest spatial tables must match the source exactly. The plan for a representative tile query must show an index scan rather than a sequential scan, and if it does not, the statistics rebuild has not finished. And a tile fetched through the real endpoint should compare byte for byte with one captured before the upgrade, which proves the whole chain from geometry through the encoder is unchanged. Engine and PostGIS versions the ones you intended, not the ones you got GEOS and PROJ underneath a different PROJ shifts the last decimal places Row counts on the largest tables must match the source exactly Plan for a representative tile query sequential scan means ANALYZE has not finished And one tile, fetched through the real endpoint, compared byte for byte with a capture taken before the switch.

Preventing recurrence

  • Rehearse on a restored snapshot. The entire procedure, including timings, on a copy of production. The initial copy duration is the number that invalidates most upgrade plans, and it is only knowable by measuring.
  • Keep the old cluster stopped, not destroyed, for a full business cycle. A weekly reconciliation job that nobody remembered will exercise a code path the first day did not.
  • Encode the post-upgrade steps as a script, not a checklist. Sequence resets, reindexes and analyzes are exactly the steps a tired operator skips at 2am.
  • Track version currency as a scheduled task. Major upgrades are painful in proportion to how many versions are skipped; upgrading one version behind is routine, upgrading four is a project.

Frequently Asked Questions

Do I have to reindex spatial indexes after a major upgrade?

Treat it as required unless the release notes explicitly say the operator classes are unchanged. Rebuilding is cheap relative to the cost of discovering, days later, that a GiST index is producing correct but slow results. Use REINDEX CONCURRENTLY so reads continue during the rebuild.

Why is the new cluster slower immediately after the switch?

Almost always missing statistics. A freshly populated cluster has none, so the planner cannot estimate selectivity and falls back to sequential scans. Run ANALYZE across the spatial tables and re-measure before drawing any conclusion about the upgrade.

Can I upgrade a read replica first and promote it?

Managed services generally do not permit a replica to run a different major version from its primary, so the promote-an-upgraded-replica pattern is not available. Logical replication into a new cluster is the supported route to the same outcome.

What if the application depends on a deprecated PostGIS function?

Fix it before the upgrade, not during. Deprecated signatures should be found by the dependency audit in the prerequisites; discovering one during the cut-over turns a switch you can reverse into an incident you cannot, because the old cluster has by then diverged.