BUILDING Parts of this page describe what is not built yet, and each says so.
PostgreSQL change capture
PostgreSQL change capture is the source type postgres_cdc. It is meant to read every insert, update and delete
of the tables you name from a PostgreSQL database’s write-ahead log (WAL) and land them as a changelog in bronze. What you can do with it today is
declare it and prepare the database for it. Reading the write-ahead log is not built yet.
Sources says what works and how to declare one.
What the database needs
rl source prereqs writes eight steps for a PostgreSQL source, in the order you do them. Each names the check that
verifies it:
| Step | What it makes true |
|---|---|
tcp | The plane must be able to open a connection to the host on the declared port. This step has no SQL: it is for your network. |
wal_level | Postgres writes the detail change capture needs to its log. The setting takes a restart. |
replication_grant | The login may stream changes from the database. |
table_select | The login may read every table the source reads. |
heartbeat_table | A small table of one row that the login may update, so the database can keep moving its change log forward when nothing else is writing. The table is public.rl_heartbeat, and the publication carries it. No plane updates it yet. |
publication | The login may create the publication for the declared tables. It carries the heartbeat table too. |
replica_identity | Each declared table has a primary key or a replica identity, so a change can name its row. |
replication_slot | The database has a replication slot and a WAL sender free for this source. |
The role is the user the file names in credentials.user, and a source without one is refused in a sentence of its own,
because the SQL names that role. The publication and the slot are named rl_ and the source’s name with its hyphens as
underscores: rl_orders_db for orders-db. A table with no primary key is given
REPLICA IDENTITY FULL, because a change to a row with no key could not otherwise name the row. The SQL for the
whole of it is on Sources.
That identity lets a change name its row; it does not give the table a key, and a table with no key can hold two rows that are identical. Copying the rows a table already holds is not built yet, and the plan is that it will land them in batches. If a table has no key, a batch of rows that are identical to each other can be mistaken for a batch it has already landed, and then those rows are lost from the first copy. It takes two or more whole batches whose rows for the table are the same, so a table with only a few identical rows is not affected. A table with a key is not affected, and nor are changes after the first copy. The connection test is meant to warn about each declared table that has no primary key and no unique index over columns that cannot be null; the warning does not fail the test. No connection test can run yet, so nothing shows that warning yet. To clear it, add a primary key to the table, or a unique index over columns that cannot be null. If the table already holds identical rows, tell them apart first, for example with a new id column.
The heartbeat table is not one of the tables a source reads. When you plan a change that writes public.rl_heartbeat into a source’s read.tables, in capitals or in any mix of capital and small letters, it is refused: the steps make that table and the plane is meant to update it, so a source that read it would be meant to land the plane’s own updates as your data. A source that already has it keeps a warning until you take the name out. It can still be removed, and edited, unless the edit writes the name again in any spelling.
Flavours
The steps differ with the kind of database you run. Nothing detects the flavour of your database yet. You say which with
-flavour, as in rl source prereqs -flavour rds orders-db, and without it the steps are written for a
database you run yourself.
| Flavour | Turning on logical decoding | The replication role |
|---|---|---|
self_managed |
ALTER SYSTEM SET wal_level = 'logical', then restart PostgreSQL. |
CREATE ROLE … WITH LOGIN REPLICATION. |
rds |
In the DB parameter group, set rds.logical_replication to 1, then reboot the instance. |
CREATE ROLE … WITH LOGIN, then GRANT rds_replication to the role. |
aurora |
In the DB cluster parameter group, set rds.logical_replication to 1, then reboot the writer instance. |
CREATE ROLE … WITH LOGIN, then GRANT rds_replication to the role. |
cloud_sql |
In the instance’s database flags, set cloudsql.logical_decoding to on, then restart the instance. |
CREATE ROLE … WITH LOGIN, then ALTER ROLE … WITH REPLICATION. |
On RDS, Aurora and Cloud SQL the wal_level step is a setting of the service and not a statement, so the SQL for it
is a comment that says where to make the change and how to check it afterwards. In every flavour the setting takes effect only after a restart, and
the step says so. In none of them does a step carry a password.
The slot and its danger
Change capture reads the log through a replication slot, which is PostgreSQL’s bookmark for a reader of the log. PostgreSQL keeps all the log that a slot has still to read. A slot that nothing reads makes the database keep log without end, and when its disk fills the database stops. That is how PostgreSQL replication slots work, and it is the reason this section exists: a source that is removed, or that stops being read, must not leave its slot behind.
Change capture is designed to use one publication and one slot for each source, both named as above. The publication is made by the SQL on Sources, for the tables the file names and the heartbeat table. The slot is not: the SQL says the plane is meant to make it when it first starts, and that nothing does yet. Nothing creates a replication slot in your database yet, so no plane reports one and none is keeping log for you.
PostgreSQL 13 and later can cap how much log a slot may keep, with max_slot_wal_keep_size. Past the cap PostgreSQL
discards the log the slot needed and the slot can no longer be read. Whether to set it is a decision for the owner of the database,
and a cap is how a slot that stops being read is kept from filling the disk.
rl source get prints a source’s slot, its state and the log it keeps, as dashes until a plane reports them.
Nothing reports a slot, and neither the console’s page for a source nor rl source get finds a source once it is removed, so neither shows a slot’s name or the statement that drops it today.
The design is that a source removed with its slot still in your database shows the slot’s name and the statement that drops it, for you to run in your database.
rl source drop-slot makes no call and drops nothing: it says this, and to check first that the source is removed, because a paused source is meant to keep its slot.
Remove a source
Remove a source as you remove any file: delete sources/orders-db.yaml, then plan and apply the change. Today that
retires the source and removes the state the control plane recorded for it. It does not touch your database: the role, the
heartbeat table, the publication and the settings you made from the steps stay until you remove them yourself. The tables declared for the source are
files of their own, and they stay unless the same change deletes them.
Dropping the slot and the publication of a removed source is not built yet.
Not built yet
- Copying the rows a table already holds, before changes are followed, is not built yet.
- Landing changes in bronze as a changelog is not built yet.
- Watching how much log a slot keeps is not built yet.
- No plane updates the heartbeat table yet.