Run Bulk Operations from Your Cluster

On this page Carat arrow pointing down

CockroachDB Serverless and CockroachDB Dedicated offer different levels of support for the following bulk operations. This page provides information on the availability of these operations in both types of CockroachDB Cloud cluster and examples.

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.

For information on userfile commands, visit the following pages:

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.

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.

Backup and restore data

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)

In cases when your database needs to be restored, run the following:

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

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.

Cockroach Labs runs full backups daily and incremental backups hourly for every CockroachDB Cloud cluster. The full backups are retained for 30 days, while incremental backups are retained for 7 days. For more information, read Restore Data From a Backup.

The following examples show how to run manual backups and restores:

Backup 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';

Backup 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';

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

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 taking backups and restoring to your cluster, read the following pages:

Import data into your CockroachDB Cloud cluster

To import a table from userfile, use the following command:

icon/buttons/copy
IMPORT TABLE customers (
        id UUID PRIMARY KEY,
        name TEXT,
        INDEX name_idx (name)
)
   CSV DATA ('userfile:///test-data.csv');

userfile:/// references the default path (userfile://defaultdb.public.userfiles_$user/).

        job_id       |  status   | fraction_completed |  rows  | index_entries |  bytes
---------------------+-----------+--------------------+--------+---------------+-----------
  599865027685613569 | succeeded |                  1 | 300024 |             0 | 13389972
(1 row)

For more import options, see IMPORT.

To import a table into your cluster:

icon/buttons/copy
> IMPORT TABLE customers (
        id UUID PRIMARY KEY,
        name TEXT,
        INDEX name_idx (name)
)
CSV DATA ('s3://{BUCKET NAME}/{customer-data}?AWS_ACCESS_KEY_ID={ACCESS KEY}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}')
;

Read the IMPORT page for more examples and guidance.

Export data out of CockroachDB Cloud

Note:

Using EXPORT with userfile is not recommended. If you need to export data from a Serverless cluster, you can either set up billing for your organization to access cloud storage or export data to a local CSV file.

The following example exports the customers table from the bank database into a cloud storage bucket in CSV format:

EXPORT INTO CSV
  's3://{BUCKET NAME}/{customer-export-data}?AWS_ACCESS_KEY_ID={ACCESS KEY}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}'
  WITH delimiter = '|' FROM TABLE bank.customers;

Read the EXPORT page for more examples and guidance.

See also


Yes No
On this page

Yes No