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:

  • 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

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.