> ## 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. # Set Up Physical Cluster Replication 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 {children} ; }; Physical cluster replication is supported in CockroachDB self-hosted clusters and is in limited access on CockroachDB Cloud. 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*, continuously receiving updates from the primary cluster. The replication stream replicates 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 * You need two separate CockroachDB clusters (primary and standby), each with a minimum of three nodes. 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](#initialize-the-primary-cluster) and for the [standby cluster](#initialize-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 cluster certificates](#step-3-manage-cluster-certificates-and-generate-connection-strings). * 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 can have different region topologies. However, behavior for features that rely on multi-region primitives, such as Region by Row and Region by Table, may be affected. For more information, refer to Multi-region behavior and best practices. To set up PCR from an existing CockroachDB cluster, which will serve as the primary cluster, refer to [Set up PCR from an existing cluster](#set-up-pcr-from-an-existing-cluster). ## Step 1. Create the primary cluster ### Initialize the primary cluster To enable PCR, it is necessary to initialize the CockroachDB cluster with the appropriate flag to create the appropriate virtual clusters on the primary and standby cluster. When initializing the [primary cluster](#initialize-the-primary-cluster), you pass the `--virtualized` flag to create a *virtualized cluster* with a `system` virtual cluster and a `main` virtual cluster. When initializing the [standby cluster](#initialize-the-standby-cluster), you pass the `--virtualized-empty` flag to create a virtualized standby cluster that contains a `system` virtual cluster. For example, the `cockroach init` command to initialize the primary cluster (according to the prerequisite deployment guide): ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} cockroach init --certs-dir=certs --host={address of any node on --join list} --virtualized ``` 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: ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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. You should only connect to the system virtual cluster for cluster administration. To work with databases, tables, or workloads, connect to a virtual cluster. 2. 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: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SET CLUSTER SETTING kv.rangefeed.enabled = true; ``` 3. Confirm the status of your virtual cluster: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW VIRTUAL CLUSTERS; ``` The output will include the `system` virtual cluster and the `main` virtual cluster: ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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 user with replication privileges 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: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE USER {your username} WITH PASSWORD '{your password}'; ``` If you need to change the password later, refer to `ALTER USER`. 2. Grant the `REPLICATION` system privilege to your user: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} GRANT SYSTEM REPLICATION TO {your username}; ``` ### 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: ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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 {'{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=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: ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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: ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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 Similarly to the primary cluster, you must initialize the standby cluster with the `--virtualized-empty` flag. This creates a *virtualized cluster* with a system virtual cluster. For example, the `cockroach init` command to initialize the standby cluster (according to the prerequisite deployment guide): ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} cockroach init --certs-dir=certs --host={address of any node on --join list} --virtualized-empty ``` 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: ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SET CLUSTER SETTING cluster.organization = 'your organization'; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SET CLUSTER SETTING kv.rangefeed.enabled = true; ``` 4. Confirm the status of your virtual cluster: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW VIRTUAL CLUSTERS; ``` The output will show the `system` virtual cluster, but no `main` virtual cluster: ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} id | name | data_state | service_mode ---+----------+------------+--------------- 1 | system | ready | shared (1 rows) ``` ### Create a user with replication privileges on 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: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE USER {your username} WITH LOGIN PASSWORD {'your password'}; ``` 2. To observe the replication activity, your user will need `admin` privileges: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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 cluster certificates and generate connection strings 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. At this point, the primary and standby clusters are both running. The next step creates a connection URI with the certifications needed to connect the two clusters. In most cases, we recommend ensuring that all nodes on the primary cluster have access to the certificate of the standby cluster, and vice versa. This ensures that PCR is able to parallelize the work. Use the `cockroach encode-uri` command to generate a connection string containing a cluster's certificate for any PCR statements that require a connection string. Pass the replication user, IP and port, along with the path to the certificate for the **primary cluster**, into the `encode-uri` command: ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} cockroach encode-uri {replication user}:{password}@{node IP or hostname}:26257 --ca-cert {path to certs directory}/certs/ca.crt --inline ``` The connection string output contains the primary cluster's certificate: ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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 ``` Copy the output ready for [Step 4](#step-4-start-replication), which requires the connection string to the primary cluster. ## Step 4. Start replication The system virtual cluster in the standby cluster initializes 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: If you generated the connection string using [`cockroach encode-uri`](#step-3-manage-cluster-certificates-and-generate-connection-strings): ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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](#create-a-user-with-replication-privileges). * 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. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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. You can also start a PCR stream that includes a read-only standby virtual cluster that allows you to read data on the standby cluster. For more details, refer to the `CREATE VIRTUAL CLUSTER` page. 2. To view all virtual clusters on the standby, run: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW VIRTUAL CLUSTERS; ``` The standby cluster will show the `main` virtual cluster is in a `replicating` state. ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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 failover. 3. To manage the replication stream, you can pause and resume the replication stream as well as show the current details for the job: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER VIRTUAL CLUSTER main PAUSE REPLICATION; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER VIRTUAL CLUSTER main RESUME REPLICATION; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW VIRTUAL CLUSTER main WITH REPLICATION STATUS; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} id | name | source_tenant_name | source_cluster_uri | retained_time | replicated_time | replication_lag | failover_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. You cannot pause a PCR job for longer than 24 hours. PCR jobs paused for longer than 24 hours fail and cannot be recovered. ## Set up PCR from an existing cluster You can set up PCR replication from an existing CockroachDB cluster that does not have cluster virtualization enabled. However, the standby cluster must have cluster virtualization enabled. In the PCR setup, the existing cluster is the primary cluster. When you start PCR with an existing primary cluster that does **not** have cluster virtualization enabled, you will not be able to *fail back* to the original primary cluster from the promoted, original standby. For more details on the failback process when you have started PCR with a non-virtualized primary, refer to Fail back after replicating from an existing cluster. Before you begin, you will need: * An existing primary cluster that is running v24.3. If you need to upgrade your existing cluster, refer to Upgrade CockroachDB self-hosted. * A v24.3 standby 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](#initialize-the-standby-cluster). * Review the remaining [prerequisites](#before-you-begin) 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](#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: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE USER {your username} WITH PASSWORD '{your password}'; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} GRANT SYSTEM REPLICATION TO {your username}; ``` 3. View the virtual clusters on **both** clusters: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW VIRTUAL CLUSTERS; ``` You will find that both clusters only have the `system` virtual cluster: ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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 cluster certificates and generate connection strings](#step-3-manage-cluster-certificates-and-generate-connection-strings). 5. If you would like to run a test workload on your existing **primary cluster**, you can use `cockroach workload` like the following: ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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**: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW VIRTUAL CLUSTERS; ``` The output shows the newly created `main` virtual cluster and that the replication job is `initializing`: ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} id | name | data_state | service_mode -----+--------+--------------------------+--------------- 1 | system | ready | shared 3 | main | initializing replication | none (2 rows) ``` At this point, your replication stream will be running. To *fail over* to the standby cluster, follow the instructions on the Fail Over from a Primary Cluster to a Standby Cluster page. For details on how to *fail back* after replicating a non-virtualized cluster, refer to Fail back after replicating from an existing cluster. ## 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. You can use an external connection to define a name for connections using the `postgresql://` scheme. The table uses `main` as an example name for the virtual cluster that contains user table data in the primary and standby clusters. | Cluster | Virtual Cluster | 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"`Use the `--certs-dir` flag to specify the path to your certificate. | | Primary | Main | Add and run a workload with `cockroach workload`. | `"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"` 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: | | Standby | System | Manage the replication stream. Connect with `cockroach sql`. | `"postgresql://root@{node IP or hostname}:{26257}?options=-ccluster=system&sslmode=verify-full"`Use the `--certs-dir` flag to specify the path to your certificate. | | Standby/Primary | System | Connect to the other cluster. | `"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"`

Generate the connection string with [`cockroach encode-uri`](#step-3-manage-cluster-certificates-and-generate-connection-strings). Use the generated connection string in: | | Standby | Read only | Run read queries on the standby's replicating virtual cluster | `"postgresql://root@{node IP or hostname}:{26257}?options=-ccluster=main-readonly&sslmode=verify-full"`Use the `--certs-dir` flag to specify the path to your certificate. | ## What's next * Physical Cluster Replication Monitoring * Fail Over from a Primary Cluster to a Standby Cluster * `CREATE VIRTUAL CLUSTER` * `ALTER VIRTUAL CLUSTER` * `DROP VIRTUAL CLUSTER` * `SHOW VIRTUAL CLUSTER`