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.
This is an enterprise-only feature. Request a 30-day trial license to try it out.
ADD REGION
is a subcommand of ALTER DATABASE
.
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
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:
- Membership to the
admin
role for the cluster. - Either ownership or the
CREATE
privilege for the database and allREGIONAL 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
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)