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

# Migrate to Multi-Region SQL

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

This page describes how to migrate a multi-region cluster from using replication zones to using multi-region SQL abstractions.

## Overview

<Note>
  If you are already using <InternalLink path="multiregion-overview">multi-region SQL statements</InternalLink> to control your multi-region cluster, you can ignore this page.
</Note>

CockroachDB v21.1 added support for <InternalLink path="multiregion-overview">improved multi-region capabilities that make it easier to run global applications</InternalLink>. Using high-level SQL statements, you can control where your data is stored and how it is accessed to provide good performance and tunable latency for your application's users.

Prior to v21.1, the only way to accomplish these goals in a multi-region cluster involved using lower-level mechanisms called <InternalLink path="configure-replication-zones">replication zones</InternalLink> in specific patterns called *Duplicate indexes*, *Geo-partitioned replicas*, and *Geo-partitioned leaseholders*.

These patterns and the use of replication zones are still fully supported. However, for most users, they are harder to use and in some cases can result in worse performance than the multi-region SQL abstractions.

This page discusses:

* Mappings from each of the legacy replication-zone-based patterns to the multi-region SQL abstractions that are designed to replace them.

* Instructions for migrating from replication-zone-based workflows to multi-region SQL abstractions.

* A review of the underlying zone configuration changes that result from running the multi-region SQL statements.

## Replication zone patterns and multi-region SQL abstractions

| Replication Zone Pattern     | Multi-Region SQL                                                                                                                                                                                                        |
| ---------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Duplicate indexes            | <InternalLink path="global-tables">`GLOBAL` tables</InternalLink>                                                                                                                                                       |
| Geo-partitioned replicas     | <InternalLink path="regional-tables#regional-by-row-tables">`REGIONAL BY ROW` tables</InternalLink> with <InternalLink path="multiregion-survival-goals#survive-zone-failures">`ZONE` survival goals</InternalLink>     |
| Geo-partitioned leaseholders | <InternalLink path="regional-tables#regional-by-row-tables">`REGIONAL BY ROW` tables</InternalLink> with <InternalLink path="multiregion-survival-goals#survive-region-failures">`REGION` survival goals</InternalLink> |

<Note>
  CockroachDB will no longer provide the <InternalLink path="topology-follow-the-workload">Follow-the-Workload</InternalLink> pattern's behavior for a database if you use the <InternalLink path="multiregion-overview">multi-region SQL statements</InternalLink> with that database. In other words, the multi-region SQL statements do not provide a behavior that is analogous to Follow-the-Workload.
</Note>

For more information about how to use `ZONE` vs. `REGION` survival goals, see <InternalLink path="multiregion-overview#survival-goals">When to Use `ZONE` vs. `REGION` Survival Goals</InternalLink>.

For more information about when to use `GLOBAL` vs. `REGIONAL` tables, see <InternalLink path="table-localities#when-to-use-regional-vs-global-tables">When to `Use` `REGIONAL` vs. `GLOBAL` Tables</InternalLink>.

## How to migrate a database to the multi-region SQL abstractions

The following instructions assume that you will re-use your existing <InternalLink path="multiregion-overview#cluster-regions">cluster regions</InternalLink> (that is, regions added during cluster setup using <InternalLink path="cockroach-start#locality">`cockroach start --locality`</InternalLink>).

### Performance considerations

Expect performance to be temporarily affected by this process. When you run the commands described below, the cluster may need to do a significant amount of work to meet the new replica placement constraints it has just been given. Therefore, we recommend running this procedure at a time when you would normally perform scheduled maintenance.

Depending on how long you are willing to wait between steps for the replica rebalancing process to settle down, data movement may still be occurring when the next set of constraints is added using the multi-region SQL abstractions; this may lead to further data movement.

In other words, the process described here may result in a significant increase in CPU usage, IOPS, and network traffic while the cluster rebalances replicas to meet the final set of constraints you have provided it with. Until this process completes, the cluster may not be able to handle its normal workload.

Note that the process of dropping the old zone configs that occurs when the old configurations are removed **must be complete** before you can <InternalLink path="multiregion-overview#database-regions">add regions to the database</InternalLink> and set <InternalLink path="alter-table#set-locality">table localities</InternalLink>. You can ensure this process is complete by waiting for the <InternalLink path="alter-database#configure-zone">`ALTER DATABASE ... CONFIGURE ZONE DISCARD`</InternalLink> statement shown below to finish successfully.

For a tutorial that shows how to transition a database to using multi-region SQL statements, see <InternalLink path="demo-low-latency-multi-region-deployment">Low Latency Reads and Writes in a Multi-Region Cluster</InternalLink>.

<Note>
  As part of this migration, data may move temporarily out of the geography where you have constrained it to be placed. For more information about CockroachDB's support for data domiciling, see <InternalLink path="data-domiciling">Data Domiciling with CockroachDB</InternalLink>.
</Note>

### Step 1. Remove the old replication zone configurations

Depending on which <InternalLink path="topology-patterns#multi-region">legacy multi-region topology pattern</InternalLink> you are migrating from, the procedure will vary. For instructions showing how to remove the existing zone configuration for each pattern, see below.

* [Duplicate indexes](#duplicate-indexes)
* [Geo-partitioned leaseholders](#geo-partitioned-leaseholders)
* [Geo-partitioned replicas](#geo-partitioned-replicas)

<Tip>
  You can check the state of any schema object's replication zone configuration at any time using <InternalLink path="show-zone-configurations">`SHOW ZONE CONFIGURATIONS`</InternalLink>.
</Tip>

#### Duplicate indexes

If you used the duplicate indexes pattern, the steps for backing out the old configuration are:

1. Remove the replication zone configurations you added using the <InternalLink path="alter-database#configure-zone">`ALTER DATABASE ... CONFIGURE ZONE DISCARD`</InternalLink> statement. Note that this will remove all zone configurations from the table. If you had any additional customizations beyond what are required for the duplicate indexes pattern, you will have to reapply them.

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

2. Drop the extra indexes you added. This will have the side effect of also deleting the zone configurations you added to those indexes.

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   DROP INDEX postal_codes@idx_central;
   DROP INDEX postal_codes@idx_east;
   ```

The latency and resiliency benefits of the duplicate indexes pattern can be replaced by making `postal_codes` a <InternalLink path="global-tables">`GLOBAL` table</InternalLink> with a <InternalLink path="multiregion-overview#survival-goals">survival goal</InternalLink> that meets your needs.

#### Geo-partitioned replicas

If you applied the geo-partitioned replicas pattern, the steps for backing out the old configuration are:

1. Remove the manually created table partition. This will also automatically remove the replication zone configurations that were applied to the partition as part of the instructions.

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   ALTER TABLE users PARTITION BY NOTHING;
   ```

2. Remove the manually created partition on the secondary indexes. This will also automatically remove the replication zone configurations that were applied to the partition as part of the instructions.

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   ALTER INDEX users_last_name_index PARTITION BY NOTHING;
   ```

The latency and resiliency benefits of the geo-partitioned replicas pattern can be replaced by making `users` a <InternalLink path="regional-tables#regional-by-row-tables">`REGIONAL BY ROW` table</InternalLink> with a <InternalLink path="multiregion-overview#survival-goals">`ZONE` survival goal</InternalLink>.

<Note>
  The multi-region SQL abstractions use a hidden <InternalLink path="alter-table#set-locality">`crdb_region`</InternalLink> column to represent the row's home region. You may need to modify your existing schema to take this into account. For example, if you already have a column you are using to denote each row's home region, you can use that name instead of `crdb_region` by following the instructions on the <InternalLink path="alter-table#set-locality">`ALTER TABLE ... SET LOCALITY`</InternalLink> page.
</Note>

#### Geo-partitioned leaseholders

If you applied the geo-partitioned leaseholders pattern, the steps for backing out the old configuration are:

1. Remove the manually created table partition. This will also automatically remove the replication zone configurations that were applied to the partition as part of the instructions.

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   ALTER TABLE users PARTITION BY NOTHING;
   ```

2. Remove the manually created partition on the secondary indexes. This will also automatically remove the replication zone configurations that were applied to the partition as part of the instructions.

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   ALTER INDEX users_last_name_index PARTITION BY NOTHING;
   ```

The latency and resiliency benefits of the geo-partitioned leaseholders pattern can be replaced by making `users` a <InternalLink path="regional-tables#regional-by-row-tables">`REGIONAL BY ROW` table</InternalLink> with a <InternalLink path="multiregion-overview#survival-goals">`ZONE` survival goal</InternalLink>.

### Step 2. Add a primary region to your database

The steps from this point forward assume that you have cleared your prior replication zone configurations and will be using the <InternalLink path="multiregion-overview">multi-region SQL abstractions</InternalLink> to work with a cluster that has existing <InternalLink path="multiregion-overview#cluster-regions">cluster regions</InternalLink>.

Every multi-region database needs to have a primary region. To set the primary region, issue the <InternalLink path="alter-database#set-primary-region">ALTER DATABASE ... SET PRIMARY REGION</InternalLink> statement:

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

### Step 3. Add more regions to the database

To add another region to the database, issue the <InternalLink path="alter-database#add-region">`ADD REGION`</InternalLink> statement:

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

### Step 4. (Optional) Configure your database survival goal

Depending on your desired database <InternalLink path="multiregion-overview#survival-goals">survival goal</InternalLink>, you can choose from the following settings:

* <InternalLink path="multiregion-overview#survival-goals">`ZONE` survival</InternalLink> (Default): the database will remain fully available for reads and writes if one zone in a region goes down. More than one zone going down concurrently may affect availability.
* <InternalLink path="multiregion-overview#survival-goals">`REGION` survival</InternalLink>: the database will remain fully available for reads and writes if one region goes down. More than one region going down concurrently may affect availability.

For example, to set a region survival goal, issue the following SQL statement:

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

For more information about when to use `ZONE` vs. `REGION` survival goals, see <InternalLink path="multiregion-overview#survival-goals">When to Use `ZONE` vs. `REGION` Survival Goals</InternalLink>.

### Step 5. Configure table localities

For each table in your database, apply the <InternalLink path="multiregion-overview">table locality</InternalLink> that provides the latency and resiliency requirements you need for that table.

As described in [Replication zone patterns and multi-region SQL abstractions](#replication-zone-patterns-and-multi-region-sql-abstractions), the mapping from legacy replication zone patterns to multi-region SQL abstractions is:

| Replication Zone Pattern     | Multi-Region SQL                                                                                                                                                                                                        |
| ---------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Duplicate indexes            | <InternalLink path="global-tables">`GLOBAL` tables</InternalLink>                                                                                                                                                       |
| Geo-partitioned replicas     | <InternalLink path="regional-tables#regional-by-row-tables">`REGIONAL BY ROW` tables</InternalLink> with <InternalLink path="multiregion-survival-goals#survive-zone-failures">`ZONE` survival goals</InternalLink>     |
| Geo-partitioned leaseholders | <InternalLink path="regional-tables#regional-by-row-tables">`REGIONAL BY ROW` tables</InternalLink> with <InternalLink path="multiregion-survival-goals#survive-region-failures">`REGION` survival goals</InternalLink> |

For example, to configure the `postal_codes` table from the [duplicate indexes example](#duplicate-indexes) to use <InternalLink path="multiregion-overview">multi-region SQL</InternalLink>, you would enter the following statements to make the `postal_codes` table a <InternalLink path="global-tables">`GLOBAL` table</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER TABLE postal_codes SET LOCALITY GLOBAL;
```

For more information about when to use `GLOBAL` vs. `REGIONAL` tables, see <InternalLink path="table-localities">When to Use `REGIONAL` vs. `GLOBAL` Tables</InternalLink>.

### Step 6. (Optional) View the updated zone configurations

The multi-region SQL statements operate on the same replication zone configurations that you have access to via the <InternalLink path="alter-database#configure-zone">`ALTER TABLE ... CONFIGURE ZONE`</InternalLink> statement. If you are interested in seeing how they work with the lower-level zone config mechanisms, you can use the <InternalLink path="show-zone-configurations">`SHOW ZONE CONFIGURATIONS`</InternalLink> statement to view the zone configurations.

For example, given a multi-region demo cluster set up using the instructions in <InternalLink path="demo-low-latency-multi-region-deployment">Low Latency Reads and Writes in a Multi-Region Cluster</InternalLink>, here is what the zone configs for several tables in the <InternalLink path="movr">MovR schema</InternalLink> look like.

#### Regional by row tables

A <InternalLink path="table-localities#regional-by-row-tables">`REGIONAL BY ROW`</InternalLink> table differs from the default by setting the following zone configuration settings:

* <InternalLink path="configure-replication-zones">`num_replicas`</InternalLink>
* <InternalLink path="configure-replication-zones">`num_voters`</InternalLink>
* <InternalLink path="configure-replication-zones">`constraints`</InternalLink>
* <InternalLink path="configure-replication-zones">`voter_constraints`</InternalLink>
* <InternalLink path="configure-replication-zones">`lease_preferences`</InternalLink>

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW ZONE CONFIGURATION FROM TABLE users;
```

```
     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)
```

The same table, loaded in a demo cluster with no zone configuration changes, looks like this:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW ZONE CONFIGURATION FROM TABLE users;
```

```
     target     |              raw_config_sql
----------------+-------------------------------------------
  RANGE default | ALTER RANGE default CONFIGURE ZONE USING
                |     range_min_bytes = 134217728,
                |     range_max_bytes = 536870912,
                |     gc.ttlseconds = 90000,
                |     num_replicas = 3,
                |     constraints = '[]',
                |     lease_preferences = '[]'
(1 row)

```

<InternalLink path="multiregion-overview#secondary-regions">Secondary regions</InternalLink> are not compatible with databases containing <InternalLink path="table-localities#regional-by-row-tables">`REGIONAL BY ROW`</InternalLink> 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.

#### Global tables

A <InternalLink path="table-localities">`GLOBAL`</InternalLink> table differs from the default by setting the following zone configuration settings:

* <InternalLink path="configure-replication-zones">`global_reads`</InternalLink>
* <InternalLink path="configure-replication-zones">`num_replicas`</InternalLink>
* <InternalLink path="configure-replication-zones">`num_voters`</InternalLink>
* <InternalLink path="configure-replication-zones">`constraints`</InternalLink>
* <InternalLink path="configure-replication-zones">`voter_constraints`</InternalLink>
* <InternalLink path="configure-replication-zones">`lease_preferences`</InternalLink>

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW ZONE CONFIGURATION FROM TABLE promo_codes;
```

```
       target       |                                      raw_config_sql
--------------------+-------------------------------------------------------------------------------------------
  TABLE promo_codes | ALTER TABLE promo_codes CONFIGURE ZONE USING
                    |     range_min_bytes = 134217728,
                    |     range_max_bytes = 536870912,
                    |     gc.ttlseconds = 90000,
                    |     global_reads = true,
                    |     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)
```

The same table, loaded in a demo cluster with no zone configuration changes, looks like this:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW ZONE CONFIGURATION FROM TABLE promo_codes;
```

```
     target     |              raw_config_sql
----------------+-------------------------------------------
  RANGE default | ALTER RANGE default CONFIGURE ZONE USING
                |     range_min_bytes = 134217728,
                |     range_max_bytes = 536870912,
                |     gc.ttlseconds = 90000,
                |     num_replicas = 3,
                |     constraints = '[]',
                |     lease_preferences = '[]'
(1 row)
```

## See also

* <InternalLink path="multiregion-overview">Multi-Region Capabilities Overview</InternalLink>
* <InternalLink path="multiregion-overview#survival-goals">When to use zone vs. region survival goals</InternalLink>
* <InternalLink path="topology-patterns">Topology Patterns</InternalLink>
* <InternalLink path="disaster-recovery">Disaster Recovery</InternalLink>
* <InternalLink path="demo-low-latency-multi-region-deployment">Low Latency Reads and Writes in a Multi-Region Cluster</InternalLink>
* <InternalLink path="configure-replication-zones">Configure Replication Zones</InternalLink>
* <InternalLink path="architecture/replication-layer#non-voting-replicas">Non-voting replicas</InternalLink>
* <InternalLink path="troubleshoot-replication-zones">Troubleshoot Replication Zones</InternalLink>