Logical data replication is only supported in CockroachDB self-hosted clusters.
immediate mode applies row changes independently as they arrive, transactional mode applies the writes from each source transaction together.
This page describes when to use transactional mode, how to start it, and how to manage conflicts, schema changes, and foreign key constraints on a transactional LDR stream.
When to use transactional mode
Use transactional mode when:- The replicating tables have foreign key constraints, which
immediatemode does not support. Transactional mode detects the dependencies between the writes in a transaction and orders them so that constraints are satisfied as the transaction applies. - The replicating tables have unique constraints.
immediatemode supports unique constraints, but it does not enforce an order between rows, so a write can hit a unique constraint violation and need to be retried. Transactional mode orders the writes automatically. - Your application requires atomicity across the rows a transaction touches. In
immediatemode, a destination reader can observe a partially applied source transaction.
immediate mode, because the destination buffers and applies each source transaction as a unit. If the replicating tables have no constraints that span rows and your application tolerates row-at-a-time application, immediate mode offers lower replication lag.
The following table summarizes the differences:
Before you begin
- LDR requires two CockroachDB self-hosted clusters that can reach each other over the network.
- Transactional mode streams do not run an initial scan, so the destination tables must already exist and contain the source data before the stream starts. Refer to Seed the destination tables.
- To replicate tables with unique constraints or foreign keys, you must set the
ON_CONSTRAINT_CONFLICToption. To run supported schema changes while the stream runs, you must set theON_SCHEMA_CONFLICToption. Refer to Set the conflict policies. - Transactional mode does not support replicating tables with partial unique indexes,
REGIONAL BY ROWtables, or tables withJSONBcolumns or virtual computed columns in a primary key, unique, or foreign key constraint. - For external connection setup between the clusters, refer to Set Up Logical Data Replication.
Start LDR in transactional mode
Transactional LDR uses two statements:CREATE LOGICALLY REPLICATEDcreates the destination tables, copies the source data with a fast offline scan, and starts replicating. For two clusters, this statement is usually all you need, because it seeds the tables and creates the stream in one step.CREATE LOGICAL REPLICATION STREAMstarts replication between tables that already exist and match on both clusters, from the timestamp set with thecursoroption. Use it after seeding with backup and restore, to resume from a timestamp after canceling a stream, or to connect the remaining pairs in a mesh of more than two clusters.
Seed the destination tables
From the destination cluster, which does not yet contain the tables, start a bidirectional transactional stream:{database.public.table_name_...}: The fully qualified table names. The source and destination lists map positionally, so keep them in the same order.{source_connection}: The external connection that points to the source cluster.{destination_connection}: The external connection that points to the destination cluster, used for the reverse stream.
Tables with user-defined types cannot use
CREATE LOGICALLY REPLICATED. Seed those tables with backup and restore instead, then connect the clusters with CREATE LOGICAL REPLICATION STREAM.Connect clusters whose tables already match
When the tables already exist and match on both clusters, start the stream from the destination cluster withcursor = 'now' to skip the backfill:
dlq = {schema}.{prefix}: Sets where the job creates its dead letter queue (DLQ) tables. You can also use the three-part form{database}.{schema}.{prefix}.on_constraint_conflict,on_schema_conflict: The conflict policies for the stream.
cursor to the same timestamp in both statements, rather than using cursor = 'now', so that both directions start from the same point in time and the clusters cannot diverge between the two starts.
Set the conflict policies
In transactional mode, whole transactions apply together, so conflicts that the workload itself does not cause are unexpected: a conflict usually indicates a problem with the setup, such as tables that did not match when the stream started. In a bidirectional stream, the workload can still cause conflicts, because each cluster accepts transactions that are valid on that cluster alone. For example, with anorders table that holds a foreign key to a customers table:
- On cluster A, a transaction inserts an order for customer 1.
- Before that transaction replicates, a transaction on cluster B deletes customer 1.
- Both transactions commit, because each is valid on its own cluster.
- When the insert replicates to cluster B, it references a customer that no longer exists and violates the foreign key constraint.
on_constraint_conflict = 'pause' | 'dlq': Sets how constraint violations, such as foreign key or unique constraint violations, are handled. You must set this option to replicate tables with unique constraints or foreign keys.on_schema_conflict = 'pause' | 'dlq': Sets how incompatible schemas are handled. You must set this option to run supported schema changes while the stream runs.
'pause' when you expect the workload to create no conflicts, so that any conflict stops replication for investigation. Choose 'dlq' when you expect occasional conflicts, so that replication continues while you handle the conflicting transactions from the DLQ tables.
With 'dlq' set, the job also requires the dlq option, which sets where the job creates its dead letter queue (DLQ) tables. Source transactions that fail to apply with a DLQ-eligible error are recorded in the DLQ tables and replication continues past them. To review and retry DLQ entries, refer to Manage entries in the DLQ.
Resolve a paused job
With a conflict policy set to'pause', the job applies every transaction before the conflicting transaction and then pauses. You can continue replication in one of the following ways:
- To retry the transaction, correct the destination so that the transaction can apply, and then resume the job. For example, insert the missing parent row for a foreign key violation, or run the schema change on the destination.
-
To continue without applying the transaction, skip it and then resume the job:
SKIPskips one transaction, and the destination does not receive the writes from that transaction. The clusters diverge for those rows until you reconcile them. If a later transaction also conflicts, the job pauses again.
Modify the conflict policies
To modify the conflict policies on a job, pause the job and then use one of the following statements:{policy}: Either'pause'or'dlq'. You can set a policy to'dlq'only if the job was created with a DLQ path.
Seed with backup and restore
If you can stop write traffic during setup, you can seed the destination with backup and restore instead ofCREATE LOGICALLY REPLICATED:
- Stop writes on the source cluster.
- Back up the replicating tables or their database from the source cluster.
- Restore the backup onto the destination cluster.
- After the restore completes, start the stream in each direction following Connect clusters whose tables already match.
cursor timestamp, so the streams start with nothing to reconcile.
Extend to more than two clusters
The workflow generalizes to a full mesh of N clusters, where every cluster replicates with every other cluster. First create and seed the tables on every cluster, then create bidirectional streams between every remaining pair:- Seed phase: Pick one populated cluster as the seed. For every other cluster, run
CREATE LOGICALLY REPLICATEDon that cluster to seed it from the seed cluster with a bidirectional transactional stream. - Mesh-fill phase: For every remaining pair of seeded clusters, run
CREATE LOGICAL REPLICATION STREAMin both directions, withcursorset to the same timestamp in both.
Schema changes in transactional mode
When LDR starts on a table, the job locks the schema. In transactional mode, the following schema changes are allowed on a replicating table while LDR runs, in addition to the allowlist schema changes available in both modes:
LDR does not replicate schema changes. You run the statement on each cluster, and
on_schema_conflict governs transactions that reference the new column while the schemas differ between the clusters. Other schema changes, including DROP COLUMN, still require you to coordinate the change across the clusters.
Example: add a column to a replicating table
The following example adds aloyalty_tier column to a customers table that is replicating bidirectionally in transactional mode between cluster A and cluster B.
-
Confirm that every LDR job watching the table sets
on_schema_conflict. To view the LDR jobs and their options on each cluster, run: -
Add the column as nullable, without a default value, on cluster A:
-
Run the same statement on cluster B. The clusters can run the statement in either order:
While only one cluster has the column, transactions that write to it are handled according to the
on_schema_conflictpolicy of the jobs watching the table. Running the statement on both clusters promptly keeps this window short. -
Verify that writes to the new column replicate. On cluster A, update a row:
On cluster B, read the row:The command returns the following:
- Update your application to write to the new column.
-
(Optional) To add a
NOT NULLconstraint, first backfill the column on existing rows so that noNULLvalues remain, then run the following on both clusters:
Defer foreign key checks
This feature is in limited access and is only available to enrolled organizations. To enroll your organization, contact your Cockroach Labs account team. This feature is subject to change.
- Two tables hold foreign keys that reference each other, and one transaction writes matching rows to both.
- A table holds a self-referential foreign key, and one transaction writes rows that reference each other. For example, a single transaction that inserts two
employeesrows whosemanager_idvalues point at each other.
Monitor transactional LDR
Monitor a transactional stream the same way as any LDR job. Refer to Logical Data Replication Monitoring for the SQL shell, DB Console, Prometheus, and Datadog workflows. With the'dlq' conflict policies set, replication continues past transactions that fail to apply, so monitor the DLQ tables and alert on growth. Refer to Manage entries in the DLQ.

