SHOW REGIONS

On this page

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

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.	✓
`secondary_region_of`	A set of database names for which the region is the secondary 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.
`secondary_region`	The secondary 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.

$ 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.

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

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:

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 Secondary regions.

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)

Pricing

Contact us

Sign In