Registry / data / dbt-postgres

dbt-postgres

library1.10.0pypypiunverified

dbt-postgres is an adapter that enables dbt-core to connect and transform data within PostgreSQL databases. It provides the necessary protocols and base functionality for dbt to interact with Postgres, allowing data analysts and engineers to build data transformation pipelines using SQL. The library is currently at version 1.10.0 and follows the release cadence of dbt-core, with minor versions typically introducing new features and patches addressing bugs, aiming for backward compatibility for end-users.

data database

pip install dbt-core dbt-postgres

github ↗package ↗homepage ↗docs ↗changelog ↗

Install & Compatibility

Where this runs

No compatibility data collected yet for this library.

Code

Verified usage

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

dbt --version

Debug

Known issues

breakingdbt-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).

affects: >=1.9.0

breakingStarting 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.

affects: >=1.8.0

gotchaBy 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`.

affects: All versions

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.

affects: All versions

gotchaIn 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.

affects: >=1.10.0

Errors

Common errors & fixes

ModuleNotFoundError: No module named 'dbt-postgres'

The 'dbt-postgres' adapter is either not installed in your Python environment or is not accessible within the environment where dbt is being executed.

fix

Ensure dbt-core and the dbt-postgres adapter are correctly installed using pip: `python -m pip install dbt-core dbt-postgres`

OperationalError: (psycopg2.OperationalError) connection to server at "host" (IP), port XXXX failed: FATAL: password authentication failed for user "user"

dbt-postgres cannot establish a connection to your PostgreSQL database, most commonly due to incorrect credentials (username, password), an inaccessible host or port, or the database server not running or being blocked by a firewall.

fix

Verify the `host`, `port`, `user`, `password`, and `dbname` in your `profiles.yml` file match your PostgreSQL server configuration exactly. Ensure the PostgreSQL server is running and network access is permitted from where dbt is being run.

Database Error in model ..., relation "schema.table" does not exist

The dbt model or SQL query is referencing a table or view that does not exist in the database, which can happen if dependent models haven't been run, the schema configuration is incorrect, or there's a typo in the `ref()` or `source()` function.

fix

Run `dbt run` to materialize all necessary models. Double-check your `schema.yml` and `dbt_project.yml` for correct schema configurations and verify that `ref()` and `source()` calls accurately point to existing models or sources. Also, confirm the dbt user has `SELECT` permissions on the relevant schemas.

psycopg2.errors.InsufficientPrivilege: permission denied for schema X

The PostgreSQL user configured in your `profiles.yml` does not have the necessary database privileges (e.g., USAGE, CREATE, SELECT) on the specified schema or database objects to perform the requested dbt operation.

fix

Grant the required permissions to your dbt user on the PostgreSQL database. For example, to grant usage and all table permissions on a schema: `GRANT USAGE ON SCHEMA <schema_name> TO <dbt_user>;` and `GRANT ALL ON ALL TABLES IN SCHEMA <schema_name> TO <dbt_user>;`

Credentials in profile "PROFILE_NAME", target: 'dev', invalid: '5432' is not of type 'integer'

The 'port' value in your `profiles.yml` is being parsed as a string instead of an integer, often when it's supplied via an environment variable which typically holds string values.

fix

Explicitly cast the port environment variable to an integer in your `profiles.yml` using Jinja templating, like: `port: "{{ env_var('DB_PORT') | int }}"`.

Upgrade

Version history

1.10.0latest on PyPI

Audit

Dependencies

dbt-corerequireddbt-postgres is an adapter for dbt-core and requires it to function. Since dbt 1.8, dbt-core is no longer automatically installed with adapters.

psycopg2-binaryoptionalDefault Python driver for PostgreSQL. Can be overridden to 'psycopg2' for potentially better performance if compiled locally.

Agent activity

14 hits · last 30 days

node

ahrefsbot

seranking-bot

sogoubot

amazonbot

Resources

githubhttps://github.com/dbt-labs/dbt-adapters ↗

packagehttps://pypi.org/project/dbt-postgres/ ↗

homepagehttps://docs.getdbt.com/docs/core/connect-data-platform/postgres-setup ↗

docshttps://docs.getdbt.com ↗

changeloghttps://github.com/dbt-labs/dbt-adapters/blob/main/dbt-postgres/CHANGELOG.md ↗