ADD REGION

On this page

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.

Synopsis

Parameters

Required privileges

To add a region to a database, the user must have one of the following:

• Membership to the admin role for the cluster.
• Either ownership or the CREATE privilege for the database and all REGIONAL BY ROW tables in 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 --no-example-database

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

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:

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:

ALTER database foo ADD region "us-west1";

ALTER DATABASE ADD REGION

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:

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:

ALTER DATABASE foo DROP REGION "us-west1";

ALTER DATABASE DROP REGION

SHOW REGIONS FROM DATABASE foo;

  database |  region  | primary |  zones
-----------+----------+---------+----------
  foo      | us-east1 |  true   | {b,c,d}
(1 row)

See also

• Multi-Region Capabilities Overview
• SET PRIMARY REGION
• DROP REGION
• SHOW REGIONS
• ALTER TABLE
• SQL Statements

Was this helpful?