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.
This is an enterprise-only feature. Request a 30-day trial license to try it out.
SET SECONDARY REGION is a subcommand of ALTER DATABASE.
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:
- Membership to the
adminrole for the cluster. - Either ownership or the
CREATEprivilege for the database.
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:
$ cockroach demo --global --nodes 9
To see the regions available to the databases in the cluster, use a SHOW REGIONS FROM CLUSTER statement:
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:
ALTER DATABASE movr SET PRIMARY REGION "us-east1";
Add the other regions:
ALTER DATABASE movr ADD REGION "us-west1";
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:
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.
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:
ALTER DATABASE movr DROP SECONDARY REGION;
ALTER DATABASE DROP SECONDARY REGION
See also
- Secondary regions
DROP SECONDARY REGION- Multi-Region Capabilities Overview
SET PRIMARY REGIONDROP REGIONSHOW REGIONSADD SUPER REGIONDROP SUPER REGIONSHOW SUPER REGIONSALTER TABLE- SQL Statements
Was this helpful?
