This feature is in preview and subject to change. To share feedback and/or issues, contact Support.
Logical data replication is only supported in CockroachDB self-hosted clusters.
New in v25.1:
The CREATE LOGICALLY REPLICATED statement starts logical data replication (LDR) on a table(s) that runs between a source and destination cluster in an active-active setup. CREATE LOGICALLY REPLICATED creates the new table on the destination cluster automatically and conducts a fast, offline initial scan. It accepts unidirectional or bidirectional on as an option to create either one of the setups automatically.
Once the offline initial scan completes, the new table will come online and is ready to serve queries. In a bidirectional setup, the second LDR stream will also initialize after the offline initial scan completes.
If the table to be replicated contains user-defined types or foreign key dependencies, you must use the CREATE LOGICAL REPLICATION STREAM statement instead. You can set up unidirectional or bidirectional LDR manually with CREATE LOGICAL REPLICATION STREAM.
This page is a reference for the CREATE LOGICALLY REPLICATED SQL statement, which includes information on its parameters and options. For a step-by-step guide to set up LDR, refer to the Set Up Logical Data Replication page.
Required privileges
CREATE LOGICALLY REPLICATED requires one of the following privileges:
- The
adminrole. - The
REPLICATIONsystem privilege.
Use the GRANT SYSTEM statement:
GRANT SYSTEM REPLICATION TO user;
Synopsis
Parameters
| Parameter | Description |
|---|---|
db_object_name |
The fully qualified name of the table on the source or destination cluster. Refer to 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 example. |
source_connection_string |
The connection string to the source cluster. Use an external connection to store the source cluster's connection URI. To start LDR, run CREATE LOGICALLY REPLICATED from the destination cluster. |
logical_replication_create_table_options |
Options to modify the behavior of the LDR stream. For a list, refer to Options. Note: bidirectional on or unidirectionalis a required option. For use cases of unidirectional and bidirectional LDR, refer to the Logical Data Replication Overview page. |
Options
| Option | Description |
|---|---|
bidirectional on / unidirectional |
(Required) Specifies whether the LDR stream will be unidirectional or bidirectional. With bidirectional on specified, LDR will set up two LDR streams between the clusters. Refer to the examples for unidirectional and bidirectional. |
label |
Tracks LDR metrics at the job level. Add a user-specified string with label. For more details, refer to Metrics labels. |
mode |
Determines how LDR replicates the data to the destination cluster. Possible values: immediate, validated. For more details, refer to LDR modes. |
LDR modes
Modes determine how LDR replicates the data to the destination cluster. There are two modes:
immediate(default): Attempts to replicate the changed row directly into the destination table, without re-running constraint validations. It does not support writing into tables with foreign key constraints.validated: Attempts to apply the write in a similar way to a user-run query, which would re-run all constraint validations relevant to the destination table(s). If the change violates foreign key dependencies, unique constraints, or other constraints, the row will be put in the dead letter queue (DLQ) instead. Like the SQL layer,validatedmode does not recognize deletion tombstones. As a result, an update to the same key from cluster A will successfully apply on cluster B, even if that key was deleted on cluster B before the LDR job streamed the cluster A update to the key.
Examples
CREATE LOGICALLY REPLICATED will automatically create the specified source tables on the destination cluster. For unidirectional and bidirectional, run the statement to start LDR on the destination cluster that does not contain the tables.
Unidirectional
From the destination cluster of the LDR stream, run:
CREATE LOGICALLY REPLICATED TABLE {database.public.destination_table_name} FROM TABLE {database.public.source_table_name} ON 'external://source' WITH unidirectional, mode=validated;
Include the following:
- Fully qualified destination table name.
- Fully qualified source table name.
- External connection for the source cluster. For instructions on creating the external connection for LDR, refer to Set Up Logical Data Replication.
unidirectionaloption.- Any other options.
For details on managing schema changes, conflicts, and jobs when LDR is running, refer to the Manage Logical Data Replication page.
Bidirectional
Both clusters will act as a source and destination in bidirectional LDR setups. To start the LDR jobs, you must run this statement from the destination cluster that does not contain the tables:
CREATE LOGICALLY REPLICATED TABLE {database.public.destination_table_name} FROM TABLE {database.public.source_table_name} ON 'external://source' WITH bidirectional ON 'external://destination';
Include the following:
- Fully qualified destination table name.
- Fully qualified source table name.
- External connection for the source cluster. For instructions on creating the external connection for LDR, refer to Set Up Logical Data Replication.
bidirectional onoption defining the external connection for the destination cluster.- Any other options.
For details on managing schema changes, conflicts, and jobs when LDR is running, refer to the Manage Logical Data Replication page.
Multiple tables
To include multiple tables in an LDR stream, add the fully qualified table names in a list format. Ensure that the table name in the source table list and destination table list are in the same order:
CREATE LOGICALLY REPLICATED TABLES ({database.public.destination_table_name_1}, {database.public.destination_table_name_2}) FROM TABLES ({database.public.source_table_name_1}, {database.public.source_table_name_2}) ON 'external://source' WITH bidirectional ON 'external://destination';
For details on managing schema changes, conflicts, and jobs when LDR is running, refer to the Manage Logical Data Replication page.
See more
Was this helpful?
