Skip to main content

Postgres setup

profiles.yml file is for dbt Core users only

If you're using dbt Cloud, you don't need to create a profiles.yml file. This file is only for dbt Core users. To connect your data platform to dbt Cloud, refer to About data platforms.

  • 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

Use pip to install the adapter. Before 1.8, installing the adapter would automatically install dbt-core and any additional dependencies. Beginning in 1.8, installing an adapter does not automatically install dbt-core. This is because adapters and dbt Core versions have been decoupled from each other so we no longer want to overwrite existing dbt-core installations. Use the following command for installation:

Configuring dbt-postgres

For Postgres-specific configuration, please refer to Postgres configs.

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]
      sslcert: [optional, set the sslcert to control the certifcate file location]
      sslkey: [optional, set the sslkey to control the location of the private key]
      sslrootcert: [optional, set the sslrootcert config value to a new file path in order to customize the file location that contain root certificates]

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​

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

sslmode​

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.

sslcert​

The sslcert config controls the location of the certificate file used to connect to Postgres when using client SSL connections. To use a certificate file that is not in the default location, set that file path using this value. Without this config set, dbt uses the Postgres default locations. See Client Certificates in the Postgres SSL docs for the default paths.

sslkey​

The sslkey config controls the location of the private key for connecting to Postgres using client SSL connections. If this config is omitted, dbt uses the default key location for Postgres. See Client Certificates in the Postgres SSL docs for the default locations.

sslrootcert​

When connecting to a Postgres server using a client SSL connection, dbt verifies that the server provides an SSL certificate signed by a trusted root certificate. These root certificates are in the ~/.postgresql/root.crt file by default. To customize the location of this file, set the sslrootcert config value to a new file path.

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.

retries​

If dbt-postgres encounters an operational error or timeout when opening a new connection, it will retry up to the number of times configured by retries. The default value is 1 retry. If set to 2+ retries, dbt will wait 1 second before retrying. If set to 0, dbt will not retry at all.

psycopg2 vs psycopg2-binary​

psycopg2-binary is installed by default when installing dbt-postgres. Installing psycopg2-binary uses a pre-built version of psycopg2 which may not be optimized for your particular machine. This is ideal for development and testing workflows where performance is less of a concern and speed and ease of install is more important. However, production environments will benefit from a version of psycopg2 which is built from source for your particular operating system and archtecture. In this scenario, speed and ease of install is less important as the on-going usage is the focus.

Installing psycopg2 often requires OS level dependencies. These dependencies may vary across operating systems and architectures.

For example, on Ubuntu, you need to install libpq-dev and python-dev:

sudo apt-get update
sudo apt-get install libpq-dev python-dev

whereas on Mac, you need to install postgresql:

brew install postgresql
pip install psycopg2

Your OS may have its own dependencies based on your particular scenario.

0
Edit this page