Warning:
Cockroach Labs will stop providing Assistance Support for this version on May 12, 2021. Prior to that date, upgrade to a more recent version to continue receiving support. For more details, see the Release Support Policy.

Use the SHOW ZONE CONFIGURATIONS statement to view details about existing replication zones.

Synopsis

SHOW ZONE CONFIGURATION FOR RANGE zone_name DATABASE database_name TABLE table_name PARTITION partition_name PARTITION partition_name OF TABLE table_name INDEX table_name @ index_name CONFIGURATIONS ALL ZONE CONFIGURATIONS

Required privileges

No privileges are required to list replication zones.

Parameters

Parameter Description
range_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 Geo-Partitioned Replicas Topology applied to the movr database.

copy
icon/buttons/copy
$ cockroach demo --geo-partitioned-replicas

View all replication zones

copy
icon/buttons/copy
> SHOW ALL ZONE CONFIGURATIONS;
                       target                      |                               raw_config_sql
+--------------------------------------------------+-----------------------------------------------------------------------------+
  RANGE default                                    | ALTER RANGE default CONFIGURE ZONE USING
                                                   |     range_min_bytes = 16777216,
                                                   |     range_max_bytes = 67108864,
                                                   |     gc.ttlseconds = 90000,
                                                   |     num_replicas = 3,
                                                   |     constraints = '[]',
                                                   |     lease_preferences = '[]'
  DATABASE system                                  | ALTER DATABASE system CONFIGURE ZONE USING
                                                   |     range_min_bytes = 16777216,
                                                   |     range_max_bytes = 67108864,
                                                   |     gc.ttlseconds = 90000,
                                                   |     num_replicas = 5,
                                                   |     constraints = '[]',
                                                   |     lease_preferences = '[]'
  TABLE system.public.jobs                         | ALTER TABLE system.public.jobs CONFIGURE ZONE USING
                                                   |     range_min_bytes = 16777216,
                                                   |     range_max_bytes = 67108864,
                                                   |     gc.ttlseconds = 600,
                                                   |     num_replicas = 5,
                                                   |     constraints = '[]',
                                                   |     lease_preferences = '[]'
  RANGE meta                                       | ALTER RANGE meta CONFIGURE ZONE USING
                                                   |     range_min_bytes = 16777216,
                                                   |     range_max_bytes = 67108864,
                                                   |     gc.ttlseconds = 3600,
                                                   |     num_replicas = 5,
                                                   |     constraints = '[]',
                                                   |     lease_preferences = '[]'
  RANGE system                                     | ALTER RANGE system CONFIGURE ZONE USING
                                                   |     range_min_bytes = 16777216,
                                                   |     range_max_bytes = 67108864,
                                                   |     gc.ttlseconds = 90000,
                                                   |     num_replicas = 5,
                                                   |     constraints = '[]',
                                                   |     lease_preferences = '[]'
  RANGE liveness                                   | ALTER RANGE liveness CONFIGURE ZONE USING
                                                   |     range_min_bytes = 16777216,
                                                   |     range_max_bytes = 67108864,
                                                   |     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 = '[]'
...

View the default replication zone for the cluster

copy
icon/buttons/copy
> SHOW ZONE CONFIGURATION FOR RANGE default;
     target     |              raw_config_sql
+---------------+------------------------------------------+
  RANGE default | ALTER RANGE default CONFIGURE ZONE USING
                |     range_min_bytes = 16777216,
                |     range_max_bytes = 67108864,
                |     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):

copy
icon/buttons/copy
> ALTER DATABASE movr CONFIGURE ZONE USING num_replicas = 5, gc.ttlseconds = 100000;
CONFIGURE ZONE 1
copy
icon/buttons/copy
> SHOW ZONE CONFIGURATION FOR DATABASE movr;
     target     |              raw_config_sql
+---------------+------------------------------------------+
  DATABASE movr | ALTER DATABASE movr CONFIGURE ZONE USING
                |     range_min_bytes = 16777216,
                |     range_max_bytes = 67108864,
                |     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):

copy
icon/buttons/copy
> ALTER TABLE users CONFIGURE ZONE USING num_replicas = 5, gc.ttlseconds = 100000;
CONFIGURE ZONE 1
copy
icon/buttons/copy
> SHOW ZONE CONFIGURATION FOR TABLE users;
    target    |             raw_config_sql
+-------------+----------------------------------------+
  TABLE users | ALTER TABLE users CONFIGURE ZONE USING
              |     range_min_bytes = 16777216,
              |     range_max_bytes = 67108864,
              |     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):

copy
icon/buttons/copy
> ALTER INDEX vehicles@vehicles_auto_index_fk_city_ref_users CONFIGURE ZONE USING num_replicas = 5, gc.ttlseconds = 100000;
CONFIGURE ZONE 1
copy
icon/buttons/copy
> SHOW ZONE CONFIGURATION FOR 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 = 16777216,
                                                       |     range_max_bytes = 67108864,
                                                       |     gc.ttlseconds = 100000,
                                                       |     num_replicas = 5,
                                                       |     constraints = '[]',
                                                       |     lease_preferences = '[]'
(1 row)

View the replication zone for a partition

Note:

This is an enterprise-only feature.

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:

copy
icon/buttons/copy
> ALTER PARTITION us_west OF INDEX vehicles@primary
    CONFIGURE ZONE USING
      num_replicas = 5,
      constraints = '[+region=us-west1]';
CONFIGURE ZONE 1
copy
icon/buttons/copy
> 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

New in v19.2: 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:

copy
icon/buttons/copy
> 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 FOR PARTITION <partition> OF INDEX <table@index>:

copy
icon/buttons/copy
> SHOW ZONE CONFIGURATION FOR 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 = 16777216,
                                              |     range_max_bytes = 67108864,
                                              |     gc.ttlseconds = 90000,
                                              |     num_replicas = 5,
                                              |     constraints = '[+region=us-west1]',
                                              |     lease_preferences = '[]'
(1 row)
Tip:

New in v19.2: 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



YesYes NoNo