Take and Restore Self-Managed Backups on CockroachDB Cloud

This page describes how to take and restore on CockroachDB Standard, Advanced, and Basic clusters.

The examples on this page provide a quick overview of the backup features you can run to your own storage bucket. For more technical detail on the complete list of backup features, refer to:

Examples

Before you begin, connect to your cluster:

• .
• .
• .

The examples on this page demonstrate how to back up and restore from your own storage bucket.

Full backup

To take a of a cluster:

BACKUP INTO 'external://backup_s3' AS OF SYSTEM TIME '-10s';

To take a of a single database:

BACKUP DATABASE bank INTO 'external://backup_s3' AS OF SYSTEM TIME '-10s';

To take a of a single table or view:

BACKUP bank.customers INTO 'external://backup_s3' AS OF SYSTEM TIME '-10s';

Backup subdirectories

BACKUP... INTO adds a backup to a location. To view the backup paths in a given collection location (your storage bucket), use :

SHOW BACKUPS IN 's3://bucket/path?AUTH=implicit';

       path
-------------------------
/2023/12/14-190909.83
/2023/12/20-155249.37
/2023/12/21-142943.73
(3 rows)

When you want to restore a specific backup, add the backup’s subdirectory path (e.g., /2023/12/21-142943.73) to the RESTORE statement.

Restore

To restore from the most recent backup () in the collection’s location, use the LATEST syntax:

RESTORE FROM LATEST IN 's3://bucket/path?AUTH=implicit';

If you are restoring an incremental backup, the storage location must contain a full backup.

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

To restore a specific full or incremental backup, specify that backup’s subdirectory in the RESTORE statement. To view the available subdirectories, use . If you are restoring an incremental backup, the URI must point to the storage location that contains the full backup:

RESTORE FROM '2023/03/23-213101.37' IN 's3://bucket/path?AUTH=implicit';

Incremental backup

When a BACKUP statement specifies an existing subdirectory in the collection, explicitly or via the LATEST keyword, an incremental backup will be added to the default /incrementals directory at the root of the storage location.

To take an incremental backup using the LATEST keyword:

BACKUP INTO LATEST IN 'external://backup_s3' AS OF SYSTEM TIME '-10s';

To store the backup in an existing subdirectory in the collection:

BACKUP INTO {'subdirectory'} IN 'external://backup_s3' AS OF SYSTEM TIME '-10s';

If you intend to take a full backup, we recommend running BACKUP INTO {collectionURI} without specifying a subdirectory.

Scheduled backup

This example for a cluster backup with revision history that is taken every day at midnight:

CREATE SCHEDULE schedule_label
  FOR BACKUP INTO 's3://test/backups/schedule_test?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x'
    WITH revision_history
    RECURRING '@daily';

     schedule_id     |     name       |                     status                     |            first_run             | schedule |                                                                               backup_stmt
---------------------+----------------+------------------------------------------------+----------------------------------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------
  588796190000218113 | schedule_label | PAUSED: Waiting for initial backup to complete | NULL                             | @daily   | BACKUP INTO LATEST IN 's3://test/schedule-test?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x' WITH revision_history, detached
  588796190012702721 | schedule_label | ACTIVE                                         | 2020-09-10 16:52:17.280821+00:00 | @weekly  | BACKUP INTO 's3://test/schedule-test?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x' WITH revision_history, detached
(2 rows)

Because the FULL BACKUP clause is not included, CockroachDB also scheduled a full backup to run @weekly. This is the default cadence for incremental backups RECURRING > 1 hour but <= 1 day.

Encrypted backup

You can take and restore encrypted backups in the following ways:

Refer to the page for technical detail and a full list of examples.

For example, you can run a backup with AWS KMS with the BACKUP statement’s kms option:

BACKUP INTO 's3://{BUCKET NAME}?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}'
    WITH kms = 'aws:///{key}?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}&REGION=us-east-1';

Locality-aware backup

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

For example, to create a 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:

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.
• If you are creating an with , 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 node.

You can restore the backup by running:

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

Backup and restore data from userfile storage

To put files on your CockroachDB cluster without external servers, use , a per-user file storage.

For information on userfile commands, visit the following pages:

We recommend starting backups from a time at least 10 seconds in the past using . Read our guidance in the section on the page.

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.

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.

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 to download the backup files to a local machine and 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 is passed with the BACKUP statement. To view the backup paths in a given destination, use :

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

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:

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 '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 to be removed from disk.

Back up a CockroachDB Cloud cluster and restore into a new cluster

You can move an existing CockroachDB Cloud cluster’s contents to a new cluster by creating a self-managed backup and restoring the backup in the new cluster.

This method of migration requires a period of downtime during which application traffic to the cluster must be stopped. If minimal downtime on the cluster is a requirement, contact the .

To move to a new cluster, follow these steps:

1. Create a of the source cluster if a self-managed backup of the cluster does not already exist. If the backup storage is in AWS S3 or Google Cloud GCS, append ?AUTH=specified to the storage URL.

2. Provision the .

3. Stop application traffic to the source cluster.

4. Perform a to include any last-minute changes to data on the cluster.

5. :

   Mac

   cockroach sql --url 'postgresql://<user@<cluster-name-<short-id.<region.cockroachlabs.cloud:26257/<database?sslmode=verify-full&sslrootcert='$HOME'/Library/CockroachCloud/certs/<cluster-name-ca.crt'
   
   Linux

   cockroach sql --url 'postgresql://<user@<cluster-name-<short-id.<region.cockroachlabs.cloud:26257/<database?sslmode=verify-full&sslrootcert='$HOME'/Library/CockroachCloud/certs/<cluster-name-ca.crt'
   
   Windows

   cockroach sql --url "postgresql://<user@<cluster-name-<short-id.<region.cockroachlabs.cloud:26257/<database?sslmode=verify-full&sslrootcert=$env:appdata\CockroachCloud\certs\$<cluster-name-ca.crt"
   

   Where:

   • <user is the SQL user. By default, this is your CockroachDB Cloud account username.

   • <cluster-name-<short-id is the short name of your cluster plus the short ID. For example, funny-skunk-3ab.

   • <cluster-id is a unique string used to identify your cluster when downloading the CA certificate. For example, 12a3bcde-4fa5-6789-1234-56bc7890d123.

   • <region is the region in which your cluster is running. If you have a multi-region cluster, you can choose any of the regions in which your cluster is running. For example, aws-us-east-1.

   • <database is the name for your database. For example, defaultdb.

     You can find these settings in the Connection parameters tab of the Connection info dialog.

6. the latest backup to the new cluster:

   a. Use SHOW BACKUPS with your external location to find the backup’s subdirectory:

     SHOW BACKUPS IN 'gs://{bucket name}/{path/to/backup}?AUTH=specified&CREDENTIALS={encoded key}';
   
             path
     ------------------------
     2021/03/23-213101.37
     2021/03/24-172553.85
     2021/03/24-210532.53
     (3 rows)
   

   b. Use the subdirectory to specify the backup to restore:

     RESTORE DATABASE example_database FROM '2021/03/23-213101.37' IN 'gs://{bucket name}/{path/to/backup}?AUTH=specified&CREDENTIALS={encoded key}';
   

7. Update application connection strings to use the new cluster.

8. Resume application traffic and monitor performance on the new cluster.

9. .

Back up a self-hosted CockroachDB cluster and restore into a CockroachDB Cloud cluster

To back up a self-hosted CockroachDB cluster and restore into a CockroachDB Cloud cluster:

1. While , your databases and/or tables to an :

   BACKUP DATABASE example_database INTO 'gs://{bucket name}/{path/to/backup}?AUTH=specified&CREDENTIALS={encoded key}';
   

If you are backing up the data to AWS or GCP, use the specified option for the AUTH parameter, as CockroachDB Cloud will need the specified credentials upon . For more information on authentication parameters to cloud storage providers, see .

2. :

   Mac

   cockroach sql --url 'postgresql://<user@<cluster-name-<short-id.<region.cockroachlabs.cloud:26257/<database?sslmode=verify-full&sslrootcert='$HOME'/Library/CockroachCloud/certs/<cluster-name-ca.crt'
   
   Linux

   cockroach sql --url 'postgresql://<user@<cluster-name-<short-id.<region.cockroachlabs.cloud:26257/<database?sslmode=verify-full&sslrootcert='$HOME'/Library/CockroachCloud/certs/<cluster-name-ca.crt'
   
   Windows

   cockroach sql --url "postgresql://<user@<cluster-name-<short-id.<region.cockroachlabs.cloud:26257/<database?sslmode=verify-full&sslrootcert=$env:appdata\CockroachCloud\certs\$<cluster-name-ca.crt"
   

   Where:

   • <user is the SQL user. By default, this is your CockroachDB Cloud account username.

   • <cluster-name-<short-id is the short name of your cluster plus the short ID. For example, funny-skunk-3ab.

   • <cluster-id is a unique string used to identify your cluster when downloading the CA certificate. For example, 12a3bcde-4fa5-6789-1234-56bc7890d123.

   • <region is the region in which your cluster is running. If you have a multi-region cluster, you can choose any of the regions in which your cluster is running. For example, aws-us-east-1.

   • <database is the name for your database. For example, defaultdb.

     You can find these settings in the Connection parameters tab of the Connection info dialog.

3. to your CockroachDB Cloud cluster.

   a. Use SHOW BACKUPS with your external location to find the backup’s subdirectory:

     SHOW BACKUPS IN 'gs://{bucket name}/{path/to/backup}?AUTH=specified&CREDENTIALS={encoded key}';
   
             path
     ------------------------
     2021/03/23-213101.37
     2021/03/24-172553.85
     2021/03/24-210532.53
     (3 rows)
   

   b. Use the subdirectory to specify the backup to restore:

     RESTORE DATABASE example_database FROM '2021/03/23-213101.37' IN 'gs://{bucket name}/{path/to/backup}?AUTH=specified&CREDENTIALS={encoded key}';
   

See also