ADD REGION

On this page Carat arrow pointing down
Warning:
CockroachDB v21.1 is no longer supported as of November 18, 2022. For more details, refer to the Release Support Policy.

New in v21.1: The ALTER DATABASE .. ADD REGION statement adds a region to 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:

ADD REGION is a subcommand of ALTER DATABASE.

Warning:

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

Synopsis

ALTER DATABASE database_name ADD REGION IF NOT EXISTS region_name

Parameters

Parameter Description
database_name The database to which you are adding a region.
region_name The region being added to this database. Allowed values include any region present in SHOW REGIONS FROM CLUSTER.

Required privileges

To add a region to 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

Given a cluster with multiple regions, any databases in that cluster that have not yet had their primary regions set will have their replicas spread as broadly as possible for resiliency. When a primary region is added to one of these databases:

  • All tables will be REGIONAL BY TABLE in the primary region by default.
  • This means that all such tables will have all of their voting replicas and leaseholders moved to the primary region. This process is known as rebalancing.

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 a region 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}
(1 row)

See also


Yes No
On this page

Yes No