03 Managed Postgres CDC

Create ClickHouse-managed Postgres and a ClickPipe with clickhousectl, then feed live rows into the taxi table.

Outcome

In about 20 minutes, live trips will flow:

Postgres managed by ClickHouse → ClickPipe → default.realtime_trips
→ materialized view → nyc_tlc_data.taxi_trips → Ops dashboard

Prerequisites: Modules 00–02 are complete, and your terminal is in ClickHouse_Demos/workshops/build_workshop/app.

Step 1 — Create managed Postgres in ClickHouse Cloud

Use the same region as your ClickHouse service:

clickhousectl cloud postgres create \
  --name my-workshop-postgres \
  --provider aws \
  --region ap-southeast-1 \
  --size c6gd.large \
  --pg-version 17 \
  --ha-type none

Save the returned Postgres ID, hostname, and one-time postgres password. The beta list and get commands may return empty or FORBIDDEN; the required readiness check is ./preflight.sh --require-postgres in Step 2.

If the password is lost, create a new one:

clickhousectl cloud postgres reset-password <postgres-id>

Step 2 — Start the trip writer

There is no local Postgres fallback. In .env.workshop, fill the blank PGHOST and PGPASSWORD fields with the values returned by clickhousectl; keep TLS required:

PGHOST=replace-with-hostname-from-clickhousectl
PGPORT=5432
PGDATABASE=postgres
PGUSER=postgres
PGPASSWORD=replace-with-one-time-password
PGSSLMODE=require
PG_PUBLICATION=pub_taxi

Validate the managed endpoint, then start the local writer against it and follow its log:

cd "$(git rev-parse --show-toplevel)/workshops/build_workshop/app"
./preflight.sh --require-postgres
docker compose --profile cdc --env-file .env.workshop -f docker-compose.workshop.yml up -d pg-trip-writer
docker compose --profile cdc --env-file .env.workshop -f docker-compose.workshop.yml logs -f pg-trip-writer

Provisioning can take a few minutes. Continue when the log repeats:

[loadgen] ensured realtime_trips table exists
[loadgen] created publication pub_taxi for public.realtime_trips
[loadgen] inserted 10 trips @ ...

Press Ctrl-C to stop following the log; the container keeps running.

Step 3 — Create the ClickPipe

Replace the ClickHouse service ID and Postgres values:

clickhousectl cloud clickpipe create postgres <clickhouse-service-id> \
  --name taxi-cdc \
  --host <managed-postgres-hostname> \
  --port 5432 \
  --pg-database postgres \
  --username postgres \
  --password '<one-time-password>' \
  --publication-name pub_taxi \
  --replication-mode cdc \
  --table-mapping "public.realtime_trips:realtime_trips"

A CLI-created ClickPipe places this target at default.realtime_trips. Check its state:

clickhousectl cloud clickpipe list <clickhouse-service-id>
clickhousectl cloud clickpipe get <clickhouse-service-id> <clickpipe-id>

Continue when the pipe is running and the initial snapshot has created the target table.

Step 4 — Create the CDC materialized view

First verify the exact source table:

clickhousectl cloud service query --id <clickhouse-service-id> --query "
  SELECT database, name, engine
  FROM system.tables
  WHERE name = 'realtime_trips'
"

Expected: default.realtime_trips. Now create one incremental materialized view; there is no variant to select or file to edit:

clickhousectl cloud service query --id <clickhouse-service-id> --query "
CREATE MATERIALIZED VIEW IF NOT EXISTS nyc_tlc_data.realtime_trips_to_taxi_trips_mv
TO nyc_tlc_data.taxi_trips
AS
SELECT
  car_type,
  CAST(vendor_id AS UInt16) AS vendor_id,
  CAST(pickup_datetime AS DateTime('UTC')) AS pickup_datetime,
  CAST(dropoff_datetime AS DateTime('UTC')) AS dropoff_datetime,
  CAST(pickup_location_id AS UInt16) AS pickup_location_id,
  CAST(dropoff_location_id AS UInt16) AS dropoff_location_id,
  CAST(passenger_count AS UInt16) AS passenger_count,
  trip_distance,
  CAST(payment_type AS UInt16) AS payment_type,
  fare_amount,
  tip_amount,
  total_amount,
  'realtime_cdc' AS filename
FROM default.realtime_trips
WHERE _peerdb_is_deleted = 0
"

Use the installed best-practices skill to check the design:

Use the ClickHouse best-practices skill to review this incremental materialized view.
Confirm why it processes inserted blocks and why FINAL is not part of this append-only path.

Step 5 — Prove rows are moving

Run this twice, about 15 seconds apart:

clickhousectl cloud service query --id <clickhouse-service-id> --query "
  SELECT
    (SELECT count() FROM default.realtime_trips) AS clickpipe_rows,
    (SELECT count() FROM nyc_tlc_data.taxi_trips
      WHERE filename = 'realtime_cdc') AS dashboard_rows
"

Both counts should increase. Then open localhost:8080, set the Ops dashboard interval to 1m and auto-refresh to 5s.

Completion check

The writer log shows repeated inserts.
clickhousectl cloud clickpipe get ... reports a running pipe.
default.realtime_trips and the dashboard-row count both increase.
The Ops dashboard updates without a manual refresh.

Continue to 04 ClickHouse Agents.