CREATE VIRTUAL CLUSTER

On this page

The CREATE VIRTUAL CLUSTER statement creates a new virtual cluster. It is supported only starting a physical cluster replication (PCR) job.

PCR happens between an active primary cluster and a passive standby cluster that accepts updates from the primary cluster. The unit of replication is a virtual cluster, which is part of the underlying infrastructure in the primary and standby clusters. The CockroachDB cluster has:

• The system virtual cluster manages the cluster's control plane and the replication of the cluster's data. Admins connect to the system virtual cluster to configure and manage the underlying CockroachDB cluster, set up PCR, create and manage a virtual cluster, and observe metrics and logs for the CockroachDB cluster and each virtual cluster.
• Each other virtual cluster manages its own data plane. Users connect to a virtual cluster by default, rather than the system virtual cluster. To connect to the system virtual cluster, the connection string must be modified. Virtual clusters contain user data and run application workloads. When PCR is enabled, the non-system virtual cluster on both primary and secondary clusters is named main.

For more detail, refer to the Physical Cluster Replication Overview.

Required privileges

CREATE VIRTUAL CLUSTER requires one of the following privileges:

• The admin role.
• The MANAGEVIRTUALCLUSTER system privilege allows the user to run all the related VIRTUAL CLUSTER SQL statements for PCR.

Use the GRANT SYSTEM statement:

GRANT SYSTEM MANAGEVIRTUALCLUSTER TO user;

Synopsis

Parameters

Options

Connection string

When you initiate a PCR stream from the standby cluster, it is necessary to pass a connection string to the system virtual cluster on the primary cluster:

'postgresql://{replication user}:{password}@{node IP or hostname}:26257?options=-ccluster=system&sslmode=verify-full&sslrootcert=certs/{primary cert}.crt'

To form a connection string similar to the example, include the following values and query parameters. Replace values in {...} with the appropriate values for your configuration:

Capabilities

Capabilities control what a virtual cluster can do. When you start a PCR stream, you can specify a virtual cluster with LIKE to ensure other virtual clusters on the standby cluster will work in the same way. LIKE will refer to a virtual cluster on the CockroachDB cluster you're running the statement from.

Examples

Start a PCR stream

To start a PCR stream to the standby of the primary's virtual cluster:

CREATE VIRTUAL CLUSTER main FROM REPLICATION OF main ON 'postgresql://{connection string to primary}';

This will create a main virtual cluster in the standby cluster. The standby's system virtual cluster will connect to the primary cluster to initiate the PCR job. For details on the PCR stream, refer to the Responses for SHOW VIRTUAL CLUSTER.

Specify a retention window for a PCR stream

When you initiate a PCR stream, you can specify a retention window to protect data from garbage collection. The retention window controls how far in the past you can fail over to:

CREATE VIRTUAL CLUSTER main FROM REPLICATION OF main ON 'postgresql://{connection string to primary}' WITH RETENTION '36h';

This will initiate a PCR stream from the primary cluster into the standby cluster's new main virtual cluster. The RETENTION option allows you to specify a timestamp in the past for failover to the standby cluster. After failover, the standby main virtual cluster will be transactionally consistent to any timestamp within that retention window.

We do not recommend setting RETENTION much higher than the 24-hour default on the standby cluster. Accumulated data from an excessive retention (failover) window could affect queries running on the standby cluster that is active following a failover.

Start a PCR stream with read from standby

New in v24.3: Use the READ VIRTUAL CLUSTER option to set up a PCR stream that also creates a read-only virtual cluster on the standby cluster. You can create a PCR job as per the Set Up Physical Cluster Replication guide and then add the option to the CREATE VIRTUAL CLUSTER statement:

CREATE VIRTUAL CLUSTER main FROM REPLICATION OF main ON 'postgresql://{connection string to primary}' WITH READ VIRTUAL CLUSTER;

View the newly created virtual clusters:

SHOW VIRTUAL CLUSTERS;

You'll find:

• The main virtual cluster, which is accepting writes from the primary cluster.
• The main-readonly virtual cluster, which is a read-only version of the main virtual cluster.

  id |     name      | data_state  | service_mode
-----+---------------+-------------+---------------
   1 | system        | ready       | shared
   3 | main          | replicating | none
   4 | main-readonly | ready       | shared
(3 rows)

To read table data from the standby cluster, connect to the readonly virtual cluster:

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

If you started the PCR stream with the READ VIRTUAL CLUSTER option, failing over with SYSTEM TIME will destroy the readonly virtual cluster. If you fail over with LATEST, the readonly virtual cluster will remain on the original standby cluster, but will not update with new writes. Use DROP VIRTUAL CLUSTER to remove the readonly virtual cluster.

For details on adding a read-only virtual cluster after a failback, refer to the ALTER VIRTUAL CLUSTER page.

See also

• DROP VIRTUAL CLUSTER
• ALTER VIRTUAL CLUSTER
• SHOW VIRTUAL CLUSTER

Was this helpful?