Managing PostGIS extension versions across environments

This page makes PostGIS versions an explicit, tested part of a dbt project: an assertion that runs before any model builds, a documented upgrade path, and a clear account of which mismatches change results rather than merely changing error messages.

When to use this approach

• Models pass in CI and fail in production, or worse, pass in both and disagree. Version skew between GEOS builds is a common cause and an invisible one until you look.
• You are about to upgrade PostGIS. The upgrade itself is one command; knowing what it changes for your models is the work.
• Your team develops on several machines. Local PostGIS installed by different package managers drifts within weeks. The setup this builds on is in setting up PostGIS with dbt.

Prerequisites

• Superuser or CREATE EXTENSION rights on the target database for the upgrade steps.
• dbt-postgres 1.7+ and the ability to run on-run-start hooks.
• A record of the versions currently running in each environment — the audit in step 1 produces it.
• A staging database you can upgrade first. Never learn what an upgrade changes in production.

Step-by-step instructions

1. Audit what is actually installed

PostGIS is three versions in a trench coat: the extension itself, the GEOS geometry engine underneath it, and PROJ for coordinate transformation. Any of them can differ between environments.

select
    postgis_version()          as postgis,
    postgis_lib_version()      as lib,
    postgis_geos_version()     as geos,
    postgis_proj_version()     as proj,
    version()                  as postgres;

 postgis |  lib   |     geos     |   proj   |         postgres
---------+--------+--------------+----------+---------------------------
 3.4 USE_GEOS=1 … | 3.4.2 | 3.12.1-CAPI-1.18.1 | 9.3.1 | PostgreSQL 16.2 …

Verify by running the same query in every environment and putting the results side by side. The row that most often differs — and matters most — is PROJ, because a PROJ upgrade can change the datum shift used for a transformation, and therefore move your coordinates by a metre or two.

2. Assert the versions before any model builds

-- macros/assert_postgis_version.sql
{% macro assert_postgis_version() %}
  {% if target.type != 'postgres' %}{{ return('') }}{% endif %}
  {% set required = var('required_postgis_major', '3.4') %}
  {% set result = run_query("select postgis_lib_version() as v") %}
  {% if execute %}
    {% set actual = result.columns[0].values()[0] %}
    {% if not actual.startswith(required) %}
      {{ exceptions.raise_compiler_error(
          "PostGIS " ~ required ~ ".x required, found " ~ actual ~
          " on target '" ~ target.name ~ "'.") }}
    {% endif %}
    {{ log("PostGIS " ~ actual ~ " on " ~ target.name, info=True) }}
  {% endif %}
{% endmacro %}

# dbt_project.yml
on-run-start:
  - "{{ assert_postgis_version() }}"

vars:
  required_postgis_major: '3.4'

Verify the guard actually stops a build:

dbt run --select stg_zones --vars '{required_postgis_major: "9.9"}'
# Expect a compilation error naming the found version — the guard works

3. Upgrade in a defined order

The extension upgrade is a single statement, but the surrounding sequence is what keeps it safe.

-- 1. Check what upgrade the installed binaries expect
select postgis_extensions_upgrade();

-- 2. Or upgrade explicitly, per extension
alter extension postgis update to '3.4.2';
alter extension postgis_raster update to '3.4.2';

-- 3. Re-check
select postgis_full_version();

# 4. Rebuild the project on the upgraded staging database and compare
dbt build --target staging --full-refresh
dbt test --select tag:parity --target staging

Verify with a parity comparison rather than a smoke test: build the same models on the old and new versions and diff the aggregates. The differences that matter are small and numerical, so an eyeball check of a few rows will not find them.

4. Pin the version where the environment is built

An assertion catches a mismatch; pinning prevents one. Wherever an environment is created from code, name the exact image or package.

# docker-compose.yml for local development
services:
  postgis:
    image: postgis/postgis:16-3.4          # never `latest`
    environment:
      POSTGRES_PASSWORD: "${PG_PASSWORD}"

# .github/workflows/dbt-spatial-ci.yml (excerpt)
    services:
      postgis:
        image: postgis/postgis:16-3.4
        options: >-
          --health-cmd "pg_isready -U postgres"
          --health-interval 10s --health-retries 5

Verify that local, CI and production report identical postgis_full_version() strings. Anything else is a difference you will eventually spend a day on.

5. Add a transformation parity test

-- tests/assert_transform_stability.sql
with reference (label, wkt, from_srid, to_srid, expected_x, expected_y) as (
    values ('berlin', 'POINT(13.377704 52.516275)', 4326, 25833, 389677.02, 5819160.53)
)
select
    label,
    round(st_x(st_transform(st_setsrid(st_geomfromtext(wkt), from_srid), to_srid))::numeric, 2) as actual_x,
    expected_x
from reference
where abs(st_x(st_transform(st_setsrid(st_geomfromtext(wkt), from_srid), to_srid)) - expected_x) > 0.5

A half-metre tolerance is deliberately tight: it passes across patch releases and fails on the datum-grid changes that a PROJ major upgrade can introduce, which is exactly the event you want to hear about.

Configuration reference

Gotchas & edge cases

• The extension version and the library version are different things. ALTER EXTENSION UPDATE changes the SQL-level definitions; the shared library comes from the OS package. A database can report a new extension version while still running old binaries until the server restarts.
• ST_MakeValid output changes between GEOS releases. Repaired geometry is not guaranteed identical across versions, so a mart built from repaired input can shift slightly after an upgrade — one reason to quarantine rather than silently repair.
• PROJ grid files are separate from PROJ itself. A missing datum grid causes a fallback to a less accurate transformation, quietly, with results differing by metres.
• Upgrading PostGIS does not reindex. GiST indexes remain valid, but statistics do not; run ANALYZE afterwards or the first builds will choose surprising plans.
• postgis_full_version() in a dbt log is worth more than any runbook. Log it on every run so an incident can be dated to a version change without archaeology.

FAQ

Related

• Setting Up PostGIS with dbt — the installation this maintains.
• How to Install the dbt PostGIS Adapter Step by Step — first-time setup.
• Automating CRS Conversions in dbt Pipelines — the transformations a PROJ change affects.

Up: Part of Setting Up PostGIS with dbt.