> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# ALTER DATABASE

The `ALTER DATABASE` [statement](/docs/stable/sql-statements.md) applies a [schema change](/docs/stable/online-schema-changes.md) to a database.

> [!NOTE]
>
> This schema change statement is registered as a job. You can view long-running jobs with [`SHOW JOBS`](/docs/stable/show-jobs).

## Required privileges

Refer to the respective [subcommands](#subcommands).

## Synopsis

![alter\_database syntax diagram](https://mintcdn.com/cockroachlabs/VQ_w3c6MHYdiS7Tn/images/sql-diagrams/common/alter_database.svg?fit=max\&auto=format\&n=VQ_w3c6MHYdiS7Tn\&q=85\&s=1d3f1704bf9b13cb35b7f948e37f1535)

## Parameters

| Parameter | Description |
| - | - |
| `database_name` | The name of the database you want to change. |

Additional parameters are documented for the respective [subcommands](#subcommands).

## Subcommands

| Subcommand | Description |
| - | - |
| [`ADD REGION`](#add-region) | Add a region to a [multi-region database](/docs/stable/multiregion-overview.md). |
| [`ADD SUPER REGION`](#add-super-region) | Add a super region made up of a set of [database regions](/docs/stable/multiregion-overview.md#super-regions) such that data from [regional tables](/docs/stable/regional-tables.md) will be stored in only those regions. |
| [`ALTER LOCALITY`](#alter-locality) | Persistently modify the configuration generated by the standard [multi-region SQL abstractions](/docs/stable/multiregion-overview.md) for a region, a super region, or a table locality. For advanced users who want to customize their multi-region setup using [Zone Config Extensions](/docs/stable/zone-config-extensions.md), a persistent, composable alternative to low-level [zone configs](/docs/stable/configure-replication-zones.md). |
| [`ALTER SUPER REGION`](#alter-super-region) | Alter an existing [super region](/docs/stable/multiregion-overview.md#super-regions) to include a different set of regions or to set its survival goal. |
| [`CONFIGURE ZONE`](#configure-zone) | [Replication Controls](/docs/stable/configure-replication-zones.md) for a database. |
| [`DROP REGION`](#drop-region) | Drop a region from a [multi-region database](/docs/stable/multiregion-overview.md). |
| [`DROP SECONDARY REGION`](#drop-secondary-region) | Drop a [secondary region](/docs/stable/multiregion-overview.md#secondary-regions) from a [multi-region database](/docs/stable/multiregion-overview.md). |
| [`DROP SUPER REGION`](#drop-super-region) | Drop a super region made up of a set of [database regions](/docs/stable/multiregion-overview.md#super-regions). |
| [`OWNER TO`](#owner-to) | Change the owner of a database. |
| [`PLACEMENT`](#placement) | Configure the replica placement policy for a [multi-region database](/docs/stable/multiregion-overview.md). |
| [`RENAME TO`](#rename-to) | Change the name of a database. |
| [`RESET {session variable}`](#reset-session-variable) | Reset the session variable values for the database to the system defaults. This syntax is identical to [`ALTER ROLE ALL IN DATABASE ... RESET {session variable}`](/docs/stable/alter-role#set-default-session-variable-values-for-all-users). |
| [`SET {session variable}`](#set-session-variable) | Set the default session variable values for the database. This syntax is identical to [`ALTER ROLE ALL IN DATABASE ... SET {session variable}`](/docs/stable/alter-role#set-default-session-variable-values-for-all-users). |
| [`SET PRIMARY REGION`](#set-primary-region) | Set the primary region of a [multi-region database](/docs/stable/multiregion-overview.md). |
| [`SET SECONDARY REGION`](#set-secondary-region) | Set the [secondary region of a multi-region database](/docs/stable/multiregion-overview.md#secondary-regions) for failover purposes. |
| [`SURVIVE {ZONE,REGION} FAILURE`](#survive-zoneregion-failure) | Add a survival goal to a [multi-region database](/docs/stable/multiregion-overview.md). |

### `ADD REGION`

`ALTER DATABASE .. ADD REGION` adds a [region](/docs/stable/multiregion-overview.md#database-regions) to a [multi-region database](/docs/stable/multiregion-overview.md). While CockroachDB processes an index modification or changing a table to or from a [`REGIONAL BY ROW` table](/docs/stable/table-localities#regional-by-row-tables), 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 [`REGIONAL BY ROW`](/docs/stable/table-localities#regional-by-row-tables) tables will be blocked.

> [!CAUTION]
>
> In order to add a region with `ADD REGION`, you must first set a primary database region with [`SET PRIMARY REGION`](#set-primary-region), or at [database creation](/docs/stable/create-database.md). For an example showing how to add a primary region with `ALTER DATABASE`, see [Set the primary region](#set-the-primary-region).

For examples, see [Manage regions](#manage-regions).

#### Required privileges

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

* Membership to the [`admin`](/docs/stable/security-reference/authorization#admin-role) role for the cluster.
* Either [ownership](/docs/stable/security-reference/authorization.md#object-ownership) or the [`CREATE` privilege](/docs/stable/security-reference/authorization#supported-privileges) for the database and all [`REGIONAL BY ROW`](/docs/stable/table-localities#regional-by-row-tables) tables in the database.

#### Parameters

| Parameter | Description |
| - | - |
| `region_name` | The [region](/docs/stable/multiregion-overview.md#database-regions) being added to this database.  Allowed values include any region present in [`SHOW REGIONS FROM CLUSTER`](/docs/stable/show-regions). |

For usage, see [Synopsis](#synopsis).

### `ADD SUPER REGION`

`ALTER DATABASE ... ADD SUPER REGION` adds a [super region](/docs/stable/multiregion-overview.md#super-regions) to a [multi-region database](/docs/stable/multiregion-overview.md).

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

* Your cluster must be a [multi-region cluster](/docs/stable/multiregion-overview.md).
* Super regions can only contain one or more [database regions](/docs/stable/multiregion-overview.md#database-regions) that have already been added with [`ALTER DATABASE ... ADD REGION`](/docs/stable/alter-database#add-region).
* 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](https://wikipedia.org/wiki/Disjoint_sets) from the set of database regions in *B*.
* You cannot [drop a region](/docs/stable/alter-database.md#drop-region) that is part of a super region until you either [alter the super region](/docs/stable/alter-database.md#alter-super-region) to remove it, or [drop the super region](/docs/stable/alter-database.md#drop-super-region) altogether.

For examples, see [Manage super regions](#manage-super-regions).

#### Required privileges

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

* Membership to the [`admin`](/docs/stable/security-reference/authorization#admin-role) role for the cluster.
* Either [ownership](/docs/stable/security-reference/authorization.md#object-ownership) or the [`CREATE` privilege](/docs/stable/security-reference/authorization#supported-privileges) for the database.

#### Parameters

| Parameter | Description |
| - | - |
| `region_name` | The name of the [super region](/docs/stable/multiregion-overview.md#super-regions) being added to this database. |
| `region_name_list` | The super region consists of this set of [database regions](/docs/stable/multiregion-overview.md#database-regions). |

For usage, see [Synopsis](#synopsis).

### `ALTER LOCALITY`

`ALTER DATABASE ... ALTER LOCALITY` provides a customization tool for advanced users to persistently modify the configuration generated by the standard [multi-region SQL abstractions](/docs/stable/multiregion-overview.md) for a region, a super region, or a table locality.

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`](#configure-zone) statement, see [Zone Config Extensions](/docs/stable/zone-config-extensions.md).

For examples, see [Use Zone Config Extensions](#use-zone-config-extensions).

#### Required privileges

The user must be a member of the [`admin`](/docs/stable/security-reference/authorization#roles) or [owner](/docs/stable/security-reference/authorization.md#object-ownership) roles, or have the [`CREATE` privilege](/docs/stable/security-reference/authorization#supported-privileges) on the database.

#### Parameters

| Parameter | Description |
| - | - |
| `GLOBAL` | Apply the Zone Configuration Extension to [global tables](/docs/stable/table-localities.md#global-tables). |
| `REGIONAL` | Apply the Zone Configuration Extension to all [`REGIONAL BY TABLE`](/docs/stable/table-localities#regional-tables) and [`REGIONAL BY ROW`](/docs/stable/table-localities#regional-by-row-tables) tables. |
| `REGIONAL IN` | Apply the Zone Configuration Extension to all `REGIONAL BY TABLE IN {region}` tables and all `{region}` [partitions](/docs/stable/partitioning.md) of `REGIONAL BY ROW` tables). |
| `SUPER REGION` | Apply the Zone Configuration Extension to `REGIONAL BY TABLE` tables and `REGIONAL BY ROW` partitions whose home region belongs to the specified super region. The extension does not apply to `GLOBAL` tables. |
| `variable` | The name of the [replication zone variable](/docs/stable/configure-replication-zones.md#replication-zone-variables) to change for the [schema objects](/docs/stable/schema-design-overview.md) in the specified [region](/docs/stable/multiregion-overview.md#database-regions). |
| `value` | The value of the [replication zone variable](/docs/stable/configure-replication-zones.md#replication-zone-variables) to change for the [schema objects](/docs/stable/schema-design-overview.md) in the specified [region](/docs/stable/multiregion-overview.md#database-regions). |

For usage, see [Synopsis](#synopsis).

### `ALTER SUPER REGION`

`ALTER DATABASE ... ALTER SUPER REGION` changes the member regions or survival goal of an existing [super region](/docs/stable/multiregion-overview.md#super-regions) in a [multi-region database](/docs/stable/multiregion-overview.md).

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

* Your cluster must be a [multi-region cluster](/docs/stable/multiregion-overview.md).
* Super regions can only contain one or more [database regions](/docs/stable/multiregion-overview.md#database-regions) that have already been added with [`ALTER DATABASE ... ADD REGION`](/docs/stable/alter-database#add-region).
* 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](https://wikipedia.org/wiki/Disjoint_sets) from the set of database regions in *B*.
* You cannot [drop a region](/docs/stable/alter-database.md#drop-region) that is part of a super region until you either [alter the super region](/docs/stable/alter-database.md#alter-super-region) to remove it, or [drop the super region](/docs/stable/alter-database.md#drop-super-region) altogether.

For examples, see [Manage super regions](#manage-super-regions).

#### Required privileges

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

* Membership to the [`admin`](/docs/stable/security-reference/authorization#admin-role) role for the cluster.
* Either [ownership](/docs/stable/security-reference/authorization.md#object-ownership) or the [`CREATE` privilege](/docs/stable/security-reference/authorization#supported-privileges) for the database.

#### Parameters

| Parameter | Description |
| - | - |
| `region_name` | The name of the [super region](/docs/stable/multiregion-overview.md#super-regions) being altered. |
| `region_name_list` | The altered super region will consist of this set of [database regions](/docs/stable/multiregion-overview.md#database-regions). |
| `SURVIVE ZONE FAILURE` | Give the super region an explicit [zone survival goal](/docs/stable/multiregion-survival-goals.md#survive-zone-failures). |
| `SURVIVE REGION FAILURE` | Give the super region an explicit [region survival goal](/docs/stable/multiregion-survival-goals.md#survive-region-failures). The super region must contain at least 3 regions. |
| `SURVIVE DEFAULT` | Remove the super region's explicit survival goal so that it inherits the [database survival goal](/docs/stable/multiregion-survival-goals.md). |

An explicit super-region survival goal is unaffected by later changes to the database survival goal. `SURVIVE REGION FAILURE` is not compatible with [`PLACEMENT RESTRICTED`](/docs/stable/alter-database#placement).

For usage, see [Synopsis](#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 [`SHOW ZONE CONFIGURATIONS`](/docs/stable/show-zone-configurations). For more information about replication zones, see [Replication Controls](/docs/stable/configure-replication-zones.md).

> [!CAUTION]
>
> If you directly change a database's zone configuration with `ALTER DATABASE ... CONFIGURE ZONE`, CockroachDB will block all [`ALTER DATABASE ... SET PRIMARY REGION`](#set-primary-region) statements on the database.

Cockroach Labs  [does not recommend modifying zone configurations manually](/docs/stable/configure-replication-zones.md#why-manual-zone-config-management-is-not-recommended).  [does not recommend modifying zone configurations manually](#why-manual-zone-config-management-is-not-recommended).

Most users should use [multi-region SQL statements](/docs/stable/multiregion-overview.md) instead. If additional control is needed, use [Zone Config Extensions](/docs/stable/zone-config-extensions.md) 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](#configure-replication-zones).

#### Required privileges

The user must be a member of the [`admin` role](/docs/stable/security-reference/authorization#admin-role) or have been granted [`CREATE`](/docs/stable/security-reference/authorization#supported-privileges) or [`ZONECONFIG`](/docs/stable/security-reference/authorization#supported-privileges) privileges. To configure [`system` objects](/docs/stable/configure-replication-zones#for-system-data), the user must be a member of the `admin` role.

#### Parameters

| Parameter | Description |
| - | - |
| `variable` | The name of the [replication zone variable](/docs/stable/configure-replication-zones.md#replication-zone-variables) to change. |
| `value` | The value of the [replication zone variable](/docs/stable/configure-replication-zones.md#replication-zone-variables) to change. |
| `DISCARD` | Remove a replication zone. |

For usage, see [Synopsis](#synopsis).

### `DROP REGION`

`ALTER DATABASE .. DROP REGION` drops a [region](/docs/stable/multiregion-overview.md#database-regions) from a [multi-region database](/docs/stable/multiregion-overview.md). While CockroachDB processes an index modification or changing a table to or from a [`REGIONAL BY ROW` table](/docs/stable/table-localities#regional-by-row-tables), 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 [`REGIONAL BY ROW`](/docs/stable/table-localities#regional-by-row-tables) tables will be blocked.

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

* The region is [set as the primary region](#set-primary-region), unless it is the last remaining region.
* The region is [set as the secondary region](#set-secondary-region). To drop the region, you must first unset the secondary region using [`DROP SECONDARY REGION`](#drop-secondary-region).
* The database uses the [`REGION` survival goal](/docs/stable/multiregion-survival-goals#survive-region-failures) and there are only three regions configured on the database.

For examples, see [Manage regions](#manage-regions).

#### Required privileges

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

* Membership to the [`admin`](/docs/stable/security-reference/authorization#roles) role for the cluster.
* Membership to the [owner](/docs/stable/security-reference/authorization.md#object-ownership) role, or the [`CREATE` privilege](/docs/stable/security-reference/authorization#supported-privileges), for the database and all [`REGIONAL BY ROW`](/docs/stable/table-localities#regional-by-row-tables) tables in the database.

#### Parameters

| Parameter | Description |
| - | - |
| `region_name` | The [region](/docs/stable/multiregion-overview.md#database-regions) being dropped from this database. Allowed values include any region present in [`SHOW REGIONS FROM DATABASE database_name`](/docs/stable/show-regions). You can only drop the primary region from a multi-region database if it is the last remaining region. |

For usage, see [Synopsis](#synopsis).

### `DROP SECONDARY REGION`

`ALTER DATABASE .. DROP SECONDARY REGION` unsets the [secondary region](/docs/stable/multiregion-overview.md#secondary-regions) (if set) from a [multi-region database](/docs/stable/multiregion-overview.md).

The secondary region is used for failover purposes: if the [primary region](#set-primary-region) fails, the secondary region acts as the new primary region. For more information, see [Secondary regions](/docs/stable/multiregion-overview.md#secondary-regions).

For examples, see [Manage regions](#manage-regions).

#### Required privileges

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

* Membership to the [`admin`](/docs/stable/security-reference/authorization#roles) role for the cluster.
* Membership to the [owner](/docs/stable/security-reference/authorization.md#object-ownership) role, or the [`CREATE` privilege](/docs/stable/security-reference/authorization#supported-privileges) for the database.

### `DROP SUPER REGION`

`ALTER DATABASE ... DROP SUPER REGION` drops a [super region](/docs/stable/multiregion-overview.md#super-regions) from a [multi-region database](/docs/stable/multiregion-overview.md).

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

* Your cluster must be a [multi-region cluster](/docs/stable/multiregion-overview.md).
* Super regions can only contain one or more [database regions](/docs/stable/multiregion-overview.md#database-regions) that have already been added with [`ALTER DATABASE ... ADD REGION`](/docs/stable/alter-database#add-region).
* 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](https://wikipedia.org/wiki/Disjoint_sets) from the set of database regions in *B*.
* You cannot [drop a region](/docs/stable/alter-database.md#drop-region) that is part of a super region until you either [alter the super region](/docs/stable/alter-database.md#alter-super-region) to remove it, or [drop the super region](/docs/stable/alter-database.md#drop-super-region) altogether.

For examples, see [Manage super regions](#manage-super-regions).

#### Required privileges

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

* Membership to the [`admin`](/docs/stable/security-reference/authorization#roles) role for the cluster.
* Either [ownership](/docs/stable/security-reference/authorization.md#object-ownership) or the [`CREATE` privilege](/docs/stable/security-reference/authorization#supported-privileges) for the database.

#### Parameters

| Parameter | Description |
| - | - |
| `region_name` | The name of the [super region](/docs/stable/multiregion-overview.md#super-regions) being dropped from this database. |

For usage, see [Synopsis](#synopsis).

### `OWNER TO`

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

For examples, see [Change database owner](#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 [role](/docs/stable/security-reference/authorization.md#roles). The user must also have the `CREATEDB` [privilege](/docs/stable/security-reference/authorization.md#managing-privileges).

#### Parameters

| Parameter | Description |
| - | - |
| `role_spec` | The role to set as the owner of the database. |

For usage, see [Synopsis](#synopsis).

### `PLACEMENT`

`ALTER DATABASE ... PLACEMENT` is used to set the replica placement policy (`DEFAULT` or `RESTRICTED`) for a [multi-region database](/docs/stable/multiregion-overview.md)'s [regional tables](/docs/stable/regional-tables.md). [Regional tables](/docs/stable/regional-tables.md) are those with [`REGIONAL BY ROW`](/docs/stable/table-localities#regional-by-row-tables) or [`REGIONAL BY TABLE`](/docs/stable/table-localities#regional-tables) localities.

`ALTER DATABASE ... PLACEMENT RESTRICTED` constrains the voting and non-voting replica placement for the database's tables to the [home regions](/docs/stable/alter-table.md) associated with those tables. This is a way of opting out of [non-voting replicas](/docs/stable/architecture/replication-layer.md#non-voting-replicas) for [regional tables](/docs/stable/regional-tables.md) to accomplish one or more of the following goals:

* Implement a [data domiciling](/docs/stable/data-domiciling.md) 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 [non-voting replicas](/docs/stable/architecture/replication-layer.md#non-voting-replicas) entirely. For example, [`GLOBAL` tables](/docs/stable/global-tables) in the database will remain unaffected by this statement. `GLOBAL` tables are designed to have replicas placed across all available [cluster regions](/docs/stable/multiregion-overview.md#cluster-regions) to ensure fast local reads.

For examples, see [Set replica placement policy](#set-replica-placement-policy).

#### Required privileges

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

* Membership to the [`admin`](/docs/stable/security-reference/authorization#roles) role for the cluster.
* [Ownership](/docs/stable/security-reference/authorization.md#object-ownership) or the [`CREATE` privilege](/docs/stable/security-reference/authorization#supported-privileges) for the database and all tables in the database.

#### Parameters

| Parameter | Description |
| - | - |
| `DEFAULT` | Set the replica placement policy for [regional tables](/docs/stable/regional-tables.md) to `DEFAULT`. |
| `RESTRICTED` | Set the replica placement policy for [regional tables](/docs/stable/regional-tables.md) to `RESTRICTED`. |

For usage, see [Synopsis](#synopsis).

The replica placement policies work as follows:

 \- `DEFAULT` (**Default**):  If the replica placement policy [is set to `DEFAULT`](#set-the-replica-placement-policy-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 [database survival goals](/docs/stable/multiregion-overview.md#survival-goals) are met.
* You can get fast stale reads from all [database regions](/docs/stable/multiregion-overview.md#database-regions).

 \- `RESTRICTED`:  If the replica placement policy [is set to `RESTRICTED`](#set-the-replica-placement-policy-to-restricted), CockroachDB will constrain replica placement to only those regions where the table has voting replicas (that is, replicas which participate in the [Raft quorum](/docs/stable/architecture/replication-layer.md#raft)). In practice, this means that voting replicas for the table will be constrained to the table's [home region](/docs/stable/multiregion-overview.md#table-localities). Specifically, for [`REGIONAL BY TABLE`](/docs/stable/table-localities#regional-tables) tables, it will only place replicas in the defined region (or the database's [primary region](#set-primary-region)); for [`REGIONAL BY ROW`](/docs/stable/table-localities#regional-by-row-tables) tables, it will only place replicas for each underlying [partition](/docs/stable/partitioning.md) 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 [`ZONE` survival goal](/docs/stable/multiregion-survival-goals#survive-zone-failures).
* This setting does not affect how [`GLOBAL` tables](/docs/stable/global-tables) work; they will still place replicas in all [database regions](/docs/stable/multiregion-overview.md#database-regions).

### `RENAME TO`

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

> [!NOTE]
>
> It is not possible to rename a database if the database is referenced by a [view](/docs/stable/views.md). For more details, see [View Dependencies](/docs/stable/views.md#view-dependencies).

For examples, see [Rename database](#rename-database).

#### Required privileges

To rename a database, the user must be a member of the `admin` role or must have the [`CREATEDB`](/docs/stable/create-role#create-a-role-that-can-create-and-rename-databases) parameter set.

#### Parameters

| Parameter | Description |
| - | - |
| `database_new_name` | The [`name`](/docs/stable/sql-grammar#name) you want to use for the database. The new name must be unique and follow these [identifier rules](/docs/stable/keywords-and-identifiers.md#identifiers). You cannot rename a database if it is set as the [current database](/docs/stable/sql-name-resolution.md#current-database) or if [`sql_safe_updates = true`](/docs/stable/set-vars). |

For usage, see [Synopsis](#synopsis).

### `RESET {session variable}`

`ALTER DATABASE ... RESET {session variable}` clears a database-level override of a [session variable](/docs/stable/set-vars.md#supported-variables) so that future sessions use the default value.

For a list of the session variables that have been updated from default values, see [`SHOW DEFAULT SESSION VARIABLES FOR ROLE`](/docs/stable/show-default-session-variables-for-role).

#### Required privileges

To reset default session variable values for a database with `ALTER DATABASE ... RESET {session variable}`, the user must be a member of the [`admin` role](/docs/stable/security-reference/authorization#admin-role) or the [owner](/docs/stable/security-reference/authorization.md#object-ownership) of the target database.

#### Parameters

| Parameter | Description |
| - | - |
| `session_var` | The name of the [session variable](/docs/stable/set-vars.md#supported-variables). |

For usage, see [Synopsis](#synopsis).

#### Aliases

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

* [`ALTER ROLE ALL IN DATABASE ... RESET {session variable}`](/docs/stable/alter-role#set-default-session-variable-values-for-all-users)

### `SET {session variable}`

`ALTER DATABASE ... SET {session variable}` sets the default value of a [session variable](/docs/stable/set-vars.md#supported-variables) 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 [`SHOW DEFAULT SESSION VARIABLES FOR ROLE`](/docs/stable/show-default-session-variables-for-role).

#### Required privileges

To set default session variable values for a database with `ALTER DATABASE ... SET {session variable}`, the user must be a member of the [`admin` role](/docs/stable/security-reference/authorization#admin-role) or the [owner](/docs/stable/security-reference/authorization.md#object-ownership) of the target database.

Additionally, 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 [privileges](/docs/stable/security-reference/authorization.md#managing-privileges) to modify.

#### Parameters

| Parameter | Description |
| - | - |
| `variable` | The name of [the session variable](/docs/stable/set-vars.md#supported-variables) 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](#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 [`ALTER ROLE ALL ...`](/docs/stable/alter-role#set-default-session-variable-values-for-all-users).

#### Session variable precedence

When a [session](/docs/stable/show-sessions.md) starts, CockroachDB determines the initial value of each [session variable](/docs/stable/session-variables.md) by evaluating the settings in the following order (items earlier in the list take precedence over later items):

1. [Connection string parameters](/docs/stable/connection-parameters.md): A value supplied as a query parameter in the connection URL (for example, `.../movr?sslmode=disable&timezone=UTC`).
2. [Per-role, per-database defaults](/docs/stable/alter-role.md#set-default-session-variable-values-for-a-role-in-a-specific-database): A value set by `ALTER ROLE {role_name} IN DATABASE {db_name} SET {var}={value}`.
3. [Per-role, all-database defaults](/docs/stable/alter-role.md#set-default-session-variable-values-for-a-role): A value set by `ALTER ROLE {role_name} SET {var}={value}`.
4. [All-role, per-database defaults](/docs/stable/alter-role.md#set-default-session-variable-values-for-a-specific-database): 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. [Cluster-wide defaults for all roles and all databases](/docs/stable/alter-role.md#set-default-session-variable-values-for-all-users): 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 [`root` user](/docs/stable/security-reference/authorization#root-user) is exempt from settings 3–5. The `root` user is only affected by values specified in the connection string.

You can set session variables for the duration of a single transaction by using [`SET LOCAL {var}={value}`](/docs/stable/set-vars#set-a-variable-for-the-duration-of-a-single-transaction). You can also [override session variables](/docs/stable/cost-based-optimizer.md#override-session-variables) for executions of a specific statement fingerprint by using the [`information_schema.crdb_set_session_variable_hint()`](/docs/stable/functions-and-operators#system-repair-functions) function.

> [!NOTE]
>
> 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`](/docs/stable/set-vars).

### `SET PRIMARY REGION`

`ALTER DATABASE .. SET PRIMARY REGION` sets the primary [region](/docs/stable/multiregion-overview.md#database-regions) of a [multi-region database](/docs/stable/multiregion-overview.md).

> [!CAUTION]
>
> If a database's [zone configuration](/docs/stable/configure-replication-zones.md) has been directly set with an [`ALTER DATABASE ... CONFIGURE ZONE`](#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`](#remove-a-replication-zone) statement.

[Secondary regions](/docs/stable/multiregion-overview.md#secondary-regions) allow you to define a [database region](/docs/stable/multiregion-overview.md#database-regions) that will be used for failover in the event your primary region goes down. For more information, see [Secondary regions](/docs/stable/multiregion-overview.md#secondary-regions).

For examples, see [Manage regions](#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 [`admin`](/docs/stable/security-reference/authorization#roles) role for the cluster.
* Membership to the [owner](/docs/stable/security-reference/authorization.md#object-ownership) role, or the [`CREATE` privilege](/docs/stable/security-reference/authorization#supported-privileges), 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 [owner](/docs/stable/security-reference/authorization.md#object-ownership) role for the database, or have the [`CREATE` privilege](/docs/stable/security-reference/authorization#supported-privileges) 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 [`SHOW REGIONS FROM CLUSTER`](/docs/stable/show-regions). |

For usage, see [Synopsis](#synopsis).

### `SET SECONDARY REGION`

`ALTER DATABASE .. SET SECONDARY REGION` adds a [secondary region](/docs/stable/multiregion-overview.md#database-regions) to a [multi-region database](/docs/stable/multiregion-overview.md) for failover purposes.

If the [primary region](#set-primary-region) fails, the secondary region becomes the new primary region. For more information, see [Secondary regions](/docs/stable/multiregion-overview.md#secondary-regions).

> [!CAUTION]
>
> 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`](#set-primary-region), or when [creating the database](/docs/stable/create-database.md). For an example showing how to add a secondary region with `ALTER DATABASE`, see [Set the secondary region](#set-the-secondary-region).

[Secondary regions](/docs/stable/multiregion-overview.md#secondary-regions) are not compatible with databases containing [`REGIONAL BY ROW`](/docs/stable/table-localities#regional-by-row-tables) 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](#manage-regions).

#### Required privileges

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

* Membership to the [`admin`](/docs/stable/security-reference/authorization#admin-role) role for the cluster.
* Either [ownership](/docs/stable/security-reference/authorization.md#object-ownership) or the [`CREATE` privilege](/docs/stable/security-reference/authorization#supported-privileges) for the database.

#### Parameters

| Parameter | Description |
| - | - |
| `region_name` | Usually, the [region](/docs/stable/multiregion-overview.md#database-regions) 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](#synopsis).

### `SURVIVE {ZONE,REGION} FAILURE`

`ALTER DATABASE ... SURVIVE {ZONE,REGION} FAILURE` [statement](/docs/stable/sql-statements.md) sets the [survival goal](/docs/stable/multiregion-overview.md#survival-goals) for a [multi-region database](/docs/stable/multiregion-overview.md).

For examples, see [Configure survival goals](#configure-survival-goals).

#### Required privileges

The user must be a member of the [`admin`](/docs/stable/security-reference/authorization#roles) or [owner](/docs/stable/security-reference/authorization.md#object-ownership) roles, or have the [`CREATE` privilege](/docs/stable/security-reference/authorization#supported-privileges) on the database.

#### Parameters

| Parameter | Description |
| - | - |
| `ZONE` | Set the [survival goal](/docs/stable/multiregion-overview.md#survival-goals) to zone failure. |
| `REGION` | Set the [survival goal](/docs/stable/multiregion-overview.md#survival-goals) to region failure. |

For usage, see [Synopsis](#synopsis).

## Examples

### Manage regions

#### Setup

Only a [cluster region](/docs/stable/multiregion-overview.md#cluster-regions) specified [at node startup](/docs/stable/cockroach-start.md#locality) can be used as a [database region](/docs/stable/multiregion-overview.md#database-regions).

To follow along with the examples in this section, start a [demo cluster](/docs/stable/cockroach-demo.md#demo-locality) with the [`--global` flag](/docs/stable/cockroach-demo#general) to simulate a multi-region cluster:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach demo --global --nodes 9
```

To see the regions available to the databases in the cluster, use a [`SHOW REGIONS FROM CLUSTER`](/docs/stable/show-regions#view-the-regions-in-a-cluster) statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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`](#set-primary-region) statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 [`REGIONAL BY TABLE`](/docs/stable/alter-table) 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 [rebalancing](/docs/stable/architecture/replication-layer.md#leaseholder-rebalancing).

#### Add regions to a database

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER DATABASE movr ADD region "us-west1";
```

```
ALTER DATABASE ADD REGION
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER DATABASE movr ADD region "europe-west1";
```

```
ALTER DATABASE ADD REGION
```

To view the regions associated with a multi-region database, use a [`SHOW REGIONS FROM DATABASE`](/docs/stable/show-regions) statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 [database region](/docs/stable/multiregion-overview.md#database-regions) (that is not already the [primary region](#set-the-primary-region)) as the [secondary region](/docs/stable/multiregion-overview.md#secondary-regions), use the following statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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.

> [!CAUTION]
>
> [Secondary regions](/docs/stable/multiregion-overview.md#secondary-regions) are not compatible with databases containing [`REGIONAL BY ROW`](/docs/stable/table-localities#regional-by-row-tables) 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 [database region](/docs/stable/multiregion-overview.md#database-regions) as the [secondary region](/docs/stable/multiregion-overview.md#secondary-regions) on a multi-region database, use the [`DROP SECONDARY REGION`](#drop-secondary-region) statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER DATABASE movr DROP SECONDARY REGION;
```

```
ALTER DATABASE DROP SECONDARY REGION
```

> [!NOTE]
>
> This statement does not drop the region from the database. To drop a region, use [`DROP REGION`](#drop-a-region-from-a-database).

#### Change the existing primary region

To change the primary region to another region in the database, use a [`SET PRIMARY REGION`](#set-primary-region) statement.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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](#add-regions-to-a-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](#drop-region) from a multi-region database, use a [`DROP REGION`](#drop-region) statement.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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](#set-the-primary-region), unless it is the last remaining region.
* The region is [set as the secondary region](#set-the-secondary-region). To drop the region, you must first unset the secondary region using [`DROP SECONDARY REGION`](#unset-the-secondary-region).
* The database uses the [`REGION` survival goal](/docs/stable/multiregion-survival-goals#survive-region-failures) and there are only three regions configured on the database.

### Manage super regions

#### Setup

Only a [cluster region](/docs/stable/multiregion-overview.md#cluster-regions) specified [at node startup](/docs/stable/cockroach-start.md#locality) can be used as a [database region](/docs/stable/multiregion-overview.md#database-regions).

To follow along with the examples in this section, start a [demo cluster](/docs/stable/cockroach-demo.md#demo-locality) with the [`--global` flag](/docs/stable/cockroach-demo#general) to simulate a multi-region cluster:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach demo --global --nodes 9
```

To see the regions available to the databases in the cluster, use a [`SHOW REGIONS FROM CLUSTER`](/docs/stable/show-regions#view-the-regions-in-a-cluster) statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 [Database Regions](/docs/stable/multiregion-overview.md#database-regions).

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 [`GLOBAL`](/docs/stable/table-localities#global-tables).

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 [`REGIONAL BY ROW`](/docs/stable/table-localities#regional-by-row-tables).

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`

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  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`

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  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`

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  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`

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  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`

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  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";
  ```

#### 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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER DATABASE movr ADD SUPER REGION "usa" VALUES "us-east1", "us-west1";
```

```
ALTER DATABASE ADD SUPER REGION
```

#### Set a survival goal for a super region

By default, a super region inherits the database survival goal. An explicit super-region survival goal is unaffected by later changes to the database survival goal. To give the `usa` super region an explicit zone survival goal, use:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER DATABASE movr ALTER SUPER REGION "usa" SURVIVE ZONE FAILURE;
```

You can use `SURVIVE REGION FAILURE` for a super region that contains at least 3 regions.

To remove an explicit goal and inherit the database survival goal again, use:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER DATABASE movr ALTER SUPER REGION "usa" SURVIVE DEFAULT;
```

CockroachDB rejects `SURVIVE DEFAULT` if the super region cannot satisfy the database survival goal. For example, a super region with fewer than 3 regions cannot inherit a `REGION` survival goal.

Use [`SHOW SUPER REGIONS FROM DATABASE`](/docs/stable/show-super-regions) to inspect explicit super-region survival goals.

#### 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](#add-a-super-region-to-a-database). If you wanted to [drop the region](#drop-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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 [primary region](/docs/stable/alter-database.md#set-primary-region) of a [multi-region database](/docs/stable/multiregion-overview.md) 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 [regional table](/docs/stable/regional-tables.md) 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` [session setting](/docs/stable/set-vars.md) to `'on'`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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` [cluster setting](/docs/stable/cluster-settings.md) to `true`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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`](#drop-super-region) statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER DATABASE movr DROP SUPER REGION "usa";
```

```
ALTER DATABASE DROP SUPER REGION
```

Note that you cannot [drop a region](#drop-region) that is part of a super region until you either [alter the super region](#alter-super-region) to remove it, or [drop the super region](#drop-super-region) altogether.

For example, using the super region that was added in [`ADD SUPER REGION`](#add-a-super-region-to-a-database):

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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  [does not recommend modifying zone configurations manually](/docs/stable/configure-replication-zones.md#why-manual-zone-config-management-is-not-recommended).  [does not recommend modifying zone configurations manually](#why-manual-zone-config-management-is-not-recommended).

Most users should use [multi-region SQL statements](/docs/stable/multiregion-overview.md) instead. If additional control is needed, use [Zone Config Extensions](/docs/stable/zone-config-extensions.md) 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 [MovR: A Global Vehicle-sharing App](/docs/stable/movr.md).

To follow along, run [`cockroach demo`](/docs/stable/cockroach-demo#demo-locality) 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.

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ 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):

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER DATABASE movr CONFIGURE ZONE USING num_replicas = 5, gc.ttlseconds = 100000;
```

```
CONFIGURE ZONE 1
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER DATABASE movr CONFIGURE ZONE USING range_min_bytes = 0, range_max_bytes = 90000, gc.ttlseconds = 89999, num_replicas = 4;
```

#### Remove a replication zone

> [!NOTE]
>
> 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 multi-region abstractions created the zone configuration.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER DATABASE movr CONFIGURE ZONE DISCARD;
```

### Troubleshoot replication zones

For instructions showing how to troubleshoot replication zones that may be misconfigured, see [Troubleshoot Replication Zone Configurations](/docs/stable/troubleshoot-replication-zones.md).

### Use Zone Config Extensions

The following examples show:

* How to [override specific fields of a schema object's zone configs](#override-specific-fields-of-a-schema-object’s-zone-configs).
* How to [reduce storage costs by removing non-voting replicas](#reduce-storage-costs-by-removing-non-voting-replicas).
* How you could [implement super regions](#implement-super-regions).
* How to [minimize cross-region write latency](#minimize-cross-region-write-latency).
* How something like the [Secondary regions](/docs/stable/multiregion-overview.md#secondary-regions) multi-region abstraction could have been implemented using Zone Config Extensions. For this example, we will call it ["Failover regions"](#failover-regions).
* How to [reset a region's Zone Config Extensions](#reset-a-region’s-zone-config-extensions).
* How to [discard a region's Zone Config Extensions](#discard-a-region’s-zone-config-extensions).

> [!NOTE]
>
> 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 [`cockroach demo`](/docs/stable/cockroach-demo#demo-locality) cluster as follows:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
cockroach demo --global --nodes=9
```

This gives us a (preloaded) [MovR](/docs/stable/movr.md) database on a cluster with the following [regions](/docs/stable/multiregion-overview.md#cluster-regions), which can be viewed with [`SHOW REGIONS`](/docs/stable/show-regions):

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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](/docs/stable/multiregion-overview.md) as follows:

1. Set the primary region using [`SET PRIMARY REGION`](#set-primary-region).
2. Add the other two regions using [`ADD REGION`](#add-region).

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER DATABASE movr SET PRIMARY REGION "us-east1";
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER DATABASE movr ADD REGION "us-west1";
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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](/docs/stable/movr.md) database to update its [`lease_preferences`](/docs/stable/configure-replication-zones) field.

Setting the [primary region](#set-primary-region) to `us-east1` [during the setup steps](#use-zone-config-extensions) added `us-east1` to `lease_preferences`.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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](#reset-a-region’s-zone-config-extensions).

#### Reduce storage costs by removing non-voting replicas

In a [multi-region database](/docs/stable/multiregion-overview.md), the default zone configuration may include additional [non-voting replicas](/docs/stable/configure-replication-zones.md) (for example, to support [follower reads](/docs/stable/follower-reads.md)). The database's [survival goal](/docs/stable/multiregion-overview.md#survival-goals) depends only on the number of voting replicas (`num_voters`), not the total number of replicas (`num_replicas`).

By default, the `movr` database [as configured for this example](#setup) has a [zone survival goal](/docs/stable/multiregion-survival-goals.md):

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW SURVIVAL GOAL FROM DATABASE movr;
```

```
  database | survival_goal
-----------+----------------
  movr     | zone
(1 row)
```

This means that the number of replicas (`num_replicas`) is greater than strictly necessary to support the zone survival goal, which you can confirm with [`SHOW ZONE CONFIGURATION`](/docs/stable/show-zone-configurations):

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 = 14400,
                |     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)
```

If you want to reduce storage costs, and can accept losing the ability to perform [follower reads](/docs/stable/follower-reads.md), use Zone Config Extensions to reduce the number of non-voting replicas by setting `num_replicas` equal to `num_voters`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER DATABASE movr ALTER LOCALITY REGIONAL CONFIGURE ZONE USING num_replicas = 3;
```

#### Customize a super region

To apply a [Zone Config Extension](/docs/stable/zone-config-extensions.md) to all `REGIONAL BY TABLE` tables and `REGIONAL BY ROW` partitions whose home region belongs to a super region, specify the super region as the locality scope. For example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER DATABASE movr ADD SUPER REGION "usa" VALUES "us-east1", "us-west1";
ALTER DATABASE movr ALTER LOCALITY SUPER REGION "usa" CONFIGURE ZONE USING num_replicas = 3;
```

CockroachDB applies a `SUPER REGION` extension after a `REGIONAL` extension and before a more-specific `REGIONAL IN` extension. A `SUPER REGION` extension cannot set [`lease_preferences`](/docs/stable/configure-replication-zones#lease_preferences). Region constraints in [`constraints`](/docs/stable/configure-replication-zones#constraints) or [`voter_constraints`](/docs/stable/configure-replication-zones#voter_constraints) can reference only regions that are members of the super region.

To discard the extension for the `usa` super region, use:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER DATABASE movr ALTER LOCALITY SUPER REGION "usa" CONFIGURE ZONE DISCARD;
```

Dropping a super region also removes its Zone Config Extension.

#### Implement super regions

In this example, [Zone Config Extensions](/docs/stable/zone-config-extensions.md) are used to provide an alternative implementation of [super regions](/docs/stable/multiregion-overview.md#super-regions), which are useful for [data domiciling](/docs/stable/data-domiciling.md).

For this example, you need to start a [`cockroach demo` cluster](/docs/stable/cockroach-demo#demo-locality) using the following command, which [defines the available cluster localities using the `--demo-locality` flag](/docs/stable/cockroach-demo#demo-locality):

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 [`movr` database](/docs/stable/movr) into a [multi-region database](/docs/stable/multiregion-overview.md) by [setting the primary region](/docs/stable/alter-database.md#set-primary-region).
* Set the `movr` database to [survive region failures](/docs/stable/multiregion-survival-goals.md#survive-region-failures).
* Add the remaining regions defined at cluster start time to the multi-region `movr` database (for a total of 6).

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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`](#alter-locality) statements to [configure replication zones](/docs/stable/configure-replication-zones.md) such that:

* [Replicas](/docs/stable/architecture/overview.md#architecture-replica) 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.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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]]';
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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, [Zone Config Extensions](/docs/stable/zone-config-extensions.md) 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 [follower reads](/docs/stable/follower-reads.md) will be made from these regions.

To minimize the cross-region write latency, [configure replication zones](/docs/stable/configure-replication-zones.md) such that the 3 US regions have voting replicas (that is, replicas that participate in [Raft quorum](/docs/stable/architecture/replication-layer.md#raft)). This will leave the 2 additional non-US regions with only [non-voting replicas](/docs/stable/architecture/replication-layer.md#non-voting-replicas).

For this example, you need to start a [`cockroach demo` cluster](/docs/stable/cockroach-demo#demo-locality) using the following command, which [defines the available cluster localities using the `--demo-locality` flag](/docs/stable/cockroach-demo#demo-locality):

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 [`movr` database](/docs/stable/movr) into a [multi-region database](/docs/stable/multiregion-overview.md) by [setting the primary region](/docs/stable/alter-database.md#set-primary-region).
* Set the `movr` database to [survive region failures](/docs/stable/multiregion-survival-goals.md#survive-region-failures).
* Add the remaining regions defined at cluster start time to the multi-region `movr` database (for a total of 5).

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 [`REGIONAL` tables](/docs/stable/regional-tables):

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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`](#alter-locality) statements to [configure replication zones](/docs/stable/configure-replication-zones.md) such that:

* The 3 US regions have voting replicas (that is, replicas that participate in [Raft quorum](/docs/stable/architecture/replication-layer.md#raft)).
* The 2 additional regions (Europe and Japan) are left with only [non-voting replicas](/docs/stable/architecture/replication-layer.md#non-voting-replicas) that can be used to serve [follower reads](/docs/stable/follower-reads.md).

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 [MovR](/docs/stable/movr.md) database so that if the [primary region](#set-primary-region) fails, enough [replicas](/docs/stable/architecture/distribution-layer.md) 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`](#parameters), which covers all [`REGIONAL BY TABLE IN {region}` tables](/docs/stable/alter-table#set-the-table-locality-to-regional-by-table) and all `{region}` [partitions](/docs/stable/partitioning.md) of [`REGIONAL BY ROW` tables](/docs/stable/alter-table#set-the-table-locality-to-regional-by-row).

> [!NOTE]
>
> This functionality is already provided by the built-in [Secondary regions](/docs/stable/multiregion-overview.md#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](/docs/stable/multiregion-overview.md) whenever possible.

1. Set the database to have a `REGION` survival goal using [`ALTER DATABASE ... SURVIVE REGION FAILURE`](/docs/stable/alter-database):

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   ALTER DATABASE movr SURVIVE REGION FAILURE;
   ```

2. Apply the `REGIONAL BY ROW` locality to the `movr.rides` table using the following statement:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   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 [zone configs](/docs/stable/configure-replication-zones.md) for the `movr.rides` table using [`SHOW ZONE CONFIGURATION`](/docs/stable/show-zone-configurations):

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   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](#use-zone-config-extensions). The output above confirms that `us-east1` is the primary region based on the values of the [`voter_constraints`](/docs/stable/configure-replication-zones) and [`lease_preferences`](/docs/stable/configure-replication-zones) 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`.

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   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 [zone configs](/docs/stable/configure-replication-zones.md) for the `movr.rides` table using [`SHOW ZONE CONFIGURATION`](/docs/stable/show-zone-configurations):

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   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).

#### 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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER DATABASE movr ALTER LOCALITY CONFIGURE ZONE USING DEFAULT;
```

> [!NOTE]
>
> This will not reset any configuration created by the [multi-region abstractions](/docs/stable/multiregion-overview.md).

#### Discard a region's Zone Config Extensions

To discard the Zone Config Extension settings from a region, enter the following statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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](/docs/stable/multiregion-overview.md).

#### 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 [Troubleshoot Replication Zone Configurations](/docs/stable/troubleshoot-replication-zones.md).

### Change database owner

#### Setup

To follow along, run [`cockroach demo`](/docs/stable/cockroach-demo#demo-locality) to start a temporary, in-memory cluster with the [`movr`](/docs/stable/movr) sample dataset preloaded:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ 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`.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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)
```

> [!NOTE]
>
> 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` [privilege](/docs/stable/security-reference/authorization.md#managing-privileges).

### Set replica placement policy

#### Setup

To follow along with the examples below:

1. Start a [demo cluster](/docs/stable/cockroach-demo.md#demo-locality) with the [`--global` flag](/docs/stable/cockroach-demo#general) to simulate a multi-region cluster:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   cockroach demo --global --nodes 9
   ```

2. Set the demo cluster's [database regions](/docs/stable/multiregion-overview.md#database-regions) and [table localities](/docs/stable/multiregion-overview.md).

3. Enable the replica placement syntax with either the [session variable](/docs/stable/set-vars.md) or the [cluster setting](/docs/stable/cluster-settings.md) as shown below.

   1. To use the session variable:

      ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
      SET enable_multiregion_placement_policy = on;
      ```

   2. To use the cluster setting:

      ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
      SET CLUSTER SETTING sql.defaults.multiregion_placement_policy.enabled = on;
      ```

> [!NOTE]
>
> Use [`ALTER ROLE ALL SET {sessionvar} = {val}`](/docs/stable/alter-role#set-default-session-variable-values-for-all-users) instead of the `sql.defaults.*` [cluster settings](/docs/stable/cluster-settings.md). This allows you to set a default value for all users for any [session variable](/docs/stable/set-vars.md) 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"](#parameters-restricted), you can do so in a [`CREATE DATABASE`](/docs/stable/create-database) statement as shown below:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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`](#parameters-restricted), you are saying that you want the underlying data to be restricted to the table or partition's [home region](/docs/stable/multiregion-overview.md#table-localities).

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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`](#set-the-replica-placement-policy-to-restricted), you can set it back to [the default](#parameters-default) by issuing the following statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER DATABASE movr PLACEMENT DEFAULT;
```

```
ALTER DATABASE PLACEMENT
```

### Rename database

#### Rename a database

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE DATABASE db1;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW DATABASES;
```

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER DATABASE db1 RENAME TO db2;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW DATABASES;
```

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

### Configure survival goals

The following examples configure the database survival goal. CockroachDB rejects a database survival-goal change if an inheriting super region cannot satisfy the new goal. To configure a super-region survival goal, see [Set a survival goal for a super region](#set-a-survival-goal-for-a-super-region).

#### Survive zone failures

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER DATABASE {db} SURVIVE ZONE FAILURE;
```

```
ALTER DATABASE SURVIVE
```

> [!NOTE]
>
> Surviving zone failures is the default setting for multi-region databases.

For more information about the zone survival goal, see [Surviving zone failures](/docs/stable/multiregion-survival-goals.md#survive-zone-failures).

#### Survive region failures

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER DATABASE {db} SURVIVE REGION FAILURE;
```

```
ALTER DATABASE SURVIVE
```

If you try to change a database with less than 3 [database regions](/docs/stable/multiregion-overview.md#database-regions) 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 [Surviving region failures](/docs/stable/multiregion-survival-goals.md#survive-region-failures).

## See also

* [Multi-Region Capabilities Overview](/docs/stable/multiregion-overview.md)
* [Secondary regions](/docs/stable/multiregion-overview.md#secondary-regions)
* [Super regions](/docs/stable/multiregion-overview.md#super-regions)
* [Zone Config Extensions](/docs/stable/zone-config-extensions.md)
* [Data Domiciling with CockroachDB](/docs/stable/data-domiciling.md)
* [`CREATE DATABASE`](/docs/stable/create-database)
* [`CREATE TABLE`](/docs/stable/create-table)
* [`ALTER TABLE`](/docs/stable/alter-table)
* [Online Schema Changes](/docs/stable/online-schema-changes.md)
* [SQL Statements](/docs/stable/sql-statements.md)
* [Troubleshoot Replication Zones](/docs/stable/troubleshoot-replication-zones.md)