Extract data from an API and load it to Postgres using Python, Git and Docker

Build a version-controlled API-to-Postgres pipeline that keeps your orchestration in YAML and your business logic in Python. This blueprint clones a Git repository of ETL scripts, runs them in isolated Docker containers to fetch data from a REST API, and loads the results into a Postgres database. It solves the common problem of tangled glue code by separating what runs (the workflow) from how it runs (the Python), so data engineers can ship API ingestion and database loads without rebuilding the scheduler each time requirements change.

How it works

1. A io.kestra.plugin.core.flow.WorkingDirectory task named wdir wraps the steps so they share one filesystem, letting cloned scripts and generated files pass between tasks.
2. io.kestra.plugin.git.Clone (clone_repository) pulls the main branch of https://github.com/kestra-io/scripts into the working directory.
3. io.kestra.plugin.scripts.python.Commands (get_users) runs python etl/get_users_from_api.py on the io.kestra.plugin.scripts.runner.docker.Docker runner with the requests dependency, extracting API data into a users.json file.
4. A second io.kestra.plugin.scripts.python.Commands task (save_users_pg) runs python etl/save_users_pg.py with pandas, psycopg2, and sqlalchemy, reading the raw file and loading it into Postgres. Connection settings are injected as env vars (DB_USERNAME, DB_PASSWORD, DB_HOST, DB_PORT).

What you get

• A reproducible ETL run where Python scripts live in Git, not pasted inline.
• Per-task Docker isolation so each step installs only the libraries it needs.
• A clean handoff of users.json between extract and load via the shared working directory.
• Database credentials supplied securely through a Kestra secret instead of hardcoded values.

Who it's for

• Data engineers building API ingestion into Postgres.
• Teams that want orchestration in YAML and transformation logic in Python.
• Platform teams standardizing Git-backed, containerized pipeline patterns.

Why orchestrate this with Kestra

Cron or a bare Python runner cannot give you the full picture: declarative YAML keeps the pipeline reviewable in pull requests, event and schedule triggers can launch it on demand or on a cadence, automatic retries recover from flaky API calls or transient database errors, and execution history records lineage across the clone, extract, and load steps. The Git clone and per-task Docker runners deliver dependency isolation and reproducibility that a single Postgres job scheduler simply was not built to coordinate.

Prerequisites

• A running Postgres instance reachable from the task runner (defaults target host.docker.internal:5432).
• Docker available so the Python tasks can run in containers.
• Network access to the source API and to the kestra-io/scripts GitHub repository.

Secrets

• DB_PASSWORD: the Postgres password injected into the load task as the DB_PASSWORD environment variable.

Quick start

1. Add the DB_PASSWORD secret in your Kestra instance.
2. Confirm Postgres is reachable at the configured host and port, or adjust DB_HOST and DB_PORT.
3. Add the flow to the company.team namespace.
4. Execute the flow and inspect the logs for each task.
5. Verify the rows landed in your Postgres table.

How to extend

• Point clone_repository at your own repository and branch to run your team's ETL scripts.
• Swap the API extraction script to pull from a different source or paginated endpoint.
• Add a schedule or event trigger to run the pipeline automatically.
• Add downstream tasks to validate or transform the loaded data, or notify on completion.

Links

• Git plugin docs
• Python scripts plugin docs
• Git orchestration
• Postgres orchestration