Free tools Windows power users keep installed
One-click scans. No signup required.
A small ETL job needs four things to work reliably: an extractor that handles pagination, a transform step that returns a predictable shape, a loader that can be rerun without duplicating rows, and a Docker Compose setup where the database is actually ready before Python connects. This guide builds that pipeline with Python, Psycopg 3 and PostgreSQL 16, using GitHub issues as the data source. It then sorts the errors you are most likely to hit into separate failure classes: hostname resolution, database readiness, authentication, and adapter installation.
The stack follows a recently published example of this exact pipeline, which uses Python 3.14, Psycopg 3, python-dotenv and PostgreSQL in Compose. The code below is my own illustrative sketch of that workflow, not a copy of the author’s files, and it has not been benchmarked. Pin versions you have verified yourself.
The pipeline at a glance
The flow is: GitHub REST API → extract paginated issues → normalize fields and compute hours to close → create the table if needed and upsert into PostgreSQL by issue ID. Because the load is keyed on the issue ID, rerunning the job updates existing rows instead of inserting duplicates.
Splitting the code by stage makes failures easier to locate:
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Fix the driver behind crashes, sound loss and screen glitches3Clear out junk files and repair common Windows errors#1 Best Overall
extract.pyowns HTTP calls and pagination.transform.pymaps source fields to the target shape.load.pyowns schema creation and database writes.main.pywires the stages together.docker-compose.yml,requirements.txt,Dockerfile,.dockerignoreand an.env.examplefile complete the project.
Step 1: Define the database service
The key points are a health check, a named volume and credentials read from an environment file.
services:
db:
image: postgres:16-alpine
environment:
POSTGRES_USER: ${POSTGRES_USER}
POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
POSTGRES_DB: ${POSTGRES_DB}
ports:
- "5432:5432" # only needed for host-side tools
volumes:
- pgdata:/var/lib/postgresql/data
healthcheck:
test: ["CMD-SHELL", "pg_isready -U ${POSTGRES_USER} -d ${POSTGRES_DB}"]
interval: 5s
timeout: 5s
retries: 10
etl:
build: .
depends_on:
db:
condition: service_healthy
environment:
DB_HOST: db
DB_PORT: "5432"
DB_NAME: ${POSTGRES_DB}
DB_USER: ${POSTGRES_USER}
DB_PASSWORD: ${POSTGRES_PASSWORD}
GITHUB_REPO: ${GITHUB_REPO}
GITHUB_TOKEN: ${GITHUB_TOKEN:-}
volumes:
pgdata:Compose reads a .env file next to the Compose file for the ${...} substitutions. Commit an .env.example with placeholder values and keep the real .env out of version control.
Step 2: Build the Python image
FROM python:3.12-slim
WORKDIR /app
COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt
COPY . .
CMD ["python", "main.py"]psycopg[binary]
python-dotenv
requestsChoose a Python tag that your Psycopg release supports; the example article used 3.14, but any version works if the wheel or build prerequisites exist for it. Add a .dockerignore containing at least .env, .git and __pycache__. Docker’s Compose quickstart explains that without one, files such as .env can be sent to the build daemon and end up in image layers.
Step 3: Extract with pagination
import os
import requests
def extract_issues(repo: str):
url = f"https://api.github.com/repos/{repo}/issues"
headers = {"Accept": "application/vnd.github+json"}
if token := os.getenv("GITHUB_TOKEN"):
headers["Authorization"] = f"Bearer {token}"
params = {"state": "all", "per_page": 100}
while url:
resp = requests.get(url, headers=headers, params=params, timeout=30)
resp.raise_for_status()
yield from resp.json()
url = resp.links.get("next", {}).get("url")
params = None # the next URL already carries its query stringFollowing the Link header’s next URL is more robust than counting pages yourself. Note that GitHub’s issues endpoint also returns pull requests; those items carry a pull_request key, so decide deliberately whether to keep them.
Step 4: Transform into a stable shape
from datetime import datetime
def _ts(value):
return datetime.fromisoformat(value.replace("Z", "+00:00")) if value else None
def transform(raw: dict) -> dict | None:
if "pull_request" in raw:
return None
created, closed = _ts(raw["created_at"]), _ts(raw.get("closed_at"))
hours = round((closed - created).total_seconds() / 3600, 2) if closed else None
return {
"id": raw["id"],
"number": raw["number"],
"title": raw.get("title") or "",
"state": raw["state"],
"author": (raw.get("user") or {}).get("login"),
"created_at": created,
"closed_at": closed,
"hours_to_close": hours,
}Using .get() for optional fields and indexing directly for fields that must exist is deliberate. The example article describes a pipeline that turned into a festival of KeyErrors, outdated schemas and payload typos; a hard KeyError on a required field is useful, but one on an optional field is a bug.
Step 5: Load with an idempotent upsert
import os
import psycopg
DDL = """
CREATE TABLE IF NOT EXISTS issues (
id BIGINT PRIMARY KEY,
number INTEGER NOT NULL,
title TEXT NOT NULL,
state TEXT NOT NULL,
author TEXT,
created_at TIMESTAMPTZ NOT NULL,
closed_at TIMESTAMPTZ,
hours_to_close NUMERIC(12,2)
);
"""
UPSERT = """
INSERT INTO issues (id, number, title, state, author, created_at, closed_at, hours_to_close)
VALUES (%(id)s, %(number)s, %(title)s, %(state)s, %(author)s,
%(created_at)s, %(closed_at)s, %(hours_to_close)s)
ON CONFLICT (id) DO UPDATE SET
title = EXCLUDED.title,
state = EXCLUDED.state,
closed_at = EXCLUDED.closed_at,
hours_to_close = EXCLUDED.hours_to_close;
"""
def connect():
return psycopg.connect(
host=os.environ["DB_HOST"], port=os.environ.get("DB_PORT", "5432"),
dbname=os.environ["DB_NAME"], user=os.environ["DB_USER"],
password=os.environ["DB_PASSWORD"],
)
def load(rows: list[dict]) -> int:
with connect() as conn: # commits on success, rolls back on exception
with conn.cursor() as cur:
cur.execute(DDL)
cur.executemany(UPSERT, rows)
return len(rows)The primary key on id is what gives ON CONFLICT (id) meaning. Decide which columns should change on a rerun: here, the mutable ones (title, state, close time, hours). Immutable ones such as created_at are left alone. Because the whole load runs in one transaction, a failure halfway leaves the table as it was.
This sketch uses parameterized inserts, not PostgreSQL’s COPY. COPY FROM is a bulk-loading tool that appends rows, normally aborts on an error, and fires destination triggers and check constraints (per the PostgreSQL 17 documentation). It is not a drop-in replacement for an upsert; a common pattern is to COPY into a staging table and then merge, but that is a different design.
Step 6: Wire it together and run
import os
from dotenv import load_dotenv
from extract import extract_issues
from transform import transform
from load import load
def main():
load_dotenv() # harmless in Docker; useful for local runs
rows = [r for raw in extract_issues(os.environ["GITHUB_REPO"])
if (r := transform(raw))]
print(f"Loaded {load(rows)} issues")
if __name__ == "__main__":
main()docker compose up --build --abort-on-container-exit
docker compose exec db psql -U "$POSTGRES_USER" -d "$POSTGRES_DB" -c "SELECT count(*) FROM issues;"Run it twice. The count should not grow on the second run unless new issues appeared upstream. That is your idempotency test.
Debugging: identify the failing layer first
Read the complete traceback and keep the original exception; wrapping it in a vague message hides the real cause. Then place the failure in one of these classes.
| Symptom | Failure class | First check |
|---|---|---|
| “could not translate host name” | Service discovery | Service name and shared network |
| “Connection refused” | Readiness or port | DB logs, health check, port number |
| “password authentication failed” | Credentials | Whether a volume already existed |
| Build error or import error for Psycopg | Adapter installation | Install extra, compiler, headers, pg_config |
| Type, constraint or SQL errors | Transform or load | Row values vs. schema |
Hostname cannot be translated
Docker’s PostgreSQL guide notes that Compose creates a project network and lets services reach each other by service name. Inside the etl container, the host must be the database service name (db above), not localhost, which refers to the etl container itself. Typos in the service name, or a service attached to a different network, produce this error. Changing port mappings will not fix it, because published ports concern host-to-container access only.
docker compose config # shows the resolved service names and networks
docker compose psConnection refused
This usually means nothing was listening at that address and port when Python tried. Possible causes: the database was still initializing, the wrong port was used, or a host-side client is using a port that was never published. Separate them:
- Run
docker compose logs dband look for the “ready to accept connections” message. Docker notes startup can take several seconds. - Run
docker compose exec db psql -U <user> -d <db>. If that works, the server is healthy and the problem is the path to it. - From inside Compose, use port 5432 (the container port). From the host, use the published host port, which may differ if you mapped something like
5433:5432.
The durable fix for the startup race is the health check plus condition: service_healthy shown in Step 1. A plain depends_on only orders container start; it does not wait for PostgreSQL to accept connections. Docker’s Compose quickstart and its Python guide both use this health-check pattern.
Password authentication fails after you changed it
The POSTGRES_PASSWORD variable only applies when the data directory is first initialized. If the named volume already exists, PostgreSQL keeps the credentials it was created with, and editing .env does nothing. Your options:
- Use the original password.
- Connect with a working role and change it:
ALTER ROLE myuser PASSWORD 'new-password'; - If the data is disposable, remove the volume with
docker compose down -vand let it reinitialize. This permanently deletes all database contents, so do not do it casually.
Psycopg will not install or import
Psycopg 3 can be installed in different modes, with different prerequisites, per the Psycopg documentation:
| Install mode | What it needs | Trade-off |
|---|---|---|
psycopg[binary] |
A prebuilt wheel for your Python and platform | No compiler needed; bundles its own libpq |
psycopg[c] |
C compiler, Python development headers, PostgreSQL client dev headers (such as libpq-dev), and pg_config |
Local C build; faster than pure Python |
psycopg (pure Python) |
The system libpq at runtime |
Easiest to build; documented as slower than the other two |
If a source build fails in a slim Docker image, either switch to psycopg[binary] or install the build prerequisites (for a Debian-based image, typically gcc and libpq-dev). If the pure-Python package imports but errors about a missing library, install libpq in the image. If no binary wheel exists for a very new Python release, pip falls back to a source build or fails, so check Psycopg’s current supported Python versions before pinning.
The example article recommends psycopg[binary] in its Windows plus Python 3.14 setup and warns against substituting psycopg2-binary. Treat that as that author’s environment-specific advice. What is general is that Psycopg 2 and 3 are separate major versions with different package names, and their APIs differ. Psycopg 2 documents its own parameter passing, transaction control and error classes, so do not mix its instructions with Psycopg 3 code. This article’s code imports psycopg, which is version 3.
Recommended Free Tools
Load-stage errors
KeyErrorin transform: a field you assumed was always present is missing or renamed. Log the offending raw record before failing.- Type errors: a value does not fit the column, such as a null in a
NOT NULLcolumn or text where a timestamp is expected. Compare the transformed dict to the DDL. - “ON CONFLICT” errors: the conflict target must match a unique index or primary key. If you changed the schema on an existing volume,
CREATE TABLE IF NOT EXISTSwill not alter the old table. - Nothing persisted: check that the connection block exited cleanly. In Psycopg 3, leaving the
with connect()block normally commits, and an exception rolls back. - COPY failures (only if you adopt COPY): inconsistent line endings in the input can trigger errors, and you can watch progress in
pg_stat_progress_copy.
The example article’s closing advice is to read tracebacks calmly because that is how pipelines get fixed when they fail. It holds up: the first and last lines of the traceback usually name the layer and the cause.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
