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
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)
New in v22.2: 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)
See also
- Multi-Region Capabilities Overview
ADD REGIONDROP REGIONADD SUPER REGIONDROP SUPER REGIONSHOW SUPER REGIONS- Secondary regions
SET SECONDARY REGIONDROP SECONDARY REGION- SQL Statements
Was this helpful?
