Set up a CockroachDB target with TLS and upserts

Create the database, the user, and let the user connect and create tables:

CREATE DATABASE demo;
CREATE USER gluesync WITH PASSWORD '<a strong password>';
GRANT CONNECT, CREATE ON DATABASE demo TO gluesync;

For tables that already exist and that you created as another user, grant the table privileges instead:

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE demo.public.orders TO gluesync;

Add DROP if you will use Empty the table first before a snapshot. The agent empties the table with TRUNCATE, and CockroachDB requires DROP for it:

GRANT DROP ON TABLE demo.public.orders TO gluesync;

Check what the user holds:

SHOW GRANTS ON DATABASE demo;
SHOW GRANTS ON TABLE demo.public.orders;

In the Control Plane, select Create your first pipeline, or the + button of the pipeline list if you already have pipelines. The Agents setup wizard opens;

In 1. Setup source, choose the PostgreSQL CDC agent tile and fill Base settings: Host name / Address, Port, Database name, Username and Password of your source database. Select Test and continue. The source appears with a green check mark. Select Next;

In 2. Setup target(s), choose the CockroachDB tile and fill Base settings:

In Security, set Enable TLS and Trust server certificate as step 2 describes. Leave the Connection string, Min connections count and Max connections count as they are unless you must change them;

Select Test and continue. The target appears with a green check mark. Select Next, enter a pipeline name and select Finish.

In the pipeline, select Entities. The Add entities page opens with the Object browser. Expand the source schema public and select the table orders. The Field mapping dialog opens;

Under Target, the Target Schema is public. In Target Table type orders and choose Create table "orders". The dialog shows New table orders will be created on confirm. and the proposed target types;

Look at the Length column. For the text, jsonb and uuid columns it shows 0 in red, with the tooltip Required, and the banner Missing required field disables Confirm. Enter 1 in each of the three cells. The number is not used: the statement in the next step has no length for these types;

Select Confirm. The Create table orders dialog shows the Read-only SQL statement. Read it, then select Create table orders to run it on CockroachDB;

Back on the Add entities page, select Confirm and continue.

In the pipeline, find the entity. On Hold, select the start options button, the one next to the play button. The Snapshot (copy-only) dialog opens;

Keep Keep and merge under Data already in the target. It overwrites the rows that have the same key and keeps the others;

Under Speed select Custom. In the Writing column, set Writers during the copy to 1 and keep If a row with the same key is already there on Upsert. Step 7 explains why one writer is the safe choice for a first load;

Select Start snapshot.

Select the play button of the entity. The Sync changes (CDC) dialog opens. Under Start from choose Now, because the rows are already in the target. Keep If a row with the same key is already there on Upsert and select Start entity. The entity becomes Active;

On the source, insert, update and delete one row each:

INSERT INTO orders VALUES (300001, 'new', 7, 12.50, now(), 'cdc row', true, '{"k":1}', gen_random_uuid());
UPDATE orders SET val = -1, kind = 'cdc-upd' WHERE id = 5;
DELETE FROM orders WHERE id = 6;

After a few seconds, read the three keys on CockroachDB:

SELECT id, kind, val FROM public.orders WHERE id IN (5, 6, 300001) ORDER BY id;
SELECT count(*) FROM public.orders;

Max Write Batch Size and Max Delete Batch Size: both default to 1000 rows. In the Add entities page, select the gear of the entity, then Entity. The two properties are at the top of the tab.

Snapshot writing concurrency in Fine tuning: the number of writers during the snapshot, from 1 to 10. The start dialog shows the same setting as Writers during the copy;

Use connection pool, Min connections count and Max connections count of the target. The pool is on by default. The agent logs a warning when you turn it off, because the performance is lower.

The user cannot empty a table that it did not create. The user holds SELECT, INSERT, UPDATE and DELETE on a table that another user owns, and you start a snapshot with Empty the table first. The entity goes to Error and the Core Hub log shows ERROR: user gluesync does not have DROP privilege on relation orders. Core Hub keeps retrying the write. Fix: run GRANT DROP ON TABLE demo.public.orders TO gluesync;, or choose Keep and merge. After the grant, the snapshot with Empty the table first ran without an error;

A transaction retry error. With two writers and Keep and merge into a table without rows, the notification Serialization failure Error shows a message that starts with ERROR: restart transaction: TransactionRetryWithProtoRefreshError: TransactionRetryError: retry txn (RETRY_SERIALIZABLE - failed preemptive refresh due to conflicting locks. CockroachDB cancelled one of two transactions that wrote overlapping keys. The entity shows Error, and Core Hub starts the snapshot again by itself with a growing delay. In the test, the copy finished about two minutes after the first error, with all 200,000 rows. Fix: set Writers during the copy to 1, which copied the 200,000 rows in about 15 seconds without an error, or start the snapshot with Empty the table first, which ran with eight writers without an error;

A TLS or password error. Step 2 lists the TLS messages, and step 3 shows the message for a wrong password.