DROP REGION

On this page Carat arrow pointing down
Warning:
As of May 16, 2023, CockroachDB v21.2 is no longer supported. For more details, refer to the Release Support Policy.

The ALTER DATABASE .. DROP REGION statement drops a region from a multi-region database. While CockroachDB processes an index modification or changing a table to or from a REGIONAL BY ROW table, attempting to drop a region from the database containing that REGIONAL BY ROW table will produce an error. Similarly, while this statement is running, all index modifications and locality changes on REGIONAL BY ROW tables will be blocked.

Note:

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

Note:

DROP REGION is a subcommand of ALTER DATABASE.

Synopsis

ALTER DATABASE database_name DROP REGION IF EXISTS region_name

Parameters

Parameter Description
database_name The database from which you are dropping a region.
region_name The region being dropped from this database. Allowed values include any region present in SHOW REGIONS FROM DATABASE database_name.
You can only drop the primary region from a multi-region database if it's the last remaining region.

Required privileges

To drop a region from a database, the user 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 --no-example-database

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

Suppose you have a database foo in your cluster, and you want to make it a multi-region database.

To add the first region to the database, or to set an already-added region as the primary region, use a SET PRIMARY REGION statement:

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

Add regions to a database

To add more regions to a database that already has at least one region, use an ADD REGION statement:

icon/buttons/copy
ALTER database foo ADD region "us-west1";
ALTER DATABASE ADD REGION
icon/buttons/copy
ALTER database foo ADD region "europe-west1";
ALTER DATABASE ADD REGION

View a database's regions

To view the regions associated with a multi-region database, use a SHOW REGIONS FROM DATABASE statement:

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

Drop regions from a database

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

icon/buttons/copy
ALTER DATABASE foo DROP REGION "us-west1";
ALTER DATABASE DROP REGION
icon/buttons/copy
SHOW REGIONS FROM DATABASE foo;
  database |    region    | primary |  zones
-----------+--------------+---------+----------
  foo      | us-east1     |  true   | {b,c,d}
  foo      | europe-west1 |  false  | {b,c,d}
(2 rows)

You can only drop the primary region from a multi-region database if it's the last remaining region.

If you try to drop the primary region when there is more than one region, CockroachDB will return an error:

icon/buttons/copy
ALTER DATABASE foo DROP REGION "us-east1";
ERROR: cannot drop region "us-east1"
SQLSTATE: 42P12
HINT: You must designate another region as the primary region using ALTER DATABASE foo PRIMARY REGION <region name> or remove all other regions before attempting to drop region "us-east1"
icon/buttons/copy
ALTER DATABASE foo DROP REGION "europe-west1";
ALTER DATABASE DROP REGION
icon/buttons/copy
SHOW REGIONS FROM DATABASE foo;
  database |  region  | primary |  zones
-----------+----------+---------+----------
  foo      | us-east1 |  true   | {b,c,d}
(1 row)
icon/buttons/copy
ALTER DATABASE foo DROP REGION "us-east1";
ALTER DATABASE DROP REGION
icon/buttons/copy
SHOW REGIONS FROM DATABASE foo;
  database | region | primary | zones
-----------+--------+---------+--------
(0 rows)

You cannot drop a region from a database if the databases uses REGION survival goal and there are only three regions configured on the database:

icon/buttons/copy
ALTER DATABASE foo SET PRIMARY REGION "us-east1";
ALTER DATABASE PRIMARY REGION
icon/buttons/copy
ALTER DATABASE foo ADD REGION "us-west1";
ALTER DATABASE ADD REGION
icon/buttons/copy
ALTER DATABASE foo ADD REGION "europe-west1";
ALTER DATABASE ADD REGION
icon/buttons/copy
ALTER DATABASE foo DROP REGION "us-west1";
ERROR: at least 3 regions are required for surviving a region failure
SQLSTATE: 22023
HINT: you must add additional regions to the database or change the survivability goal

See also


Yes No
On this page

Yes No