How to install the dbt-postgis adapter step by step

This page shows you how to stand up a working spatial adapter for dbt by installing dbt-postgres, enabling the PostGIS extension, and wiring search_path, type overrides, and a GiST index hook so geometry columns compile and materialize correctly end to end.

When to use this approach

There is no standalone dbt-postgis package on PyPI — spatial support is dbt-postgres talking to a PostgreSQL database that has the PostGIS extension enabled. Choose this stack when:

The three layers an installation has to line up Three layers stacked: the Python environment holding dbt-core and the adapter package, the database server holding the PostGIS binaries, and the target database holding the created extension. Each layer has its own version and its own failure message, and a mismatch between any two produces a different error, listed beside each layer. Python environment dbt-core + dbt-postgres, pinned together mismatch → adapter import error database server PostGIS shared libraries installed on the host missing → CREATE EXTENSION fails target database extension created in this database, in a schema on the path missing → function does not exist
  • You need a production-serving spatial engine with full topology functions and write concurrency. This is the default for setting up PostGIS with dbt; if you only need fast, file-native validation in CI, use the in-process DuckDB spatial extension instead.
  • Your serving layer is Postgres-compatible (vanilla PostgreSQL, Aurora, Cloud SQL, Supabase). If your warehouse is already Snowflake or BigQuery, weigh native GEOGRAPHY first in choosing the right spatial adapter.
  • You want geometry types, GiST indexes, and ST_ functions resolved natively at compile time rather than coerced to text — which is exactly what the type-override step below prevents.

Prerequisites

  • dbt Core 1.9.x and dbt-postgres 1.9.x (pin the minor version to avoid connection-pool and macro-resolution breaks).
  • PostgreSQL 14+ with PostGIS 3.x — needed for stable parallel spatial scans and modern index handling.
  • Database grants: CREATE on the target schema and the privilege to run CREATE EXTENSION (superuser or a role with CREATE on the database), plus USAGE on the extension schema if PostGIS is isolated.
  • Connection secrets supplied through dbt’s env_var() — never hardcode hosts or credentials in profiles.yml. Export them before running dbt:
bash
export DB_HOST=... DB_USER=... DB_PASS=... DB_NAME=analytics
The dbt-postgis stack is assembled from dbt-postgres plus the PostGIS extension A five-layer stack. The top three layers are installed on the client with pip: dbt Core orchestrates the model DAG, the dbt-postgres adapter compiles and runs SQL, and a bundled psycopg2 connection tuned with search_path links them to the database. The bottom two layers live on the database server: PostgreSQL executes and stores tables, and the PostGIS extension adds the geometry type, ST_ functions, GiST indexing, and the GEOS and PROJ libraries. There is no separate dbt-postgis package; spatial support is the combination of the dbt-postgres adapter and the PostGIS extension. pip install dbt-postgis No such package exists — spatial support is the dbt-postgres adapter (layer 2) plus the PostGIS extension (layer 5) pip install (client) in the database (server) dbt Core 1.9.x Orchestrates the model DAG pulled in as a dependency dbt-postgres 1.9.x adapter Compiles & runs SQL · no spatial type knowledge alone pip install dbt-postgres psycopg2 connection search_path → PostGIS schema (profiles.yml) bundled driver PostgreSQL 14+ Executes SQL · stores materialized tables your DB server PostGIS 3.x extension geometry type · ST_ functions · GiST · GEOS / PROJ CREATE EXTENSION postgis; TCP · search_path applied

Step-by-step instructions

1. Install the base PostgreSQL adapter

Provision a clean, isolated Python environment so spatial dependencies do not collide with other data-science libraries, then install the pinned adapter:

bash
python -m venv .venv && source .venv/bin/activate
pip install "dbt-postgres==1.9.*"

Verify the adapter and its dbt Core version are present:

bash
dbt --version
# Expect: installed: 1.9.x  and  plugins: postgres: 1.9.x

2. Enable PostGIS at the database level

The adapter cannot inject spatial functions — the extension must exist in the target database before dbt compiles any model that references geometry. Connect as a superuser or the schema owner and run:

sql
CREATE EXTENSION IF NOT EXISTS postgis SCHEMA public;

If your organization enforces schema isolation, point SCHEMA at your analytics namespace and make sure that schema is on every consumer’s search_path.

Verify the install and inspect the bundled GEOS and PROJ libraries:

sql
SELECT PostGIS_Version();
-- Expect e.g. "3.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1"

3. Configure profiles.yml and the search_path

Add credentials to ~/.dbt/profiles.yml (or inject them through your CI secret manager). The load-bearing detail is search_path: it must include the schema that owns the PostGIS extension, or dbt fails to resolve the geometry type at parse time.

yaml
your_project:
  target: dev
  outputs:
    dev:
      type: postgres
      host: "{{ env_var('DB_HOST') }}"
      port: 5432
      user: "{{ env_var('DB_USER') }}"
      password: "{{ env_var('DB_PASS') }}"
      dbname: "{{ env_var('DB_NAME') }}"
      schema: analytics
      threads: 4
      keepalives_idle: 0
      search_path: "public,analytics"

Spatial joins parallelize well, so a higher threads count helps — but watch your server’s max_connections. Setting keepalives_idle: 0 lets the OS default govern TCP keepalives, which avoids idle-connection drops on managed PostgreSQL during long-running aggregations.

Verify the connection and confirm the resolved search path:

bash
dbt debug
# Expect "Connection test: OK connection ok" and a search_path that includes the PostGIS schema.

4. Enforce geometry types with a project hook and macro

dbt’s default type handling does not recognize geometry or geography, so without an override it can cast spatial columns toward text and destroy coordinate precision. Pin a canonical SRID as a project variable, add a GiST index post-hook, and centralize type construction in one macro. Index strategy and planner steering are covered in depth under index hints for spatial queries.

yaml
# dbt_project.yml
vars:
  project_srid: 4326   # canonical storage CRS

models:
  your_project:
    +materialized: table
    +post-hook:
      - "CREATE INDEX IF NOT EXISTS idx_{{ this.name }}_geom ON {{ this }} USING GIST (geom);"
sql
-- macros/spatial/postgis_type.sql
{% macro postgis_type(type_name) %}
  {%- if type_name == 'geometry' -%}
    geometry(Geometry, {{ var('project_srid') }})
  {%- elif type_name == 'geography' -%}
    geography(Geography, {{ var('project_srid') }})
  {%- else -%}
    {{ type_name }}
  {%- endif -%}
{% endmacro %}

Routing every spatial column declaration through one macro keeps SRID enforcement consistent across the spatial model dependency graph, so no model silently emits an untagged SRID 0 geometry.

Verify the macro compiles to the type you expect:

bash
dbt compile -s some_spatial_model
# Inspect target/compiled/.../some_spatial_model.sql for geometry(Geometry, 4326).

5. Run a test model and validate it

Create a minimal staging model that constructs and labels a point, so a green run proves the whole adapter path works:

sql
-- models/staging/stg_spatial_test.sql
WITH points AS (
  SELECT
    id,
    ST_SetSRID(ST_MakePoint(longitude, latitude), {{ var('project_srid') }})::geometry AS geom
  FROM {{ source('raw', 'location_data') }}
)
SELECT id, geom, ST_AsText(geom) AS geom_text
FROM points

Verify the model materializes with a real geometry column and valid geometries:

bash
dbt run --select stg_spatial_test
dbt test --select stg_spatial_test
# Expect a materialized table whose geom column types as geometry(Point,4326), not text.

Configuration reference

Parameter Accepted values Default Spatial notes
type postgres — There is no postgis adapter type; spatial support rides on dbt-postgres
search_path comma-separated schemas unset Must include the schema that owns the PostGIS extension or geometry fails to resolve
threads integer 1 Higher values parallelize spatial joins; keep below the server’s max_connections
keepalives_idle integer (seconds) 0 0 defers to the OS default — recommended for managed PostgreSQL to avoid idle drops
var('project_srid') EPSG code 4326 Fed into the type macro and any ST_SetSRID / ST_Transform normalization
+post-hook (GiST) SQL string none Create the spatial index after materialization so the planner can use an index scan
A verification ladder that isolates where an installation broke Four checks in order, each narrowing the fault. A dbt version check proves the adapter is importable. A dbt debug proves the connection works. A PostGIS version query proves the extension is present in that database. A geometry round trip proves the type crosses the adapter intact. Failing at each rung points at exactly one layer, listed on the right. dbt --version fails → the Python environment dbt debug fails → credentials or network select postgis_version() fails → extension not created here select st_astext(st_makepoint(1,2)) fails → search_path or type handling

Gotchas & edge cases

  • type "geometry" does not exist at compile time. PostGIS is not on search_path, or the extension was never created. Run Step 2, then confirm Step 3’s resolved path with dbt debug.
  • Spatial columns come back as text. dbt coerced the type because no override was in place. Route declarations through the postgis_type macro from Step 4 instead of writing bare geometry.
  • ST_Transform returns NULL. The geometry is tagged SRID 0, so there is no source projection to reproject from. Call ST_SetSRID to label it with its true source SRID first — the governance side of this lives in spatial reference system management.
  • Spatial join shows a Seq Scan. The GiST index is missing or statistics are stale. Confirm the post-hook ran, then ANALYZE (or VACUUM ANALYZE) so the planner sees fresh statistics.
  • permission denied to create extension "postgis". Your role lacks CREATE EXTENSION. Have a superuser run Step 2 once during provisioning, or use an on-run-start hook executed by a privileged bootstrap role.

FAQ

Is there a dbt-postgis package I should pip install?

No. There is no dbt-postgis distribution on PyPI. You install dbt-postgres and enable the PostGIS extension inside the PostgreSQL database it connects to. dbt orchestrates the DAG; PostgreSQL executes the spatial computation natively.

Why does dbt compile a geometry column as text?

dbt’s default type resolution has no concept of geometry, so it falls back toward a string type and can truncate coordinate precision. Declare spatial columns through the postgis_type macro and pin project_srid so every column compiles to geometry(Geometry, 4326) with the correct SRID.

Why does dbt debug fail even though psql connects fine?

Most often the search_path in profiles.yml omits the schema that owns the PostGIS extension, so dbt cannot resolve the geometry type during its compile check. Add that schema to search_path, re-run dbt debug, and confirm the reported path includes it.

Do I need to create the GiST index myself?

Yes — PostGIS does not index geometry automatically. Add a +post-hook that runs CREATE INDEX ... USING GIST (geom) after the table materializes, then ANALYZE so the planner chooses an index scan over a sequential scan for ST_Intersects and ST_DWithin predicates.

Up: Part of Setting Up PostGIS with dbt.