Bulk Load CSV Data into PostgreSQL Using COPY for Fast ETL Ingestion

Loading CSV files into PostgreSQL row by row with INSERT statements is slow and brittle at scale. This blueprint shows the production way to do it: download a CSV dataset, ensure the target table exists, and bulk load the file into Postgres using the native COPY protocol, which is the fastest and most reliable path for ingesting structured data. It is a reusable foundation for PostgreSQL-centric ETL, database seeding, and reference-table refreshes orchestrated end to end with Kestra.

How it works

The flow runs four sequential tasks:

1. download (io.kestra.plugin.core.http.Download) fetches a public CSV dataset (ISO 3166 country codes) over HTTP and stores it in Kestra's internal storage.
2. create_table (io.kestra.plugin.jdbc.postgresql.Query) runs a CREATE TABLE IF NOT EXISTS statement so the country_referential table and its columns are in place before any load.
3. copyin (io.kestra.plugin.jdbc.postgresql.CopyIn) streams the downloaded file straight into the table using format: CSV, header: true, and from: "{{ outputs.download.uri }}", mapping the prior task's output directly into the bulk load.
4. read (io.kestra.plugin.jdbc.postgresql.Query) issues a SELECT ... LIMIT 10 with fetchType: FETCH to confirm the rows landed correctly.

What you get

• A working, end-to-end CSV-to-PostgreSQL ingestion pipeline.
• Bulk loading through the native COPY protocol instead of slow per-row inserts.
• Idempotent table creation that is safe to re-run.
• A built-in validation read that proves the load succeeded.
• A pattern that wires one task's output URI into the next with no glue code.

Who it's for

• Data engineers building batch ETL and ingestion pipelines.
• Analytics engineers seeding reference and lookup tables.
• Backend and platform teams automating database bootstrapping.
• Anyone migrating from ad hoc psql \copy scripts to orchestrated runs.

Why orchestrate this with Kestra

Postgres COPY is fast, but on its own it is just a statement: it has no scheduler, no retry logic, no dependency handling, and no record of what ran. Kestra wraps the load in a declarative YAML flow where the download, table creation, load, and validation run as ordered, observable tasks. You get event and schedule triggers, automatic retries, execution history, and full data lineage across the download and load steps, the orchestration layer that a database engine alone cannot provide.

Prerequisites

• A running PostgreSQL instance reachable from Kestra (the blueprint targets jdbc:postgresql://sample_postgres:5433/world).
• Network access from Kestra to the source CSV URL.

Secrets

This blueprint reads the Postgres password from a Kestra secret ({{ secret('POSTGRES_PASSWORD') }}) and uses a demo username (postgres). For production, also move the username and connection URL into secrets and store them in your secret backend.

Quick start

1. Add the blueprint to your Kestra instance.
2. Point the url, username, and password properties at your own Postgres database.
3. Adjust the source URL and the create_table schema to match your dataset.
4. Execute the flow and inspect the read task output to verify ingestion.

How to extend

• Swap the HTTP download for an upload input, an S3/GCS fetch, or a database extract.
• Add a Schedule or event trigger to refresh the table on a cadence.
• Truncate or stage into a temp table before copyin for full-refresh loads.
• Chain downstream transformation tasks (dbt, SQL, Python) after the validation read.

Links

• Plugin docs: https://kestra.io/plugins/plugin-jdbc-postgres
• PostgreSQL orchestration: https://kestra.io/orchestration/postgres