SET SECONDARY REGION

On this page Carat arrow pointing down

New in v22.2: The ALTER DATABASE .. SET SECONDARY REGION statement adds a secondary region to a multi-region database for failover purposes.

If the primary region fails, the secondary region becomes the new primary region.

For more information, see Secondary regions.

Note:

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

Note:

SET SECONDARY REGION is a subcommand of ALTER DATABASE.

Warning:

In order to add a secondary region with ALTER DATABASE ... SET SECONDARY REGION, you must first set a primary database region with SET PRIMARY REGION, or when creating the database. For an example showing how to add a secondary region with ALTER DATABASE, see Set the secondary region.

Synopsis

Parameters

Parameter Description
database_name The database you want to add a secondary region to.
secondary_region_clause Usually, the region being set as the secondary region for this database. E.g., "ap-southeast-2". Allowed values include any (non-primary) region present in SHOW REGIONS.

Required privileges

To add a secondary region to a database, you must have one of the following:

Examples

Setup

Only a cluster region specified at node startup can be used as a database region.

To follow along with the examples below, start a demo cluster with the --global flag to simulate a multi-region cluster:

icon/buttons/copy
$ cockroach demo --global --nodes 9

To see the regions available to the databases in the cluster, use a SHOW REGIONS FROM CLUSTER statement:

icon/buttons/copy
SHOW REGIONS FROM CLUSTER;
     region    |  zones
---------------+----------
  europe-west1 | {b,c,d}
  us-east1     | {b,c,d}
  us-west1     | {a,b,c}
(3 rows)

Set the primary region for the movr database:

icon/buttons/copy
ALTER DATABASE movr SET PRIMARY REGION "us-east1";

Add the other regions:

icon/buttons/copy
ALTER DATABASE movr ADD REGION "us-west1";
icon/buttons/copy
ALTER DATABASE movr ADD REGION "europe-west1";

Set the secondary region

To set an existing database region (that is not already the primary region) as the secondary region, use the following statement:

icon/buttons/copy
ALTER DATABASE movr SET SECONDARY REGION "us-west1";
ALTER DATABASE SET SECONDARY REGION

Now, the "us-west1" region will act as the primary region if the original primary region fails.

Note:

Setting a region as the secondary region implicitly adds it to the list of database regions, even if it wasn't previously added explicitly using ADD REGION.

Drop the secondary region

To drop the secondary region from a multi-region database, use the DROP SECONDARY REGION statement:

icon/buttons/copy
ALTER DATABASE movr DROP SECONDARY REGION;
ALTER DATABASE DROP SECONDARY REGION

See also


Yes No
On this page

Yes No