Postgres setup
profiles.yml
file is for CLI users onlyIf you're using dbt Cloud, you don't need to create a profiles.yml
file. This file is only for CLI users. To connect your data platform to dbt Cloud, refer to About data platforms.
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.
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.