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



Yes No