Set Up Logical Data Replication

In this tutorial, you will set up logical data replication (LDR) 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 create unidirectional LDR from a source table to a destination table (cluster A to cluster B) in one LDR job. Optionally, you can also create bidirectional LDR from cluster B's table to cluster A's table by starting a second LDR job. In a bidirectional setup, each cluster operates as both a source and a destination in separate LDR jobs.

For more details on use cases, refer to the Logical Data Replication Overview.

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 are:

1. Prepare the tables on each cluster with the prerequisites for starting LDR.
2. Set up an external connection on cluster B (which will be the destination cluster initially) to hold the connection URI for cluster A.
3. Start LDR from cluster B with your required modes.
4. (Optional) Run Steps 1 to 3 again with cluster B as the source and A as the destination, which starts LDR streaming from cluster B to A.
5. Check the status of the LDR job in the DB Console.

Before you begin

You'll need:

• Two separate v24.3 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.
  • To set up each cluster, you can follow Deploy CockroachDB on Premises.
  • The Deploy CockroachDB on Premises tutorial creates a self-signed certificate for each self-hosted cluster. To create certificates signed by an external certificate authority, refer to Create Security Certificates using OpenSSL.
  • 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.
• LDR replicates at the table level, which means clusters can contain other tables that are not part of the LDR job. 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.

To create bidirectional LDR, you can complete the optional step to start the second LDR job that sends writes from the table on cluster B to the table on cluster A.

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 the following:

• Column families
• Partial indexes and hash-sharded indexes
• Indexes with a virtual computed column
• Composite types in the primary key

For more details, refer to the LDR Known limitations.

When you run LDR in immediate mode, you cannot replicate a table with foreign key constraints. In validated mode, foreign key constraints must match. All constraints are enforced at the time of SQL/application write.

Step 1. Prepare the cluster

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

   cockroach sql --url "postgresql://root@{node IP or hostname}:26257?sslmode=verify-full" --certs-dir=certs
   

2. Enable the kv.rangefeed.enabled cluster setting on the source cluster:

   SET CLUSTER SETTING kv.rangefeed.enabled = true;
   

3. On the destination, create a user with the REPLICATION system privilege who will start the LDR job:

   CREATE USER {your username} WITH PASSWORD '{your password}';
   

   GRANT SYSTEM REPLICATION TO {your username};
   

   If you need to change the password later, refer to ALTER USER.

Step 2. Connect from the destination to the source

In this step, you'll set up an external connection from the destination cluster to the source cluster. 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:

   cockroach encode-uri {replication user}:{password}@{node IP}:26257 --ca-cert {path to CA certificate} --inline
   

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

   {replication 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 external connection using the source cluster's connection string. Prefix the postgresql:// scheme to the connection string and replace {source} with your external connection name:

   CREATE EXTERNAL CONNECTION {source} AS 'postgresql://{replication 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 job 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 row-level TTL and user permissions on the destination cluster.

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.

1. From the destination cluster, start LDR. Use the fully qualified table name for the source and destination tables:

   CREATE LOGICAL REPLICATION STREAM FROM TABLE {database.public.source_table_name} ON 'external://{source_external_connection}' INTO TABLE {database.public.destination_table_name};
   

   You can change the default mode using the WITH mode = validated syntax.

   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:

   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},...);
   

   Note:

   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.

   Once LDR has started, an LDR job will start on the destination cluster. You can pause, resume, or cancel the LDR job with the job ID. Use SHOW LOGICAL REPLICATION JOBS to display the LDR job IDs:

   SHOW LOGICAL REPLICATION JOBS;
   

           job_id        | status  |          targets          | 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.

2. Move on to Step 4 to set up a second LDR job. Or, once you have set up your required LDR jobs, refer to Step 5 to monitor the jobs in the DB Console.

Step 4. (Optional) Set up bidirectional LDR

At this point, you've set up one LDR job from cluster A as the source to cluster B as the destination. To set up LDR streaming in the opposite direction, complete Step 1, Step 2, and Step 3 again. Cluster B will now be the source, and cluster A will be the destination.

Step 5. Monitor the LDR jobs

In this step, you'll access the DB Console 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.

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 Jobs page 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 garbage collection 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 Logical Data Replication. This page shows graphs for monitoring LDR.

What's next

• Manage Logical Data Replication: Manage the DLQ and schema changes for the replicating tables.
• CREATE LOGICAL REPLICATION STREAM
• Data Resilience