Take and Restore Customer-Owned Backups

On this page Carat arrow pointing down

CockroachDB Cloud support two types of backups:

  • Managed-service backups: Cockroach Labs takes automated backups of CockroachDB Serverless and CockroachDB Dedicated clusters that are stored in Cockroach Labs' cloud storage. See Use Managed-Service Backups to learn more about the type and frequency of backups supported for both CockroachDB Cloud clusters.
  • Customer-owned backups: You can take manual backups and store them in your cloud storage buckets using the BACKUP statement.

This page describes how to take and restore customer-owned backups from CockroachDB Serverless and CockroachDB Dedicated clusters.

The examples demonstrate how to back up and restore from cloud storage and userfile storage. We recommend using cloud storage for your backups.

Warning:

You cannot restore a backup of a multi-region database into a single-region database.

Examples

Before you begin, connect to your cluster. For guidance on connecting to your CockroachDB Cloud cluster, visit Connect to a CockroachDB Serverless Cluster or Connect to Your CockroachDB Dedicated Cluster.

The examples on this page demonstrate how to take customer-owned backups.

Back up data

For information on userfile commands, visit the following pages:

We recommend starting backups from a time at least 10 seconds in the past using AS OF SYSTEM TIME. Read our guidance in the Performance section on the BACKUP page.

Note:

Only database and table-level backups are possible when using userfile as storage. Restoring cluster-level backups will not work because userfile data is stored in the defaultdb database, and you cannot restore a cluster with existing table data.

Database and table

When working on the same cluster, userfile storage allows for database and table-level backups.

First, run the following statement to backup a database to a directory in the default userfile space:

icon/buttons/copy
BACKUP DATABASE bank INTO 'userfile://defaultdb.public.userfiles_$user/bank-backup' AS OF SYSTEM TIME '-10s';

This directory will hold the files that make up a backup; including the manifest file and data files.

Note:

When backing up from a cluster and restoring a database or table that is stored in your userfile space to a different cluster, you can run cockroach userfile get to download the backup files to a local machine and cockroach userfile upload --url {CONNECTION STRING} to upload to the userfile of the alternate cluster.

BACKUP ... INTO adds a backup to a collection within the backup destination. The path to the backup is created using a date-based naming scheme by default, unless an explicit subdirectory is passed with the BACKUP statement. To view the backup paths in a given destination, use SHOW BACKUPS:

icon/buttons/copy
> SHOW BACKUPS IN 'userfile://defaultdb.public.userfiles_$user/bank-backup';
       path
------------------------
2021/03/23-213101.37
2021/03/24-172553.85
2021/03/24-210532.53
(3 rows)
Note:

For CockroachDB Serverless clusters, you must have billing information on file for your organization to have access to cloud storage. If you don't have billing set up, userfile is your only available storage option for bulk operations. CockroachDB Dedicated users can run bulk operations with userfile or cloud storage.

The cloud storage examples on this page use Amazon S3 for demonstration purposes. For guidance on connecting to other storage options or using other authentication parameters, read Use Cloud Storage for Bulk Operations.

Back up a cluster

To take a full backup of a cluster:

icon/buttons/copy
> BACKUP INTO \
's3://{BUCKET NAME}/{PATH}?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}' \
AS OF SYSTEM TIME '-10s';

Back up a database

To take a full backup of a single database:

icon/buttons/copy
> BACKUP DATABASE bank \
INTO 's3://{BUCKET NAME}/{PATH}?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}' \
AS OF SYSTEM TIME '-10s';

To take a full backup of multiple databases:

icon/buttons/copy
> BACKUP DATABASE bank, employees \
INTO 's3://{BUCKET NAME}/{PATH}?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}' \
AS OF SYSTEM TIME '-10s';

Back up a table or view

To take a full backup of a single table or view:

icon/buttons/copy
> BACKUP bank.customers \
INTO 's3://{BUCKET NAME}/{PATH}?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}' \
AS OF SYSTEM TIME '-10s';

To resolve database or table naming conflicts during a restore, see Troubleshooting naming conflicts.

For more information on taking backups, read the following pages:

Restore data

Note:

Only database and table-level backups are possible when using userfile as storage. Restoring cluster-level backups will not work because userfile data is stored in the defaultdb database, and you cannot restore a cluster with existing table data.

In cases when you need to restore a specific backup, add the backup subdirectory to the RESTORE statement:

icon/buttons/copy
RESTORE DATABASE bank FROM '2021/03/24-210532.53' IN 'userfile://defaultdb.public.userfiles_$user/bank-backup';

It is also possible to run userfile:///bank-backup as userfile:/// refers to the default path userfile://defaultdb.public.userfiles_$user/.

To restore from the most recent backup, use RESTORE FROM LATEST IN ...:

icon/buttons/copy
RESTORE FROM LATEST IN 'userfile://defaultdb.public.userfiles_$user/bank-backup';

Once the backup data is no longer needed, delete from the userfile storage:

icon/buttons/copy
cockroach userfile delete bank-backup --url {CONNECTION STRING}

If you use cockroach userfile delete {file}, it will take as long as the garbage collection to be removed from disk.

To resolve database or table naming conflicts during a restore, see Troubleshooting naming conflicts.

The following examples show how to run manual restores:

View the backup subdirectories

BACKUP ... INTO adds a backup to a collection within the backup destination. The path to the backup is created using a date-based naming scheme. To view the backup paths in a given destination, use SHOW BACKUPS:

icon/buttons/copy
> SHOW BACKUPS IN 's3://{bucket_name}?AWS_ACCESS_KEY_ID={key_id}&AWS_SECRET_ACCESS_KEY={access_key}';

Restore a cluster

To restore a full cluster:

icon/buttons/copy
RESTORE FROM LATEST IN 's3://{bucket_name}?AWS_ACCESS_KEY_ID={key_id}&AWS_SECRET_ACCESS_KEY={access_key}';

To view the available subdirectories to restore a backup from, use SHOW BACKUPS.

Restore a database

To restore a database:

icon/buttons/copy
RESTORE DATABASE bank FROM LATEST IN 's3://{bucket_name}?AWS_ACCESS_KEY_ID={key_id}&AWS_SECRET_ACCESS_KEY={access_key}';

To view the available subdirectories to restore a backup from, use SHOW BACKUPS.

Note:

RESTORE DATABASE can only be used if the entire database was backed up.

Restore a table

To restore a single table:

icon/buttons/copy
> RESTORE TABLE bank.customers FROM LATEST IN 's3://{bucket_name}?AWS_ACCESS_KEY_ID={key_id}&AWS_SECRET_ACCESS_KEY={access_key}';

To restore multiple tables:

icon/buttons/copy
> RESTORE TABLE bank.customers, bank.accounts FROM LATEST IN 's3://{bucket_name}?AWS_ACCESS_KEY_ID={key_id}&AWS_SECRET_ACCESS_KEY={access_key}';

To view the available subdirectories to restore a backup from, use SHOW BACKUPS.

For more information on restoring to your cluster, read the RESTORE page.

See also


Yes No
On this page

Yes No