Cloud SQL PostgreSQL CDC to CtrlB
This guide explains how to stream change data capture (CDC) events from a Cloud SQL PostgreSQL database into CtrlB using Conduit. It covers the database setup and every deployment file needed to run the pipeline with Docker Compose.
PostgreSQL endpoint → Conduit → CtrlB
The default destination is the CtrlB HTTPS endpoint. To connect to CtrlB through a private IP instead, see Section 4.
Overview
Conduit reads row-level changes from PostgreSQL using logical replication, wraps each change in a CtrlB event envelope, and posts it to your CtrlB ingestion endpoint over HTTP.
Versions Used
| Component | Version |
|---|---|
| Conduit | v0.20.4 |
| PostgreSQL connector (built-in) | v0.14.4 |
| HTTP connector (downloaded separately) | v0.4.0 |
The official Conduit image and the released HTTP plugin are used without source changes or an image build.
Prerequisites
Before beginning the setup, ensure you have:
- A Cloud SQL PostgreSQL primary and an existing application database
- Administrator and table-owner access for the SQL setup
- A Linux AMD64 or ARM64 Docker host with Docker Compose,
curl,tar,sha256sumandinstall - Durable storage for Conduit state
- A PostgreSQL endpoint reachable from the Conduit container, with authentication and TLS configured. Connect directly or through your own Cloud SQL Auth Proxy
- A CtrlB ingestion URL and authorization header
- A single-column primary key on every captured table. This connector version does not support composite primary keys
You operate Conduit and its replication slot. Logical decoding adds source CPU, I/O and WAL overhead. If capture stops or falls behind, the slot can retain WAL and exhaust source storage. This setup does not cap retained WAL or automatically remove stalled slots. Monitor connector failures, retained WAL and source free disk.
GCP authentication, networking and Cloud SQL Auth Proxy operation are your responsibility.
1. Enable Logical Decoding
Step 1: Check Replication Capacity
Run this on the primary as a database administrator:
SELECT current_setting('max_replication_slots')::int AS slot_limit,
(SELECT count(*) FROM pg_replication_slots) AS slots_used,
current_setting('max_wal_senders')::int AS sender_limit,
(SELECT count(*) FROM pg_stat_replication) AS senders_active;
Reserve one additional logical replication slot and one WAL sender for this connector. Inactive slots still consume slot capacity. Plan for other CDC consumers, replicas, backups and reconnects; active sender counts show only current connections.
Step 2: Configure Database Flags
Go to Cloud SQL → Instance → Edit → Flags, retain existing flags, and configure:
| Flag | Setting |
|---|---|
cloudsql.logical_decoding | on |
max_replication_slots | Enough for existing and planned slots, including this connector |
max_wal_senders | Enough for concurrent senders and reconnects; at least max_replication_slots |
Keep existing limits when sufficient, and schedule the required restart. If the instance has read replicas, follow Google's guidance to enable logical decoding on the replicas before the primary. See Cloud SQL logical replication and supported flags.
If you use gcloud, inspect the current flag list first:
gcloud sql instances describe INSTANCE_NAME --project=PROJECT_ID \
--format='yaml(settings.databaseFlags)'
gcloud sql instances patch --database-flags=... replaces the entire flag list. Include existing flags alongside your changes; omitted flags revert to defaults.
Step 3: Verify the Configuration
SHOW wal_level; -- must return logical
SHOW max_replication_slots;
SHOW max_wal_senders;
Rerun the capacity query from Step 1 before starting capture. Conduit creates its own slot on first startup; no manual slot creation is needed.
2. Prepare the Source Database
Connect to the application database with your administrator account. Run the SQL in this section in that database, rather than the default postgres database.
Replace the <...> placeholders before running SQL:
| Placeholder | Example |
|---|---|
<database_name> | appd |
<schema_list> | public |
<table_list> | public.orders, public.transactions |
<publication_name> | ctrlb_conduit_appd_pub |
<table_name> | public.orders or public.transactions |
Keep these names aligned with the .env file in Section 3. The application tables must already exist. Run creation statements once; reuse existing roles and publications when already configured.
Step 1: Create the Replication Role
Run as an administrator permitted to create replication roles:
CREATE ROLE ctrlb_cdc WITH LOGIN REPLICATION;
In psql, run this command by itself and enter the password at the prompt:
\password ctrlb_cdc
LOGIN permits database connections, and REPLICATION permits reading the logical change stream. Use this role's password in DB_URL.
Step 2: Grant Access and Create the Publication
Run grants as a role allowed to grant those privileges. Publication creation requires ownership rights on the captured tables and CREATE on the database.
GRANT CONNECT, CREATE ON DATABASE <database_name> TO ctrlb_cdc;
GRANT USAGE ON SCHEMA <schema_list> TO ctrlb_cdc;
GRANT SELECT ON TABLE <table_list> TO ctrlb_cdc;
CREATE PUBLICATION <publication_name> FOR TABLE <table_list>;
Configuration Notes:
- Database
CREATEis needed because this connector attempts publication creation even when the publication already exists - Precreating the publication avoids granting the CDC role ownership of application tables
- Schema
USAGEand tableSELECTpermit access to the selected tables. See connector setup
Step 3: Configure Replica Identity
Every captured table needs a single-column primary key. Use replica identity DEFAULT with that key, or FULL.
A publication that publishes updates and deletes requires valid replica identities. Without one, PostgreSQL can reject application updates and deletes even when Conduit is stopped. See PostgreSQL publication requirements.
To include complete previous-row values, run separately for each required table as its owner:
ALTER TABLE <table_name> REPLICA IDENTITY FULL;
FULL increases WAL volume. With DEFAULT, previous-row values can be absent; delete events retain their key. Unchanged large TOASTed fields can be omitted from updates. See before images and TOAST handling.
Optional: Capture All Tables in public
For PostgreSQL 15 or later, keep the replication role and database grant above, then replace the selected-table grants and CREATE PUBLICATION with the following. Run as your Cloud SQL administrator with the required publication privileges:
GRANT USAGE ON SCHEMA public TO ctrlb_cdc;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ctrlb_cdc;
CREATE PUBLICATION <publication_name> FOR TABLES IN SCHEMA public;
To change an existing publication to this scope, replace the CREATE PUBLICATION statement with:
ALTER PUBLICATION <publication_name> SET TABLES IN SCHEMA public;
This replaces the publication's existing table/schema list. Set DB_TABLES='*' in .env. The wildcard discovers tables in public at Conduit startup.
- Every discovered table must have a supported single-column primary key
- Unlogged tables cannot be published
- Use an explicit table list if
publiccontains incompatible tables
The schema publication includes future tables, but future read grants and Conduit discovery still need attention. To grant access automatically for tables created by a particular role:
ALTER DEFAULT PRIVILEGES FOR ROLE <table_creator_role> IN SCHEMA public
GRANT SELECT ON TABLES TO ctrlb_cdc;
Replace <table_creator_role> with the role that actually creates the tables, and repeat for each creator role. These defaults apply only to future tables.
Note:
Create new tables with their primary keys and grants, then recreate Conduit before writing test or application rows to them, so it discovers their keys. See Cloud SQL schema publications and default privileges.
3. Create the Deployment Files
Run the following shell blocks on the Docker host. Use a fresh deployment directory; the file-creation blocks write the named files. For an existing installation, retain its credentials, project name and state rather than replacing them with the examples.
mkdir -p conduit-cdc
cd conduit-cdc
mkdir -p pipelines certs
Run all later Docker commands from this directory. Keep the quoted delimiters such as <<'YAML': they preserve ${...} expressions for Conduit and Compose.
Step 1: Create the .env File
cat > .env <<'ENV'
COMPOSE_PROJECT_NAME=ctrlb-conduit
# URL-encode the username/password. Use a host reachable from the container.
# For direct TLS, place the CA file at certs/server-ca.pem.
DB_URL='postgres://ctrlb_cdc:REPLACE_WITH_URL_ENCODED_PASSWORD@DB_HOST:5432/appd?sslmode=verify-ca&sslrootcert=/app/certs/server-ca.pem'
# Bare table names in the connection's search_path, not public.orders.
DB_TABLES=orders,transactions
CDC_PUBLICATION_NAME=ctrlb_conduit_appd_pub
CDC_SLOT_NAME=ctrlb_conduit_appd_slot
CDC_PIPELINE=cloudsql-appd-conduit
CTRLB_HOST=ctrlb.explore.ctrlb.ai
CTRLB_INGEST_PATH=/engine/api/default/postgres_cdc/v2/_json_evolving
CTRLB_AUTHORIZATION='Basic REPLACE_WITH_CTRLB_AUTHORIZATION_VALUE'
ENV
chmod 600 .env
Edit .env before starting:
| Setting | Value |
|---|---|
DB_URL | PostgreSQL URI with URL-encoded credentials, an explicit database name and the required TLS parameters |
DB_TABLES | Bare table names such as orders,transactions, or '*' for all tables in public |
CDC_PUBLICATION_NAME | The publication created in Section 2 |
CDC_SLOT_NAME | A dedicated slot name for this connector |
CDC_PIPELINE | Event label; use letters, digits, dots, underscores and hyphens |
CTRLB_HOST | CtrlB hostname or IP address, with a port when needed, without a scheme or path |
CTRLB_INGEST_PATH | Exact ingestion path from your CtrlB request |
CTRLB_AUTHORIZATION | Complete authorization header value, including Basic when supplied |
Configuration Notes:
- The example database is
appd. Your URI must include the actual name after the port, for example:5432/staging_data_lake?sslmode=.... An omitted database name can default to the username - Single-quote literal credentials in
.envand percent-encode special characters in URI credentials. See PostgreSQL connection parameters and Compose environment-file syntax
TLS Configuration
- Direct TLS: Copy your CA certificate to
certs/server-ca.pem, or adjustsslrootcertto match its container path under/app/certs. Ensure the files are readable by the container verify-cais appropriate for a Cloud SQL per-instance CA. Shared or customer-managed CAs useverify-fullwith the certificate's DNS name- Client certificates: If required, add
sslcertandsslkeyURI parameters. See Cloud SQL TLS configuration
Cloud SQL Auth Proxy
For your own Cloud SQL Auth Proxy, use an address reachable from the Conduit container, typically with sslmode=disable and no certificate parameters. In the default bridge network, 127.0.0.1 refers to the Conduit container itself. Provide a reachable proxy endpoint and configure its networking separately.
Step 2: Download the HTTP Connector
Create the download script:
cat > download-http-connector.sh <<'SH'
#!/bin/sh
set -eu
case "$(uname -m)" in
x86_64|amd64)
release_arch=x86_64
checksum=7bf93169e5dbdfd0bef23626fb8623c4a09b793a7bfbd4334398995aa760e17f
;;
aarch64|arm64)
release_arch=arm64
checksum=e19611df75836910795ec296a7c0be51b22d7bd8e7c18cc6ac97c214eb7601a7
;;
*)
printf 'Unsupported architecture: %s\n' "$(uname -m)" >&2
exit 1
;;
esac
script_dir=$(CDPATH= cd -- "$(dirname -- "$0")" && pwd)
download_dir=$(mktemp -d)
trap 'rm -rf "$download_dir"' EXIT
trap 'exit 1' HUP INT TERM
curl --fail --show-error --location --retry 3 \
--output "$download_dir/http.tar.gz" \
"https://github.com/conduitio-labs/conduit-connector-http/releases/download/v0.4.0/conduit-connector-http_0.4.0_Linux_${release_arch}.tar.gz"
printf '%s %s\n' "$checksum" "$download_dir/http.tar.gz" | sha256sum -c -
tar -xzf "$download_dir/http.tar.gz" -C "$download_dir" conduit-connector-http
mkdir -p "$script_dir/connectors"
install -m 755 "$download_dir/conduit-connector-http" "$script_dir/connectors/http"
printf 'Installed HTTP connector v0.4.0 in %s/connectors/http\n' "$script_dir"
SH
Download the released plugin for the Docker host's architecture and verify its checksum:
sh ./download-http-connector.sh
The plugin is installed as connectors/http. Keep this file executable. The PostgreSQL connector is already included in the official Conduit image.
Step 3: Create the Pipeline Configuration
Configuration File: pipelines/conduit.yaml
cat > pipelines/conduit.yaml <<'YAML'
version: 2.2
pipelines:
- id: ctrlb-postgres
status: running
processors:
- id: http-envelope
plugin: field.set
settings:
field: .Payload.After
value: >-
[{"op":"{{ .Operation }}","key":{{ toJson .Key }},"before":{{ toJson .Payload.Before }},"after":{{ toJson .Payload.After }},"_timestamp":{{ div (int64 (index .Metadata "opencdc.readAt")) 1000 }},"source":{{ toJson .Metadata }},"cdc_pipeline":"${CDC_PIPELINE}"}]
sdk.schema.decode.key.enabled: "false"
sdk.schema.decode.payload.enabled: "false"
sdk.schema.encode.key.enabled: "false"
sdk.schema.encode.payload.enabled: "false"
- id: encode-key
plugin: json.encode
settings:
field: .Key
sdk.schema.decode.key.enabled: "false"
sdk.schema.decode.payload.enabled: "false"
sdk.schema.encode.key.enabled: "false"
sdk.schema.encode.payload.enabled: "false"
- id: encode-before
plugin: json.encode
settings:
field: .Payload.Before
sdk.schema.decode.key.enabled: "false"
sdk.schema.decode.payload.enabled: "false"
sdk.schema.encode.key.enabled: "false"
sdk.schema.encode.payload.enabled: "false"
connectors:
- id: postgres
type: source
plugin: builtin:postgres@v0.14.4
settings:
url: ${DB_URL}
tables: ${DB_TABLES}
cdcMode: logrepl
snapshotMode: never
logrepl.publicationName: ${CDC_PUBLICATION_NAME}
logrepl.slotName: ${CDC_SLOT_NAME}
logrepl.autoCleanup: "false"
logrepl.withAvroSchema: "false"
sdk.schema.extract.key.enabled: "false"
sdk.schema.extract.payload.enabled: "false"
- id: http
type: destination
plugin: standalone:http@v0.4.0
settings:
url: https://${CTRLB_HOST}${CTRLB_INGEST_PATH}
headers: "Content-Type: application/json,Authorization: ${CTRLB_AUTHORIZATION}"
method: POST
validateConnection: "false"
sdk.schema.extract.key.enabled: "false"
sdk.schema.extract.payload.enabled: "false"
YAML
snapshotMode: never skips existing rows. For other snapshot settings, see the official connector documentation.
Event Format
The processors build the CtrlB event envelope and serialize it for the HTTP plugin. Each event contains the following fields:
| Field | Description |
|---|---|
op | Operation type: create, update or delete |
key | Primary key of the changed row |
before | Previous row values (depends on replica identity) |
after | New row values |
source | Connector metadata for the change |
cdc_pipeline | The CDC_PIPELINE label from .env |
_timestamp | Connector read time in Unix microseconds |
Configuration Notes:
_timestampis derived fromopencdc.readAt. This released connector does not expose database commit time in its event metadata, so backlogged changes can have a later_timestampthan their original commit time- JSON/JSONB and binary
byteavalues become base64 strings - PostgreSQL
numericvalues use the connector's rational-string representation, such as"3/2"for1.5. See numeric handling - Verify expected columns and types in CtrlB before production
Step 4: Create the Docker Compose File
Configuration File: compose.yaml
cat > compose.yaml <<'YAML'
services:
conduit:
image: ghcr.io/conduitio/conduit:v0.20.4
restart: unless-stopped
stop_grace_period: 60s
environment:
DB_URL: ${DB_URL:?Set DB_URL in .env}
DB_TABLES: ${DB_TABLES:?Set DB_TABLES in .env}
CDC_SLOT_NAME: ${CDC_SLOT_NAME:?Set CDC_SLOT_NAME in .env}
CDC_PUBLICATION_NAME: ${CDC_PUBLICATION_NAME:?Set CDC_PUBLICATION_NAME in .env}
CDC_PIPELINE: ${CDC_PIPELINE:?Set CDC_PIPELINE in .env}
CTRLB_HOST: ${CTRLB_HOST:?Set CTRLB_HOST in .env}
CTRLB_INGEST_PATH: ${CTRLB_INGEST_PATH:?Set CTRLB_INGEST_PATH in .env}
CTRLB_AUTHORIZATION: ${CTRLB_AUTHORIZATION:?Set CTRLB_AUTHORIZATION in .env}
command:
- /app/conduit
- run
- --connectors.path=/app/connectors
- --pipelines.path=/app/pipelines
- --db.badger.path=/app/state/conduit.db
- --api.http.address=127.0.0.1:8080
- --api.grpc.address=127.0.0.1:8084
- --log.format=json
volumes:
- ./connectors:/app/connectors:ro
- ./pipelines/conduit.yaml:/app/pipelines/conduit.yaml:ro
- ./certs:/app/certs:ro
- conduit-state:/app/state
volumes:
conduit-state:
YAML
Only the selected pipeline file is mounted. Create it before starting Docker; it must be a regular file. A missing bind source can otherwise be created as a directory, leaving Conduit with zero loaded pipelines.
Step 5: Create the .gitignore File
cat > .gitignore <<'IGNORE'
.env
certs/
connectors/
*.log
IGNORE
4. Optional: CtrlB Through a Private IP
Use a CtrlB private IP reachable from the Conduit container. Configure routing and firewall rules to allow access to the ingestion port, and ensure CtrlB listens on the private interface. The example below uses 10.0.0.10:8080 over HTTP; replace it with your endpoint.
Change these values in .env, retaining the authorization value for that CtrlB instance:
CTRLB_HOST=10.0.0.10:8080
CTRLB_INGEST_PATH=/api/default/postgres_cdc/v2/_json_evolving
For an HTTP endpoint, change the HTTP destination's url setting in pipelines/conduit.yaml to:
url: http://${CTRLB_HOST}${CTRLB_INGEST_PATH}
Configuration Notes:
- For HTTPS, keep
https://and use a hostname or IP covered by the server certificate - Use the exact ingestion path for your deployment. The public gateway example uses
/engine/api/..., while this private backend example uses/api/... - Keep the
compose.yamlfrom Section 3. A reachable private IP uses the default Docker bridge network; no host-network override is required
5. Start Conduit
Use the same command block for the default HTTPS endpoint or the private IP configuration. The subshell stops startup if the pipeline file or plugin is missing:
(
set -eu
test -f pipelines/conduit.yaml
test -s pipelines/conduit.yaml
test -x connectors/http
docker compose config --quiet
docker compose up -d
)
Inspect logs:
docker compose logs -f --tail=100 conduit
Press Ctrl+C to stop following logs. This does not stop the container.
Verify
Look for the following messages in the logs:
pipeline startedLogical replication startedsource connector plugin successfully started
Existing-publication and existing-slot warnings are expected on restarts. Missing standalone processor-directory warnings do not prevent these built-in processors from running.
Plugin startup alone does not confirm CtrlB ingestion. Verify that fresh committed source changes arrive in CtrlB.
Note:
Use a fresh dedicated slot and empty state only for the first deployment. Preserve the same slot, project name, pipeline/connector IDs and state volume on restarts.
6. Troubleshooting
| Symptom | Check / Correction |
|---|---|
Zero pipelines, or provisioning reports created: [] on an empty deployment | Check that pipelines/conduit.yaml is a nonempty regular file and is mounted under /app/pipelines. |
database "ctrlb_cdc" does not exist | Add the actual database path to DB_URL, for example /appd or /staging_data_lake, and recreate Conduit. |
| Primary-key lookup fails with a connection error | Correct the underlying connection error first; the surrounding primary-key text does not establish a missing key. |
HTTP response to HTTPS client | The endpoint speaks plain HTTP but the pipeline URL uses HTTPS. Match the scheme to the endpoint. |
| Private IP connection times out or is refused | Verify the container has a route to the private IP, firewall rules allow the ingestion port, and CtrlB listens on that interface and port. |
context canceled after a source or sink error | Fix the first source/sink error; cancellation messages are consequences of pipeline teardown. |
| Source starts but CtrlB shows no events | Make fresh committed changes after startup, confirm table selection/publication membership and CtrlB time range, then inspect sink errors and the CtrlB ingestion response. |
Inspecting the Pipeline Mount
To inspect file types and mounts without printing credentials:
ls -ld pipelines/conduit.yaml
docker compose exec -T conduit ls -la /app/pipelines
A file begins with - in an ls -l listing; a directory begins with d.
If Docker created pipelines/conduit.yaml as an empty directory:
- Stop the deployment.
- Remove that empty directory with
rmdir. - Recreate the real file from Section 3.
- Recreate Conduit.
Preserve the existing named state volume and replication slot.