How to Build a Real-Time Postgres CDC Sync Pipeline in 15 Minutes

Stop Polling: Handle Real-Time Postgres Sync with Pure SQL

Last month, I helped a friend troubleshoot a data platform issue. They wrote a Python script that polled a Postgres table's updated_at column every minute to pull changes into ClickHouse. It ran fine for a week until a network jitter caused it to pull a million duplicate rows, completely breaking downstream reports.

This "Postgres to external system sync" scenario is nothing new. Whether you're connecting to a data warehouse for OLAP analysis, writing to a message queue for event-driven architecture, or archiving to object storage, traditional approaches usually fall into two categories:

1. Scheduled Polling (SELECT): High latency, heavy load on the source DB, and tricky to handle edge cases (missing data, duplicates).
2. Binlog/WAL-based CDC Frameworks: Tools like Debezium or Canal are powerful but heavy on components and configuration. Setting up Kafka Connect + Zookeeper + Debezium is a chore.

If you only need to sync Postgres, PeerDB offers a lighter alternative. It does one thing well: push Postgres data changes (CDC) to various destinations in real-time. Its unique selling point? You configure and monitor the entire ETL process using SQL. No need to learn a new DSL or YAML config files.

Today, I'll guide you from zero to a fully functional PeerDB environment, setting up a real-time Postgres → Destination sync pipeline. All you need is Docker and a psql client.

Prerequisites

• Docker + Docker Compose: PeerDB's local deployment relies on Docker containers (including the Postgres catalog, Temporal workflow engine, PeerDB server, etc.).
• psql >= 14.0: Connect to PeerDB via the standard Postgres protocol to execute SQL commands.
• Basic knowledge of Postgres SQL and WAL/Logical Replication concepts.

Why psql? PeerDB exposes a Postgres Wire Protocol-compatible interface locally (default port 9900). You can treat it like a regular Postgres database. This means you can use pgAdmin, DBeaver, or even JDBC, but this guide uses psql for its simplicity.

Quick Start: Spin Up PeerDB

PeerDB provides a one-click script to spin up the entire container stack.

Step 1: Clone the Repo & Start the Service

git clone git@github.com:PeerDB-io/peerdb.git
cd peerdb

## Start all containers: catalog, Temporal, PeerDB server, UI, etc.
bash ./run-peerdb.sh

Once running, docker-compose will launch multiple containers. Here's what each component does:

• Postgres (catalog): Stores PeerDB's metadata (e.g., Mirror definitions).
• Temporal: Workflow engine for scheduling sync tasks, managing state, and retries.
• PeerDB server & workers: The actual sync engine that reads the source WAL and writes to the destination.
• PeerDB UI: Optional web interface.

Step 2: Connect to PeerDB

Once all containers are up (takes ~1-2 mins), connect via psql:

psql "port=9900 host=localhost password=peerdb"

You'll see the peerdb=> prompt. Note: You're not connecting to your business DB here. This is PeerDB's control plane. You'll run SQL here to create data source connections and sync tasks.

Hands-On: Building a Postgres → ClickHouse CDC Pipeline

Below is a complete walkthrough: assuming you have an orders database orders_db, we'll sync it in real-time to ClickHouse for analytical queries. We'll complete this in three steps: create a source connection, create a destination connection, and start the Mirror sync.

Step 1: Create the Postgres Source Connection

PeerDB uses the CREATE PEER statement to register data sources. This SQL tells PeerDB where your source DB lives and how to connect.

CREATE PEER my_source_pg FROM postgres (
  host = 'host.docker.internal',
  port = '5432',
  user = 'postgres',
  password = 'your_password',
  database = 'orders_db'
);

Design Rationale: PeerDB abstracts all connections as "Peers". This makes swapping destinations (e.g., from ClickHouse to Snowflake) effortless without changing the Mirror config.

After creation, PeerDB prepares the source (enables logical replication, creates slots, etc.). Verify connectivity with:

SELECT peerdb_catalog.list_tables('my_source_pg', 'public');

This lists all tables under the public schema in your source DB.

Step 2: Create the ClickHouse Destination Connection

Assuming your ClickHouse is already deployed (local or Cloud), create the destination Peer:

CREATE PEER my_ch_clickhouse FROM clickhouse (
  host = 'host.docker.internal',
  port = '8123',
  user = 'default',
  password = ''
);

Pitfall Alert: If your ClickHouse is installed directly on the host (outside Docker), PeerDB uses MinIO for temporary file staging. Ensure ClickHouse can reach MinIO. The official README specifically notes changing AWS_ENDPOINT_URL_S3 in docker-compose.yml to a host-reachable IP. I ran into a connection timeout during sync due to this exact misconfiguration.

Step 3: Create & Start the Mirror

A "Mirror" is PeerDB's core sync task. When creating it, you specify source tables, destination, sync mode, and other parameters.

CREATE MIRROR my_order_mirror FROM my_source_pg TO my_ch_clickhouse
FOR TABLES (
  public.orders,
  public.order_items
)
WITH (
  mode = 'cdc',
  initial_load = true,
  resync = false
);

Key parameter breakdown:

• mode = 'cdc': WAL-based log sync. Lowest latency, ideal for real-time. (PeerDB also supports cursor-based and XMIN modes).
• initial_load = true: Full load historical data on first run. Usually set to true for initial sync; set to false if the destination already has data and you only want incremental changes.
• resync = false: Set to true to restart a failed sync from scratch.

Once executed, PeerDB immediately starts working: it handles the full initial load (if configured), then seamlessly switches to CDC incremental mode, streaming WAL changes to the destination in real-time.

Step 4: Verify Sync Status

No log diving needed. Query status directly via SQL:

-- View all Mirror statuses
SELECT name, status, source_peer, destination_peer FROM peerdb_catalog.mirrors;

-- Check sync progress (shows percentage during initial load)
SELECT * FROM peerdb_catalog.mirror_status('my_order_mirror');

-- Troubleshoot: view recent errors
SELECT * FROM peerdb_catalog.mirror_errors('my_order_mirror') ORDER BY created_at DESC LIMIT 10;

A status of active means it's running smoothly. Test with a few INSERT/UPDATE commands in the source DB and check if the data lands in ClickHouse in real-time.

Common Issues & Pitfalls

1. psql Connection Fails: Ensure run-peerdb.sh finished and all containers are healthy. Startup takes 1-2 mins. Check with docker ps.
2. Sudden Sync Latency Spike: PeerDB CDC latency is usually under a few seconds. If it spikes, check Postgres replication slots for backlog (SELECT * FROM pg_replication_slots). PeerDB relies on logical replication slots; a backlog means the consumer can't keep up.
3. Large TOAST Column Sync Slowdown: PeerDB optimizes TOAST columns, but syncing massive rows (e.g., 10s of MB JSONB) can bottleneck. Consider excluding unnecessary columns in the Mirror config.
4. MinIO Connectivity in Isolated Networks: As mentioned, ensure external destinations can reach PeerDB's MinIO staging endpoint. Set AWS_ENDPOINT_URL_S3 to a host-accessible address.

Wrap-Up

Today, we built a PeerDB environment from scratch and configured a real-time Postgres → ClickHouse CDC pipeline using pure SQL. The core workflow is just three steps: CREATE PEER (register connections), CREATE MIRROR (create sync task), and SQL queries for monitoring. No extra config files, no complex framework APIs. For Postgres-first teams, the onboarding curve is significantly lower.

Next Steps to Explore:

• Try cursor-based sync (ideal when WAL isn't enabled).
• Configure schema evolution to auto-handle source table changes.
• Use Airflow or cron to automate Mirror creation/start-stop (it's all SQL, after all).
• Check PeerDB docs for bulk initial load performance tuning parameters.

If you've battled data sync issues before, spend 15 minutes spinning up PeerDB. Experience what SQL-driven ETL really feels like. The Slack community is quite active if you get stuck.