1. Initialize a project with a pipeline that loads to Postgres by running
dlt init chess postgres
2. Install the necessary dependencies for Postgres by running
pip install -r requirements.txt
This will install dlt with postgres extra which contains
3. Create a new database after setting up a Postgres instance and
psql / query editor by running
CREATE DATABASE dlt_data;
dlt_data database to
4. Create a new user by running
CREATE USER loader WITH PASSWORD '<password>';
loader user and
<password> password to
5. Give the
loader user owner permissions by running
ALTER DATABASE dlt_data OWNER TO loader;
It is possible to set more restrictive permissions (e.g. give user access to a specific schema).
6. Enter your credentials into
It should now look like
database = "dlt_data"
username = "loader"
password = "<password>" # replace with your password
host = "localhost" # or the IP address location of your database
port = 5432
connect_timeout = 15
You can also pass a database connection string similar to the one used by
psycopg2 library or SQLAlchemy. Credentials above will look like this:
# keep it at the top of your toml file! before any section starts
To pass credentials directly you can use
credentials argument passed to
pipeline = dlt.pipeline(pipeline_name='chess', destination='postgres', dataset_name='chess_data', credentials="postgresql://loader:<password>@localhost/dlt_data")
All write dispositions are supported
If you set the
replace strategy to
staging-optimized the destination tables will be dropped and replaced by the staging tables.
dlt will load data using large INSERT VALUES statements by default. Loading is multithreaded (20 threads by default).
Supported file formats
- insert-values is used by default
Supported column hints
postgres will create unique indexes for all columns with
unique hints. This behavior may be disabled
Additional destination options
Postgres destination creates UNIQUE indexes by default on columns with
unique hint (ie.
_dlt_id). To disable this behavior
This destination integrates with dbt via dbt-postgres.
This destination fully supports dlt state sync