> ## 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.

# CREATE LOGICAL REPLICATION STREAM

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.

The `CREATE LOGICAL REPLICATION STREAM` statement starts <InternalLink path="logical-data-replication-overview">**logical data replication (LDR)**</InternalLink> that runs between a source and destination cluster in an active-active setup.

This page is a reference for the `CREATE LOGICAL REPLICATION STREAM` SQL statement, which includes information on its parameters and possible options. For a step-by-step guide to set up LDR, refer to the <InternalLink path="set-up-logical-data-replication">Set Up Logical Data Replication</InternalLink> page.

<Tip>
  If the table you're replicating does not contain <InternalLink path="enum">user-defined types</InternalLink>, we recommend using the <InternalLink path="create-logically-replicated">`CREATE LOGICALLY REPLICATED`</InternalLink> syntax that provides a fast, offline initial scan and automatic table setup on the destination cluster.
</Tip>

## Required privileges

`CREATE LOGICAL REPLICATION STREAM` requires one of the following privileges:

* The <InternalLink path="security-reference/authorization#admin-role">`admin` role</InternalLink>.
* The <InternalLink path="security-reference/authorization#privileges">`REPLICATION` system privilege</InternalLink>.

Use the <InternalLink path="grant">`GRANT SYSTEM`</InternalLink> statement:

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

## Synopsis

\<?xml version="1.0" encoding="UTF-8"?>

### Parameters

| Parameter                            | Description                                                                                                                                                                                                                                                                  |
| ------------------------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `db_object_name`                     | The fully qualified name of the table on the source or destination cluster. Refer to [Examples](#examples).                                                                                                                                                                  |
| `logical_replication_resources_list` | A list of the fully qualified table names on the source or destination cluster to include in the LDR stream. Refer to the [LDR with multiple tables](#multiple-tables) example.                                                                                              |
| `source_connection_string`           | The connection string to the source cluster. Use an <InternalLink path="create-external-connection">external connection</InternalLink> to store the source cluster's connection URI. To start LDR, you run `CREATE LOGICAL REPLICATION STREAM` from the destination cluster. |
| `logical_replication_options`        | Options to modify the behavior of the LDR stream.                                                                                                                                                                                                                            |

## Options

| Option    | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| --------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `cursor`  | Emits any changes after the specified timestamp. LDR will not perform an initial backfill with the `cursor` option, it will stream any changes after the specified timestamp. The LDR job will encounter an error if you specify a `cursor` timestamp that is before the configured <InternalLink path="architecture/storage-layer#garbage-collection">garbage collection</InternalLink> window for that table. **Warning:** Apply the `cursor` option carefully to LDR streams. Using a timestamp in error could cause data loss.                                |
| `discard` | (<InternalLink path="logical-data-replication-overview#use-cases">**Unidirectional LDR only**</InternalLink>) Ignore <InternalLink path="row-level-ttl">TTL deletes</InternalLink> in an LDR stream with `discard = ttl-deletes`. **Note**: To ignore row-level TTL deletes in an LDR stream, it is necessary to set the <InternalLink path="row-level-ttl#ttl-storage-parameters">`ttl_disable_changefeed_replication`</InternalLink> storage parameter on the source table. Refer to the [Ignore row-level TTL deletes](#ignore-row-level-ttl-deletes) example. |
| `label`   | Tracks LDR metrics at the job level. Add a user-specified string with `label`. Refer to <InternalLink path="logical-data-replication-monitoring#metrics-labels">Metrics labels</InternalLink>.                                                                                                                                                                                                                                                                                                                                                                    |

## Bidirectional LDR

*Bidirectional* LDR consists of two clusters with two LDR jobs running in opposite directions between the clusters. If you're setting up <InternalLink path="logical-data-replication-overview#use-cases">bidirectional LDR</InternalLink>, both clusters will act as a source and a destination in the respective LDR jobs.

LDR supports starting with two empty tables, or one non-empty table. LDR does **not** support starting with two non-empty tables. When you set up bidirectional LDR, if you're starting with one non-empty table, start the first LDR job from empty to non-empty table. Therefore, you would run `CREATE LOGICAL REPLICATION STREAM` from the destination cluster where the non-empty table exists.

## Examples

To start LDR, you must run the `CREATE LOGICAL REPLICATION STREAM` statement from the **destination** cluster. Use the <InternalLink path="sql-name-resolution#how-name-resolution-works">fully qualified table name(s)</InternalLink>. The following examples show statement usage with different options and use cases.

### Start an LDR stream

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.

#### Single table

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

#### Multiple tables

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

### Ignore row-level TTL deletes

If you would like to ignore <InternalLink path="row-level-ttl">row-level TTL</InternalLink> deletes in a **unidirectional** LDR stream, set the <InternalLink path="row-level-ttl#ttl-storage-parameters">`ttl_disable_changefeed_replication` storage parameter</InternalLink> on the table. On the **source** cluster, alter the table to set the table storage parameter:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER TABLE {table_name} SET (ttl_disable_changefeed_replication = 'true');
```

When you start LDR on the **destination cluster**, include the [`discard = ttl-deletes` option](#discard-ttl-deletes-option) in the statement:

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

## See also

* <InternalLink path="show-logical-replication-jobs">`SHOW LOGICAL REPLICATION JOBS`</InternalLink>