On this page 
Warning:
CockroachDB v21.2 is no longer supported as of May 16, 2023. For more details, refer to the Release Support Policy.
Use the SHOW ZONE CONFIGURATIONS statement to view details about existing replication zones.
Synopsis
Required privileges
No privileges are required to list replication zones.
Parameters
| Parameter | Description |
|---|---|
zone_name |
The name of the system range for which to show replication zone configurations. |
database_name |
The name of the database for which to show replication zone configurations. |
table_name |
The name of the table for which to show replication zone configurations. |
partition_name |
The name of the partition for which to show replication zone configurations. |
index_name |
The name of the index for which to show replication zone configurations. |
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 --geo-partitioned-replicas flag. This command opens an interactive SQL shell to a temporary, 9-node in-memory cluster with the movr database.
$ cockroach demo --geo-partitioned-replicas
View all replication zones
> SHOW ALL ZONE CONFIGURATIONS;
target | raw_config_sql
-------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------
RANGE default | ALTER RANGE default CONFIGURE ZONE USING
| range_min_bytes = 134217728,
| range_max_bytes = 536870912,
| gc.ttlseconds = 90000,
| num_replicas = 3,
| constraints = '[]',
| lease_preferences = '[]'
DATABASE system | ALTER DATABASE system CONFIGURE ZONE USING
| range_min_bytes = 134217728,
| range_max_bytes = 536870912,
| gc.ttlseconds = 90000,
| num_replicas = 5,
| constraints = '[]',
| lease_preferences = '[]'
RANGE meta | ALTER RANGE meta CONFIGURE ZONE USING
| range_min_bytes = 134217728,
| range_max_bytes = 536870912,
| gc.ttlseconds = 3600,
| num_replicas = 5,
| constraints = '[]',
| lease_preferences = '[]'
RANGE system | ALTER RANGE system CONFIGURE ZONE USING
| range_min_bytes = 134217728,
| range_max_bytes = 536870912,
| gc.ttlseconds = 90000,
| num_replicas = 5,
| constraints = '[]',
| lease_preferences = '[]'
RANGE liveness | ALTER RANGE liveness CONFIGURE ZONE USING
| range_min_bytes = 134217728,
| range_max_bytes = 536870912,
| gc.ttlseconds = 600,
| num_replicas = 5,
| constraints = '[]',
| lease_preferences = '[]'
TABLE system.public.replication_constraint_stats | ALTER TABLE system.public.replication_constraint_stats CONFIGURE ZONE USING
| gc.ttlseconds = 600,
| constraints = '[]',
| lease_preferences = '[]'
TABLE system.public.replication_stats | ALTER TABLE system.public.replication_stats CONFIGURE ZONE USING
| gc.ttlseconds = 600,
| constraints = '[]',
| lease_preferences = '[]'
PARTITION us_west OF INDEX movr.public.users@primary | ALTER PARTITION us_west OF INDEX movr.public.users@primary CONFIGURE ZONE USING
| constraints = '[+region=us-west1]'
PARTITION us_east OF INDEX movr.public.users@primary | ALTER PARTITION us_east OF INDEX movr.public.users@primary CONFIGURE ZONE USING
| constraints = '[+region=us-east1]'
PARTITION europe_west OF INDEX movr.public.users@primary | ALTER PARTITION europe_west OF INDEX movr.public.users@primary CONFIGURE ZONE USING
| constraints = '[+region=europe-west1]'
PARTITION us_west OF INDEX movr.public.vehicles@primary | ALTER PARTITION us_west OF INDEX movr.public.vehicles@primary CONFIGURE ZONE USING
| constraints = '[+region=us-west1]'
PARTITION us_west OF INDEX movr.public.vehicles@vehicles_auto_index_fk_city_ref_users | ALTER PARTITION us_west OF INDEX movr.public.vehicles@vehicles_auto_index_fk_city_ref_users CONFIGURE ZONE USING
| constraints = '[+region=us-west1]'
PARTITION us_east OF INDEX movr.public.vehicles@primary | ALTER PARTITION us_east OF INDEX movr.public.vehicles@primary CONFIGURE ZONE USING
| constraints = '[+region=us-east1]'
PARTITION us_east OF INDEX movr.public.vehicles@vehicles_auto_index_fk_city_ref_users | ALTER PARTITION us_east OF INDEX movr.public.vehicles@vehicles_auto_index_fk_city_ref_users CONFIGURE ZONE USING
| constraints = '[+region=us-east1]'
PARTITION europe_west OF INDEX movr.public.vehicles@primary | ALTER PARTITION europe_west OF INDEX movr.public.vehicles@primary CONFIGURE ZONE USING
| constraints = '[+region=europe-west1]'
PARTITION europe_west OF INDEX movr.public.vehicles@vehicles_auto_index_fk_city_ref_users | ALTER PARTITION europe_west OF INDEX movr.public.vehicles@vehicles_auto_index_fk_city_ref_users CONFIGURE ZONE USING
| constraints = '[+region=europe-west1]'
PARTITION us_west OF INDEX movr.public.rides@primary | ALTER PARTITION us_west OF INDEX movr.public.rides@primary CONFIGURE ZONE USING
| constraints = '[+region=us-west1]'
PARTITION us_west OF INDEX movr.public.rides@rides_auto_index_fk_city_ref_users | ALTER PARTITION us_west OF INDEX movr.public.rides@rides_auto_index_fk_city_ref_users CONFIGURE ZONE USING
| constraints = '[+region=us-west1]'
PARTITION us_west OF INDEX movr.public.rides@rides_auto_index_fk_vehicle_city_ref_vehicles | ALTER PARTITION us_west OF INDEX movr.public.rides@rides_auto_index_fk_vehicle_city_ref_vehicles CONFIGURE ZONE USING
| constraints = '[+region=us-west1]'
PARTITION us_east OF INDEX movr.public.rides@primary | ALTER PARTITION us_east OF INDEX movr.public.rides@primary CONFIGURE ZONE USING
| constraints = '[+region=us-east1]'
PARTITION us_east OF INDEX movr.public.rides@rides_auto_index_fk_city_ref_users | ALTER PARTITION us_east OF INDEX movr.public.rides@rides_auto_index_fk_city_ref_users CONFIGURE ZONE USING
| constraints = '[+region=us-east1]'
PARTITION us_east OF INDEX movr.public.rides@rides_auto_index_fk_vehicle_city_ref_vehicles | ALTER PARTITION us_east OF INDEX movr.public.rides@rides_auto_index_fk_vehicle_city_ref_vehicles CONFIGURE ZONE USING
| constraints = '[+region=us-east1]'
PARTITION europe_west OF INDEX movr.public.rides@primary | ALTER PARTITION europe_west OF INDEX movr.public.rides@primary CONFIGURE ZONE USING
| constraints = '[+region=europe-west1]'
PARTITION europe_west OF INDEX movr.public.rides@rides_auto_index_fk_city_ref_users | ALTER PARTITION europe_west OF INDEX movr.public.rides@rides_auto_index_fk_city_ref_users CONFIGURE ZONE USING
| constraints = '[+region=europe-west1]'
PARTITION europe_west OF INDEX movr.public.rides@rides_auto_index_fk_vehicle_city_ref_vehicles | ALTER PARTITION europe_west OF INDEX movr.public.rides@rides_auto_index_fk_vehicle_city_ref_vehicles CONFIGURE ZONE USING
| constraints = '[+region=europe-west1]'
PARTITION us_west OF INDEX movr.public.vehicle_location_histories@primary | ALTER PARTITION us_west OF INDEX movr.public.vehicle_location_histories@primary CONFIGURE ZONE USING
| constraints = '[+region=us-west1]'
PARTITION us_east OF INDEX movr.public.vehicle_location_histories@primary | ALTER PARTITION us_east OF INDEX movr.public.vehicle_location_histories@primary CONFIGURE ZONE USING
| constraints = '[+region=us-east1]'
PARTITION europe_west OF INDEX movr.public.vehicle_location_histories@primary | ALTER PARTITION europe_west OF INDEX movr.public.vehicle_location_histories@primary CONFIGURE ZONE USING
| constraints = '[+region=europe-west1]'
TABLE movr.public.promo_codes | ALTER TABLE movr.public.promo_codes CONFIGURE ZONE USING
| num_replicas = 3,
| constraints = '{+region=us-east1: 1}',
| lease_preferences = '[[+region=us-east1]]'
INDEX movr.public.promo_codes@promo_codes_idx_us_west | ALTER INDEX movr.public.promo_codes@promo_codes_idx_us_west CONFIGURE ZONE USING
| num_replicas = 3,
| constraints = '{+region=us-west1: 1}',
| lease_preferences = '[[+region=us-west1]]'
INDEX movr.public.promo_codes@promo_codes_idx_europe_west | ALTER INDEX movr.public.promo_codes@promo_codes_idx_europe_west CONFIGURE ZONE USING
| num_replicas = 3,
| constraints = '{+region=europe-west1: 1}',
| lease_preferences = '[[+region=europe-west1]]'
PARTITION us_west OF INDEX movr.public.user_promo_codes@primary | ALTER PARTITION us_west OF INDEX movr.public.user_promo_codes@primary CONFIGURE ZONE USING
| constraints = '[+region=us-west1]'
PARTITION us_east OF INDEX movr.public.user_promo_codes@primary | ALTER PARTITION us_east OF INDEX movr.public.user_promo_codes@primary CONFIGURE ZONE USING
| constraints = '[+region=us-east1]'
PARTITION europe_west OF INDEX movr.public.user_promo_codes@primary | ALTER PARTITION europe_west OF INDEX movr.public.user_promo_codes@primary CONFIGURE ZONE USING
| constraints = '[+region=europe-west1]'
(34 rows)
View the default replication zone for the cluster
> SHOW ZONE CONFIGURATION FROM RANGE default;
target | raw_config_sql
----------------+-------------------------------------------
RANGE default | ALTER RANGE default CONFIGURE ZONE USING
| range_min_bytes = 134217728,
| range_max_bytes = 536870912,
| gc.ttlseconds = 90000,
| num_replicas = 3,
| constraints = '[]',
| lease_preferences = '[]'
(1 row)
View the replication zone for a database
To control replication for a specific database, use the ALTER DATABASE ... CONFIGURE ZONE statement to define the relevant values (other values will be inherited from the parent zone):
> ALTER DATABASE movr CONFIGURE ZONE USING num_replicas = 5, gc.ttlseconds = 100000;
CONFIGURE ZONE 1
> SHOW ZONE CONFIGURATION FROM DATABASE movr;
target | raw_config_sql
----------------+-------------------------------------------
DATABASE movr | ALTER DATABASE movr CONFIGURE ZONE USING
| range_min_bytes = 134217728,
| range_max_bytes = 536870912,
| gc.ttlseconds = 100000,
| num_replicas = 5,
| constraints = '[]',
| lease_preferences = '[]'
(1 row)
View the replication zone for a table
To control replication for a specific table, use the ALTER TABLE ... CONFIGURE ZONE statement to define the relevant values (other values will be inherited from the parent zone):
> ALTER TABLE users CONFIGURE ZONE USING num_replicas = 5, gc.ttlseconds = 100000;
CONFIGURE ZONE 1
> SHOW ZONE CONFIGURATION FROM TABLE users;
target | raw_config_sql
--------------+-----------------------------------------
TABLE users | ALTER TABLE users CONFIGURE ZONE USING
| range_min_bytes = 134217728,
| range_max_bytes = 536870912,
| gc.ttlseconds = 100000,
| num_replicas = 5,
| constraints = '[]',
| lease_preferences = '[]'
(1 row)
You can also use SHOW CREATE TABLE to view zone configurations for a table. If a table is partitioned, but no zones are configured, the SHOW CREATE TABLE output includes a warning.
View the replication zone for an index
To control replication for a specific table, use the ALTER INDEX ... CONFIGURE ZONE statement to define the relevant values (other values will be inherited from the parent zone):
> ALTER INDEX vehicles@vehicles_auto_index_fk_city_ref_users CONFIGURE ZONE USING num_replicas = 5, gc.ttlseconds = 100000;
CONFIGURE ZONE 1
> SHOW ZONE CONFIGURATION FROM INDEX vehicles@vehicles_auto_index_fk_city_ref_users;
target | raw_config_sql
-------------------------------------------------------+----------------------------------------------------------------------------------
INDEX vehicles@vehicles_auto_index_fk_city_ref_users | ALTER INDEX vehicles@vehicles_auto_index_fk_city_ref_users CONFIGURE ZONE USING
| range_min_bytes = 134217728,
| range_max_bytes = 536870912,
| gc.ttlseconds = 100000,
| num_replicas = 5,
| constraints = '[]',
| lease_preferences = '[]'
(1 row)
View the replication zone for a partition
Tip:
Most users should not need to use partitioning directly. Instead, they should use CockroachDB's built-in multi-region capabilities, which automatically handle geo-partitioning and other low-level details.
Note:
This is an enterprise-only feature. Request a 30-day trial license to try it out.
Once partitions have been defined for a table or a secondary index, to control replication for a partition, use ALTER PARTITION <partition> OF INDEX <table@index> CONFIGURE ZONE:
> ALTER PARTITION us_west OF INDEX vehicles@primary
CONFIGURE ZONE USING
num_replicas = 5,
constraints = '[+region=us-west1]';
CONFIGURE ZONE 1
> ALTER PARTITION us_west OF INDEX vehicles@vehicles_auto_index_fk_city_ref_users
CONFIGURE ZONE USING
num_replicas = 5,
constraints = '[+region=us-west1]';
CONFIGURE ZONE 1
To define replication zones for identically named partitions of a table and its secondary indexes, you can use the <table>@* syntax to save several steps:
> ALTER PARTITION us_west OF INDEX vehicles@*
CONFIGURE ZONE USING
num_replicas = 5,
constraints = '[+region=us-west1]';
To view the zone configuration for a partition, use SHOW ZONE CONFIGURATION FROM PARTITION <partition> OF INDEX <table@index>:
> SHOW ZONE CONFIGURATION FROM PARTITION us_west OF INDEX vehicles@primary;
target | raw_config_sql
----------------------------------------------+-------------------------------------------------------------------------
PARTITION us_west OF INDEX vehicles@primary | ALTER PARTITION us_west OF INDEX vehicles@primary CONFIGURE ZONE USING
| range_min_bytes = 134217728,
| range_max_bytes = 536870912,
| gc.ttlseconds = 90000,
| num_replicas = 5,
| constraints = '[+region=us-west1]',
| lease_preferences = '[]'
(1 row)
Tip:
You can also use the SHOW CREATE TABLE statement or SHOW PARTITIONS statements to view details about all of the replication zones defined for the partitions of a table and its secondary indexes.
See also
- Configure Replication Zones
CONFIGURE ZONEALTER DATABASEALTER INDEXALTER RANGEALTER TABLE- SQL Statements
Was this helpful?
