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

# Take and Restore Locality-aware Backups

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

Locality-aware backups allow you to partition and store backup data in a way that is optimized for locality. When you run a locality-aware backup, nodes write backup data to the <InternalLink path="use-cloud-storage">cloud storage</InternalLink> bucket that is closest to the node locality configured at <InternalLink path="cockroach-start">node startup</InternalLink>.

<Danger>
  While a locality-aware backup will always match the node locality and storage bucket locality, a <InternalLink path="architecture/overview#range">range's</InternalLink> locality will not necessarily match the node's locality. The backup job will attempt to back up ranges through nodes matching that range's locality, however this is not always possible. As a result, **Cockroach Labs cannot guarantee that all ranges will be backed up to a cloud storage bucket with the same locality.** You should consider this as you plan a backup strategy that must comply with <InternalLink path="data-domiciling">data domiciling</InternalLink> requirements.
</Danger>

A locality-aware backup is specified by a list of URIs, each of which has a `COCKROACH_LOCALITY` URL parameter whose single value is either `default` or a single locality key-value pair such as `region=us-east`. At least one `COCKROACH_LOCALITY` must be the `default`. [Restore jobs can read from a locality-aware backup](#restore-from-a-locality-aware-backup) when you provide the list of URIs that together contain the locations of all of the files for a single locality-aware backup.

A successful locality-aware backup job requires that each node in the cluster has access to each storage location. This is because any node in the cluster can claim the job and become the <InternalLink path="backup-architecture#job-creation-phase">*coordinator*</InternalLink> node.

## Technical overview

For a technical overview of how a locality-aware backup works, refer to <InternalLink path="backup-architecture#job-coordination-and-export-of-locality-aware-backups">Job coordination and export of locality-aware backups</InternalLink>.

## Supported products

Locality-aware backups are available in **CockroachDB Advanced**, **CockroachDB Standard**, **CockroachDB Basic**, and **CockroachDB self-hosted** clusters when you are running <InternalLink version="cockroachcloud" path="take-and-restore-self-managed-backups">self-managed backups</InternalLink>. For a full list of features on CockroachDB Cloud, refer to <InternalLink version="cockroachcloud" path="backup-and-restore-overview">Backup and Restore Overview</InternalLink>.

Both CockroachDB Standard and CockroachDB Basic clusters operate with a different architecture compared to CockroachDB self-hosted. These architectural differences have implications for how locality-aware backups can run. Standard and Basic clusters will scale resources depending on whether they are actively in use. This makes it less likely to have a SQL pod available in every locality. As a result, your Serverless cluster may not have a SQL pod in the locality where the data resides, which can lead to the cluster uploading that data to a storage bucket in a locality where you do have active SQL pods. You should consider this as you plan a backup strategy that must comply with <InternalLink path="data-domiciling">data domiciling</InternalLink> requirements.

CockroachDB also supports *locality-restricted backup execution*, which allows you to specify a set of locality filters for a backup job to restrict the nodes that can participate in the backup process to that locality. This allows only nodes to execute a backup that meet certain requirements, such as being located in a specific region or having access to a certain storage bucket. Refer to <InternalLink path="take-locality-restricted-backups">Take Locality-restricted Backups</InternalLink> for more detail.

## Create a locality-aware backup

For example, to create a locality-aware backup where nodes with the locality `region=us-west` write backup files to `s3://us-west-bucket`, and all other nodes write to `s3://us-east-bucket` by default, run:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BACKUP INTO
      ('s3://us-east-bucket?COCKROACH_LOCALITY=default', 's3://us-west-bucket?COCKROACH_LOCALITY=region%3Dus-west');
```

When you run the `BACKUP` statement for a locality-aware backup, check the following:

* The locality query string parameters must be [URL-encoded](https://wikipedia.org/wiki/Percent-encoding).
* If you are creating an <InternalLink path="create-external-connection">external connection</InternalLink> with <InternalLink path="backup#query-parameters">`BACKUP` query parameters</InternalLink> or <InternalLink path="cloud-storage-authentication">authentication</InternalLink> parameters, you must pass them in uppercase otherwise you will receive an `unknown query parameters` error.
* A successful locality-aware backup job requires that each node in the cluster has access to each storage location. This is because any node in the cluster can claim the job and become the <InternalLink path="backup-architecture#job-creation-phase">*coordinator*</InternalLink> node.

Locality-aware backups attempt to match data to buckets in corresponding regions as specified. However, edge cases exist in which a locality-aware backup may be forced to write to the default bucket instead, such as when the cluster has unhealthy nodes. Use the <InternalLink path="backup#options">`STRICT`</InternalLink> option when creating a locality-aware backup to force the backup to fail in these cases to avoid violating <InternalLink path="data-domiciling">data domiciling</InternalLink> requirements.

You can restore the backup by running:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
RESTORE FROM LATEST IN ('s3://us-east-bucket', 's3://us-west-bucket');
```

Note that the first URI in the list has to be the URI specified as the `default` URI when the backup was created. If you have moved your backups to a different location since the backup was originally taken, the first URI must be the new location of the files originally written to the `default` location.

To restore from a specific backup, use <InternalLink path="restore#restore-a-specific-full-or-incremental-backup">`RESTORE FROM {subdirectory} IN...`</InternalLink>

For guidance on how to identify the locality of a node to pass in a backup query, see [Show a node's locality](#show-a-node’s-locality).

<Note>
  For guidance on connecting to other storage options or using other authentication parameters, read <InternalLink path="use-cloud-storage">Use Cloud Storage</InternalLink>.
</Note>

## Show a node's locality

To determine the locality that a node was started with, run <InternalLink path="show-locality">`SHOW LOCALITY`</InternalLink>:

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

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    locality
+---------------------+
region=us-east,az=az1
(1 row)
```

The output shows the locality to which the node will write backup data. One of the single locality key-value pairs can be passed to `BACKUP` with the `COCKROACH_LOCALITY` parameter (e.g., `'s3://us-east-bucket?COCKROACH_LOCALITY=region%3Dus-east'`).

<Note>
  Specifying both locality tier pairs (e.g., `region=us-east,az=az1`) from the output will cause the backup query to fail with: `tier must be in the form "key=value"`.
</Note>

## Show locality-aware backups

<Note>
  `SHOW BACKUP` is able to display metadata using `check_files` for locality-aware backups taken with the <InternalLink path="show-backup#show-a-backup-taken-with-the-incremental-location-option">`incremental_location`</InternalLink> option.
</Note>

To view a list of <InternalLink path="take-and-restore-locality-aware-backups">locality-aware backups</InternalLink>, pass the endpoint <InternalLink path="backup#backup-file-urls">collection URI</InternalLink> that is set as the `default` location with `COCKROACH_LOCALITY=default`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW BACKUPS IN 's3://{default collection URI}/{path}?AWS_ACCESS_KEY_ID={placeholder}&AWS_SECRET_ACCESS_KEY={placeholder}';
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
        path
-------------------------
/2023/02/23-150925.62
/2023/03/08-192859.44
(2 rows)
```

To view a <InternalLink path="take-and-restore-locality-aware-backups">locality-aware backup</InternalLink>, pass locality-aware backup URIs to `SHOW BACKUP`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW BACKUP FROM LATEST IN ('s3://{bucket name}/locality?AWS_ACCESS_KEY_ID={placeholder}&AWS_SECRET_ACCESS_KEY={placeholder}&COCKROACH_LOCALITY=default', 's3://{bucket name}/locality?AWS_ACCESS_KEY_ID={placeholder}&AWS_SECRET_ACCESS_KEY={placeholder}&COCKROACH_LOCALITY=region%3Dus-west');
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  database_name | parent_schema_name |        object_name         | object_type | backup_type | start_time |          end_time          | size_bytes | rows | is_full_cluster
----------------+--------------------+----------------------------+-------------+-------------+------------+----------------------------+------------+------+------------------
  NULL          | NULL               | movr                       | database    | full        | NULL       | 2023-02-23 15:09:25.625777 |       NULL | NULL |        f
  movr          | NULL               | public                     | schema      | full        | NULL       | 2023-02-23 15:09:25.625777 |       NULL | NULL |        f
  movr          | public             | users                      | table       | full        | NULL       | 2023-02-23 15:09:25.625777 |       5633 |   58 |        f
  movr          | public             | vehicles                   | table       | full        | NULL       | 2023-02-23 15:09:25.625777 |       3617 |   17 |        f
  movr          | public             | rides                      | table       | full        | NULL       | 2023-02-23 15:09:25.625777 |     159269 |  511 |        f
  movr          | public             | vehicle_location_histories | table       | full        | NULL       | 2023-02-23 15:09:25.625777 |      79963 | 1092 |        f
  movr          | public             | promo_codes                | table       | full        | NULL       | 2023-02-23 15:09:25.625777 |     221763 | 1003 |        f
  movr          | public             | user_promo_codes           | table       | full        | NULL       | 2023-02-23 15:09:25.625777 |        927 |   11 |        f
(8 rows)
```

## Restore from a locality-aware backup

Given a list of URIs that together contain the locations of all of the files for a single [locality-aware backup](#create-a-locality-aware-backup), <InternalLink path="restore">`RESTORE`</InternalLink> can read in that backup. Note that the list of URIs passed to <InternalLink path="restore">`RESTORE`</InternalLink> may be different from the URIs originally passed to <InternalLink path="backup">`BACKUP`</InternalLink>. This is because it's possible to move the contents of one of the parts of a locality-aware backup (i.e., the files written to that destination) to a different location, or even to consolidate all the files for a locality-aware backup into a single location.

When restoring a <InternalLink path="take-full-and-incremental-backups#full-backups">full backup</InternalLink>, the cluster data is restored first, then the system table data "as is." This means that the restored <InternalLink path="alter-partition#create-a-replication-zone-for-a-partition">zone configurations</InternalLink> can point to regions that do not have active nodes in the new cluster. For example, if your full backup has the following zone configurations:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER PARTITION europe_west OF INDEX movr.public.rides@rides_pkey \
        CONFIGURE ZONE USING constraints = '[+region=europe-west1]';

ALTER PARTITION us_east OF INDEX movr.public.rides@rides_pkey \
        CONFIGURE ZONE USING constraints = '[+region=us-east1]';

ALTER PARTITION us_west OF INDEX movr.public.rides@rides_pkey \
        CONFIGURE ZONE USING constraints = '[+region=us-west1]';
```

And the restored cluster does not have <InternalLink path="partitioning#node-attributes">nodes with the locality</InternalLink> `region=us-west1`, the restored cluster will still have a zone configuration for `us-west1`. This means that the cluster's data will **not** be reshuffled to `us-west1` because the region does not exist. The data will be distributed as if the zone configuration does not exist. For the data to be distributed correctly, you can <InternalLink path="cockroach-start">add node(s)</InternalLink> with the missing region or <InternalLink path="configure-replication-zones#remove-a-replication-zone">remove the zone configuration</InternalLink>.

For example, use the following to create a locality-aware backup:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BACKUP INTO
      ('s3://us-east-bucket?COCKROACH_LOCALITY=default', 's3://us-west-bucket?COCKROACH_LOCALITY=region%3Dus-west')
```

Restore a locality-aware backup with:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
RESTORE FROM LATEST IN ('s3://us-east-bucket/', 's3://us-west-bucket/');
```

To restore from a specific backup, use <InternalLink path="restore#restore-a-specific-full-or-incremental-backup">`RESTORE FROM {subdirectory} IN...`</InternalLink>.

<Note>
  <InternalLink path="restore">`RESTORE`</InternalLink> is not truly locality-aware; while restoring from backups, a node may read from a store that does not match its locality. This can happen in the cases that either the <InternalLink path="backup">`BACKUP`</InternalLink> or <InternalLink path="restore">`RESTORE`</InternalLink> was not of a <InternalLink path="take-full-and-incremental-backups#full-backups">full cluster</InternalLink>. Note that during a locality-aware restore, some data may be temporarily located on another node before it is eventually relocated to the appropriate node.
</Note>

## Create an incremental locality-aware backup

If you back up to a destination already containing a <InternalLink path="take-full-and-incremental-backups#full-backups">full backup</InternalLink>, an <InternalLink path="take-full-and-incremental-backups#incremental-backups">incremental backup</InternalLink> will be appended to the full backup in a subdirectory. To take a locality-aware incremental backup and restore from it successfully, you must maintain the same storage URI to `COCKROACH_LOCALITY` query parameter mappings for all incrementals in the backup collection. Alternatively, take another full backup with the necessary storage URI to `COCKROACH_LOCALITY` mapping before running an incremental backup.

There is different syntax for taking an incremental backup depending on where you need to store the backups:

* To append your incremental backup to the full backup in the <InternalLink path="take-full-and-incremental-backups#backup-collections">`incrementals` directory</InternalLink>:

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  BACKUP INTO LATEST IN
      ('s3://us-east-bucket?COCKROACH_LOCALITY=default', 's3://us-west-bucket?COCKROACH_LOCALITY=region%3Dus-west');
  ```

<Note>
  When [restoring from an incremental locality-aware backup](#restore-from-an-incremental-locality-aware-backup), you need to include **every** locality ever used, even if it was only used once. At least one `COCKROACH_LOCALITY` must be the `default`.
</Note>

* To explicitly control the subdirectory for your incremental backup:

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  BACKUP INTO {subdirectory} IN
          ('s3://us-east-bucket?COCKROACH_LOCALITY=default', 's3://us-west-bucket?COCKROACH_LOCALITY=region%3Dus-west');
  ```

  To view the available subdirectories, use <InternalLink path="restore#view-the-backup-subdirectories">`SHOW BACKUPS`</InternalLink>.
* To append your incremental backup to the full backup using the <InternalLink path="backup#options">`incremental_location`</InternalLink> option to send your incremental backups to a different location, you must include the same number of locality-aware URIs for the full backup destination and the `incremental_location` option:

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  BACKUP INTO LATEST IN
      ('s3://us-east-bucket?COCKROACH_LOCALITY=default', 's3://us-west-bucket?COCKROACH_LOCALITY=region%3Dus-west') WITH incremental_location = ('s3://us-east-bucket-2?COCKROACH_LOCALITY=default', 's3://us-west-bucket-2?COCKROACH_LOCALITY=region%3Dus-west');
  ```

  For more detail on using the `incremental_location` option, see <InternalLink path="take-full-and-incremental-backups#incremental-backups-with-explicitly-specified-destinations">Incremental backups with explicitly specified destinations</InternalLink>.

<Danger>
  **Removed in v26.2**: The `incremental_location` option has been removed in v26.2, following its prior <InternalLink version="releases" path="v25.4">deprecation</InternalLink>. Existing backups taken with this option cannot be restored in v26.2 or later. If you have backups that use `incremental_location`, you can only restore them using a cluster running v26.1 or earlier.
</Danger>

## Restore from an incremental locality-aware backup

A locality-aware backup URI can also be used in place of any incremental backup URI in <InternalLink path="restore">`RESTORE`</InternalLink>.

For example, an incremental locality-aware backup created with

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BACKUP INTO LATEST IN
      ('s3://us-east-bucket?COCKROACH_LOCALITY=default', 's3://us-west-bucket?COCKROACH_LOCALITY=region%3Dus-west')
```

can be restored by running:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
RESTORE FROM LATEST IN ('s3://us-east-bucket/', 's3://us-west-bucket/');
```

To restore from a specific backup, use <InternalLink path="restore#restore-a-specific-full-or-incremental-backup">`RESTORE FROM {subdirectory} IN...`</InternalLink>.

When <InternalLink path="take-and-restore-locality-aware-backups#restore-from-an-incremental-locality-aware-backup">restoring from an incremental locality-aware backup</InternalLink>, you need to include **every** locality ever used, even if it was only used once.

## See also

* <InternalLink path="backup">`BACKUP`</InternalLink>
* <InternalLink path="restore">`RESTORE`</InternalLink>
* <InternalLink path="take-full-and-incremental-backups">Take Full and Incremental Backups</InternalLink>
* <InternalLink path="take-and-restore-encrypted-backups">Take and Restore Encrypted Backups</InternalLink>
* <InternalLink path="take-backups-with-revision-history-and-restore-from-a-point-in-time">Take Backups with Revision History and Restore from a Point-in-time</InternalLink>
* <InternalLink path="cockroach-sql">Use the Built-in SQL Client</InternalLink>
* <InternalLink path="cockroach-commands">`cockroach` Commands Overview</InternalLink>