Skip to content

Database tools

Logical replication (CDC)

Tested with:Databasezy API v1 · zb CLI · ZB-621, ZB-743 (written; live check pending)

Logical replication streams the row changes of the tables you choose out of your instance: every insert, update, delete and truncate, in commit order. An external subscriber reads them for change data capture (CDC). That can be Debezium on Kafka Connect, pg_recvlogical, or any client that speaks PostgreSQL’s pgoutput protocol.

It works on PostgreSQL and TimescaleDB instances only. You decide which tables are published, and your subscriber creates and owns its replication slot. Databasezy keeps the slots within your plan’s limits so that a stalled subscriber can never fill the instance’s disk.

If you want the changes in ClickHouse or BigQuery without running a CDC tool yourself, use Pipelines: it is the managed alternative, with its own publication and slot.

Logical replication is a plan feature. The Free plan does not include it. Plans that include it set three limits per instance:

LimitWhat it bounds
Replication slotsThe slots your subscribers may keep. The platform drops slots beyond the limit.
PublicationsThe publications you may create.
WAL a slot may hold backA tenth of the instance’s volume, at least 64 MiB and at most 4 GiB, and never more than your plan’s cap.

The Replication page and GET .../replication show the exact values for your instance (max_slots, max_publications and wal_cap_bytes). Plans and prices are on the pricing page and in Limits and quotas.

Replication users are database users. Each one counts toward your plan’s database users per instance.

On a secure-placement (HIPAA) instance, first set an IP allow-list under Network. It must not contain 0.0.0.0/0 or ::/0. Until it is set, replication on that instance is refused.

  1. Create a replication user. In the portal, open the database and choose Replication (or Database users), then add a user with the role replication. With the API:

    shell
    zb api POST /v1/orgs/{org}/instances/inst_01jb.../users -d '{"name": "cdc", "role": "replication"}'

    You need the credentials:reveal permission in the instance’s team and a second factor completed in the last 15 minutes. The user may log in for replication and connect to the app database. It is never a superuser. The first time, the user can log in once Databasezy has locked down the database’s replication functions for it, usually within a minute. Until then, Replication and GET .../replication say that replication users cannot log in yet.

  2. Reveal its password. In Database users, choose Reveal next to the user. The password is generated inside the instance’s cell and never stored by the control plane. Each reveal shows it once, so copy it straight into your CDC tool’s secret store.

  3. Create a publication. A publication names the tables your subscriber receives. In the portal, open Replication → Publications. With the API:

    shell
    zb api POST /v1/orgs/{org}/instances/inst_01jb.../replication/publications -d '{
    "name": "mypub",
    "tables": ["public.orders", "public.customers"],
    "schemas": [],
    "operations": ["insert", "update", "delete", "truncate"]
    }'
    • The name is 1 to 48 lowercase letters, digits and _, starting with a letter. Names starting with zb, supabase, pg_, cnpg or realtime are reserved for the platform.
    • List up to 100 tables as schema.table, and up to 10 schemas. A schema publishes every table in it, now and later.
    • operations defaults to all four.
    • Platform schemas (auth, storage, realtime, vault and the others) are never published.
    • Give every published table a primary key, or set alter table ... replica identity full. PostgreSQL refuses UPDATE and DELETE on a table that publishes them without a replica identity.

    The answer is 202 Accepted with the state pending. The state then turns applied. If it turns failed, message says why, and the platform retries. Once it is applied, your replication users may SELECT exactly the published tables, which is what a subscriber needs for its initial snapshot. Creating a publication needs the same permission and recent second factor as step 1.

  4. Connect your subscriber. Use the host and port that Connect and Replication show. Connections go through the TLS gateway, like every other connection.

    • TLS is required: the gateway refuses plaintext, so set sslmode=require. To have your tool also check the server’s certificate, use verify-full with the CA bundle instead.
    • The database is app. Replication users reach no other database.
    • The output plugin is pgoutput. It is the only one the platform keeps.

Save the connector as databasezy-orders.json:

databasezy-orders.json
{
"name": "databasezy-orders",
"config": {
"connector.class": "io.debezium.connector.postgresql.PostgresConnector",
"plugin.name": "pgoutput",
"database.hostname": "<host>",
"database.port": "<port>",
"database.user": "cdc",
"database.password": "${file:/secrets/databasezy.properties:password}",
"database.dbname": "app",
"database.sslmode": "require",
"topic.prefix": "databasezy",
"publication.name": "mypub",
"publication.autocreate.mode": "disabled",
"slot.name": "debezium_orders",
"snapshot.mode": "initial",
"table.include.list": "public.orders,public.customers"
}
}

Then register it with your Kafka Connect cluster:

shell
curl -sS -X POST -H "Content-Type: application/json" \
--data @databasezy-orders.json http://localhost:8083/connectors
  • publication.autocreate.mode must be disabled. The replication user cannot create or change publications: you manage them in the portal or the API, and Debezium uses the one you name in publication.name.
  • Leave slot.drop.on.stop at its default, false. With true, Debezium drops its slot with the SQL function pg_drop_replication_slot, which replication users cannot run. Drop slots in the portal or with the API instead.
  • slot.name must be unique for each connector. A slot serves one subscriber at a time and keeps that subscriber’s position. Do not start a slot name with zb, supabase, pg_, cnpg, realtime or _: those names belong to the platform, and the API does not drop such slots for you.
  • snapshot.mode initial copies the published tables once, then streams changes from the slot.
  • database.sslmode require encrypts the connection. To also verify the server, set verify-full and point database.sslrootcert at the CA bundle.
  • The password comes from a Kafka Connect config provider, so it is never in the connector’s JSON. The file provider needs config.providers=file and config.providers.file.class=org.apache.kafka.common.config.provider.FileConfigProvider in the worker’s configuration. Any other secret provider works too. Never commit the password.
  • If your published tables change rarely while the rest of the database changes often, read Debezium’s notes on WAL disk space and heartbeats (heartbeat.interval.ms).

pg_recvlogical is handy to check that changes arrive. Put the password in PGPASSWORD or ~/.pgpass, never on the command line:

shell
pg_recvlogical -d "host=<host> port=<port> dbname=app user=<user> sslmode=require replication=database" --slot=myslot --create-slot -P pgoutput -o proto_version=1 -o publication_names=mypub --start -f -

Replace <host> and <port> with the instance’s host and port and <user> with your replication user. To also verify the server, use sslmode=verify-full and add sslrootcert=databasezy-ca.pem.

pgoutput writes PostgreSQL’s binary replication protocol, so the output is only partly readable. A CDC tool decodes it for you.

When you are done, drop the slot. A slot you leave behind keeps holding WAL:

shell
pg_recvlogical -d "host=<host> port=<port> dbname=app user=<user> sslmode=require replication=database" --slot=myslot --drop-slot

You can also drop it in the portal or with the API (see the next section).

Your own PostgreSQL server can subscribe too, with CREATE SUBSCRIPTION. Use the replication user and the host and port from Replication, and sslmode=require in the connection string. The initial copy (copy_data = true) works: its temporary table-sync slots are allowed.

psql (your server)
CREATE SUBSCRIPTION databasezy_orders
CONNECTION 'host=<host> port=<port> dbname=app user=<user> password=<password> sslmode=require'
PUBLICATION mypub;

A connection with replication=database runs only what a logical subscriber needs. The gateway checks every command before it reaches the database:

  • Replication commands: IDENTIFY_SYSTEM, SHOW for a few settings (such as server_version), and CREATE_REPLICATION_SLOT ... LOGICAL pgoutput, START_REPLICATION SLOT ... LOGICAL with pgoutput’s options (proto_version and publication_names are required), DROP_REPLICATION_SLOT and ALTER_REPLICATION_SLOT.
  • SQL: one statement per query, either SELECT, BEGIN, COMMIT, ROLLBACK, or COPY ... TO STDOUT.

Anything else ends the connection with an error, SQLSTATE 42501, and is recorded as a security event. That includes:

  • physical replication (replication=true, pg_basebackup, pg_receivewal) and BASE_BACKUP;
  • slots of any output plugin other than pgoutput, such as test_decoding or wal2json;
  • slots and publications whose names start with zb, supabase, pg_, cnpg, realtime or _, which belong to the platform. The table-sync slots of a PostgreSQL subscription are the exception;
  • the replication slot functions, such as pg_create_logical_replication_slot, pg_logical_slot_get_changes and pg_drop_replication_slot. The database refuses them to replication users on every connection, not only on replication connections.

A slot holds back WAL until its subscriber confirms that it has received the changes. The Replication page shows each slot’s retained WAL as a percentage of the cap, its unconfirmed lag, whether a subscriber is streaming it, and PostgreSQL’s wal_status (reserved, extended, unreserved or lost).

  • At 80% of the cap, the slot is reported as lagging and Databasezy’s on-call is alerted.
  • At the cap, PostgreSQL invalidates the slot (wal_status lost). The disk never fills, but the subscriber cannot resume from that slot.
  • To recover, drop the invalidated slot and take a new snapshot with a new slot. With Debezium, create the connector again under a new name with a new slot.name and snapshot.mode initial. Kafka Connect keeps offsets per connector name, so a new name starts from a fresh snapshot.
  • An inactive slot still holds WAL. Drop the slots of subscribers you no longer run.

To drop a slot, choose Drop next to it on the Replication page, or call DELETE .../replication/slots/{slot_name}. The platform ends the stream that is reading the slot, then drops it. The drop is asynchronous: the answer is 202 and the slot shows drop_pending until it is gone. It needs the instances:operate permission.

  • Only logical slots with the pgoutput plugin are kept. The platform drops physical slots, slots of other output plugins (for example wal2json or test_decoding) and slots beyond your plan’s limit.
  • The platform’s own slots (realtime, migrations, pipelines and high availability) are not listed and do not count toward your limit.
  • A replication user connects only to app. It reads only the published tables during a snapshot.
  • The initial snapshot follows row-level security: on a table with row-level security turned on, it holds only the rows a policy lets the replication user read. Streamed changes do not: logical replication sends every change of a published table, whatever the policies say. Publish only tables whose every row the subscriber may see.
  • A replication user streams publications only, never other tables: base backups, physical replication, other output plugins and the replication slot functions are refused. See What a replication connection may run.
  • Logical replication carries row changes only. Schema changes (DDL), sequence values and large objects are not streamed.
  • On TimescaleDB, a hypertable keeps its rows in chunk tables in _timescaledb_internal, a platform schema that is never published. Do not rely on a publication to stream a hypertable.
  • Secure-placement (HIPAA) instances need an IP allow-list without 0.0.0.0/0 or ::/0 before replication is allowed. See Secure hosting and the BAA.
  • Creating publications and replication users, revealing passwords and dropping slots are recorded in your organization’s audit log.
shell
zb instances users create inst_01jb... cdc --role replication # the subscriber's login
zb instances users reveal inst_01jb... cdc # shows its password once
zb replication status inst_01jb... # limits, users, host and port
zb replication publications create inst_01jb... mypub --table public.orders --table public.customers
zb replication publications create inst_01jb... sales --schema sales --operations insert,update,delete
zb replication publications list inst_01jb...
zb replication publications delete inst_01jb... mypub
zb replication slots list inst_01jb...
zb replication slots drop inst_01jb... debezium_orders

publications delete and slots drop ask you to confirm. slots list shows each slot’s retained WAL, its share of the cap, its lag and whether it is invalidated.

Every path below is under /v1/orgs/{org}/instances/{id}. Every lookup is scoped to the organization in the URL; another organization’s instance is a 404.

CallNeeds
GET …/replicationinstances:read. Whether the engine and plan allow it (supported, allowed, reason), the limits, the replication users, publications, slots and the connection (host, port, database, sslmode, plugin)
GET …/replication/publicationsinstances:read
POST …/replication/publications {name, tables, schemas, operations}credentials:reveal in the instance’s team and a recent second factor (202, state pending)
DELETE …/replication/publications/{publication_name}credentials:reveal. Subscribers stop receiving its tables, and the replication users lose SELECT on tables no other publication publishes
GET …/replication/slotsinstances:read
DELETE …/replication/slots/{slot_name}instances:operate (202, drop_pending)
POST …/users {name, role: "replication"}credentials:reveal and a recent second factor
POST …/users/{user_name}/revealcredentials:reveal and a recent second factor; a one-time link that shows the password once
RequestAnswer
An engine other than PostgreSQL or TimescaleDB422 https://databasezy.com/problems/replication-unsupported
A plan without logical replication, or a limit already used up402
A secure-placement instance without an IP allow-list, or with 0.0.0.0/0 or ::/0 on it422 https://databasezy.com/problems/replication-requires-allow-list
No second factor in the last 15 minutes, where one is needed403 mfa_required

The full schema of every call is in the REST API reference.