ADD REGION

The ALTER DATABASE .. ADD REGION statement adds a region to a multi-region database.

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

YesYes NoNo