dbt-postgres Adapter

Warnings

• breaking dbt-postgres 1.9.0 dropped support for Python 3.8. Users must upgrade their Python environment to 3.9 or higher. dbt 1.10 supports Python 3.13 for the Postgres adapter.
  Fix: Upgrade Python to version 3.9 or newer (e.g., Python 3.10, 3.11, 3.12, or 3.13).
• breaking Starting with dbt version 1.8, installing a dbt adapter (like `dbt-postgres`) no longer automatically installs `dbt-core`. This change decouples adapter and core versions. Users must now explicitly install both `dbt-core` and the desired adapter.
  Fix: When installing, use `pip install dbt-core dbt-postgres` (or your specific adapter) to ensure both are present.
• gotcha By default, `dbt-postgres` installs `psycopg2-binary`, which is a pre-built version. For optimized performance, especially in production environments, users may prefer to install `psycopg2` (which compiles against local PostgreSQL libraries). This can be done by setting the `DBT_PSYCOPG2_NAME` environment variable.
  Fix: To use `psycopg2` instead of `psycopg2-binary`, first uninstall `psycopg2-binary`, then `export DBT_PSYCOPG2_NAME=psycopg2` before installing `dbt-postgres`.
• gotcha `profiles.yml` is used for configuring connections when running `dbt Core` or `dbt Fusion` locally. If you are using `dbt Cloud`, you typically do not need to create or manage a `profiles.yml` file, as connection details are handled within the dbt Cloud platform.
  Fix: Ensure you understand whether your dbt setup requires local `profiles.yml` management or leverages dbt Cloud's integrated connection settings.
• gotcha In dbt 1.10, environment variables specifically for the dbt engine now use the `DBT_ENGINE_` prefix to avoid potential conflicts with user-defined `DBT_` prefixed variables. While older `DBT_` variables still function, new engine-specific variables will adhere to the `DBT_ENGINE_` prefix.
  Fix: When defining or referencing dbt engine-specific environment variables, prefer the `DBT_ENGINE_` prefix where applicable, and be aware of this distinction to avoid naming collisions.

Install

• pip install dbt-core dbt-postgres Install dbt-core and dbt-postgres

Quickstart

This quickstart guides you through setting up a new dbt project with the `dbt-postgres` adapter. It covers installation, project initialization, `profiles.yml` configuration to connect to a PostgreSQL database, debugging the connection, and running a simple dbt model. Ensure you have a running PostgreSQL instance and replace the placeholder credentials in `profiles.yml` with your actual database details. Environment variables like `DBT_PG_USER` and `DBT_PG_PASSWORD` can be used for sensitive information.

# 1. Install dbt-core and dbt-postgres (if not already installed)
pip install dbt-core dbt-postgres

# 2. Initialize a new dbt project
dbt init my_postgres_project

# 3. Navigate into your project directory
cd my_postgres_project

# 4. Configure your profiles.yml (usually located at ~/.dbt/profiles.yml)
#    Replace placeholders with your PostgreSQL credentials. Example below:
#
#    my_postgres_project:
#      target: dev
#      outputs:
#        dev:
#          type: postgres
#          host: localhost
#          user: {{ env_var('DBT_PG_USER', 'postgres') }}
#          password: {{ env_var('DBT_PG_PASSWORD', 'mysecretpassword') }}
#          port: 5432
#          dbname: my_database
#          schema: public
#          threads: 4
#          keepalives_idle: 0
#          connect_timeout: 10

# 5. Verify your dbt connection
dbt debug

# 6. Create a sample model (e.g., models/my_first_model.sql)
#    Content of models/my_first_model.sql:
#    -- models/my_first_model.sql
#    select
#        1 as id,
#        'hello dbt!' as message

# 7. Run your dbt models
dbt run

# 8. Test your dbt models (if tests are defined)
dbt test