Backfill API Data to Postgres in Resumable Batches (n8n)

Backfill historical data from an HTTP API into Postgres—without losing progress

This n8n workflow backfills historical records from a source HTTP API into your Postgres table in resumable batches. It persistently saves a checkpoint and progress stats in a backfill_progress table, so if you stop or rerun, the workflow continues exactly where it left off.

What this workflow does

• Start or resume: Run on demand via Manual Trigger or Webhook Trigger.
• Prepare progress tracking: Creates a backfill_progress table (if needed) and loads your backfill configuration and the current checkpoint used for paging through history.
• Completion check: If the job is already marked completed, it skips processing and returns a completion summary.
• Fetch next batch: Requests the next batch of records from the source HTTP API using the current checkpoint, end date, and configured batch limit.
• Normalize & page forward: Maps the API response into the target fields, determines the next checkpoint, and continues only when records exist.
• Upsert into Postgres: Upserts the batch into your destination Postgres table using the unique key required for upserts.
• Persist checkpoint & stats: Updates the checkpoint and cumulative progress counters in backfill_progress, then loops until the per-run limit is reached or no more data is available.
• Finalize: Produces a summary and can optionally respond via webhook.

Use cases

• Backfilling orders, events, or other historical datasets from an HTTP API into Postgres.
• Recovering from timeouts or deployment restarts while keeping an accurate “resume from here” checkpoint.
• Incrementally importing date-ranged history for SaaS reporting and analytics.

Technical details

• Nodes / logic: if, code, no op, wait, webhook, postgres
• Integrations: HTTP API (via configurable base URL + authorization header/token) and Postgres
• Core feature: resumable batching using a persistent backfill_progress checkpoint

Setup: Create a Postgres credential in n8n, ensure the target table exists (e.g., orders_history) with the upsert unique key, and update the configuration in the “Code - Load Config & Checkpoint” step with your API base URL, authorization token/header, and date range.