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.
Plans and limits
Section titled “Plans and limits”Logical replication is a plan feature. The Free plan does not include it. Plans that include it set three limits per instance:
| Limit | What it bounds |
|---|---|
| Replication slots | The slots your subscribers may keep. The platform drops slots beyond the limit. |
| Publications | The publications you may create. |
| WAL a slot may hold back | A 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.
Set it up
Section titled “Set it up”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.
-
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:revealpermission 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 theappdatabase. 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 andGET .../replicationsay that replication users cannot log in yet. -
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.
-
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 withzb,supabase,pg_,cnpgorrealtimeare 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. operationsdefaults to all four.- Platform schemas (
auth,storage,realtime,vaultand the others) are never published. - Give every published table a primary key, or set
alter table ... replica identity full. PostgreSQL refusesUPDATEandDELETEon a table that publishes them without a replica identity.
The answer is
202 Acceptedwith the statepending. The state then turnsapplied. If it turnsfailed,messagesays why, and the platform retries. Once it is applied, your replication users maySELECTexactly 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. - The name is 1 to 48 lowercase letters, digits and
-
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, useverify-fullwith 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.
- TLS is required: the gateway refuses plaintext, so set
Debezium on Kafka Connect
Section titled “Debezium on Kafka Connect”Save the connector as 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:
curl -sS -X POST -H "Content-Type: application/json" \ --data @databasezy-orders.json http://localhost:8083/connectorspublication.autocreate.modemust bedisabled. The replication user cannot create or change publications: you manage them in the portal or the API, and Debezium uses the one you name inpublication.name.- Leave
slot.drop.on.stopat its default,false. Withtrue, Debezium drops its slot with the SQL functionpg_drop_replication_slot, which replication users cannot run. Drop slots in the portal or with the API instead. slot.namemust 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 withzb,supabase,pg_,cnpg,realtimeor_: those names belong to the platform, and the API does not drop such slots for you.snapshot.modeinitialcopies the published tables once, then streams changes from the slot.database.sslmoderequireencrypts the connection. To also verify the server, setverify-fulland pointdatabase.sslrootcertat 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=fileandconfig.providers.file.class=org.apache.kafka.common.config.provider.FileConfigProviderin 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
Section titled “pg_recvlogical”pg_recvlogical is handy to check that changes arrive. Put the password in PGPASSWORD or ~/.pgpass, never on
the command line:
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:
pg_recvlogical -d "host=<host> port=<port> dbname=app user=<user> sslmode=require replication=database" --slot=myslot --drop-slotYou can also drop it in the portal or with the API (see the next section).
A PostgreSQL subscription
Section titled “A PostgreSQL subscription”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.
CREATE SUBSCRIPTION databasezy_orders CONNECTION 'host=<host> port=<port> dbname=app user=<user> password=<password> sslmode=require' PUBLICATION mypub;What a replication connection may run
Section titled “What a replication connection may run”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,SHOWfor a few settings (such asserver_version), andCREATE_REPLICATION_SLOT ... LOGICAL pgoutput,START_REPLICATION SLOT ... LOGICALwith pgoutput’s options (proto_versionandpublication_namesare required),DROP_REPLICATION_SLOTandALTER_REPLICATION_SLOT. - SQL: one statement per query, either
SELECT,BEGIN,COMMIT,ROLLBACK, orCOPY ... 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) andBASE_BACKUP; - slots of any output plugin other than
pgoutput, such astest_decodingorwal2json; - slots and publications whose names start with
zb,supabase,pg_,cnpg,realtimeor_, 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_changesandpg_drop_replication_slot. The database refuses them to replication users on every connection, not only on replication connections.
Slot lag and invalidation
Section titled “Slot lag and invalidation”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_statuslost). 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.nameandsnapshot.modeinitial. 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.
Limits and security
Section titled “Limits and security”- Only logical slots with the
pgoutputplugin are kept. The platform drops physical slots, slots of other output plugins (for examplewal2jsonortest_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/0or::/0before 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.
With the CLI
Section titled “With the CLI”zb instances users create inst_01jb... cdc --role replication # the subscriber's loginzb instances users reveal inst_01jb... cdc # shows its password oncezb replication status inst_01jb... # limits, users, host and portzb replication publications create inst_01jb... mypub --table public.orders --table public.customerszb replication publications create inst_01jb... sales --schema sales --operations insert,update,deletezb replication publications list inst_01jb...zb replication publications delete inst_01jb... mypubzb replication slots list inst_01jb...zb replication slots drop inst_01jb... debezium_orderspublications 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.
With the API
Section titled “With the API”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.
| Call | Needs |
|---|---|
GET …/replication | instances: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/publications | instances: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/slots | instances: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}/reveal | credentials:reveal and a recent second factor; a one-time link that shows the password once |
| Request | Answer |
|---|---|
| An engine other than PostgreSQL or TimescaleDB | 422 https://databasezy.com/problems/replication-unsupported |
| A plan without logical replication, or a limit already used up | 402 |
A secure-placement instance without an IP allow-list, or with 0.0.0.0/0 or ::/0 on it | 422 https://databasezy.com/problems/replication-requires-allow-list |
| No second factor in the last 15 minutes, where one is needed | 403 mfa_required |
The full schema of every call is in the REST API reference.