SHOW REGIONS

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

Synopsis

SHOW REGIONS FROM CLUSTER DATABASE database_name ALL DATABASES

Required privileges

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

Parameters

Parameter Description
FROM CLUSTER Show the cluster regions for a cluster.
Cluster regions are specified at cluster startup.
FROM DATABASE Show all database regions for the current database.
Database regions can be added at database creation, or after a database has been created.
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 primary region.
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.

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 MovR: A Global Vehicle-sharing App.

To follow along, run cockroach demo with the --nodes and --demo-locality 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 current database.

icon/buttons/copy
$ 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:

icon/buttons/copy
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.

Add an available region as the primary region for the movr database:

icon/buttons/copy
ALTER DATABASE movr PRIMARY REGION "us-east";
ALTER DATABASE PRIMARY REGION
Note:

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

Then, add more regions to the database:

icon/buttons/copy
ALTER DATABASE movr ADD REGION "us-west";
ALTER DATABASE ADD REGION
icon/buttons/copy
ALTER DATABASE movr ADD REGION "us-central";
ALTER DATABASE ADD REGION

To view the regions associated with the database:

icon/buttons/copy
SHOW REGIONS FROM DATABASE movr;
  database |   region   | primary |            zones
-----------+------------+---------+------------------------------
  movr     | us-east    |  true   | {us-east-a,us-east-b}
  movr     | us-central |  false  | {us-central-a,us-central-b}
  movr     | us-west    |  false  | {us-west-a,us-west-b}
(3 rows)

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

icon/buttons/copy
SHOW REGIONS FROM DATABASE;
  database |   region   | primary |            zones
-----------+------------+---------+------------------------------
  movr     | us-east    |  true   | {us-east-a,us-east-b}
  movr     | us-central |  false  | {us-central-a,us-central-b}
  movr     | us-west    |  false  | {us-west-a,us-west-b}
(3 rows)

View the regions for all databases in a cluster

Create another database in the cluster with a primary region:

icon/buttons/copy
CREATE DATABASE cockroachlabs PRIMARY REGION "us-east";

Then, add another region to the database:

icon/buttons/copy
ALTER DATABASE cockroachlabs ADD REGION "us-west";

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

icon/buttons/copy
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:

icon/buttons/copy
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

YesYes NoNo