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

# Set Up Logical Data Replication

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

<Note>
  **This feature is in <InternalLink path="cockroachdb-feature-availability">preview</InternalLink>** and subject to change. To share feedback and/or issues, contact [Support](https://support.cockroachlabs.com).
</Note>

Logical data replication is only supported in CockroachDB self-hosted clusters.

In this tutorial, you will set up <InternalLink path="logical-data-replication-overview">**logical data replication (LDR)**</InternalLink> streaming data from a source table to a destination table between two CockroachDB clusters. Both clusters are active and can serve traffic. You can apply the outlined steps to set up one of the following:

* *Unidirectional* LDR from a source table to a destination table (cluster A to cluster B) in one LDR job.
* *Bidirectional* LDR for the same table from cluster A to cluster B and from cluster B to cluster A. In a bidirectional setup, each cluster operates as both a source and a destination in separate LDR jobs.

New in v25.1: Create the new table on the destination cluster automatically and conduct a fast, offline initial scan with the <InternalLink path="create-logically-replicated">`CREATE LOGICALLY REPLICATED`</InternalLink> syntax. `CREATE LOGICALLY REPLICATED` accepts `unidirectional` or `bidirectional on` as an option in order to create one of the setups automatically. [Step 3](#step-3-start-ldr) outlines when to use the `CREATE LOGICALLY REPLICATED` or the `CREATE LOGICAL REPLICATION STREAM` syntax to start LDR.

In the following diagram, **LDR stream 1** creates a unidirectional LDR setup, introducing **LDR stream 2** extends the setup to bidirectional.

<img src="https://mintcdn.com/cockroachlabs/3M6JVFCnEJN7A4hn/images/v25.1/bidirectional-stream.svg?fit=max&auto=format&n=3M6JVFCnEJN7A4hn&q=85&s=b68b10c43b20ac6bd2cb3c4b5351f440" alt="Diagram showing bidirectional LDR from cluster A to B and back again from cluster B to A." style={{ width: "70%" }} width="512" height="261" data-path="images/v25.1/bidirectional-stream.svg" />

For more details on use cases, refer to the <InternalLink path="logical-data-replication-overview">Logical Data Replication Overview</InternalLink>.

## Tutorial overview

If you're setting up bidirectional LDR, both clusters will act as a source and a destination in the respective LDR jobs. The high-level steps for setting up bidirectional or unidirectional LDR:

1. Prepare the clusters with the required settings, users, and privileges according to the LDR setup.
2. Set up <InternalLink path="create-external-connection">external connection(s)</InternalLink> on the destination to hold the connection URI for the source.
3. Start LDR from the destination cluster with your required syntax and options.
4. Check the status of the LDR job in the <InternalLink path="ui-overview">DB Console</InternalLink>.

## Before you begin

You'll need:

* Two separate v25.1 CockroachDB self-hosted clusters with connectivity between every node in both clusters. That is, all nodes in cluster A must be able to contact each node in cluster B and vice versa. The SQL advertised address should be the cluster node advertise address so that the LDR job can plan node-to-node connections between clusters for maximum performance. The source and destination clusters must be configured with similar hardware profiles, number of nodes, and overall size. Significant discrepancies in the cluster configurations may result in degraded performance.
  * To set up each cluster, you can follow <InternalLink path="deploy-cockroachdb-on-premises">Deploy CockroachDB on Premises</InternalLink>.
  * The <InternalLink path="deploy-cockroachdb-on-premises">Deploy CockroachDB on Premises</InternalLink> tutorial creates a self-signed certificate for each self-hosted cluster. To create certificates signed by an external certificate authority, refer to <InternalLink path="create-security-certificates-openssl">Create Security Certificates using OpenSSL</InternalLink>.
  * All nodes in each cluster will need access to the Certificate Authority for the other cluster. Refer to [Step 2. Connect from the destination to the source](#step-2-connect-from-the-destination-to-the-source).
* LDR replicates at the table level, which means clusters can contain other tables that are not part of the LDR job. (For [`CREATE LOGICAL REPLICATION STREAM`](#create-logical-replication-stream) only): If both clusters are empty, create the tables that you need to replicate with **identical** schema definitions (excluding indexes) on both clusters. If one cluster already has an existing table that you'll replicate, ensure the other cluster's table definition matches. For more details on the supported schemas, refer to [Schema Validation](#schema-validation).

### Schema validation

Before you start LDR, you must ensure that all column names, types, constraints, and unique indexes on the destination table match with the source table.

You cannot use LDR on a table with a schema that contains:

* <InternalLink path="column-families">Column families</InternalLink>
* <InternalLink path="partial-indexes">Partial indexes</InternalLink> and <InternalLink path="hash-sharded-indexes">hash-sharded indexes</InternalLink>
* Indexes with a <InternalLink path="computed-columns">virtual computed column</InternalLink>
* Composite types in the <InternalLink path="primary-key">primary key</InternalLink>
* <InternalLink path="foreign-key">Foreign key</InternalLink> dependencies

Additionally, for the `CREATE LOGICALLY REPLCATED` syntax, you cannot use LDR on a table with a schema that contains:

* <InternalLink path="enum">User-defined types</InternalLink>

For more details, refer to the LDR <InternalLink path="logical-data-replication-overview#known-limitations">Known limitations</InternalLink>.

#### Unique secondary indexes

When the destination table includes unique <InternalLink path="schema-design-indexes">secondary indexes</InternalLink>, it can cause rows to enter the <InternalLink path="manage-logical-data-replication">*dead letter queue* (DLQ)</InternalLink>. The two clusters in LDR operate independently, so writes to one cluster can conflict with writes to the other.

If the application modifies the same row in both clusters, LDR resolves the conflict using <InternalLink path="manage-logical-data-replication#conflict-resolution">*last write wins* (LWW)</InternalLink> conflict resolution. <InternalLink path="unique">`UNIQUE` constraints</InternalLink> are validated locally in each cluster, therefore if a replicated write violates a `UNIQUE` constraint on the destination cluster (possibly because a conflicting write was already applied to the row) the replicating row will be applied to the DLQ.

For example, consider a table with a unique `name` column where the following operations occur in this order in a source and destination cluster running LDR:

On the **source cluster**:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
-- writes to the source table
INSERT INTO city (1, nyc); -- timestamp 1
UPDATE city SET name = 'philly' WHERE id = 1; -- timestamp 2
INSERT INTO city (100, nyc); -- timestamp 3
```

LDR replicates the write to the **destination cluster**:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
-- replicates to the destination table
INSERT INTO city (100, nyc); -- timestamp 4
```

*Timestamp 5:* <InternalLink path="architecture/glossary#range">Range</InternalLink> containing primary key `1` on the destination cluster is unavailable for a few minutes due to a <InternalLink path="cluster-setup-troubleshooting#network-partition">network partition</InternalLink>.

*Timestamp 6:* On the destination cluster, LDR attempts to replicate the row `(1, nyc)`, but it enters the retry queue for 1 minute due to the unavailable range. LDR adds `1, nyc` to the DLQ table after retrying and observing the `UNIQUE` constraint violation:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
-- writes to the DLQ
INSERT INTO city (1, nyc); -- timestamp 6
```

*Timestamp 7:* LDR continues to replicate writes:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
-- replicates to the destination table
INSERT INTO city (1, philly); -- timestamp 7
```

To prevent expected DLQ entries and allow LDR to be eventually consistent, we recommend:

* For **unidirectional** LDR, validate unique index constraints on the source cluster only.
* For **bidirectional** LDR, remove unique index constraints on both clusters.

## Step 1. Prepare the cluster

In this step you'll prepare the required settings and privileges for LDR.

<Note>
  If you are setting up bidirectional LDR, you **must** run this step on both clusters.
</Note>

1. Enter the SQL shell for **both** clusters in separate terminal windows:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   cockroach sql --url "postgresql://root@{node IP or hostname}:26257?sslmode=verify-full" --certs-dir=certs
   ```

2. Enable the `kv.rangefeed.enabled` <InternalLink path="cluster-settings">cluster setting</InternalLink> on the **source** cluster:

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

3. On the **destination**, create a user with the <InternalLink path="security-reference/authorization#supported-privileges">`REPLICATION` system privilege</InternalLink> who will start the LDR job:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   CREATE USER {your username} WITH PASSWORD '{your password}';
   ```

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   GRANT SYSTEM REPLICATION TO {your username};
   ```

   To change the password later, refer to <InternalLink path="alter-user">`ALTER USER`</InternalLink>.

## Step 2. Connect from the destination to the source

In this step, you'll set up <InternalLink path="create-external-connection">external connection(s)</InternalLink> to store the connection string for one or both clusters. Depending on how you manage certificates, you must ensure that all nodes between the clusters have access to the certificate of the other cluster.

You can use the `cockroach encode-uri` command to generate a connection string containing a cluster's certificate.

1. On the **source** cluster in a new terminal window, generate a connection string, by passing the replication user, node IP, and port, along with the directory to the source cluster's CA certificate:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   cockroach encode-uri {user}:{password}@{node IP}:26257 --ca-cert {path to CA certificate} --inline
   ```

   The connection string output contains the source cluster's certificate:

   ```
   {user}:{password}@{node IP}:26257?options=-ccluster%3Dsystem&sslinline=true&sslmode=verify-full&sslrootcert=-----BEGIN+CERTIFICATE-----{encoded certificate}-----END+CERTIFICATE-----%0A
   ```

2. In the SQL shell on the **destination** cluster, create an <InternalLink path="create-external-connection">external connection</InternalLink> using the source cluster's connection string. Prefix the `postgresql://` scheme to the connection string and replace <code>{'{source}'}</code> with your external connection name:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   CREATE EXTERNAL CONNECTION {source} AS 'postgresql://{user}:{password}@{node IP}:26257?options=-ccluster%3Dsystem&sslinline=true&sslmode=verify-full&sslrootcert=-----BEGIN+CERTIFICATE-----{encoded certificate}-----END+CERTIFICATE-----%0A';
   ```

### (Optional) Bidirectional: Create the connection for LDR stream 2

(Optional) For bidirectional LDR, you'll need to repeat creating the certificate output and the external connection for the opposite cluster. Both clusters will act as the source and destination. At this point, you've created an external connection for LDR stream 1, so cluster **A** (source) to **B** (destination). Now, create the same for LDR stream 2 cluster **B** (source) to cluster **A** (destination).

1. On cluster **B**, run:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   cockroach encode-uri {user}:{password}@{node IP}:26257 --ca-cert {path to CA certificate} --inline
   ```

   The connection string output contains the source cluster's certificate:

   ```
   {user}:{password}@{node IP}:26257?options=-ccluster%3Dsystem&sslinline=true&sslmode=verify-full&sslrootcert=-----BEGIN+CERTIFICATE-----{encoded certificate}-----END+CERTIFICATE-----%0A
   ```

2. On cluster **A**, create an <InternalLink path="create-external-connection">external connection</InternalLink> using cluster B's connection string (source in LDR stream 2). Prefix the `postgresql://` scheme to the connection string and replace <code>{'{source}'}</code> with your external connection name:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   CREATE EXTERNAL CONNECTION {source} AS 'postgresql://{user}:{password}@{node IP}:26257?options=-ccluster%3Dsystem&sslinline=true&sslmode=verify-full&sslrootcert=-----BEGIN+CERTIFICATE-----{encoded certificate}-----END+CERTIFICATE-----%0A';
   ```

## Step 3. Start LDR

In this step, you'll start the LDR stream(s) from the destination cluster. You can replicate one or multiple tables in a single LDR job. You cannot replicate system tables in LDR, which means that you must manually apply configurations and cluster settings, such as <InternalLink path="row-level-ttl">row-level TTL</InternalLink> and user permissions on the destination cluster.

### Syntax

LDR streams can be started using one of the following SQL statements, depending on your requirements:

* New in v25.1: <InternalLink path="create-logically-replicated">`CREATE LOGICALLY REPLICATED`</InternalLink>: Creates the new table on the destination cluster automatically, and conducts a fast, offline initial scan. `CREATE LOGICALLY REPLICATED` accepts `unidirectional` or `bidirectional on` as an option in order to create either one of the setups automatically. **The table cannot contain a <InternalLink path="enum">user-defined types</InternalLink>.** Follow [these steps](#create-logically-replicated) for setup instructions.
* <InternalLink path="create-logical-replication-stream">`CREATE LOGICAL REPLICATION STREAM`</InternalLink>: Starts the LDR stream after you've created the matching table on the destination cluster. **If the table contains user-defined types, you must use this syntax.** Allows for manual creation of unidirectional or bidirectional LDR. Follow [these steps](#create-logical-replication-stream) for setup instructions.

Also, for both SQL statements, note:

* It is necessary to use the <InternalLink path="sql-name-resolution">fully qualified</InternalLink> table name for the source table and destination table in the statement.
* There are some tradeoffs between enabling one table per LDR job versus multiple tables in one LDR job. Multiple tables in one LDR job can be easier to operate. For example, if you pause and resume the single job, LDR will stop and resume for all the tables. However, the most granular level observability will be at the job level. One table in one LDR job will allow for table-level observability.

#### `CREATE LOGICALLY REPLICATED`

Use `CREATE LOGICALLY REPLICATED` to create either a unidirectional or bidirectional LDR stream automatically:

* Unidirectional LDR: run the following from the **destination** cluster:

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  CREATE LOGICALLY REPLICATED TABLE {database.public.destination_table_name} FROM TABLE {database.public.source_table_name} ON 'external://source' WITH unidirectional;
  ```

* Bidirectional LDR: This statement will first create the LDR jobs for the first stream. You must run it from the **destination** cluster that does not contain the table. Once the offline initial scan completes, the reverse stream will be initialized so that the original destination cluster can send changes to the original source.

  Run the following from the **destination** cluster (i.e, the cluster that does not have the table currently):

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  CREATE LOGICALLY REPLICATED TABLE {database.public.destination_table_name} FROM TABLE {database.public.source_table_name} ON 'external://source' WITH bidirectional ON 'external://destination';
  ```

You can include multiple tables in the LDR stream for unidirectional or bidirectional setups. Ensure that the table name in the source table list and destination table list are in the same order so that the tables correctly map between the source and destination for replication:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE LOGICALLY REPLICATED TABLE ({database.public.destination_table_name_1}, {database.public.destination_table_name_2}) FROM TABLE ({database.public.source_table_name_1}, {database.public.source_table_name_2}) ON 'external://source' WITH bidirectional ON 'external://destination', label=track_job;
```

With the LDR streams created, move to [Step 4](#step-4-manage-and-monitor-the-ldr-jobs) to manage and monitor the jobs.

#### `CREATE LOGICAL REPLICATION STREAM`

Ensure you've created the table on the destination cluster with a matching schema definition to the source cluster table. From the **destination** cluster, start LDR. Use the <InternalLink path="sql-name-resolution">fully qualified</InternalLink> table name for the source and destination tables:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE LOGICAL REPLICATION STREAM FROM TABLE {database.public.source_table_name} ON 'external://{source_external_connection}' INTO TABLE {database.public.destination_table_name};
```

If you would like to add multiple tables to the LDR job, ensure that the table name in the source table list and destination table list are in the same order:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE LOGICAL REPLICATION STREAM FROM TABLES ({database.public.source_table_name_1},{database.public.source_table_name_2},...)  ON 'external://{source_external_connection}' INTO TABLES ({database.public.destination_table_name_1},{database.public.destination_table_name_2},...);
```

(**Optional**) At this point, you've set up one LDR stream from cluster A as the source to cluster B as the destination. To set up LDR streaming in the opposite direction using `CREATE LOGICAL REPLICATION STREAM`, run the statement again but cluster B will now be the source, and cluster A will be the destination.

## Step 4. Manage and monitor the LDR jobs

Once LDR has started, an LDR job will run on the destination cluster. You can <InternalLink path="pause-job">pause</InternalLink>, <InternalLink path="resume-job">resume</InternalLink>, or <InternalLink path="cancel-job">cancel</InternalLink> the LDR job with the job ID. Use <InternalLink path="show-logical-replication-jobs">`SHOW LOGICAL REPLICATION JOBS`</InternalLink> to display the LDR job IDs:

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

```
        job_id        | status  |          tables           | replicated_time
----------------------+---------+---------------------------+------------------
1012877040439033857   | running | {database.public.table}   | NULL
(1 row)
```

If you're setting up bidirectional LDR, both clusters will have a history retention job and an LDR job running.

### DB Console

You'll access the <InternalLink path="ui-overview">DB Console</InternalLink> and monitor the status and metrics for the created LDR jobs. Depending on which cluster you would like to view, follow the instructions for either the source or destination.

<Tip>
  You can use the <InternalLink path="ui-overview">DB Console</InternalLink>, the SQL shell, <InternalLink path="datadog#enable-metrics-collection">Metrics Export</InternalLink> with Prometheus and Datadog, and <InternalLink path="multi-dimensional-metrics">labels with some LDR metrics</InternalLink> to monitor the job.

  For a full reference on monitoring LDR, refer to <InternalLink path="logical-data-replication-monitoring">Logical Data Replication Monitoring</InternalLink>.
</Tip>

1. Access the DB Console at `http://{node IP or hostname}:8080` and enter your user's credentials.
2. On the source cluster, navigate to the <InternalLink path="ui-jobs-page">**Jobs** page</InternalLink> to view a list of all jobs. Use the job **Type** dropdown and select **Replication Producer**. This will display the history retention job. This will run while the LDR job is active to protect changes to the table from <InternalLink path="architecture/storage-layer#garbage-collection">garbage collection</InternalLink> until they have been replicated to the destination cluster.
3. On the destination cluster, use the job **Type** dropdown and select **Logical Replication Ingestion**. This page will display the logical replication stream job. There will be a progress bar in the **Status** column when LDR is replicating a table with existing data. This progress bar shows the status of the initial scan, which backfills the destination table with the existing data.
4. On the destination cluster, click on **Metrics** in the left-hand navigation menu. Use the **Dashboard** dropdown to select <InternalLink path="ui-logical-data-replication-dashboard">**Logical Data Replication**</InternalLink>. This page shows graphs for monitoring LDR.

## What's next

* <InternalLink path="manage-logical-data-replication">Manage Logical Data Replication</InternalLink>: Manage the DLQ and schema changes for the replicating tables.
* <InternalLink path="create-logical-replication-stream">`CREATE LOGICAL REPLICATION STREAM`</InternalLink>
* <InternalLink path="data-resilience">Data Resilience</InternalLink>