Set Up Physical Cluster Replication

On this page Carat arrow pointing down
Note:

This feature is in preview. This feature is subject to change. To share feedback and/or issues, contact Support.

New in v23.2: In this tutorial, you will set up physical cluster replication (PCR) between a primary cluster and standby cluster. The primary cluster is active, serving application traffic. The standby cluster is passive, accepting updates from the primary cluster. The replication stream will send changes from the primary to the standby.

The unit of replication is a virtual cluster, which is part of the underlying infrastructure in the primary and standby clusters.

In this tutorial, you will connect to:

  • 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

The high-level steps in this tutorial are:

  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

Note:

This is an enterprise-only feature. Request a 30-day trial license to try it out.

  • Two separate CockroachDB clusters (primary and standby) with a minimum of three nodes each, and each using the same CockroachDB v23.2 version.
  • All nodes in each cluster will need access to the Certificate Authority for the other cluster. Refer to Copy certificates.
  • An Enterprise license on the primary and standby clusters. You must use the system virtual cluster on the primary and standby clusters to enable your Enterprise license.
  • 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

Start the primary cluster

To enable PCR, it is necessary to start each node with the appropriate configuration profile set with the --config-profile flag. A configuration profile applies a custom configuration to the server at initialization time. When using PCR, the replication-source and replication-target configuration profiles are used to create a virtualized cluster with a system virtual cluster and an application virtual cluster.

The primary cluster requires the following value:

icon/buttons/copy
--config-profile replication-source

For example, a cockroach start command according to the prerequisite deployment guide:

icon/buttons/copy
cockroach start \
--certs-dir=certs \
--advertise-addr=<node1 address> \
--join=<node1 address>,<node2 address>,<node3 address> \
--cache=.25 \
--max-sql-memory=.25 \
--background \
--config-profile replication-source

Ensure that you follow the prerequisite deployment guide to initialize your cluster before continuing to set up PCR.

Connect to the primary cluster system virtual cluster

Connect to your primary cluster's system virtual cluster using cockroach sql.

  1. To connect to the system virtual cluster, pass the options=-ccluster=system parameter in the URL:

    icon/buttons/copy
    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.

    Note:

    You should only connect to the system virtual cluster for cluster administration. To work with databases, tables, or workloads, connect to the application virtual cluster.

  2. Add your cluster organization and Enterprise license to the cluster:

    icon/buttons/copy
    SET CLUSTER SETTING cluster.organization = 'your organization';
    
    icon/buttons/copy
    SET CLUSTER SETTING enterprise.license = 'your enterprise license';
    
  3. Confirm the status of your virtual cluster:

    icon/buttons/copy
    SHOW VIRTUAL CLUSTERS;
    

    The output will include the system interface and the application virtual cluster:

    id |    name     | data_state | service_mode
    ---+-------------+------------+---------------
    1  | system      | ready      | shared
    2  | template    | ready      | none
    3  | application | ready      | shared
    (3 rows)
    

    Because this is the primary cluster rather than the standby cluster, data_state of all rows is ready, rather than replicating or another status.

Create a replication user and password

The standby cluster connects to the primary cluster's system virtual cluster using an identity with the REPLICATION privilege. Connect to the primary cluster's system virtual cluster and create a user with a password:

  1. From the primary's system virtual cluster SQL shell, create a user and password:

    icon/buttons/copy
    CREATE USER {your username} WITH PASSWORD '{your password}';
    
  2. Grant the REPLICATION system privilege to your user:

    icon/buttons/copy
    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 application virtual cluster, open a new terminal window and use cockroach workload to run the workload.

    For example, to initiate the movr workload:

    icon/buttons/copy
    cockroach workload init movr "postgresql://root@{node_advertise_address}:{node_advertise_port}?options=-ccluster=application&sslmode=verify-full&sslrootcert=certs/ca.crt&sslcert=certs/client.root.crt&sslkey=certs/client.root.key"
    

    Replace {node_advertise_address} and {node_advertise_port} 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=application
    • 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:

    icon/buttons/copy
    cockroach workload run movr --duration=5m "postgresql://root@{node_advertise_address}:{node_advertise_port}?options=-ccluster=application&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 application virtual cluster, use the ccluster=application parameter:

    icon/buttons/copy
    cockroach sql --url \
    "postgresql://root@{node IP or hostname}:26257?options=-ccluster=application&sslmode=verify-full" \
    --certs-dir "certs"
    

    The prompt will include application when you are connected to the application virtual cluster.

  4. Create a user for your primary cluster's application virtual cluster:

    icon/buttons/copy
    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

Start the standby cluster

Similarly to the primary cluster, each node on the standby cluster must be started with the --config-profile flag set to replication-target. This creates a virtualized cluster with a system virtual cluster and an application virtual cluster, and sets up all the required configuration for starting a replication stream.

For example, a cockroach start command according to the prerequisite deployment guide:

icon/buttons/copy
cockroach start \
--certs-dir=certs \
--advertise-addr=<node1 address> \
--join=<node1 address>,<node2 address>,<node3 address> \
--cache=.25 \
--max-sql-memory=.25 \
--background \
--config-profile replication-target

Ensure that you follow the prerequisite deployment guide to initialize your cluster before continuing to set up PCR.

Connect to the standby cluster system virtual cluster

Connect to your standby cluster's system virtual cluster using cockroach sql.

  1. To connect to the system virtual cluster, pass the options=-ccluster=system parameter in the URL:

    icon/buttons/copy
    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.

  2. Add your cluster organization and Enterprise license to the cluster:

    icon/buttons/copy
    SET CLUSTER SETTING cluster.organization = 'your organization';
    
    icon/buttons/copy
    SET CLUSTER SETTING enterprise.license = 'your enterprise license';
    
  3. Confirm the status of your virtual cluster:

    icon/buttons/copy
    SHOW VIRTUAL CLUSTERS;
    

    The output will show the system interface, but no application virtual cluster:

    id |   name   | data_state | service_mode
    ---+----------+------------+---------------
    1  | system   | ready      | shared
    2  | template | ready      | none
    (2 rows)
    

    The configuration profile included at startup creates the template virtual cluster with the same set of capabilities per CockroachDB version. When you start a replication stream, you can specify the template VC with LIKE to ensure other virtual clusters on the standby cluster will work in the same way. Refer to Step 4: Start replication for syntax details.

Create a user for the standby cluster

If you would like to access the DB Console to observe your replication, you will need to create a user:

  1. Create a user:

    icon/buttons/copy
    CREATE USER {your username} WITH LOGIN PASSWORD {'your password'};
    
  2. To observe the replication activity, your user will need admin privileges:

    icon/buttons/copy
    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. Copy certificates

At this point, the primary and standby clusters are both running. The next step allows the standby cluster to connect to the primary cluster and begin ingesting its data. Depending on how you manage certificates, you must ensure that all nodes on the primary and the standby cluster have access to the certificate of the other cluster.

Warning:

It is important to carefully manage the exchange of CA certificates between clusters if you have generated self-signed certificates with cockroach cert as part of the prerequisite deployment tutorial.

To create certificates signed by an external certificate authority, refer to Create Security Certificates using OpenSSL.

For example, if you followed the Deploy CockroachDB prerequisite, you need to add the ca.crt from the primary cluster to the certs directory on all the nodes in the standby cluster.

  1. Name the ca.crt from the primary cluster to a new name on the standby cluster. For example, ca_primary.crt.
  2. Securely copy ca_primary.crt to the certs directory of the standby cluster nodes.

You need to add the ca.crt from the standby cluster to the certs directory on all the nodes in the primary cluster.

  1. Name the ca.crt from the standby cluster to a new name on the primary cluster. For example, ca_standby.crt.
  2. Securely copy ca_standby.crt to the certs directory of the primary cluster nodes.

Step 4. Start replication

The system virtual cluster in the standby cluster initiates and controls the replication stream by pulling from the primary cluster. In this section, you will connect to the primary from the standby to initiate the replication stream.

  1. From the standby cluster, use your connection string to the primary.

    The connection string 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.
    icon/buttons/copy
    CREATE VIRTUAL CLUSTER application LIKE template
    FROM REPLICATION OF application
    ON 'postgresql://{replication user}:{password}@{node IP or hostname}:26257?options=-ccluster=system&sslmode=verify-full&sslrootcert=certs/{primary cert}.crt';
    

    Including the LIKE template parameter ensures that the virtual cluster on the standby is created with the correct capabilities, which manage what the virtual cluster can do. LIKE will refer to a virtual cluster on the CockroachDB cluster you're running the statement from.

    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:

    icon/buttons/copy
    SHOW VIRTUAL CLUSTERS;
    

    The standby cluster will show the application virtual cluster is in a replicating state.

    id |        name        |     data_state     | service_mode
    ---+--------------------+--------------------+---------------
     1 | system             | ready              | shared
     2 | template           | ready              | none
     3 | application        | replicating        | none
    (3 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:

    icon/buttons/copy
    ALTER VIRTUAL CLUSTER application PAUSE REPLICATION;
    
    icon/buttons/copy
    ALTER VIRTUAL CLUSTER application RESUME REPLICATION;
    
    icon/buttons/copy
    SHOW VIRTUAL CLUSTER application WITH REPLICATION STATUS;
    
    icon/buttons/copy
    id |        name        |     data_state     | service_mode | source_tenant_name |                                                     source_cluster_uri                                              | replication_job_id |        replicated_time        |         retained_time         | cutover_time
    ---+--------------------+--------------------+--------------+--------------------+---------------------------------------------------------------------------------------------------------------------+--------------------+-------------------------------+-------------------------------+---------------
    3  | application        | replicating        | none         | application        | postgresql://{user}:{password}@{hostname}:26257?options=-ccluster%3Dsystem&sslmode=verify-full&sslrootcert=redacted | 899090689449132033 | 2023-09-11 22:29:35.085548+00 | 2023-09-11 16:51:43.612846+00 |     NULL
    (1 row)s
    

    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.

Connection reference

This table outlines the connection strings you will need for this setup tutorial.

For additional detail on the standard CockroachDB connection parameters, refer to Client Connection Parameters.

Cluster Interface Usage URL and Parameters
Primary System Set up a replication user and view running virtual clusters. Connect with cockroach sql. "postgresql://root@{node IP or hostname}:26257?options=-ccluster=system&sslmode=verify-full"

  • options=-ccluster=system
  • sslmode=verify-full
Use the --certs-dir flag to specify the path to your certificate.
Primary Application Add and run a workload with cockroach workload. "postgresql://root@{node IP or hostname}:{26257}?options=-ccluster=application&sslmode=verify-full&sslrootcert=certs/ca.crt&sslcert=certs/client.root.crt&sslkey=certs/client.root.key"

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=application
  • sslmode=verify-full
  • sslrootcert={path}/certs/ca.crt
  • sslcert={path}/certs/client.root.crt
  • sslkey={path}/certs/client.root.key
Standby System Manage the replication stream. Connect with cockroach sql. "postgresql://root@{node IP or hostname}:26257?options=-ccluster=system&sslmode=verify-full"

  • options=-ccluster=system
  • sslmode=verify-full
Use the --certs-dir flag to specify the path to your certificate.

What's next


Yes No
On this page

Yes No