SET UP POSTGRESQL

A COMPLETE WORKING PROFILE

Password auth is the common case. Drop this in your profiles.yml and you have a working output — usable as your project's default target, or named by any source's meta.connection:

my_project:
  target: pg_dev
  outputs:
    pg_dev:
      type: postgres
      host: localhost
      port: 5432
      user: analytics
      password: "{{ env_var('PG_PASSWORD') }}"
      dbname: analytics_db
      schema: public
      threads: 4

Then let DVT install what the profile needs, and prove the connection end to end:

dvt sync           # reads profiles.yml, installs the Postgres adapter + driver
dvt debug --all    # connects in both lanes and says exactly what failed if anything

dvt sync works per-profile: it looks at the type: of every output you actually declared and installs exactly those drivers — nothing speculative.

OTHER WAYS TO AUTHENTICATE

TLS client certificates. If your server authenticates clients with certificates, point the profile at the key material. The same three fields feed both lanes — the dbt lane hands them to libpq, and the data-movement lane passes the same query keys to its own driver. Paths may start with ~; DVT expands it.

    pg_secure:
      type: postgres
      host: db.internal.example.com
      port: 5432
      user: analytics
      password: "{{ env_var('PG_PASSWORD') }}"
      dbname: analytics_db
      schema: public
      sslmode: verify-full
      sslrootcert: ~/.postgresql/root.crt
      sslcert: ~/.postgresql/analytics.crt
      sslkey: ~/.postgresql/analytics.key

No password at all. A local dev server running with trust auth needs no credential — keep the key present and empty, and DVT simply connects without one:

    pg_local:
      type: postgres
      host: localhost
      port: 5432
      user: postgres
      password: ""
      dbname: postgres
      schema: public

SSLMODE — WHAT EACH LANE ACTUALLY DOES

The dbt lane speaks libpq, so every libpq mode works exactly as documented: disable, allow, prefer, require, verify-ca, verify-full. The data-movement lane's driver has no soft modes — it doesn't know prefer or allow — so DVT translates those (and an unset sslmode) to disable there, and honors the strict modes as written.

The practical rule: if your server must be reached over TLS, say so explicitly — write require, verify-ca, or verify-full. The soft defaults exist for local development, where most Postgres servers don't speak TLS at all.

ENGINE BEHAVIOR WORTH KNOWING

Naming. dbt-postgres's canonical key for the database is dbname; DVT also accepts database as an alias and treats them identically. The default schema is public, and unquoted identifiers fold to lowercase — Postgres's own rule, worth remembering when a source table was created with quoted mixed-case names.

Session knobs. role switches the session role after connecting (useful when you log in as a login role and do work as a group role), and connect_timeout caps connection attempts in seconds. Both ride into both lanes.

Local dev.One line gets you a disposable server that matches this page's first example:

docker run -d --name pg -p 5432:5432 \
  -e POSTGRES_USER=analytics -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_DB=analytics_db postgres:16

INCREMENTAL MODELS — FOUR STRATEGIES, AND A DEFAULT THAT READS YOUR KEY

On a Postgres default target, incremental_strategy accepts exactly four names, spelled as written here: append, delete+insert, merge, and microbatch. That list is declared by the adapter itself, so it holds regardless of which Postgres server you point at.

Leave incremental_strategy out and the answer depends on your key: with a unique_key set, Postgres runs delete+insert; without one, it runs a plain append. There is no fixed default name to memorise — the key decides, which is why an incremental model that quietly loses its unique_key also quietly stops de-duplicating.

{{ config(
    materialized='incremental',
    incremental_strategy='delete+insert',   -- append | delete+insert | merge | microbatch
    unique_key='order_id'                   -- omit the strategy and this alone picks
) }}                                        -- delete+insert; drop the key too and it appends

select order_id, status, updated_at
from {{ source('app_db', 'orders') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

mergeis where the conditions live. The adapter ships no Postgres-specific merge macro — it dispatches to dbt's generic one, which emits a real MERGE INTO statement. Nothing in the adapter checks that your server can execute that statement: there is no version gate in the code, so an older Postgres fails at the database with its own syntax error rather than being caught up front. And merge without a unique_key is not an error either — the join predicate becomes FALSE and the matched branch is skipped, leaving you with an insert-only run that looks like it merged.

microbatch has one hard requirement in the opposite direction: it needs a unique_key, and it is implemented as a merge rather than as delete+insert. Without the key the model refuses to compile: dbt-postgres 'microbatch' requires a `unique_key` config.

insert_overwrite is a dbt builtin but is absent from the Postgres list, so it is refused by name: The incremental strategy 'insert_overwrite' is not valid for this adapter. A name that isn't one of dbt's builtins at all is treated as your own custom strategy macro and fails differently, with dbt could not find an incremental strategy macro with the name "…" — worth knowing, because a typo can produce either message depending on what you typed.

When Postgres is the target of a federated f_incremental model, the vocabulary shrinks to three: append, merge, and delete+insert. insert_overwrite and microbatch are refused outright — has no Sling equivalent — because the federated lane computes the model in DuckDB and lands the rows through a bulk loader rather than compiling engine SQL. On that lane merge and delete+insert are the same thing: both become a merge on unique_key, and there is no delete-then-insert. Without a unique_key they fall back to a full refresh with a warning; append without a watermark_column does the same; and an unrecognised name is a warning plus a full refresh, not a failure.

Postgres is also on the short list of engines whose bulk-load connector has been verified to honour an update-key-only load, so a keyless f_incremental driven by a watermark_column runs here instead of being refused — DVT stops that shape with DVT026on every engine it has not measured (today StarRocks, Snowflake, Databricks, BigQuery, Fabric and Athena). DVT rewrites nothing on Postgres, either: the product makes exactly one strategy substitution anywhere, and it is ClickHouse's merge → delete+insert.

REFERENCE — EVERY PROFILE FIELD

Everything a type: postgresoutput reads. Fields not listed here are ignored by DVT's connection mapping.