PostgreSQL change capture
Ronja connects to your own PostgreSQL database in one of two ways:
- The ordinary PostgreSQL connector re-reads the tables you choose on a schedule, using a column such as
updated_atto find what is new. It needs nothing from your database but a read-only login. - PostgreSQL (change capture) reads your database’s own record of what changed, so every insert, update and delete arrives, including deletes, which a scheduled import can’t see. 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, or when the tables are too large to re-read comfortably. It doesn’t make data arrive sooner: changes are collected on the connection’s schedule, at most once an hour, as for every connection. Pick the ordinary connector for everything else.
What your database needs
Section titled “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
REPLICATIONattribute andSELECTon the tables you plan to capture, and nothing else. On Amazon RDS and Aurora that isGRANT rds_replication TO <role>; on a self-hosted server it isALTER ROLE <role> WITH REPLICATION. - Change recording turned on. PostgreSQL has to be configured with
wal_level = logical. On RDS and Aurora, set the parameterrds.logical_replicationto1in the parameter group and reboot — the parameter does not take effect until the instance restarts. On a self-hosted server, setwal_level = logicalinpostgresql.confand restart. - The
wal2jsonoutput 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_slotslimit, the connection is refused and the message tells you how many of how many are in use.
Ronja checks each of these when you save the connection, and a refusal names what is wrong. The check for wal2json waits for every transaction open on the database, so on a busy server it can take a few minutes, once; if it times out, the message says so.
What Ronja will not do to your database
Section titled “What Ronja will not do to your database”- 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
Section titled “Connect it”Connections start in a conversation: Add Connection opens a chat, Ronja works out which connector you mean, and she hands you the right form.
- Go to Connections and click Add Connection. Ronja opens a conversation and asks what you would like to connect.
- Say you want to capture changes from your own PostgreSQL — “capture every change to our orders database, including deletes” is enough — or ask Ronja to connect your PostgreSQL and pick Change capture from the ways she offers.
- 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.
- 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.
- Save. Ronja runs the checks above and tells you plainly if something is missing.
- Pick the schema, then pick the tables you want captured.
- 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
Section titled “Which tables you can pick”The table list greys out any table that breaks one of these rules, and a selection that breaks one is refused on save, naming the tables to fix.
- Every captured table needs a primary key. Without one, a delete can’t say which row it removed. Ronja won’t change your table to add one, so a table with no primary key can’t be captured.
- A partitioned table is picked one partition at a time. PostgreSQL records changes against each partition, never the parent, so a parent would copy once and then never change. Pick the partitions.
What you get
Section titled “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.
The table is a history of changes, and when you query it Ronja reconstructs the current state for you:
- 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 after an interruption. The reconstruction absorbs it.
- An update that doesn’t touch a very large text or binary column leaves that column out rather than blanking it. The reconstruction handles that too.
Pausing, and what it costs
Section titled “Pausing, and what it costs”The connection page shows the deadline. Deleting the connection works the same way: restore it within 24 hours and capture continues; after that, restoring re-copies.
When Ronja cannot release the reservation
Section titled “When Ronja cannot release the reservation”Ronja’s reservation tells PostgreSQL to keep the change records Ronja hasn’t read yet. A reservation nobody reads keeps growing, and left unnoticed it eventually fills your server’s disk.
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, as creating it did. If the role was narrowed after setup, Ronja can’t release it however long it tries, and 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>');What your admins get told
Section titled “What your admins get told”Every Admin gets these in the notification bell, each with the exact statement to run where there is one.
- “Ronja could not release a replication slot on your database”: the first time a release fails, once per connection. Ronja keeps retrying for 30 days.
- “A replication slot on your database was left behind”: the 30 days are up and Ronja has stopped. It is yours to drop.
- “A failing connection is still holding WAL on your database”: the connection isn’t deleted or paused but fails every collection, and its reservation keeps holding change records. Ronja won’t release a reservation for a connection that still exists, since releasing it means a full re-copy. Repair what the collection error names, and capture resumes 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 fails every collection and its reservation is gone. Nothing accumulates and there is nothing to run, but the next collection that succeeds copies every selected table from the beginning.
When something goes wrong
Section titled “When something goes wrong”An amber Degraded badge, in the Connections list and on the connection’s page, means capture is running with something wrong that will cost you if left. The badge says which:
- 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 next collection re-copies every selected table. A failover on RDS or Aurora does this too, since they don’t carry reservations across.
- 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 one run is marked on its row rather than retried forever. To try again while the connection isn’t syncing, deselect it, save, then select it again (saving an unchanged selection does nothing). Or split the work.
The checks run only when you connect. If someone later changes wal_level or narrows the role, a collection fails with a message naming the parameter or the privilege.
Related
Section titled “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.