SHOW REGIONS

The SHOW REGIONS lists the for a multi-region cluster, or the for the databases in a multi-region cluster.

Synopsis

Required privileges

Only members of the can run SHOW REGIONS. By default, the root user belongs to the admin role.

Parameters

Parameter	Description
FROM CLUSTER	Show the for a cluster. Cluster regions are specified .
FROM DATABASE	Show all for the current database. Database regions can be added , or .
FROM DATABASE database\_name	Show all database regions from the database named database\_name.
FROM ALL DATABASES	Show the database regions for all databases in the cluster.

Response

SHOW REGIONS, SHOW REGIONS FROM CLUSTER, and SHOW REGIONS FROM DATABASE return the following fields for each region:

Field	Description	SHOW REGIONS	SHOW REGIONS FROM CLUSTER	SHOW REGIONS FROM DATABASE
region	The name of the region.	✓	✓	✓
zones	The availability zones for the region.	✓	✓	✓
database\_names	A set of database names that use the region.	✓
primary\_region\_of	A set of database names for which the region is the .	✓
secondary\_region\_of	A set of database names for which the region is the .	✓
database	The name of the database that uses the region.			✓
primary	If true, indicates that the region is the primary region.			✓

SHOW REGIONS FROM ALL DATABASES returns the following fields for each database:

Field	Description
database\_name	The name of the database.
regions	A set of region names in use by the database.
primary\_region	The primary region of the database.
secondary\_region	The of the database.

Examples

Setup

The following examples use MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see .

To follow along, run with the and flags. This command opens an interactive SQL shell to a temporary, multi-node in-memory cluster with the movr database preloaded and set as the .

$ cockroach demo --nodes=6 --demo-locality=region=us-east,zone=us-east-a:region=us-east,zone=us-east-b:region=us-central,zone=us-central-a:region=us-central,zone=us-central-b:region=us-west,zone=us-west-a:region=us-west,zone=us-west-b

View the regions in a cluster

After cluster startup, you can view all of the cluster regions available in the cluster with SHOW REGIONS FROM CLUSTER:

SHOW REGIONS FROM CLUSTER;

    region   |            zones
-------------+------------------------------
  us-central | {us-central-a,us-central-b}
  us-east    | {us-east-a,us-east-b}
  us-west    | {us-west-a,us-west-b}
(3 rows)

View the regions in a single database

SHOW REGIONS FROM DATABASE returns the database regions for a specific database.

as the primary region for the movr database:

ALTER DATABASE movr PRIMARY REGION "us-east";

ALTER DATABASE PRIMARY REGION

Only (i.e., regions that are defined at ) can be added to a multi-region database.

Then, add more regions to the database:

ALTER DATABASE movr ADD REGION "us-west";

ALTER DATABASE ADD REGION

ALTER DATABASE movr ADD REGION "us-central";

ALTER DATABASE ADD REGION

To view the regions associated with the database:

SHOW REGIONS FROM DATABASE movr;

  database |   region   | primary | secondary |            zones
-----------+------------+---------+-----------+------------------------------
  movr     | us-east    |    t    |     f     | {us-east-a,us-east-b}
  movr     | us-central |    f    |     f     | {us-central-a,us-central-b}
  movr     | us-west    |    f    |     f     | {us-west-a,us-west-b}
(3 rows)

The secondary column in each row says whether that region has been made a secondary region for failover purposes. For more information, see .

With movr set as the current database, the following statement returns the same results:

SHOW REGIONS FROM DATABASE;

View the regions for all databases in a cluster

Create another database in the cluster with a primary region:

CREATE DATABASE cockroachlabs PRIMARY REGION "us-east";

Then, add another region to the database:

ALTER DATABASE cockroachlabs ADD REGION "us-west";

To show the regions in use by all the databases in a cluster, use SHOW REGIONS:

SHOW REGIONS;

    region   |            zones            |    database_names    |  primary_region_of
-------------+-----------------------------+----------------------+-----------------------
  us-central | {us-central-a,us-central-b} | {movr}               | {}
  us-east    | {us-east-a,us-east-b}       | {cockroachlabs,movr} | {cockroachlabs,movr}
  us-west    | {us-west-a,us-west-b}       | {cockroachlabs,movr} | {}
(3 rows)

To show the region information for each database in the cluster, use SHOW REGIONS FROM ALL DATABASES:

SHOW REGIONS FROM ALL DATABASES;

  database_name |           regions            | primary_region
----------------+------------------------------+-----------------
  cockroachlabs | {us-east,us-west}            | us-east
  defaultdb     | {}                           | NULL
  movr          | {us-central,us-east,us-west} | us-east
  postgres      | {}                           | NULL
  system        | {}                           | NULL
(5 rows)

See also