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

The feature described on this page is supported in **CockroachDB self-hosted** clusters. It is also available in **CockroachDB Standard**, **CockroachDB Advanced**, and **CockroachDB Basic** Cloud clusters when you are running [self-managed backups](https://www.cockroachlabs.com/docs/cockroachcloud/take-and-restore-self-managed-backups). For a full list of features, refer to:

* CockroachDB self-hosted: <InternalLink path="backup-and-restore-overview#backup-and-restore-product-support">Backup and restore product support</InternalLink>.
* CockroachDB Cloud: [Backup and restore support](https://www.cockroachlabs.com/docs/cockroachcloud/backup-and-restore-overview#backup-and-restore-support).

CockroachDB Basic and Standard clusters operate with a different architecture compared to CockroachDB self-hosted and CockroachDB Advanced clusters. These architectural differences have implications for how locality-aware backups can run. Basic and Standard clusters will scale resources depending on whether they are actively in use, which means that it is less likely to have a SQL pod available in every locality. As a result, your 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 external connection<InternalLink path="create-external-connection">external connection</InternalLink> with <InternalLink path="backup#query-parameters">`BACKUP` query parameters</InternalLink>, 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.

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

```
	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>
  New in v23.1: `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}';
```

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

```
  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. To avoid this, you can [manually restore zone configurations from a locality-aware backup](#manually-restore-zone-configurations-from-a-locality-aware-backup).
</Note>

## Create an incremental locality-aware backup

If you backup 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. When you're taking an incremental backup, you must ensure that the incremental backup localities match the full backup localities otherwise you will receive an error. Alternatively, take another full backup with the matching localities before running the 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.

## Manually restore zone configurations from a locality-aware backup

During a [locality-aware restore](#restore-from-a-locality-aware-backup), some data may be temporarily located on another node before it is eventually relocated to the appropriate node. To avoid this, you need to manually restore <InternalLink path="configure-replication-zones">zone configurations</InternalLink> first:

Once the locality-aware restore has started, <InternalLink path="pause-job">pause the restore</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
PAUSE JOB 27536791415282;
```

The `system.zones` table stores your cluster's <InternalLink path="configure-replication-zones">zone configurations</InternalLink>, which will prevent the data from rebalancing. To restore them, you must restore the `system.zones` table into a new database because you cannot drop the existing `system.zones` table:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
RESTORE TABLE system.zones FROM '2021/03/23-213101.37' IN
	'azure-blob://acme-co-backup?AZURE_ACCOUNT_KEY=hash&AZURE_ACCOUNT_NAME=acme-co'
	WITH into_db = 'newdb';
```

After it's restored into a new database, you can write the restored `zones` table data to the cluster's existing `system.zones` table:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
INSERT INTO system.zones SELECT * FROM newdb.zones;
```

Then drop the temporary table you created:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
DROP TABLE newdb.zones;
```

Then, <InternalLink path="resume-job">resume the restore</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
RESUME JOB 27536791415282;
```

## 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 version="molt" path="migration-overview">Migration Overview</InternalLink>
* <InternalLink path="cockroach-sql">Use the Built-in SQL Client</InternalLink>
* <InternalLink path="cockroach-commands">`cockroach` Commands Overview</InternalLink>