DROP SECONDARY REGION

On this page

New in v22.2: The ALTER DATABASE .. DROP SECONDARY REGION statement drops the secondary region (if set) from a multi-region database.

The secondary region is used for failover purposes: if the primary region fails, the secondary region acts as the new primary region.

For more information, see Secondary regions.

Synopsis

Parameters

Required privileges

To drop a secondary region from a database, you must have one of the following:

• Membership to the admin role for the cluster.
• Membership to the owner role, or the CREATE privilege 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";

Drop the secondary region

To drop the secondary region from the movr database (that was added in Set the secondary region) use the following statement:

ALTER DATABASE movr DROP SECONDARY REGION;

ALTER DATABASE DROP SECONDARY REGION

See also

• Secondary regions
• SET SECONDARY REGION
• Multi-Region Capabilities Overview
• SET PRIMARY REGION
• ADD REGION
• SHOW REGIONS
• ADD SUPER REGION
• DROP SUPER REGION
• SHOW SUPER REGIONS
• Zone Config Extensions
• ALTER TABLE
• SQL Statements

Was this helpful?