DROP REGION

The ALTER DATABASE .. DROP REGION statement drops a region from a multi-region database.

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 cluster regions specified at node startup can be used as database regions.

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)

See also

YesYes NoNo