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

Download the managed-Postgres CA certificate

The managed Postgres certificate chain is private, so ClickPipe needs the instance-specific CA certificate to verify the TLS connection. In the ClickHouse Cloud console, open your managed Postgres service, then select Settings → Security → Download CA certificate. Save the download as .work/managed-postgres-ca.pem in the app directory. Do not reuse a certificate from another Postgres instance.

In Terminal, move the downloaded certificate from your Downloads folder (replace the downloaded filename with the file you received):

mkdir -p .work
mv "$HOME/Downloads/<downloaded-ca-filename>" .work/managed-postgres-ca.pem

Read the Postgres values saved in .env.workshop, then replace only the ClickHouse service ID (it is not stored in the environment file). This does not evaluate the environment file as shell code:

workshop_env() { sed -n "s/^$1=//p" .env.workshop | tail -n 1; }
PGHOST=$(workshop_env PGHOST)
PGPORT=$(workshop_env PGPORT)
PGDATABASE=$(workshop_env PGDATABASE)
PGUSER=$(workshop_env PGUSER)
PGPASSWORD=$(workshop_env PGPASSWORD)
PG_PUBLICATION=$(workshop_env PG_PUBLICATION)
unset -f workshop_env
PG_CA_CERT="$PWD/.work/managed-postgres-ca.pem"
test -s "$PG_CA_CERT" || { echo "Missing CA certificate: $PG_CA_CERT" >&2; exit 1; }

clickhousectl cloud clickpipe create postgres <clickhouse-service-id> \
  --name taxi-cdc \
  --host "$PGHOST" \
  --port "$PGPORT" \
  --pg-database "$PGDATABASE" \
  --username "$PGUSER" \
  --password="$PGPASSWORD" \
  --publication-name "$PG_PUBLICATION" \
  --ca-certificate "$PG_CA_CERT" \
  --replication-mode cdc \
  --table-mapping "public.realtime_trips:realtime_trips"

clickhousectl has no interactive password prompt for this command. The --password=... form keeps the password out of shell history and works when the generated password begins with -. The value is still briefly visible to local process-inspection tools while the command runs, so use a trusted machine.

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.