Posted inDatabase

Deploy Airbyte on Docker: Automate ELT Data Sync Across Databases and Warehouses

Database tutorial - IT technology blog
Database tutorial - IT technology blog

The Data Sync Problem You’ve Probably Hit

Running data across multiple systems — a PostgreSQL production database, a MongoDB analytics store, a Redshift warehouse, and maybe a third-party SaaS — turns into a maintenance nightmare fast. You start with a few cron jobs and Python scripts. Six months later, you have 14 scripts nobody fully understands, half of them silently failing, and your data team spends more time firefighting pipelines than actually analyzing data.

The root cause isn’t laziness. Hand-rolled ETL simply doesn’t scale. Each new data source means a new integration, new auth handling, new error recovery logic, and new monitoring. The combinatorial explosion of source × destination pairs is where most teams eventually get stuck.

That’s where Airbyte comes in. It ships with 300+ pre-built connectors and a standardized sync protocol — configure a connection once and the platform handles auth, retries, and schema evolution. The ELT approach loads raw data into the destination first, then transforms happen inside the warehouse using dbt or SQL. Your source database never sees the transform workload.

Why Run Airbyte on Docker Instead of the Cloud?

The platform ships as a set of Docker containers. Spin it up with Docker Compose on a VPS or your local machine and you get:

  • Full control — nothing leaves your infrastructure
  • No SaaS subscription cost for self-hosted deployments
  • Easy upgrades via docker compose pull
  • Identical setup locally and in production

Airbyte Cloud works fine if you want a fully managed setup. But when your data can’t leave your infrastructure — HIPAA requirements, internal security policy, or just strong preference — self-hosting is the only real option. Your data stays on your servers, full stop.

What makes ELT different from traditional ETL is worth a moment. Classic ETL transforms data before loading it — every business rule change means touching the pipeline. ELT flips that: raw data lands in the warehouse first, and transforms run inside it via dbt models or SQL views. Change a business rule? Update the model, not the sync. Your ingestion layer stays stable.

Installation: Deploy Airbyte with Docker Compose

Prerequisites

Before starting, make sure you have:

  • Docker Engine 20.10+ and Docker Compose v2
  • At least 4 GB RAM (8 GB recommended for multiple concurrent syncs)
  • Ports 8000 (UI) and 8001 (API) available

Check your versions first:

docker --version
docker compose version

Pull and Start Airbyte

Skip writing your own Compose file — Airbyte ships an official startup script that handles the full setup. Three commands to get it running:

# Create a working directory
mkdir airbyte && cd airbyte

# Download the official startup script
curl -LsfS https://raw.githubusercontent.com/airbytehq/airbyte/refs/heads/master/run-ab-platform.sh \
  -o run-ab-platform.sh

# Make it executable and run in background
chmod +x run-ab-platform.sh
./run-ab-platform.sh -b

The -b flag runs everything in the background. The first run pulls all images — expect 5–10 minutes depending on your connection speed.

Once up, open http://localhost:8000. Default credentials are airbyte / password.

If this is on a remote server, change the credentials before starting. Set them in the .env file Airbyte generates:

# Edit airbyte/.env before the first run
BASIC_AUTH_USERNAME=yourname
BASIC_AUTH_PASSWORD=a-strong-random-password

Verify all containers came up cleanly:

docker compose ps

You should see airbyte-server, airbyte-webapp, airbyte-worker, airbyte-db, and airbyte-temporal all in running state. Any container stuck in a restart loop? Grab its logs:

docker compose logs <container-name>

Configuration: Setting Up Your First ELT Pipeline

Adding a Source (PostgreSQL Example)

In the Airbyte UI, go to Sources → New Source. Search for “Postgres” and fill in the connection details:

  • Host: your database hostname or IP
  • Port: 5432
  • Database: myapp_production
  • Username / Password: your DB credentials
  • Replication Method: Standard (or CDC for real-time change capture)

One catch: if your Postgres runs in Docker on the same machine, don’t use localhost as the host. That address resolves inside the Airbyte container, not the host machine. Fix it with a shared Docker network:

docker network create airbyte_network

Add it to both your Airbyte compose and your database compose files under networks: with external: true. Then use the Postgres container name as the host in Airbyte.

Adding a Destination

Go to Destinations → New Destination. The list covers BigQuery, Snowflake, Redshift, another Postgres instance, S3, and much more. For local testing without a cloud account, use the Local JSON destination — it writes synced data as JSON files inside the Airbyte container at /tmp/airbyte_local/.

If you’re testing with sample data, you’ll often need to convert CSV exports to JSON first. For that, toolcraft.app/en/tools/data/csv-to-json converts entirely in the browser — no upload, no server-side processing. Useful when the sample data is sensitive and you don’t want it leaving your machine.

Creating a Connection and Choosing Sync Mode

With source and destination ready, go to Connections → New Connection, select your source and destination, then choose sync settings per stream (table):

  • Full Refresh | Overwrite — replace the destination table on every sync
  • Full Refresh | Append — keep all historical records
  • Incremental | Append — only sync new/changed rows (requires a cursor field like updated_at)
  • Incremental | Append + Deduped — smart upsert; best for production OLTP-to-warehouse pipelines

For most production setups, Incremental | Append + Deduped is the right default. It only transfers rows that changed since the last sync, skips full table scans on the source, and keeps the destination correctly up to date without accumulating duplicates.

Set a sync frequency (manual, hourly, every 6 hours, daily), select which streams to include, and click Set up connection.

Verification & Monitoring

Triggering and Watching a Sync

After creating a connection, trigger a manual sync from the UI with the Sync now button, or via the Airbyte API on port 8001:

curl -X POST http://localhost:8001/api/v1/connections/sync \
  -H "Content-Type: application/json" \
  -d '{"connectionId": "your-connection-id"}'

Find the connection ID in the URL when viewing a connection in the UI (/connections/<uuid>).

Open Job History and click any job to see per-stream stats: rows synced, bytes transferred, and full error traces if anything failed. One key behavior: Airbyte isolates failures at the stream level. If one table errors out, the remaining streams in that sync still complete.

Container-Level Log Inspection

# Worker handles the actual sync jobs
docker compose logs -f airbyte-worker

# Server handles API calls and scheduling
docker compose logs -f airbyte-server

Polling Sync Status via Python

If you want sync status feeding into your own monitoring stack, the Airbyte API makes it straightforward:

import requests

BASE = "http://localhost:8001/api/v1"
headers = {"Content-Type": "application/json"}

# List recent sync jobs for a connection
response = requests.post(
    f"{BASE}/jobs/list",
    json={"configId": "your-connection-id", "configType": "sync"},
    headers=headers
)

jobs = response.json().get("jobs", [])
for job in jobs[:5]:
    info = job["job"]
    print(f"Job {info['id']}: {info['status']} | created: {info['createdAt']}")

Webhook Notifications for Failed Syncs

For production, set up failure alerts under Settings → Notifications. Airbyte supports Slack webhooks natively. Point it at your Slack channel or a webhook-to-PagerDuty bridge to get alerted on sync failures without polling the UI manually.

Upgrading Airbyte

Connection settings and sync history persist in the airbyte_db Docker volume, so upgrades don’t wipe your configuration:

# Pull latest images and restart
./run-ab-platform.sh -b

# Or manually with compose
docker compose pull
docker compose up -d

Check the Airbyte releases page before upgrading to catch any breaking changes in connector versions or API behavior. Running the upgrade on a staging box first is worth the extra 10 minutes.

Once rows start landing in the destination, you’re past the initial setup hurdle. Adding a second or third source follows the exact same steps: pick a connector, configure credentials, choose sync mode. The connectors absorb the integration complexity — you focus on the actual data work.

Share:
Tags: