> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Transactional Logical Data Replication

> [!NOTE]
>
> Logical data replication is only supported in CockroachDB self-hosted clusters.

*Transactional mode* is a [**logical data replication (LDR)**](https://docs.cockroachlabs.com/docs/v26.4/logical-data-replication-overview.md) mode that applies each replicated source transaction as a single transaction on the destination cluster. Where the default `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](https://docs.cockroachlabs.com/docs/v26.4/foreign-key.md) constraints, which `immediate` mode 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](https://docs.cockroachlabs.com/docs/v26.4/unique.md) constraints. `immediate` mode 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 `immediate` mode, a destination reader can observe a partially applied source transaction.

Transactional mode has a higher base latency than `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:

| Behavior | `immediate` mode | `transactional` mode |
| - | - | - |
| Application of changes | Row by row, as changes arrive | Whole source transactions |
| Initial scan | Performs an initial scan of the source table | No initial scan. [Seed the destination tables](#seed-the-destination-tables) first, and set the required [`cursor`](https://docs.cockroachlabs.com/docs/v26.4/create-logical-replication-stream#options) option |
| Tables with foreign keys | Not supported | Supported with [`ON_CONSTRAINT_CONFLICT`](#set-the-conflict-policies) set |
| Tables with unique constraints | Supported. Rows apply in no particular order, so a write can hit a unique constraint violation and be retried | Supported with [`ON_CONSTRAINT_CONFLICT`](#set-the-conflict-policies) set. Dependent writes are ordered automatically |
| Replication lag | Lower | Higher base latency |

## 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](#seed-the-destination-tables).
* To replicate tables with unique constraints or foreign keys, you must set the `ON_CONSTRAINT_CONFLICT` option. To run [supported schema changes](#schema-changes-in-transactional-mode) while the stream runs, you must set the `ON_SCHEMA_CONFLICT` option. Refer to [Set the conflict policies](#set-the-conflict-policies).
* Transactional mode does not support replicating tables with [partial unique indexes](https://docs.cockroachlabs.com/docs/v26.4/partial-indexes.md), [`REGIONAL BY ROW`](https://docs.cockroachlabs.com/docs/v26.4/table-localities#regional-by-row-tables) tables, or tables with [`JSONB`](https://docs.cockroachlabs.com/docs/v26.4/jsonb) columns or [virtual computed columns](https://docs.cockroachlabs.com/docs/v26.4/computed-columns.md) in a primary key, unique, or foreign key constraint.
* For external connection setup between the clusters, refer to [Set Up Logical Data Replication](https://docs.cockroachlabs.com/docs/v26.4/set-up-logical-data-replication.md#step-2-connect-from-the-destination-to-the-source).

## Start LDR in transactional mode

Transactional LDR uses two statements:

* [`CREATE LOGICALLY REPLICATED`](https://docs.cockroachlabs.com/docs/v26.4/create-logically-replicated) creates 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 STREAM`](https://docs.cockroachlabs.com/docs/v26.4/create-logical-replication-stream) starts replication between tables that already exist and match on both clusters, from the timestamp set with the [`cursor`](https://docs.cockroachlabs.com/docs/v26.4/create-logical-replication-stream#options) option. Use it after [seeding with backup and restore](#seed-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](#extend-to-more-than-two-clusters).

### Seed the destination tables

From the destination cluster, which does not yet contain the tables, start a bidirectional transactional stream:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE LOGICALLY REPLICATED TABLES ({database.public.table_name_1}, {database.public.table_name_2}) FROM TABLES ({database.public.table_name_1}, {database.public.table_name_2}) ON 'external://{source_connection}' WITH bidirectional ON 'external://{destination_connection}', mode = 'transactional';
```

* `{database.public.table_name_...}`: The [fully qualified](https://docs.cockroachlabs.com/docs/v26.4/sql-name-resolution.md) table names. The source and destination lists map positionally, so keep them in the same order.
* `{source_connection}`: The [external connection](https://docs.cockroachlabs.com/docs/v26.4/create-external-connection.md) that points to the source cluster.
* `{destination_connection}`: The external connection that points to the destination cluster, used for the reverse stream.

The statement creates the tables on the destination, runs the offline initial scan, brings the tables online, and starts replication in both directions in transactional mode.

> [!CAUTION]
>
> Tables with [user-defined types](https://docs.cockroachlabs.com/docs/v26.4/enum.md) cannot use `CREATE LOGICALLY REPLICATED`. Seed those tables with [backup and restore](#seed-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 with `cursor = 'now'` to skip the backfill:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE LOGICAL REPLICATION STREAM FROM TABLES ({database.public.table_name_1}, {database.public.table_name_2}) ON 'external://{source_connection}' INTO TABLES ({database.public.table_name_1}, {database.public.table_name_2}) WITH mode = 'transactional', cursor = 'now', dlq = {schema}.{prefix}, on_constraint_conflict = 'dlq', on_schema_conflict = 'dlq';
```

* `dlq = {schema}.{prefix}`: Sets where the job creates its [dead letter queue (DLQ)](https://docs.cockroachlabs.com/docs/v26.4/manage-logical-data-replication.md#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](#set-the-conflict-policies) for the stream.

For a bidirectional pair, run the statement on both clusters, reversing the source and destination. Set `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 an `orders` table that holds a foreign key to a `customers` table:

1. On cluster A, a transaction inserts an order for customer 1.
2. Before that transaction replicates, a transaction on cluster B deletes customer 1.
3. Both transactions commit, because each is valid on its own cluster.
4. When the insert replicates to cluster B, it references a customer that no longer exists and violates the foreign key constraint.

The stream requires you to opt in to how conflicts are handled, so a conflict policy is set either way; choose the policy based on whether your workload can cause conflicts like this:

* `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](#schema-changes-in-transactional-mode) while the stream runs.

Choose `'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)](https://docs.cockroachlabs.com/docs/v26.4/manage-logical-data-replication.md#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](https://docs.cockroachlabs.com/docs/v26.4/manage-logical-data-replication.md#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](https://docs.cockroachlabs.com/docs/v26.4/resume-job.md) 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:

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  ALTER LOGICAL REPLICATION STREAM {job_id} SKIP;
  RESUME JOB {job_id};
  ```

  `SKIP` skips 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](https://docs.cockroachlabs.com/docs/v26.4/pause-job.md) the job and then use one of the following statements:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER LOGICAL REPLICATION STREAM {job_id} SET ON_CONSTRAINT_CONFLICT = {policy};
ALTER LOGICAL REPLICATION STREAM {job_id} SET ON_SCHEMA_CONFLICT = {policy};
ALTER LOGICAL REPLICATION STREAM {job_id} UNSET ON_CONSTRAINT_CONFLICT;
ALTER LOGICAL REPLICATION STREAM {job_id} UNSET ON_SCHEMA_CONFLICT;
```

* `{policy}`: Either `'pause'` or `'dlq'`. You can set a policy to `'dlq'` only if the job was created with a DLQ path.

After you modify the conflict policies, [resume](https://docs.cockroachlabs.com/docs/v26.4/resume-job.md) the job.

### Seed with backup and restore

If you can stop write traffic during setup, you can seed the destination with [backup and restore](https://docs.cockroachlabs.com/docs/v26.4/backup-and-restore-overview.md) instead of `CREATE LOGICALLY REPLICATED`:

1. Stop writes on the source cluster.
2. [Back up](https://docs.cockroachlabs.com/docs/v26.4/backup.md) the replicating tables or their database from the source cluster.
3. [Restore](https://docs.cockroachlabs.com/docs/v26.4/restore.md) the backup onto the destination cluster.
4. After the restore completes, start the stream in each direction following [Connect clusters whose tables already match](#connect-clusters-whose-tables-already-match).

Keep writes stopped until all restores complete. This guarantees that both sides are identical at the `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:

1. **Seed phase:** Pick one populated cluster as the seed. For every other cluster, run [`CREATE LOGICALLY REPLICATED`](#seed-the-destination-tables) on that cluster to seed it from the seed cluster with a bidirectional transactional stream.
2. **Mesh-fill phase:** For every remaining pair of seeded clusters, run [`CREATE LOGICAL REPLICATION STREAM`](#connect-clusters-whose-tables-already-match) in both directions, with `cursor` set 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](https://docs.cockroachlabs.com/docs/v26.4/manage-logical-data-replication.md#supported-schema-changes) available in both modes:

| Transactional mode schema change | Conditions |
| - | - |
| [`ALTER TABLE ... ADD COLUMN`](https://docs.cockroachlabs.com/docs/v26.4/alter-table#add-column) | Every LDR job that watches the table must set a [conflict policy](#set-the-conflict-policies). The new column cannot use `DEFAULT`, `ON UPDATE`, `SERIAL`, or `GENERATED ... AS IDENTITY`. To add a default value, run [`ALTER COLUMN ... SET DEFAULT`](https://docs.cockroachlabs.com/docs/v26.4/alter-table#alter-column) after the column is added. |
| [`ALTER TABLE ... ALTER COLUMN ... SET NOT NULL` / `DROP NOT NULL`](https://docs.cockroachlabs.com/docs/v26.4/alter-table#alter-column) | Every LDR job that watches the table must set a [conflict policy](#set-the-conflict-policies). |

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](https://docs.cockroachlabs.com/docs/v26.4/manage-logical-data-replication.md#coordinate-other-schema-changes) across the clusters.

### Example: add a column to a replicating table

The following example adds a `loyalty_tier` column to a `customers` table that is replicating bidirectionally in transactional mode between cluster A and cluster B.

1. Confirm that every LDR job watching the table sets `on_schema_conflict`. To view the LDR jobs and their options on each cluster, run:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   SHOW LOGICAL REPLICATION JOBS WITH DETAILS;
   ```

2. Add the column as nullable, without a default value, on cluster A:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   ALTER TABLE db.public.customers ADD COLUMN loyalty_tier STRING;
   ```

3. Run the same statement on cluster B. The clusters can run the statement in either order:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   ALTER TABLE db.public.customers ADD COLUMN loyalty_tier STRING;
   ```

   While only one cluster has the column, transactions that write to it are handled according to the `on_schema_conflict` policy of the jobs watching the table. Running the statement on both clusters promptly keeps this window short.

4. Verify that writes to the new column replicate. On cluster A, update a row:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   UPDATE db.public.customers SET loyalty_tier = 'gold' WHERE id = 1;
   ```

   On cluster B, read the row:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   SELECT id, loyalty_tier FROM db.public.customers WHERE id = 1;
   ```

   The command returns the following:

   ```
     id | loyalty_tier
   -----+---------------
      1 | gold
   (1 row)
   ```

5. Update your application to write to the new column.

6. (Optional) To add a `NOT NULL` constraint, first backfill the column on existing rows so that no `NULL` values remain, then run the following on both clusters:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   ALTER TABLE db.public.customers ALTER COLUMN loyalty_tier SET NOT NULL;
   ```

The workflow is the same for unidirectional and bidirectional LDR.

## Defer foreign key checks

> [!NOTE]
>
> **This feature is in [limited access](https://docs.cockroachlabs.com/docs/releases/cockroachdb-feature-availability.md)** and is only available to enrolled organizations. To enroll your organization, contact your Cockroach Labs account team. This feature is subject to change.

Transactional mode orders the rows within each replicated transaction so that foreign key dependencies are satisfied as the transaction applies. When rows within a transaction form a reference cycle, no valid order exists, so the transaction cannot apply. Reference cycles occur when:

* 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 `employees` rows whose `manager_id` values point at each other.

To replicate these transactions, defer the foreign key checks to the end of each applied transaction. Enable the cluster setting on the destination cluster:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SET CLUSTER SETTING logical_replication.consumer.deferred_fk_checks.enabled = true;
```

The setting defers foreign key checks only in the transactions that the LDR stream applies; it does not change how foreign key checks run for other transactions on the cluster. In a bidirectional setup, enable the setting on both clusters.

When deferred foreign key checks become generally available, transactional mode will defer foreign key checks unconditionally, and the cluster setting will no longer be required.

## Monitor transactional LDR

Monitor a transactional stream the same way as any LDR job. Refer to [Logical Data Replication Monitoring](https://docs.cockroachlabs.com/docs/v26.4/logical-data-replication-monitoring.md) 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](https://docs.cockroachlabs.com/docs/v26.4/manage-logical-data-replication.md#manage-entries-in-the-dlq).

## See also

* [Logical Data Replication Overview](https://docs.cockroachlabs.com/docs/v26.4/logical-data-replication-overview.md)
* [Set Up Logical Data Replication](https://docs.cockroachlabs.com/docs/v26.4/set-up-logical-data-replication.md)
* [Manage Logical Data Replication](https://docs.cockroachlabs.com/docs/v26.4/manage-logical-data-replication.md)
* [`CREATE LOGICALLY REPLICATED`](https://docs.cockroachlabs.com/docs/v26.4/create-logically-replicated)
* [`CREATE LOGICAL REPLICATION STREAM`](https://docs.cockroachlabs.com/docs/v26.4/create-logical-replication-stream)
