PostgreSQL change capture

Who can do this

Connections are Admin only, and so is this one. You will also need someone who can change settings on the PostgreSQL database itself — usually a database administrator — because change capture has requirements the ordinary scheduled import does not.

Ronja can connect to your own PostgreSQL database in two different ways.

The ordinary PostgreSQL connector re-reads the tables you choose on a schedule, using a column like updated_at to work out what is new. It is simple and it needs nothing from your database but a read-only login.

PostgreSQL (change capture) is the other one. Instead of re-reading tables, it reads your database’s own record of what changed, so every insert, update and delete arrives — including deletes, which a schedule-based import can never see. The trade is that it asks more of your database, and it is an ongoing arrangement rather than a query that starts and finishes.

Pick change capture when you need deletes, when you need changes within minutes rather than at the next scheduled run, or when the tables are too large to re-read comfortably. Pick the ordinary connector for everything else.

What your database needs

Give this list to whoever administers the database. All of it is on the PostgreSQL side; none of it is a Ronja setting.

• A login of its own. Create a dedicated role for Ronja with the REPLICATION attribute and SELECT on the tables you plan to capture, and nothing else. On Amazon RDS and Aurora that is GRANT rds_replication TO <role>; on a self-hosted server it is ALTER ROLE <role> WITH REPLICATION.
• Change recording turned on. PostgreSQL has to be configured with wal_level = logical. On RDS and Aurora, set the parameter rds.logical_replication to 1 in the parameter group and reboot — the parameter does not take effect until the instance restarts. On a self-hosted server, set wal_level = logical in postgresql.conf and restart.
• The wal2json output plugin. Amazon RDS and Aurora ship it. Google Cloud SQL and Azure Database for PostgreSQL Flexible Server do not, and Ronja will refuse a connection to those with that reason.
• The primary, not a read replica. Change capture has to run against the writer. Point the connection at the primary endpoint.
• Room for one more reader. Ronja reserves one. If the database is already at its max_replication_slots limit, the connection is refused and the message tells you how many of how many are in use.

Ronja checks every one of these when you save the connection, and refuses with the specific thing that is wrong rather than a generic failure. One of the checks — confirming the wal2json plugin is really there — has to create and drop a temporary reservation, and PostgreSQL makes that wait for every transaction currently open on the database. On a busy production server that can take a few minutes. It is normal, it happens once, and if it does time out the message says so rather than claiming the database is unreachable.

What Ronja will not do to your database

This matters more than the feature list, because it is what makes the arrangement safe to agree to.

• Ronja makes no schema changes. It creates no publication, alters no table, and changes no table’s replica identity. The only object it creates is its own reservation for reading changes, and the only other thing it does is read.
• Ronja never writes. Change capture only reads. Nothing flows back into your database.
• New tables do not join by themselves. Ronja captures exactly the tables you pick. A table created on the source later is simply not captured until someone picks it.

Connect it

Connections start in a conversation. There is no form to hunt for: Add Connection opens a chat with Ronja, she works out which connector you mean, and she hands you the right form.

1. Go to Connections and click Add Connection. Ronja opens a conversation and asks what you would like to connect.
2. Say that you want to capture changes from your own PostgreSQL as they happen — “keep our orders database continuously up to date, including deletes” is enough. Saying change capture (or CDC) is what separates this from the ordinary PostgreSQL connector, which she will otherwise reasonably offer.
3. She confirms she means PostgreSQL (change capture) and puts a link in her reply, rendered as a button. Click it and the credential form opens.
4. Fill in host, port, database, username and password, and the SSL mode. If the database is only reachable through a bastion host, fill in the SSH fields too.
5. Save. Ronja runs the checks above and tells you plainly if something is missing.
6. Pick the schema, then pick the tables you want captured.
7. Set how often Ronja collects what has accumulated, on the How often changes are collected card. The minimum is once per hour. The more often Ronja collects, the less your database has to keep for it.

You do not have to start from the Connections page. Asking in any conversation — “capture changes from our orders database” — reaches the same button and the same form.

Which tables you can pick

Two rules. The table list greys out anything that breaks either one, so you can see it before you try, and both are enforced again when you save. A selection that breaks either is refused and names the tables to fix; nothing is written until it passes.

• Every captured table needs a primary key. Without one, a delete arrives with nothing to identify the row it removed, so Ronja could not tell you what the table currently contains. Ronja will not fix this by changing your table, so a table with no primary key cannot be captured.
• A partitioned table is picked one partition at a time. PostgreSQL records changes against the individual partition, never against the parent. Pick the parent and it would copy once and then never receive another change — healthy-looking and silently frozen. Pick the partitions instead; their names are what both the first copy and the ongoing changes use.

The schema is fixed once capture has started

You can add and remove tables at any time. You cannot point an established connection at a different schema — two schemas’ rows would end up mixed in one history with no way to tell them apart. Create a second connection instead.

What you get

When capture starts, Ronja first copies each selected table in full, then keeps up with changes from that moment on. The copy and the stream land in the same place: an Integration table in the feature, which records every change as a new row rather than overwriting the old one.

That means the table is a history of changes, not a tidy mirror — and when you query it, Ronja reconstructs the current state for you. You do not need to know how. What is worth knowing is what the history does and does not contain:

• Each row carries which kind of change it was, when it happened at the source, and its position in the database’s change record.
• The same change can appear twice. Ronja is built to never lose a change, and the price of that guarantee is an occasional repeat after an interruption. Reconstructing the current state absorbs it.
• An update that does not touch a very large text or binary column leaves that column out of the change rather than blanking it. Again, the reconstruction handles it.

TRUNCATE is not captured

Emptying a table with TRUNCATE produces no change record at all, so Ronja does not see it and the captured table still shows the rows that were there. The same applies to Ronja’s managed databases. If you truncate a captured table, tell Ronja to re-copy it, or the two will disagree until rows are inserted again.

Pausing, and what it costs

Pausing for more than 24 hours means a full re-copy

While capture is running, your database keeps the changes Ronja has not collected yet. Pausing stops the collecting but not the keeping, so Ronja releases its reservation on the source 24 hours after you pause.

Come back inside those 24 hours and capture continues from exactly where it stopped. Come back after them and resuming copies every selected table again from the beginning — which, on a large database, is not a small thing.

The connection page says which side of that line you are on, and gives you the deadline. Deleting the connection works the same way: restore it within 24 hours and capture continues; after that, restoring re-copies.

The same 24 hours is why a short pause is free. If you are pausing to get through a maintenance window, you are fine. If you are pausing indefinitely, expect the re-copy.

When Ronja cannot release the reservation

This is the one failure worth understanding in advance, because it happens on your server rather than in Ronja.

Ronja’s reservation tells PostgreSQL to keep the change records Ronja has not read yet. That is exactly what makes capture lossless. It also means that a reservation nobody is reading keeps accumulating, and on a database where that goes unnoticed it will eventually fill the disk.

So whenever capture stops for good — you delete the connection, or leave it paused past the window — Ronja releases the reservation. If it cannot reach your database at that moment, it keeps trying for 30 days, and tells the organization’s admins the first time it fails and again if it gives up. The message names the exact statement to run.

Two things can leave Ronja unable to do it at all:

• The database stays unreachable. Past 30 days Ronja stops trying.
• The login lost its privilege. Releasing the reservation needs REPLICATION just as creating it did. If the role was narrowed after the connection was set up, Ronja cannot release it however long it tries — and this one is easy to miss, because nothing else about the connection looks wrong.

In either case, run this on the database, as a role that does hold REPLICATION:

-- find it: Ronja's reservations are all named ronja_cdc_…
SELECT slot_name, active FROM pg_replication_slots;

SELECT pg_drop_replication_slot('<the ronja_cdc_… name>');

Worth a standing check

If your organization ever deletes a Ronja connection while the database is down, or retires a database Ronja was capturing from, check pg_replication_slots on that server afterwards. One leftover entry costs nothing for a while and then costs a disk.

What your admins get told

Everyone with the Admin role gets these in the notification bell, and each one carries the exact statement to run where there is one to run. There are four, and the one most organizations meet is the third.

• “Ronja could not release a replication slot on your database” — the first time a release fails. Ronja is still retrying, and will go on retrying for 30 days. You get this once per connection, not once per attempt.
• “A replication slot on your database was left behind” — the 30 days are up and Ronja has stopped retrying. From here it is yours to drop.
• “A failing connection is still holding WAL on your database” — the connection is not deleted and not paused, it is simply failing every collection, and its reservation goes on holding change records on your database the whole time. Ronja will not release a reservation for a connection that still exists, and that is deliberate: releasing it is irreversible and costs a full re-copy of every selected table, while most of what causes a run of failures — a rotated password, a role that was narrowed, a failover, a maintenance window — is fixed in minutes. So the choice is handed to you, with both ways out: repair what the collection error names and capture resumes from where it stopped, or delete the connection (or run the statement in the message) and accept the re-copy.
• “A failing connection has lost its replication slot” — the connection is failing every collection and its reservation is gone from your database. Nothing is accumulating there any more and there is nothing to run, but the next collection that succeeds copies every selected table from the beginning. It is worth knowing before the bill arrives rather than after.

When something goes wrong

Ronja gives the connection an amber Degraded badge — in the Connections list and on the connection’s own page — and says why. Capture is still running; Degraded means it is running with something wrong that will cost you if it is left. The conditions worth acting on:

• Close to the retention limit. Your database is holding more change records for Ronja than it is comfortable with, and will eventually discard them. Collect more often, or raise the database’s retention.
• The reservation is gone or was discarded. The changes Ronja needed are no longer there, so the next collection re-copies every selected table. This also happens after a failover on RDS or Aurora, which do not carry reservations across — it is not a fault, but it is a full re-copy, so it is worth knowing it happened.
• Capturing nothing. The reservation is holding change records on your database but the connection has no tables. Pick tables for it, or delete it.
• A first copy that could not finish. A table too large to copy in a single run is marked as such on its own row rather than retrying forever. Re-select that table to try again, or split the work.

The checks Ronja runs when you connect are a snapshot of that moment. Nothing re-runs them later, so if a DBA changes wal_level or narrows the role six months from now, you find out as a failing collection — with a message naming the parameter or the privilege, rather than a driver error.

Related

• Connect a data source — the ordinary connection flow, and which sources sync on a schedule versus being queried in place.
• Manage secrets — how the database credentials are stored.
• Managed databases — the Ronja-hosted PostgreSQL you can build a system on, which can be mirrored into a feature the same way.