> ## Documentation Index
> Fetch the complete documentation index at: https://www.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Set Up Physical Cluster Replication

export const InlineImage = ({src, alt = "", height = "1.6em"}) => {
  return <img noZoom src={src} alt={alt} style={{
    display: "inline",
    verticalAlign: "start",
    height: height,
    margin: "0"
  }} />;
};

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 <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

export const version = "v23.2";

<Note>
  **This feature is in <InternalLink path="cockroachdb-feature-availability">preview</InternalLink>** and subject to change. To share feedback and/or issues, contact [Support](https://support.cockroachlabs.com).
</Note>

Physical cluster replication is supported in CockroachDB self-hosted clusters.
<InlineImage alt="Megaphone" src="/images/common/icon-megaphone.png" /> New in v23.2: In this tutorial, you will set up <InternalLink path="physical-cluster-replication-overview">**physical cluster replication (PCR)**</InternalLink> 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 <InternalLink path="cluster-virtualization-overview">virtual cluster</InternalLink>, 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

* Two separate CockroachDB clusters (primary and standby) with a minimum of three nodes each, and each using the same CockroachDB v23.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 <InternalLink path="deploy-cockroachdb-on-premises">Deploy CockroachDB on Premises</InternalLink>. When you start each node in your cluster with the `cockroach start` command, you **must** pass the `--config-profile` flag with a `replication` value. Refer to cluster creation steps for the [primary cluster](#start-the-primary-cluster) and for the [standby cluster](#start-the-standby-cluster) for details.
  * The <InternalLink path="deploy-cockroachdb-on-premises">Deploy CockroachDB on Premises</InternalLink> tutorial creates a self-signed certificate for each self-hosted cluster. To create certificates signed by an external certificate authority, refer to <InternalLink path="create-security-certificates-openssl">Create Security Certificates using OpenSSL</InternalLink>.
* All nodes in each cluster will need access to the Certificate Authority for the other cluster. Refer to [Copy certificates](#step-3-copy-certificates).
* The primary and standby clusters **must have the same <InternalLink path="topology-patterns">region topology</InternalLink>**. 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:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
--config-profile replication-source
```

For example, a `cockroach start` command according to the <InternalLink path="deploy-cockroachdb-on-premises#step-3-start-nodes">prerequisite deployment guide</InternalLink>:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 <InternalLink path="deploy-cockroachdb-on-premises#step-4-initialize-the-cluster">prerequisite deployment guide</InternalLink> 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 <InternalLink path="cockroach-sql">`cockroach sql`</InternalLink>.

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.

<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.
</Note>

2. 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` interface and the `application` virtual cluster:

   ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   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:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   CREATE USER {your username} WITH PASSWORD '{your password}';
   ```
2. Grant the <InternalLink path="security-reference/authorization#supported-privileges">`REPLICATION` system privilege</InternalLink> to your user:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   GRANT SYSTEM REPLICATION TO {your username};
   ```

   If you need to change the password later, refer to <InternalLink path="alter-user">`ALTER USER`</InternalLink>.

### 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 <InternalLink path="cockroach-workload">`cockroach workload`</InternalLink> to run the workload.

   For example, to initiate the <InternalLink path="movr">`movr`</InternalLink> workload:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   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 <code>{'{node_advertise_address}'}</code> and <code>{'{node_advertise_port}'}</code> with a node's <InternalLink path="cockroach-start#flags-advert-addr">`--advertise-addr`</InternalLink> IP address or hostname and port.

   The `cockroach workload` command does not support connection or security flags like other <InternalLink path="cockroach-commands">`cockroach` commands</InternalLink>. Instead, you must use a <InternalLink path="connection-parameters">connection string</InternalLink> 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 <InternalLink path="connection-parameters#connect-using-a-url">Client Connection Parameters</InternalLink>.
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=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:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   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:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   CREATE USER {your username} WITH PASSWORD '{your password}';
   ```
5. You can connect to the <InternalLink path="ui-overview">DB Console</InternalLink> 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 <InternalLink path="deploy-cockroachdb-on-premises#step-3-start-nodes">prerequisite deployment guide</InternalLink>:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 <InternalLink path="deploy-cockroachdb-on-premises#step-4-initialize-the-cluster">prerequisite deployment guide</InternalLink> 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 <InternalLink path="cockroach-sql">`cockroach sql`</InternalLink>.

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.
2. Add your cluster organization and <InternalLink path="licensing-faqs">Enterprise license</InternalLink> 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. 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` interface, but no `application` virtual cluster:

   ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   id |   name   | data_state | service_mode
   ---+----------+------------+---------------
   1  | system   | ready      | shared
   2  | template | ready      | none
   (2 rows)
   ```

   The [configuration profile](#start-the-standby-cluster) 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](#step-4-start-replication) for syntax details.

### Create a user for the standby cluster

If you would like to access the <InternalLink path="ui-overview">DB Console</InternalLink> 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 <InternalLink path="security-reference/authorization#admin-role">`admin`</InternalLink> 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 <InternalLink path="physical-cluster-replication-monitoring">Physical Cluster Replication Monitoring</InternalLink> 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.

<Danger>
  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 <InternalLink path="deploy-cockroachdb-on-premises">prerequisite deployment tutorial</InternalLink>.

  To create certificates signed by an external certificate authority, refer to <InternalLink path="create-security-certificates-openssl">Create Security Certificates using OpenSSL</InternalLink>.
  For example, if you followed the <InternalLink path="deploy-cockroachdb-on-premises">Deploy CockroachDB</InternalLink> prerequisite, you need to add the `ca.crt` from the primary cluster to the `certs` directory on all the nodes in the standby cluster.
</Danger>

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](#create-a-replication-user-and-password).
   * 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 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:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   SHOW VIRTUAL CLUSTERS;
   ```

   The standby cluster will show the `application` 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
    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 <InternalLink path="cutover-replication#step-2-complete-the-cutover">start its service after cutover</InternalLink>.
3. To manage the replication stream, you can <InternalLink path="alter-virtual-cluster">pause and resume</InternalLink> the replication stream as well as <InternalLink path="show-virtual-cluster">show</InternalLink> the current details for the job:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   ALTER VIRTUAL CLUSTER application PAUSE REPLICATION;
   ```

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   ALTER VIRTUAL CLUSTER application RESUME REPLICATION;
   ```

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   SHOW VIRTUAL CLUSTER application WITH REPLICATION STATUS;
   ```

   ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   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 <InternalLink path="physical-cluster-replication-monitoring">Physical Cluster Replication Monitoring</InternalLink>.

<Note>
  You cannot pause a PCR job for longer than 24 hours. PCR jobs paused for longer than 24 hours fail and cannot be recovered.
</Note>

## 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 <InternalLink path="connection-parameters#connect-using-a-url">Client Connection Parameters</InternalLink>.

| Cluster | Interface   | Usage                                                                                                                                        | URL and Parameters                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| ------- | ----------- | -------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| Primary | System      | Set up a replication user and view running virtual clusters. Connect with <InternalLink path="cockroach-sql">`cockroach sql`</InternalLink>. | `"postgresql://root@{node IP or hostname}:26257?options=-ccluster=system&sslmode=verify-full"`<ul><li>`options=-ccluster=system`</li><li>`sslmode=verify-full`</li></ul>Use the `--certs-dir` flag to specify the path to your certificate.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
| Primary | Application | Add and run a workload with <InternalLink path="cockroach-workload">`cockroach workload`</InternalLink>.                                     | `"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 <InternalLink path="cockroach-commands">`cockroach` commands</InternalLink>. Instead, you must use a <InternalLink path="connection-parameters">connection string</InternalLink> at the end of the command. As a result, for the example in this tutorial, you will need:<ul><li>`options=-ccluster=application`</li><li>`sslmode=verify-full`</li><li>`sslrootcert={path}/certs/ca.crt`</li><li>`sslcert={path}/certs/client.root.crt`</li><li>`sslkey={path}/certs/client.root.key`</li></ul> |
| Standby | System      | Manage the replication stream. Connect with <InternalLink path="cockroach-sql">`cockroach sql`</InternalLink>.                               | `"postgresql://root@{node IP or hostname}:26257?options=-ccluster=system&sslmode=verify-full"`<ul><li>`options=-ccluster=system`</li><li>`sslmode=verify-full`</li></ul>Use the `--certs-dir` flag to specify the path to your certificate.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |

## What's next

* <InternalLink path="physical-cluster-replication-monitoring">Physical Cluster Replication Monitoring</InternalLink>
* <InternalLink path="cutover-replication">Cut Over from a Primary Cluster to a Standby Cluster</InternalLink>
* <InternalLink path="create-virtual-cluster">`CREATE VIRTUAL CLUSTER`</InternalLink>
* <InternalLink path="alter-virtual-cluster">`ALTER VIRTUAL CLUSTER`</InternalLink>
* <InternalLink path="drop-virtual-cluster">`DROP VIRTUAL CLUSTER`</InternalLink>
* <InternalLink path="show-virtual-cluster">`SHOW VIRTUAL CLUSTER`</InternalLink>