SET PRIMARY REGION

The ALTER DATABASE .. SET PRIMARY REGION statement sets the primary region of a multi-region database.

Note:

This is an enterprise-only feature. Request a 30-day trial license to try it out.

Note:

SET PRIMARY REGION is a subcommand of ALTER DATABASE.

Warning:

If a database's zone configuration has been directly set with an ALTER DATABASE ... CONFIGURE ZONE statement, CockroachDB will block all ALTER DATABASE ... SET PRIMARY REGION statements on the database.

To remove existing, manually-configured zones from a database (and unblock SET PRIMARY REGION statements on the database), use an ALTER DATABASE ... CONFIGURE ZONE DISCARD statement.

Synopsis

ALTER DATABASE database_name SET PRIMARY REGION = region_name

Parameters

Parameter Description
database_name The database whose primary region to set.
region_name The region to set as the database's primary region.
Allowed values include any region present in SHOW REGIONS FROM CLUSTER.

Required privileges

To add a primary region to a database with no existing regions, the user must have one of the following:

  • Membership to the admin role for the cluster.
  • Membership to the owner role, or the CREATE privilege, for the database and all tables in the database.

To switch primary regions to a region that has already been added to a database, the user must have membership to the owner role for the database, or have the CREATE privilege on the database.

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 more regions to the database

To add more regions to the database, use an ADD REGION statement:

icon/buttons/copy
ALTER database foo ADD region "europe-west1";
ALTER DATABASE ADD REGION

To view the database's regions, and to see which region is the primary region, 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}
(2 rows)

Change an existing primary region

To change the primary region to another region in the database, use a SET PRIMARY REGION statement.

You can only change an existing primary region to a region that has already been added to the database. If you try to change the primary region to a region that is not already associated with a database, CockroachDB will return an error:

icon/buttons/copy
ALTER DATABASE foo SET PRIMARY REGION "us-west1";
ERROR: region "us-west1" has not been added to the database
SQLSTATE: 42602
HINT: you must add the region to the database before setting it as primary region, using ALTER DATABASE foo ADD REGION "us-west1"
icon/buttons/copy
ALTER database foo ADD region "us-west1";
ALTER DATABASE ADD REGION
icon/buttons/copy
ALTER DATABASE foo SET PRIMARY REGION "us-west1";
ALTER DATABASE PRIMARY REGION
icon/buttons/copy
SHOW REGIONS FROM DATABASE foo;
  database |    region    | primary |  zones
-----------+--------------+---------+----------
  foo      | us-west1     |  true   | {a,b,c}
  foo      | europe-west1 |  false  | {b,c,d}
  foo      | us-east1     |  false  | {b,c,d}
(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}
  foo      | europe-west1 |  false  | {b,c,d}
(2 rows)
Note:

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