New in v22.2: The ALTER DATABASE .. ALTER LOCALITY statement provides a customization tool for advanced users to persistently modify the configuration generated by the standard multi-region SQL abstractions on a per-region basis.
The feature this statement enables is also known as Zone Config Extensions. For more information about how it works, and the benefits over using the low-level CONFIGURE ZONE statement, see Zone Config Extensions.
ALTER LOCALITY is a subcommand of ALTER DATABASE.
Synopsis
ALTER DATABASE <database> ALTER LOCALITY <locality_scope> CONFIGURE ZONE USING <set_zone_config>;
Parameters
| Parameter | Description |
|---|---|
database |
The multi-region database containing the regions whose zone configs you want to modify. |
locality_scope |
One of: GLOBAL (global tables), REGIONAL (all REGIONAL BY TABLE and REGIONAL BY ROW tables), or REGIONAL IN (all REGIONAL BY TABLE IN {region} tables and all {region} partitions of REGIONAL BY ROW tables). |
set_zone_config |
The zone config you want to apply to the schema objects in that region. |
Required privileges
The user must be a member of the admin or owner roles, or have the CREATE privilege on the database.
Examples
The following examples show:
- How to override specific fields of a schema object's zone configs.
- How something like the Secondary regions multi-region abstraction could have been implemented using Zone Config Extensions. For this example, we will call it "Failover regions".
- How to reset a region's Zone Config Extensions
- How to discard a region's Zone Config Extensions
We strongly recommend using the multi-region abstractions over "rolling your own" using Zone Config Extensions. These examples are provided to show the flexibility of Zone Config Extensions.
Setup
The setup described in this section will be used in the examples below.
Start a cluster
Start a cockroach demo cluster as follows:
cockroach demo --global --nodes=9
This gives us a (preloaded) MovR database on a cluster with the following regions, which can be viewed with SHOW REGIONS:
SHOW REGIONS;
region | zones | database_names | primary_region_of | secondary_region_of
---------------+---------+----------------+-------------------+----------------------
europe-west1 | {b,c,d} | {} | {} | {}
us-east1 | {b,c,d} | {} | {} | {}
us-west1 | {a,b,c} | {} | {} | {}
(3 rows)
Make the database multi-region
Next, modify the database to use the multi-region abstractions as follows:
- Set the primary region using
SET PRIMARY REGION. - Add the other two regions using
ADD REGION.
ALTER DATABASE movr SET PRIMARY REGION "us-east1";
ALTER DATABASE movr ADD REGION "us-west1";
ALTER DATABASE movr ADD REGION "europe-west1";
Override specific fields of a schema object's zone configs
In this example we will configure a multi-region MovR database to update its lease_preferences field.
Setting the primary region to us-east1 during the setup steps added us-east1 to lease_preferences.
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 = 90000,
| num_replicas = 5,
| num_voters = 3,
| constraints = '{+region=europe-west1: 1, +region=us-east1: 1, +region=us-west1: 1}',
| voter_constraints = '[+region=us-east1]',
| lease_preferences = '[[+region=us-east1]]'
(1 row)
We will now use ALTER DATABASE ... ALTER LOCALITY to overwrite the lease_preferences field to add us-west1 to the list of regions:
ALTER DATABASE movr ALTER LOCALITY REGIONAL IN "us-east1" CONFIGURE ZONE USING lease_preferences = '[[+region=us-east1], [+region=us-west1]]';
To view the updated zone configs, enter the following statement:
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 = 90000,
| num_replicas = 5,
| num_voters = 3,
| constraints = '{+region=europe-west1: 1, +region=us-east1: 1, +region=us-west1: 1}',
| voter_constraints = '[+region=us-east1]',
| lease_preferences = '[[+region=us-east1], [+region=us-west1]]'
(1 row)
The lease_preferences field is now updated to include us-west1.
To remove the changes made in this example, reset the Zone Config Extensions.
Failover regions
In this example we will use Zone Config Extensions to configure a multi-region MovR database so that if the primary region fails, enough replicas will be found in another region (the "failover" region) to take over for the primary region. We will set the locality scope for this configuration to be REGIONAL IN, which covers all REGIONAL BY TABLE IN {region} tables and all {region} partitions of REGIONAL BY ROW tables.
This functionality is already provided by the built-in Secondary regions feature. It is used here to show the flexibility of Zone Config Extensions. We strongly recommend using the built-in multi-region features whenever possible.
First, set the database to have a REGION survival goal using ALTER DATABASE ... SURVIVE REGION FAILURE:
ALTER DATABASE movr SURVIVE REGION FAILURE;
Next, apply the REGIONAL BY ROW locality to the movr.rides table using the following statement.
ALTER TABLE rides ADD COLUMN region crdb_internal_region AS (
CASE WHEN city = 'amsterdam' THEN 'europe-west1'
WHEN city = 'paris' THEN 'europe-west1'
WHEN city = 'rome' THEN 'europe-west1'
WHEN city = 'new york' THEN 'us-east1'
WHEN city = 'boston' THEN 'us-east1'
WHEN city = 'washington dc' THEN 'us-east1'
WHEN city = 'san francisco' THEN 'us-west1'
WHEN city = 'seattle' THEN 'us-west1'
WHEN city = 'los angeles' THEN 'us-west1'
END
) STORED;
ALTER TABLE rides ALTER COLUMN REGION SET NOT NULL;
ALTER TABLE rides SET LOCALITY REGIONAL BY ROW AS "region";
Next, view the zone configs for the movr.rides table using SHOW ZONE CONFIGURATION:
SHOW ZONE CONFIGURATION FROM TABLE movr.rides;
target | raw_config_sql
----------------+-------------------------------------------------------------------------------------------
DATABASE movr | ALTER DATABASE movr CONFIGURE ZONE USING
| range_min_bytes = 134217728,
| range_max_bytes = 536870912,
| gc.ttlseconds = 90000,
| num_replicas = 5,
| num_voters = 5,
| constraints = '{+region=europe-west1: 1, +region=us-east1: 1, +region=us-west1: 1}',
| voter_constraints = '{+region=us-east1: 2}',
| lease_preferences = '[[+region=us-east1]]'
(1 row)
Remember that we configured us-east1 to be our primary region during cluster setup. The output above confirms that us-east1 is the primary region based on the values of the voter_constraints and lease_preferences keys.
Next, we would like to configure us-west1 to be the failover region for us-east1, so we will update the configuration to keep additional voting replicas and leaseholders in us-west1. The following SQL statement accomplishes this by configuring us-east1 to keep additional voting replicas and leaseholders in us-west1. This means that if us-east1 fails, it will fail over to us-west1.
ALTER DATABASE movr ALTER LOCALITY REGIONAL IN "us-east1" CONFIGURE ZONE USING voter_constraints = '{+region=us-east1: 2, +region=us-west1: 2}', lease_preferences = '[[+region=us-east1], [+region=us-west1]]';
After updating the configuration as shown above, view the zone configs for the movr.rides table using SHOW ZONE CONFIGURATION:
SHOW ZONE CONFIGURATION FROM TABLE movr.rides;
target | raw_config_sql
----------------+-------------------------------------------------------------------------------------------
DATABASE movr | ALTER DATABASE movr CONFIGURE ZONE USING
| range_min_bytes = 134217728,
| range_max_bytes = 536870912,
| gc.ttlseconds = 90000,
| num_replicas = 5,
| num_voters = 5,
| constraints = '{+region=europe-west1: 1, +region=us-east1: 1, +region=us-west1: 1}',
| voter_constraints = '{+region=us-east1: 2, +region=us-west1: 2}',
| lease_preferences = '[[+region=us-east1], [+region=us-west1]]'
(1 row)
The following changes are shown:
- There are now 2 voting replicas stored in
us-west1. - There is now a preference that if leases cannot be placed in
us-east1, they should be placed inus-west1.
Both of these changes combine to ensure that if us-east1 goes down, the cluster will still be able to operate until some mitigation is in place.
To remove the zone config changes made in this example, reset the Zone Config Extensions.
Reset a region's Zone Config Extensions
To reset the Zone Config Extension configuration applied to a region to the default settings, enter the following statement:
ALTER DATABASE movr ALTER LOCALITY CONFIGURE ZONE USING DEFAULT;
This will not reset any configuration created by the multi-region abstractions.
Discard a region's Zone Config Extensions
To discard the Zone Config Extension settings from a region, enter the following statement:
ALTER DATABASE movr ALTER LOCALITY CONFIGURE ZONE DISCARD;
When you discard a zone configuration, the objects it was applied to will then inherit a configuration from an object "the next level up"; e.g., if the object whose configuration is being discarded is a table, it will use its parent database's configuration.
However, this statement will not remove any configuration created by the multi-region abstractions.
See also
Was this helpful?
