ALTER DATABASE

The ALTER DATABASE applies a to a database.

This schema change statement is registered as a job. You can view long-running jobs with .

Required privileges

Refer to the respective subcommands.

Synopsis

Parameters

Parameter	Description
database\_name	The name of the database you want to change.

Additional parameters are documented for the respective subcommands.

Subcommands

Subcommand	Description
ADD REGION	Add a region to a .
ADD SUPER REGION	Add a super region made up of a set of such that data from will be stored in only those regions.
ALTER LOCALITY	Persistently modify the configuration generated by the standard on a per-region basis. For advanced users who want to customize their multi-region setup using , a persistent, composable alternative to low-level .
ALTER SUPER REGION	Alter an existing to include a different set of regions. A super region is made up of a set of regions added with ADD REGION such that data from will be stored in only those regions.
CONFIGURE ZONE	for a database.
DROP REGION	Drop a region from a .
DROP SECONDARY REGION	Drop a from a .
DROP SUPER REGION	Drop a super region made up of a set of .
OWNER TO	Change the owner of a database.
PLACEMENT	Configure the replica placement policy for a .
RENAME TO	Change the name of a database.
RESET {session variable}	Reset the session variable values for the database to the system defaults. This syntax is identical to .
SET {session variable}	Set the default session variable values for the database. This syntax is identical to .
SET PRIMARY REGION	Set the primary region of a .
SET SECONDARY REGION	Set the for failover purposes.
SURVIVE {ZONE,REGION} FAILURE	Add a survival goal to a .

ADD REGION

ALTER DATABASE.. ADD REGION adds a to a . While CockroachDB processes an index modification or changing a table to or from a , attempting to drop a region from the database containing that REGIONAL BY ROW table will produce an error. Similarly, while this statement is running, all index modifications and locality changes on tables will be blocked.

In order to add a region with ADD REGION, you must first set a primary database region with SET PRIMARY REGION, or at . For an example showing how to add a primary region with ALTER DATABASE, see Set the primary region.

For examples, see Manage regions.

Required privileges

To add a region to a database, the user must have one of the following:

• Membership to the role for the cluster.
• Either or the for the database and all tables in the database.

Parameters

Parameter	Description
region\_name	The being added to this database. Allowed values include any region present in .

For usage, see Synopsis.

ADD SUPER REGION

ALTER DATABASE.. ADD SUPER REGION adds a to a .

This feature is in and subject to change. To share feedback and/or issues, contact Support.

To use super regions, keep the following considerations in mind:

• Your cluster must be a .
• Super regions .
• Super regions can only contain one or more that have already been added with .
• Each database region can only belong to one super region. In other words, given two super regions A and B, the set of database regions in A must be disjoint from the set of database regions in B.
• You cannot that is part of a super region until you either to remove it, or altogether.

For examples, see Manage super regions.

Required privileges

To add a super region to a database, the user must have one of the following:

• Membership to the role for the cluster.
• Either or the for the database.

Parameters

Parameter	Description
region\_name	The name of the being added to this database.
region\_name\_list	The super region consists of this set of .

For usage, see Synopsis.

ALTER LOCALITY

ALTER DATABASE.. ALTER LOCALITY provides a customization tool for advanced users to persistently modify the configuration generated by the standard 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 .

For examples, see Use Zone Config Extensions.

Required privileges

The user must be a member of the or roles, or have the on the database.

Parameters

Parameter	Description
GLOBAL	Apply the Zone Configuration Extension to .
REGIONAL	Apply the Zone Configuration Extension to all and tables.
REGIONAL IN	Apply the Zone Configuration Extension to all REGIONAL BY TABLE IN {region} tables and all of REGIONAL BY ROW tables).
variable	The name of the to change for the in the specified .
value	The value of the to change for the in the specified .

For usage, see Synopsis.

ALTER SUPER REGION

ALTER DATABASE.. ALTER SUPER REGION alters an existing of a .

This feature is in and subject to change. To share feedback and/or issues, contact Support.

To use super regions, keep the following considerations in mind:

• Your cluster must be a .
• Super regions .
• Super regions can only contain one or more that have already been added with .
• Each database region can only belong to one super region. In other words, given two super regions A and B, the set of database regions in A must be disjoint from the set of database regions in B.
• You cannot that is part of a super region until you either to remove it, or altogether.

For examples, see Manage super regions.

Required privileges

To alter a database’s super region, the user must have one of the following:

• Membership to the role for the cluster.
• Either or the for the database.

Parameters

Parameter	Description
region\_name	The name of the being altered.
region\_name\_list	The altered super region will consist of this set of .

For usage, see Synopsis.

CONFIGURE ZONE

ALTER DATABASE... CONFIGURE ZONE is used to add, modify, reset, or remove replication zones for a database. To view details about existing replication zones, use . For more information about replication zones, see .

If you directly change a database’s zone configuration with ALTER DATABASE... CONFIGURE ZONE, CockroachDB will block all ALTER DATABASE... SET PRIMARY REGION statements on the database.

Cockroach Labs .

Most users should use instead. If additional control is needed, use to augment the multi-region SQL statements.

You can use replication zones to control the number and location of replicas for specific sets of data, both when replicas are first added and when they are rebalanced to maintain cluster equilibrium.

For examples, see Replication Controls.

Required privileges

The user must be a member of the or have been granted or privileges. To configure , the user must be a member of the admin role.

Parameters

Parameter	Description
variable	The name of the to change.
value	The value of the to change.
DISCARD	Remove a replication zone.

For usage, see Synopsis.

DROP REGION

ALTER DATABASE.. DROP REGION drops a from a . While CockroachDB processes an index modification or changing a table to or from a , attempting to drop a region from the database containing that REGIONAL BY ROW table will produce an error. Similarly, while this statement is running, all index modifications and locality changes on tables will be blocked.

You cannot drop a region from a multi-region database if:

• The region is set as the primary region, unless it is the last remaining region.
• The region is set as the secondary region. To drop the region, you must first unset the secondary region using DROP SECONDARY REGION.
• The database uses the and there are only three regions configured on the database.

For examples, see Manage regions.

Required privileges

To drop a region from a database, the user must have one of the following:

• Membership to the role for the cluster.
• Membership to the role, or the , for the database and all tables in the database.

Parameters

Parameter	Description
region\_name	The being dropped from this database. Allowed values include any region present in . You can only drop the primary region from a multi-region database if it is the last remaining region.

For usage, see Synopsis.

DROP SECONDARY REGION

ALTER DATABASE.. DROP SECONDARY REGION unsets the (if set) from a .

The secondary region is used for failover purposes: if the primary region fails, the secondary region acts as the new primary region. For more information, see .

For examples, see Manage regions.

Required privileges

To unset a secondary region on a database, the user must have one of the following:

• Membership to the role for the cluster.
• Membership to the role, or the for the database.

DROP SUPER REGION

ALTER DATABASE.. DROP SUPER REGION drops a from a .

This feature is in and subject to change. To share feedback and/or issues, contact Support.

To use super regions, keep the following considerations in mind:

• Your cluster must be a .
• Super regions .
• Super regions can only contain one or more that have already been added with .
• Each database region can only belong to one super region. In other words, given two super regions A and B, the set of database regions in A must be disjoint from the set of database regions in B.
• You cannot that is part of a super region until you either to remove it, or altogether.

For examples, see Manage super regions.

Required privileges

To drop a super region from a database, the user must have one of the following:

• Membership to the role for the cluster.
• Either or the for the database.

Parameters

Parameter	Description
region\_name	The name of the being dropped from this database.

For usage, see Synopsis.

OWNER TO

ALTER DATABASE... OWNER TO is used to change the owner of a database.

For examples, see Change database owner.

Required privileges

To change the owner of a database, the user must be an admin user, or the current owner of the database and a member of the new owner . The user must also have the CREATEDB .

Parameters

Parameter	Description
role\_spec	The role to set as the owner of the database.

For usage, see Synopsis.

PLACEMENT

ALTER DATABASE... PLACEMENT is used to set the replica placement policy (DEFAULT or RESTRICTED) for a ’s . are those with or localities.

ALTER DATABASE... PLACEMENT RESTRICTED constrains the voting and non-voting replica placement for the database’s tables to the associated with those tables. This is a way of opting out of for to accomplish one or more of the following goals:

• Implement a strategy.
• Reduce the amount of data stored on the cluster.
• Reduce the overhead of replicating data across a large number of regions (e.g., 10 or more) for databases with heavier write loads.

Note that ALTER DATABASE... PLACEMENT RESTRICTED does not allow you to opt out of placing entirely. For example, in the database will remain unaffected by this statement. GLOBAL tables are designed to have replicas placed across all available to ensure fast local reads.

For examples, see Set replica placement policy.

Required privileges

To use this statement, the user must have one of the following:

• Membership to the role for the cluster.
• or the for the database and all tables in the database.

Parameters

Parameter	Description
DEFAULT	Set the replica placement policy for to DEFAULT.
RESTRICTED	Set the replica placement policy for to RESTRICTED.

For usage, see Synopsis.

The replica placement policies work as follows:

- DEFAULT (Default): If the replica placement policy is set to DEFAULT, CockroachDB will use its default replica placement settings, which mean that:

• Data will be placed in as many regions as necessary to ensure your are met.
• You can get fast stale reads from all . - RESTRICTED: If the replica placement policy is set to RESTRICTED, CockroachDB will constrain replica placement to only those regions where the table has voting replicas (that is, replicas which participate in the ). In practice, this means that voting replicas for the table will be constrained to the table’s . Specifically, for tables, it will only place replicas in the defined region (or the database’s primary region); for tables, it will only place replicas for each underlying in the partition’s specified region. Finally, note that:
• Regional tables with this placement setting will no longer provide “fast stale reads” from other (non-home) regions, since fast stale reads rely on the presence of non-voting replicas.
• The RESTRICTED replica placement policy is only available for databases with the .
• This setting does not affect how work; they will still place replicas in all .

RENAME TO

ALTER DATABASE... RENAME TO changes the name of a database.

It is not possible to rename a database if the database is referenced by a . For more details, see .

For examples, see Rename database.

Required privileges

To rename a database, the user must be a member of the admin role or must have the parameter set.

Parameters

Parameter	Description
database\_new\_name	The you want to use for the database. The new name must be unique and follow these . You cannot rename a database if it is set as the or if .

For usage, see Synopsis.

RESET session variable

ALTER DATABASE... RESET session variable clears a database-level override of a so that future sessions use the default value.

For a list of the session variables that have been updated from default values, see .

Required privileges

No are required to reset a session setting.

Parameters

Parameter	Description
session\_var	The name of the .

For usage, see Synopsis.

Aliases

In CockroachDB, the following are aliases for ALTER DATABASE... RESET session variable:

SET session variable

ALTER DATABASE... SET session variable sets the default value of a for all future sessions on the database. This command does not alter the session setting of the current session.

For a list of the session variables that have been updated from default values, see .

Required privileges

To set the role session variable, the current user must be a member of the admin role, or a member of the target role.

All other session variables do not require to modify.

Parameters

Parameter	Description
variable	The name of to set. The variable name is case-insensitive.
value	The value, or list of values, to assign to the session variable.

For usage, see Synopsis.

Aliases

In CockroachDB, the following are aliases for ALTER DATABASE... SET session variable:

• ALTER ROLE ALL IN DATABASE... SET session variable

For more information, refer to .

Session variable precedence

When a starts, CockroachDB determines the initial value of each by evaluating the settings in the following order (items earlier in the list take precedence over later items):

1. : A value supplied as a query parameter in the connection URL (for example, .../movr?sslmode=disable&timezone=UTC ).
2. : A value set by ALTER ROLE {role_name} IN DATABASE {db_name} SET {var}={value}.
3. : A value set by ALTER ROLE {role_name} SET {var}={value}.
4. : A value set by ALTER ROLE ALL IN DATABASE {db_name} SET {var}={value} or equivalently by ALTER DATABASE {db_name} SET {var}={value}.
5. : A value set by ALTER ROLE ALL SET {var}={value}.

If a session variable is not modified using any of the preceding methods, the built-in system default value is used. Note that the is exempt from settings 3–5. The root user is only affected by values specified in the connection string.

You can also set session variables for the duration of a single transaction by using .

Changes to defaults using the preceding methods only apply to future sessions. This is because session variable resolution happens at session start time. To change a default value in an existing open session, set the variable explicitly with .

SET PRIMARY REGION

ALTER DATABASE.. SET PRIMARY REGION sets the primary of a .

If a database’s has been directly set with an ALTER DATABASE... CONFIGURE ZONE statement, CockroachDB will block all ALTER DATABASE... SET PRIMARY REGION statements on the database.

To remove existing, manually-configured zones from a database (and unblock SET PRIMARY REGION statements on the database), use an ALTER DATABASE... CONFIGURE ZONE DISCARD statement.

allow you to define a that will be used for failover in the event your primary region goes down. For more information, see .

For examples, see Manage regions.

Required privileges

To add a primary region to a database with no existing regions, the user must have one of the following:

• Membership to the role for the cluster.
• Membership to the role, or the , for the database and all tables in the database.

To switch primary regions to a region that has already been added to a database, the user must have membership to the role for the database, or have the on the database.

Parameters

Parameter	Description
region\_name	The region to set as the database’s primary region. Allowed values include any region present in .

For usage, see Synopsis.

SET SECONDARY REGION

ALTER DATABASE.. SET SECONDARY REGION adds a to a for failover purposes.

If the primary region fails, the secondary region becomes the new primary region. For more information, see .

In order to add a secondary region with ALTER DATABASE... SET SECONDARY REGION, you must first set a primary database region with SET PRIMARY REGION, or when . For an example showing how to add a secondary region with ALTER DATABASE, see Set the secondary region.

are not compatible with databases containing tables. CockroachDB does not prevent you from defining secondary regions on databases with regional by row tables, but the interaction of these features is not supported.

Therefore, Cockroach Labs recommends that you avoid defining secondary regions on databases that use regional by row table configurations. For examples, see Manage regions.

Required privileges

To add a secondary region to a database, the user must have one of the following:

• Membership to the role for the cluster.
• Either or the for the database.

Parameters

Parameter	Description
region\_name	Usually, the being set as the secondary region for this database. E.g., "ap-southeast-2". Allowed values include any (non-primary) region present in SHOW REGIONS.

For usage, see Synopsis.

SURVIVE {ZONE,REGION} FAILURE

ALTER DATABASE... SURVIVE {ZONE,REGION} FAILURE sets the for a .

For examples, see Configure survival goals.

Required privileges

The user must be a member of the or roles, or have the on the database.

Parameters

Parameter	Description
ZONE	Set the to zone failure.
REGION	Set the to region failure.

For usage, see Synopsis.

Examples

Manage regions

Setup

Only a specified can be used as a .

To follow along with the examples in this section, start a with the to simulate a multi-region cluster:

$ cockroach demo --global --nodes 9

To see the regions available to the databases in the cluster, use a statement:

SHOW REGIONS FROM CLUSTER;

     region    |  zones
---------------+----------
  europe-west1 | {b,c,d}
  us-east1     | {b,c,d}
  us-west1     | {a,b,c}
(3 rows)

Set the primary region

Suppose you want to make the database movr a multi-region database.

To add the first region to the database, or to set an already-added region as the primary region, use a SET PRIMARY REGION statement:

ALTER DATABASE movr SET PRIMARY REGION "us-east1";

ALTER DATABASE PRIMARY REGION

Given a cluster with multiple regions, any databases in that cluster that have not yet had their primary regions set will have their replicas spread as broadly as possible for resiliency. When a primary region is added to one of these databases:

• All tables will be in the primary region by default.
• This means that all such tables will have all of their voting replicas and leaseholders moved to the primary region. This process is known as .

Add regions to a database

To add more regions to a database that already has at least one region:

ALTER DATABASE movr ADD region "us-west1";

ALTER DATABASE ADD REGION

ALTER DATABASE movr ADD region "europe-west1";

ALTER DATABASE ADD REGION

To view the regions associated with a multi-region database, use a statement:

SHOW REGIONS FROM DATABASE movr;

  database |    region    | primary | secondary |  zones
-----------+--------------+---------+-----------+----------
  movr     | us-east1     |    t    |     f     | {b,c,d}
  movr     | europe-west1 |    f    |     f     | {b,c,d}
  movr     | us-west1     |    f    |     f     | {a,b,c}
(3 rows)

Set the secondary region

To set an existing (that is not already the primary region) as the , use the following statement:

ALTER DATABASE movr SET SECONDARY REGION "us-west1";

ALTER DATABASE SET SECONDARY REGION

Now, the "us-west1" region will act as the primary region if the original primary region fails.

are not compatible with databases containing tables. CockroachDB does not prevent you from defining secondary regions on databases with regional by row tables, but the interaction of these features is not supported.

Therefore, Cockroach Labs recommends that you avoid defining secondary regions on databases that use regional by row table configurations.

Unset the secondary region

To unset an existing as the on a multi-region database, use the DROP SECONDARY REGION statement:

ALTER DATABASE movr DROP SECONDARY REGION;

ALTER DATABASE DROP SECONDARY REGION

This statement does not drop the region from the database. To drop a region, use DROP REGION.

Change the existing primary region

To change the primary region to another region in the database, use a SET PRIMARY REGION statement.

ALTER DATABASE movr SET PRIMARY REGION "europe-west1";

ALTER DATABASE PRIMARY REGION

You can only change an existing primary region to a region that has already been added to the database. If you try to change the primary region to a region that is not already associated with a database, CockroachDB will return an error.

Drop a region from a database

To drop a region from a multi-region database, use a DROP REGION statement.

ALTER DATABASE movr DROP REGION "us-east1";

ALTER DATABASE DROP REGION

You cannot drop a region from a multi-region database if:

• The region is set as the primary region, unless it is the last remaining region.
• The region is set as the secondary region. To drop the region, you must first unset the secondary region using DROP SECONDARY REGION.
• The database uses the and there are only three regions configured on the database.

Manage super regions

Setup

Only a specified can be used as a .

To follow along with the examples in this section, start a with the to simulate a multi-region cluster:

$ cockroach demo --global --nodes 9

To see the regions available to the databases in the cluster, use a statement:

SHOW REGIONS FROM CLUSTER;

     region    |  zones
---------------+----------
  europe-west1 | {b,c,d}
  us-east1     | {b,c,d}
  us-west1     | {a,b,c}
(3 rows)

Set up MovR database regions

Execute the following statements. They will tell CockroachDB about the database’s regions. This information is necessary so that CockroachDB can later move data around to optimize access to particular data from particular regions. For more information about how this works at a high level, see .

ALTER DATABASE movr PRIMARY REGION "us-east1";
ALTER DATABASE movr ADD REGION "europe-west1";
ALTER DATABASE movr ADD REGION "us-west1";

Set up MovR global tables

Because the data in promo_codes is not updated frequently (a.k.a., “read-mostly”), and needs to be available from any region, the right table locality is .

ALTER TABLE promo_codes SET locality GLOBAL;

Next, alter the user_promo_codes table to have a foreign key into the global promo_codes table. This will enable fast reads of the promo_codes.code column from any region in the cluster.

ALTER TABLE user_promo_codes
  ADD CONSTRAINT user_promo_codes_code_fk
    FOREIGN KEY (code)
    REFERENCES promo_codes (code)
    ON UPDATE CASCADE;

Set up MovR regional tables

All of the tables except promo_codes contain rows which are partitioned by region, and updated very frequently. For these tables, the right table locality for optimizing access to their data is .

Apply this table locality to the remaining tables. These statements use a CASE statement to put data for a given city in the right region and can take around 1 minute to complete for each table.

• rides

  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";
  

• user_promo_codes

  ALTER TABLE user_promo_codes 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 user_promo_codes ALTER COLUMN REGION SET NOT NULL;
  ALTER TABLE user_promo_codes SET LOCALITY REGIONAL BY ROW AS "region";
  

• users

  ALTER TABLE users 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 users ALTER COLUMN REGION SET NOT NULL;
  ALTER TABLE users SET LOCALITY REGIONAL BY ROW AS "region";
  

• vehicle_location_histories

  ALTER TABLE vehicle_location_histories 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 vehicle_location_histories ALTER COLUMN REGION SET NOT NULL;
  ALTER TABLE vehicle_location_histories SET LOCALITY REGIONAL BY ROW AS "region";
  

• vehicles

  ALTER TABLE vehicles 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 vehicles ALTER COLUMN REGION SET NOT NULL;
  ALTER TABLE vehicles SET LOCALITY REGIONAL BY ROW AS "region";
  

Enable super regions

To enable super regions, set the enable_super_regions to 'on':

SET enable_super_regions = 'on';

SET

You can also set the sql.defaults.super_regions.enabled to true:

SET CLUSTER SETTING  sql.defaults.super_regions.enabled = true;

SET CLUSTER SETTING

Add a super region to a database

To add a super region to a multi-region database, use the ALTER DATABASE... ADD SUPER REGION statement:

ALTER DATABASE movr ADD SUPER REGION "usa" VALUES "us-east1", "us-west1";

ALTER DATABASE ADD SUPER REGION

Alter a super region

This example assumes you have already added a "usa" super region as shown in the example Add a super region to a database. If you wanted to drop the region us-west1, you would first need to remove it from the super region.

To remove a region from a super region, use the ALTER DATABASE... ALTER SUPER REGION statement and list only the regions that should remain in the super region:

ALTER DATABASE movr ALTER SUPER REGION "usa" VALUES "us-east1";

ALTER DATABASE ALTER SUPER REGION

To add a region to a super region, alter the super region as shown above to be a list of regions that includes the existing and the new regions.

Allow user to modify a primary region that is part of a super region

By default, you may not change the of a when that region is part of a super region. This is a safety setting designed to prevent you from accidentally moving the data for a that is meant to be stored in the super region out of that super region, which could break your data domiciling setup.

If you are sure about what you are doing, you can allow modifying the primary region by setting the alter_primary_region_super_region_override to 'on':

SET alter_primary_region_super_region_override = 'on';

SET

You can also accomplish this by setting the sql.defaults.alter_primary_region_super_region_override.enable to true:

SET CLUSTER SETTING  sql.defaults.alter_primary_region_super_region_override.enable = true;

SET CLUSTER SETTING

Drop a super region from a database

To drop a super region from a multi-region database, use a DROP SUPER REGION statement:

ALTER DATABASE movr DROP SUPER REGION "usa";

ALTER DATABASE DROP SUPER REGION

Note that you cannot drop a region that is part of a super region until you either alter the super region to remove it, or drop the super region altogether.

For example, using the super region that was added in ADD SUPER REGION:

ALTER DATABASE movr DROP REGION "us-west1";

ERROR: region us-west1 is part of super region usa
SQLSTATE: 2BP01
HINT: you must first drop super region usa before you can drop the region us-west1

Configure replication zones

Cockroach Labs .

Most users should use instead. If additional control is needed, use to augment the multi-region SQL statements.

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 .

To follow along, run 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

Create a 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)

Edit a replication zone

ALTER DATABASE movr CONFIGURE ZONE USING range_min_bytes = 0, range_max_bytes = 90000, gc.ttlseconds = 89999, num_replicas = 4;

Remove a replication zone

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.

You cannot DISCARD any zone configurations on multi-region tables, indexes, or partitions if the created the zone configuration.

ALTER DATABASE movr CONFIGURE ZONE DISCARD;

Troubleshoot replication zones

For instructions showing how to troubleshoot replication zones that may be misconfigured, see .

Use Zone Config Extensions

The following examples show:

• How to override specific fields of a schema object’s zone configs.
• How something like the 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.
• How you could implement super regions.
• How to minimize cross-region write latency.

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 following examples.

Start a cluster

Start a cluster as follows:

cockroach demo --global --nodes=9

This gives us a (preloaded) database on a cluster with the following , which can be viewed with :

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 as follows:

1. Set the primary region using SET PRIMARY REGION.
2. 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 database to update its 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.

Implement super regions

In this example, are used to provide an alternative implementation of , which are useful for .

For this example, you need to start a using the following command, which :

cockroach demo --nodes=9 --demo-locality=region=us-east1:region=us-east1:region=us-central1:region=us-west1:region=europe-west1:region=europe-west1:region=europe-central1:region=europe-east1:region=europe-east1

Next, enter the following statements at the SQL prompt. These statements will:

• Make the into a by .
• Set the movr database to .
• Add the remaining regions defined at cluster start time to the multi-region movr database (for a total of 6).

ALTER DATABASE movr SET PRIMARY REGION "us-east1";
ALTER DATABASE movr ADD REGION "us-central1";
ALTER DATABASE movr ADD REGION "us-west1";
ALTER DATABASE movr ADD REGION "europe-west1";
ALTER DATABASE movr ADD REGION "europe-central1";
ALTER DATABASE movr ADD REGION "europe-east1";
ALTER DATABASE movr SURVIVE REGION FAILURE;

Now that the multi-region cluster is configured, we will use the following ALTER LOCALITY statements to such that:

• in the us-east1 region are only stored on nodes in the US.
• Replicas in the europe-central1 region are only stored on nodes in the EU.

ALTER DATABASE movr ALTER LOCALITY REGIONAL IN "us-east1" CONFIGURE ZONE USING
    num_replicas = 7,
    constraints = '{+region=us-east1: 3,
                    +region=us-central1: 3,
                    +region=us-west1: 1}',
    lease_preferences = '[[+region=us-east1], [+region=us-central1]]';

ALTER DATABASE movr ALTER LOCALITY REGIONAL IN "europe-central1" CONFIGURE ZONE USING
    num_replicas = 7,
    constraints = '{+region=europe-central1: 3,
                    +region=europe-west1: 3,
                    +region=europe-east1: 1}',
    lease_preferences = '[[+region=europe-central1], [+region=europe-west1]]';

Minimize cross-region write latency

In this example, are used to minimize cross-region write latency in a cluster with the following characteristics:

• Three main US regions ( us-west1, us-east1, us-central1 ).
• Two additional distant regions ( europe-west1, asia-northeast1 ). Only will be made from these regions.

To minimize the cross-region write latency, such that the 3 US regions have voting replicas (that is, replicas that participate in ). This will leave the 2 additional non-US regions with only .

For this example, you need to start a using the following command, which :

cockroach demo --nodes=5 --demo-locality=region=us-east1:region=us-west1:region=us-central1:region=europe-west1:region=asia-northeast1

Next, enter the following statements at the SQL prompt. These statements will:

• Make the into a by .
• Set the movr database to .
• Add the remaining regions defined at cluster start time to the multi-region movr database (for a total of 5).

ALTER DATABASE movr SET PRIMARY REGION "us-west1";
ALTER DATABASE movr ADD REGION "us-central1";
ALTER DATABASE movr ADD REGION "us-east1";
ALTER DATABASE movr ADD REGION "europe-west1";
ALTER DATABASE movr ADD REGION "asia-northeast1";
ALTER DATABASE movr SURVIVE REGION FAILURE;

By default, all tables in the movr database are :

SHOW TABLES;

  schema_name |         table_name         | type  | owner | estimated_row_count |              locality
--------------+----------------------------+-------+-------+---------------------+--------------------------------------
  public      | promo_codes                | table | demo  |                1000 | REGIONAL BY TABLE IN PRIMARY REGION
  public      | rides                      | table | demo  |                 500 | REGIONAL BY TABLE IN PRIMARY REGION
  public      | user_promo_codes           | table | demo  |                   5 | REGIONAL BY TABLE IN PRIMARY REGION
  public      | users                      | table | demo  |                  50 | REGIONAL BY TABLE IN PRIMARY REGION
  public      | vehicle_location_histories | table | demo  |                1000 | REGIONAL BY TABLE IN PRIMARY REGION
  public      | vehicles                   | table | demo  |                  15 | REGIONAL BY TABLE IN PRIMARY REGION
(6 rows)

Now that the multi-region cluster is configured, we will use the following ALTER LOCALITY statements to such that:

• The 3 US regions have voting replicas (that is, replicas that participate in ).
• The 2 additional regions (Europe and Japan) are left with only that can be used to serve .

ALTER DATABASE movr ALTER LOCALITY REGIONAL CONFIGURE ZONE USING voter_constraints = '{+region=us-west1: 2, +region=us-central1: 2, +region=us-east1: 1}';

Failover regions

In this example we will use Zone Config Extensions to configure a multi-region database so that if the primary region fails, enough 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 and all of .

This functionality is already provided by the built-in feature. It is used here to show the flexibility of Zone Config Extensions. We strongly recommend using the whenever possible.

1. Set the database to have a REGION survival goal using :

    ALTER DATABASE movr SURVIVE REGION FAILURE;
   

2. 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";
   

3. View the for the movr.rides table using :

    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 and keys.

4. Update the configuration to keep additional voting replicas and leaseholders in us-west1. We do this because we would like to configure us-west1 to be the failover region for us-east1. 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]]';
   

5. View the for the movr.rides table using :

    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 in us-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 .

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 .

Troubleshoot Zone Config Extensions

The process for troubleshooting Zone Config Extensions is the same as troubleshooting any other changes to zone configs.

For instructions showing how to troubleshoot replication zones that may be misconfigured, see .

Change database owner

Setup

To follow along, run to start a temporary, in-memory cluster with the sample dataset preloaded:

$ cockroach demo

Change a database’s owner

Suppose that the current owner of the movr database is root and you want to change the owner to a new user named max.

> ALTER DATABASE movr OWNER TO max;

To verify that the owner is now max, query the pg_catalog.pg_database and pg_catalog.pg_roles tables:

> SELECT rolname FROM pg_catalog.pg_database d JOIN pg_catalog.pg_roles r ON d.datdba = r.oid WHERE datname = 'movr';

  rolname
-----------
  max
(1 row)

If the user running the command is not an admin user, they must own the database and be a member of the new owning role. They must also have the CREATEDB .

Set replica placement policy

Setup

To follow along with the examples below:

1. Start a with the to simulate a multi-region cluster:

   cockroach demo --global --nodes 9
   

2. Set the demo cluster’s and as described in (specifically, starting at ).

3. Enable the replica placement syntax with either the or the as shown below.

   1. To use the session variable:

      SET enable_multiregion_placement_policy = on;
      

   2. To use the cluster setting:

      SET CLUSTER SETTING sql.defaults.multiregion_placement_policy.enabled = on;
      

Use instead of the sql.defaults.* . This allows you to set a default value for all users for any that applies during login, making the sql.defaults.* cluster settings redundant.

Create a database with the replica placement policy set to restricted

If you know at database creation time that you’d like to set the database’s replica placement policy to “restricted”, you can do so in a statement as shown below:

CREATE DATABASE movr2 PRIMARY REGION "us-east1" REGIONS "us-west1", "europe-west1" PLACEMENT RESTRICTED;

CREATE DATABASE

Set the replica placement policy to RESTRICTED

When you set the database’s placement policy to RESTRICTED, you are saying that you want the underlying data to be restricted to the table or partition’s .

ALTER DATABASE movr PLACEMENT RESTRICTED;

ALTER DATABASE PLACEMENT

Set the replica placement policy to DEFAULT

If previously you set the replica placement policy to RESTRICTED, you can set it back to the default by issuing the following statement:

ALTER DATABASE movr PLACEMENT DEFAULT;

ALTER DATABASE PLACEMENT

Rename database

Rename a database

> CREATE DATABASE db1;

> SHOW DATABASES;

  database_name
-----------------
  db1
  defaultdb
  movr
  postgres
  system
(5 rows)

> ALTER DATABASE db1 RENAME TO db2;

> SHOW DATABASES;

  database_name
-----------------
  db2
  defaultdb
  movr
  postgres
  system
(5 rows)

Configure survival goals

Survive zone failures

To change the survival goal of a multi-region database to survive zone failures, use the following statement:

ALTER DATABASE {db} SURVIVE ZONE FAILURE;

ALTER DATABASE SURVIVE

Surviving zone failures is the default setting for multi-region databases.

For more information about the zone survival goal, see .

Survive region failures

To change the survival goal of a multi-region database to survive region failures, use the following statement:

ALTER DATABASE {db} SURVIVE REGION FAILURE;

ALTER DATABASE SURVIVE

If you try to change a database with less than 3 to survive region failures, the following error will be signalled:

ERROR: at least 3 regions are required for surviving a region failure
SQLSTATE: 42602
HINT: you must add additional regions to the database using ALTER DATABASE mr ADD REGION <region_name>

For more information about the region survival goal, see .

See also