Skip to main content

Postgres setup

Overview of dbt-postgres

  • Maintained by: dbt Labs
  • Authors: core dbt maintainers
  • GitHub repo: dbt-labs/dbt-core
  • PyPI package: dbt-postgres
  • Slack channel: #db-postgres
  • Supported dbt Core version: v0.4.0 and newer
  • dbt Cloud support: Supported
  • Minimum data platform version: n/a

Installing dbt-postgres

pip is the easiest way to install the adapter:

pip install dbt-postgres

Installing dbt-postgres will also install dbt-core and any other dependencies.

Configuring dbt-postgres

For Postgres-specifc configuration please refer to Postgres Configuration

For further info, refer to the GitHub repository: dbt-labs/dbt-core

Profile Configuration​

Postgres targets should be set up using the following configuration in your profiles.yml file.

~/.dbt/profiles.yml
company-name:
  target: dev
  outputs:
    dev:
      type: postgres
      host: [hostname]
      user: [username]
      password: [password]
      port: [port]
      dbname: [database name] # or database instead of dbname
      schema: [dbt schema]
      threads: [optional, 1 or more]
      keepalives_idle: 0 # default 0, indicating the system default. See below
      connect_timeout: 10 # default 10 seconds
      retries: 1  # default 1 retry on error/timeout when opening connections
      search_path: [optional, override the default postgres search_path]
      role: [optional, set the role dbt assumes when executing queries]
      sslmode: [optional, set the sslmode used to connect to the database]

Configurations​

search_path​

The search_path config controls the Postgres "search path" that dbt configures when opening new connections to the database. By default, the Postgres search path is "$user, public", meaning that unqualified table names will be searched for in the public schema, or a schema with the same name as the logged-in user. Note: Setting the search_path to a custom value is not necessary or recommended for typical usage of dbt.

role​

▶Changelog

The role config controls the Postgres role that dbt assumes when opening new connections to the database.

sslmode​

▶Changelog

The sslmode config controls how dbt connectes to Postgres databases using SSL. See the Postgres docs on sslmode for usage information. When unset, dbt will connect to databases using the Postgres default, prefer, as the sslmode.

keepalives_idle​

If the database closes its connection while dbt is waiting for data, you may see the error SSL SYSCALL error: EOF detected. Lowering the keepalives_idle value may prevent this, because the server will send a ping to keep the connection active more frequently.

dbt's default setting is 0 (the server's default value), but can be configured lower (perhaps 120 or 60 seconds), at the cost of a chattier network connection.

0
Edit this page