Set Up Physical Cluster Replication

• The system virtual cluster for administration tasks in both clusters, and starting the replication stream from the standby cluster.
• The application virtual cluster on the primary cluster to work with databases, tables, workloads, and so on.

​

Overview

1. Create and start the primary cluster.
2. Configure and create a user on the primary cluster.
3. Create and start the standby cluster.
4. Configure and create a user on the standby cluster.
5. Securely copy certificates.
6. Start the replication stream from the standby cluster.

​

Before you begin

• Two separate CockroachDB clusters (primary and standby) with a minimum of three nodes each, and each using the same CockroachDB v24.2 version. The standby cluster should be the same version or one version ahead of the primary cluster. The primary and standby 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 Deploy CockroachDB on Premises. When you initialize the cluster with the cockroach init command, you must pass the --virtualized or --virtualized-empty flag. Refer to the cluster creation steps for the primary cluster and for the standby cluster for details.
  • 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 Manage the cluster certificates.
• The primary and standby clusters must have the same region topology. For example, replicating a multi-region primary cluster to a single-region standby cluster is not supported. Mismatching regions between a multi-region primary and standby cluster is also not supported.

​

Step 1. Create the primary cluster

​

Initialize the primary cluster

cockroach init --certs-dir=certs --host={address of any node on --join list} --virtualized

​

Connect to the primary cluster system virtual cluster

1. To connect to the system virtual cluster, pass the options=-ccluster=system parameter in the URL:
   cockroach sql --url \
   "postgresql://root@{node IP or hostname}:26257?options=-ccluster=system&sslmode=verify-full" \
   --certs-dir "certs"
   
   The prompt will include system when you are connected to the system virtual cluster.

1. Set the kv.rangefeed.enabled cluster setting to true. The replication job connects to a long-lived request, a rangefeed, which pushes changes as they happen:
   SET CLUSTER SETTING kv.rangefeed.enabled = true;
   
2. Confirm the status of your virtual cluster:
   SHOW VIRTUAL CLUSTERS;
   
   The output will include the system virtual cluster and the main virtual cluster:
     id |  name  | data_state | service_mode
   -----+--------+------------+---------------
      1 | system | ready      | shared
      3 | main   | ready      | shared
   (2 rows)
   
   Because this is the primary cluster rather than the standby cluster, the data_state of all rows is ready, rather than replicating or another status.

​

Create a replication user and password

1. From the primary’s system virtual cluster SQL shell, create a user and password:
   CREATE USER {your username} WITH PASSWORD '{your password}';
   
2. Grant the REPLICATION system privilege to your user:
   GRANT SYSTEM REPLICATION TO {your username};
   
   If you need to change the password later, refer to ALTER USER.

​

Connect to the primary virtual cluster (optional)

1. If you would like to run a sample workload on the primary’s virtual cluster, open a new terminal window and use cockroach workload to run the workload. For example, to initiate the movr workload:
   cockroach workload init movr "postgresql://root@{node_advertise_address}:{node_advertise_port}?options=-ccluster=main&sslmode=verify-full&sslrootcert=certs/ca.crt&sslcert=certs/client.root.crt&sslkey=certs/client.root.key"
   
   Replace and with a node’s --advertise-addr IP address or hostname and port. The cockroach workload command does not support connection or security flags like other cockroach commands. Instead, you must use a connection string at the end of the command. As a result, for the example in this tutorial, you will need:
   • options=-ccluster=main
   • sslmode=verify-full
   • sslrootcert={path}/certs/ca.crt: the path to the CA certificate.
   • sslcert={path}/certs/client.root.crt: the path to the client certificate.
   • sslkey={path}/certs/client.root.key: the path to the client private key.
   For additional detail on the standard CockroachDB connection parameters, refer to Client Connection Parameters.
2. Run the movr workload for a set duration using the same connection string:
   cockroach workload run movr --duration=5m "postgresql://root@{node_advertise_address}:{node_advertise_port}?options=-ccluster=main&sslmode=verify-full&sslrootcert=certs/ca.crt&sslcert=certs/client.root.crt&sslkey=certs/client.root.key"
   
3. To connect to the primary cluster’s virtual cluster, use the options=-ccluster={virtual_cluster_name} parameter:
   cockroach sql --url \
   "postgresql://root@{node IP or hostname}:26257?options=-ccluster=main&sslmode=verify-full" \
   --certs-dir "certs"
   
   The prompt will include main when you are connected to the virtual cluster.
4. Create a user for your primary cluster’s main virtual cluster:
   CREATE USER {your username} WITH PASSWORD '{your password}';
   
5. You can connect to the DB Console with this user to observe activity on the primary cluster. Open a web browser at https://{node IP or hostname}:8080/ and enter your credentials.

​

Step 2. Create the standby cluster

​

Initialize the standby cluster

cockroach init --certs-dir=certs --host={address of any node on --join list} --virtualized-empty

​

Connect to the standby cluster system virtual cluster

1. To connect to the system virtual cluster, pass the options=-ccluster=system parameter in the URL:
   cockroach sql --url \
   "postgresql://root@{node IP or hostname}:{26257}?options=-ccluster=system&sslmode=verify-full" \
   --certs-dir "certs"
   
2. Add your cluster organization and Enterprise license to the cluster:
   SET CLUSTER SETTING cluster.organization = 'your organization';
   
   SET CLUSTER SETTING enterprise.license = 'your enterprise license';
   
3. Set the kv.rangefeed.enabled cluster setting to true. The replication job connects to a long-lived request, a rangefeed, which pushes changes as they happen:
   SET CLUSTER SETTING kv.rangefeed.enabled = true;
   
4. Confirm the status of your virtual cluster:
   SHOW VIRTUAL CLUSTERS;
   
   The output will show the system virtual cluster, but no main virtual cluster:
   id |   name   | data_state | service_mode
   ---+----------+------------+---------------
   1  | system   | ready      | shared
   (1 rows)
   

​

Create a user for the standby cluster

1. Create a user:
   CREATE USER {your username} WITH LOGIN PASSWORD {'your password'};
   
2. To observe the replication activity, your user will need admin privileges:
   GRANT admin TO {your username};
   
   Open the DB Console in your web browser: https://{node IP or hostname}:8080/, where you will be prompted for these credentials. Refer to Physical Cluster Replication Monitoring for more detail on tracking relevant metrics for your replication stream.

​

Step 3. Manage the cluster certificates

cockroach encode-uri {replication user}:{password}@{node IP or hostname}:26257 --ca-cert certs/ca.crt --inline

postgresql://{replication user}:{password}@{node IP or hostname}:26257/defaultdb?options=-ccluster%3Dsystem&sslinline=true&sslmode=verify-full&sslrootcert=-----BEGIN+CERTIFICATE-----{encoded_cert}-----END+CERTIFICATE-----%0A

​

Step 4. Start replication

1. From the standby cluster, use your connection string to the primary: If you generated the connection string using cockroach encode-uri:
   CREATE VIRTUAL CLUSTER main
   FROM REPLICATION OF main
   ON 'postgresql://{replication user}:{password}@{node IP or hostname}:{26257}/defaultdb?options=-ccluster%3Dsystem&sslinline=true&sslmode=verify-full&sslrootcert=-----BEGIN+CERTIFICATE-----{encoded_cert}-----END+CERTIFICATE-----%0A';
   
   Otherwise, pass the connection string that contains:
   • The replication user and password that you created for the primary cluster.
   • The node IP address or hostname of one node from the primary cluster.
   • The path to the primary node’s certificate on the standby cluster.
   CREATE VIRTUAL CLUSTER main
   FROM REPLICATION OF main
   ON 'postgresql://{replication user}:{password}@{node IP or hostname}:{26257}?options=-ccluster=system&sslmode=verify-full&sslrootcert=certs/{primary cert}.crt';
   
   Once the standby cluster has made a connection to the primary cluster, the standby will pull the topology of the primary cluster and will distribute the replication work across all nodes in the primary and standby.
2. To view all virtual clusters on the standby, run:
   SHOW VIRTUAL CLUSTERS;
   
   The standby cluster will show the main virtual cluster is in a replicating state.
     id |  name  | data_state  | service_mode
   -----+--------+-------------+---------------
      1 | system | ready       | shared
      3 | main   | replicating | none
   (2 rows)
   
   The standby cluster’s virtual cluster is offline while the replication stream is running. To bring it online, you must explicitly start its service after cutover.
3. To manage the replication stream, you can pause and resume the replication stream as well as show the current details for the job:
   ALTER VIRTUAL CLUSTER main PAUSE REPLICATION;
   
   ALTER VIRTUAL CLUSTER main RESUME REPLICATION;
   
   SHOW VIRTUAL CLUSTER main WITH REPLICATION STATUS;
   
   id | name | source_tenant_name |              source_cluster_uri                        |         retained_time         |    replicated_time     | replication_lag | cutover_time |   status
   ---+------+--------------------+--------------------------------------------------------+-------------------------------+------------------------+-----------------+--------------+--------------
   3  | main | main               | postgresql://user@{node IP or hostname}:{26257}?redacted | 2024-04-17 20:14:31.952783+00 | 2024-04-17 20:18:50+00 | 00:00:08.738176 |         NULL | replicating
   (1 row)
   
   With the replication stream running, you can monitor the job via the DB Console, SQL shell, or Prometheus. You can also verify data is correct on the standby cluster at a specific point in time. For more detail, refer to Physical Cluster Replication Monitoring.

​

Set up PCR from an existing cluster

• An existing primary cluster. If you need to upgrade your existing cluster, refer to Upgrade page.
• A standby cluster that is at the same version or one version ahead of the primary cluster. To set up the cluster, you can follow Deploy CockroachDB on Premises. When you initialize the cluster with the cockroach init command, you must pass the --virtualized-empty flag. For details, refer to the cluster creation steps for the standby cluster.
• Review the remaining prerequisites at the start of this page, which also apply to running PCR from an existing cluster.

​

Example

1. To configure the standby cluster ready for PCR, follow the steps outlined in Connect to the standby cluster system virtual cluster that include enabling the necessary cluster settings.
2. Create a user on both clusters and grant the SYSTEM REPLICATION privilege to the created user on each cluster:
   CREATE USER {your username} WITH PASSWORD '{your password}';
   
   GRANT SYSTEM REPLICATION TO {your username};
   
3. View the virtual clusters on both clusters:
   SHOW VIRTUAL CLUSTERS;
   
   You will find that both clusters only have the system virtual cluster:
     id |  name  | data_state | service_mode
   -----+--------+------------+---------------
   1    | system | ready      | shared
   (1 row)
   
4. To create the replication job, you will need a connection string for the primary cluster containing its CA certificate. For steps to generate a connection string with cockroach encode-uri, refer to Step 3. Manage the cluster certificates.
5. If you would like to run a test workload on your existing primary cluster, you can use cockroach workload like the following:
   cockroach workload init movr 'postgresql://root@{node IP or hostname}:{26257}?options=-ccluster=main&sslmode=verify-full&sslrootcert=certs/ca.crt&sslcert=certs/client.root.crt&sslkey=certs/client.root.key'
   
6. To start the replication, you will need to replicate system from the existing primary cluster in order to create a new virtual cluster on the standby cluster (main in this example). On the standby cluster, run:
   CREATE VIRTUAL CLUSTER main FROM REPLICATION OF system ON 'postgresql://{replication user}:{password}@{node IP or hostname}:26257/defaultdb?options=-ccluster%3Dsystem&sslinline=true&sslmode=verify-full&sslrootcert=-----BEGIN+CERTIFICATE-----{encoded_cert}-----END+CERTIFICATE-----%0A';
   
   This statement includes:
   • main: The name of the virtual cluster to create on the standby cluster.
   • system: The system virtual cluster on the existing primary cluster.
   • The connection string for the existing primary cluster.
7. View the virtual clusters on the standby cluster:
   SHOW VIRTUAL CLUSTERS;
   
   The output shows the newly created main virtual cluster and that the replication job is initializing:
     id |  name  |        data_state        | service_mode
   -----+--------+--------------------------+---------------
   1    | system | ready                    | shared
   3    | main   | initializing replication | none
   (2 rows)
   

​

Connection reference

​

What’s next

• Physical Cluster Replication Monitoring
• Cut Over from a Primary Cluster to a Standby Cluster
• CREATE VIRTUAL CLUSTER
• ALTER VIRTUAL CLUSTER
• DROP VIRTUAL CLUSTER
• SHOW VIRTUAL CLUSTER