Take and Restore Customer-Owned Backups on CockroachDB Cloud

For information on userfile commands, visit the following pages:

• cockroach userfile upload
• cockroach userfile list
• cockroach userfile get
• cockroach userfile delete

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.

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:

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.

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:

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

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.

Back up a cluster

To take a full backup of a cluster:

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

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

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

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

• BACKUP
• Full and incremental backups
• Scheduled backups

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

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

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

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

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:

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

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:

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.

Restore a table

To restore a single table:

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

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