RESTORE

Warning:
The RESTORE feature is only available to enterprise users. For non-enterprise restores, see Restore Data.

The RESTORE statement restores your cluster's schemas and data from an enterprise BACKUP stored on a services such as AWS S3, Google Cloud Storage, NFS, or HTTP storage.

Because CockroachDB is designed with high fault tolerance, restores are designed primarily for disaster recovery, i.e., restarting your cluster if it loses a majority of its nodes. Isolated issues (such as small-scale node outages) do not require any intervention.

Functional details

Restore targets

You can restore entire tables (which automatically includes their indexes) or views from a backup. This process uses the data stored in the backup to create entirely new tables or views in the target database.

The notion of "restoring a database" simply restores all of the tables and views that belong to the database, but does not create the database. For more information, see Target Database.

Note:
RESTORE only offers table-level granularity; it does not support restoring subsets of a table.

Because this process is designed for disaster recovery, CockroachDB expects that the tables do not currently exist in the target database. This means the target database must have not have tables or views with the same name as the restored table or view. If any of the restore target's names are being used, you can:

Object dependencies

Dependent objects must be restored at the same time as the objects they depend on.

ObjectDepends On
Table with foreign key constraintsThe table it REFERENCES (however, this dependency can be removed during the restore).
Table with a sequenceThe sequence.
ViewsThe tables used in the view's SELECT statement.
Interleaved tablesThe parent table in the interleaved hierarchy.

Target database

By default, tables and views are restored into a database with the name of the database from which they were backed up. However, also consider:

Users and privileges

Table and view users/privileges are not restored. Restored tables and views instead inherit the privileges of the database into which they're restored.

However, every backup includes system.users, so you can restore users and their passwords.

Table-level privileges must be granted to users after the restore is complete.

Restore types

You can either restore from a full backup or from a full backup with incremental backups, based on the backup files you include.

Restore TypeParameters
Full backupInclude only the path to the full backup.
Full backup + incremental backupsInclude the path to the full backup as the first argument and the subsequent incremental backups from oldest to newest as the following arguments.

Point-in-time restore

Warning:

This is a beta feature. It is currently undergoing continued testing. Please file a Github issue with us if you identify a bug.

If the full or incremental backup was taken with revision history, you can restore the data as it existed at the specified point-in-time within the revision history captured by that backup.

If you do not specify a point-in-time, the data will be restored to the backup timestamp; that is, the restore will work as if the data was backed up without revision history.

Performance

The RESTORE process minimizes its impact to the cluster's performance by distributing work to all nodes. Subsets of the restored data (known as ranges) are evenly distributed among randomly selected nodes, with each range initially restored to only one node. Once the range is restored, the node begins replicating it others.

Note:
When a RESTORE fails or is canceled, partially restored data is properly cleaned up. This can have a minor, temporary impact on cluster performance.

Viewing and controlling restore jobs

After CockroachDB successfully initiates a restore, it registers the restore as a job, which you can view with SHOW JOBS.

After the restore has been initiated, you can control it with PAUSE JOB, RESUME JOB, and CANCEL JOB.

Synopsis

RESTORETABLEtable_pattern,DATABASEdatabase_name,FROMfull_backup_locationincremental_backup_location,ASOFSYSTEMTIMEtimestampWITHkv_option_list

Note:
The RESTORE statement cannot be used within a transaction.

Required privileges

Only members of the admin role can run RESTORE. By default, the root user belongs to the admin role.

Parameters

ParameterDescription
table_patternThe table or view you want to restore.
database_nameThe name of the database you want to restore (i.e., restore all tables and views in the database). You can restore an entire database only if you had backed up the entire database.
full_backup_locationThe URL where the full backup is stored. For information about this URL structure, see Backup File URLs.
incremental_backup_locationThe URL where an incremental backup is stored. Lists of incremental backups must be sorted from oldest to newest. The newest incremental backup's timestamp must be within the table's garbage collection period.For information about this URL structure, see Backup File URLs. For more information about garbage collection, see Configure Replication Zones.
AS OF SYSTEM TIME timestampRestore data as it existed as of timestamp. You can restore point-in-time data only if you had taken full or incremental backup with revision history.
kv_option_listControl your backup's behavior with these options.

Backup file URLs

The URL for your backup's locations must use the following format:

  1. [scheme]://[host]/[path]?[parameters]
LocationSchemeHostParameters
Amazon S3s3Bucket nameAWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY, AWS_SESSION_TOKEN
AzureazureN/A (see Example file URLsAZURE_ACCOUNT_KEY, AZURE_ACCOUNT_NAME
Google Cloud 1gsBucket nameAUTH (optional; can be default, implicit, or specified), CREDENTIALS
HTTP 2httpRemote hostN/A
NFS/Local 3nodelocalEmpty or nodeID 4 (see Example file URLs)N/A
S3-compatible services 5s3Bucket nameAWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY, AWS_SESSION_TOKEN, AWS_REGION 6 (optional), AWS_ENDPOINT

Note:

The location parameters often contain special characters that need to be URI-encoded. Use Javascript's encodeURIComponent function or Go language's url.QueryEscape function to URI-encode the parameters. Other languages provide similar functions to URI-encode special characters.

Note:

If your environment requires an HTTP or HTTPS proxy server for outgoing connections, you can set the standard HTTP_PROXY and HTTPS_PROXY environment variables when starting CockroachDB.

  • 1If the AUTH parameter is not specified, the cloudstorage.gs.default.key cluster setting will be used if it is non-empty, otherwise the implicit behavior is used. If the AUTH parameter is implicit, all GCS connections use Google's default authentication strategy. If the AUTH parameter is default, the cloudstorage.gs.default.key cluster setting must be set to the contents of a service account file which will be used during authentication. New in v19.1: If the AUTH parameter is specified, GCS connections are authenticated on a per-statement basis, which allows the JSON key object to be sent in the CREDENTIALS parameter. The JSON key object should be base64-encoded (using the standard encoding in RFC 4648).

  • 2 You can create your own HTTP server with Caddy or nginx. A custom root CA can be appended to the system's default CAs by setting the cloudstorage.http.custom_ca cluster setting, which will be used when verifying certificates from HTTPS URLs.

  • 3 The file system backup location on the NFS drive is relative to the path specified by the —external-io-dir flag set while starting the node. If the flag is set to disabled, then imports from local directories and NFS drives are disabled.

  • 4New in v19.1: The host component of NFS/Local can either be empty or the nodeID. If the nodeID is specified, it is currently ignored (i.e., any node can be sent work and it will look in its local input/output directory); however, the nodeID will likely be required in the future.

  • 5 A custom root CA can be appended to the system's default CAs by setting the cloudstorage.http.custom_ca cluster setting, which will be used when verifying certificates from an S3-compatible service.

  • 6 The AWS_REGION parameter is optional since it is not a required parameter for most S3-compatible services. Specify the parameter only if your S3-compatible service requires it.

Example file URLs

LocationExample
Amazon S3s3://acme-co/employees.sql?AWS_ACCESS_KEY_ID=123&AWS_SECRET_ACCESS_KEY=456
Azureazure://employees.sql?AZURE_ACCOUNT_KEY=123&AZURE_ACCOUNT_NAME=acme-co
Google Cloudgs://acme-co/employees.sql
HTTPhttp://localhost:8080/employees.sql
NFS/Localnodelocal:///employees.sql, nodelocal://2/employees.sql

Restore option list

You can include the following options as key-value pairs in the kv_option_list to control the restore process's behavior.

into_db

  • Description: If you want to restore a table or view into a database other than the one it originally existed in, you can change the target database. This is useful if you want to restore a table that currently exists, but do not want to drop it.
  • Key: into_db
  • Value: The name of the database you want to use
  • Example: WITH into_db = 'newdb'

skip_missing_foreign_keys

  • Description: If you want to restore a table with a foreign key but do not want to restore the table it references, you can drop the Foreign Key constraint from the table and then have it restored.
  • Key: skip_missing_foreign_keys
  • Value: No value
  • Example: WITH skip_missing_foreign_keys

skip_missing_sequences

  • Description: If you want to restore a table that depends on a sequence but do not want to restore the sequence it references, you can drop the sequence dependency from a table (i.e., the DEFAULT expression that uses the sequence) and then have it restored.
  • Key: skip_missing_sequences
  • Value: No value
  • Example: WITH skip_missing_sequences

Examples

Restore a single table

  1. > RESTORE bank.customers FROM 'gs://acme-co-backup/database-bank-2017-03-27-weekly';

Restore multiple tables

  1. > RESTORE bank.customers, bank.accounts FROM 'gs://acme-co-backup/database-bank-2017-03-27-weekly';

Restore an entire database

  1. > RESTORE DATABASE bank FROM 'gs://acme-co-backup/database-bank-2017-03-27-weekly';

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

Point-in-time restore

  1. > RESTORE bank.customers FROM 'gs://acme-co-backup/database-bank-2017-03-27-weekly' \
  2. AS OF SYSTEM TIME '2017-02-26 10:00:00';

Restore from incremental backups

  1. > RESTORE bank.customers \
  2. FROM 'gs://acme-co-backup/database-bank-2017-03-27-weekly', 'gs://acme-co-backup/database-bank-2017-03-28-nightly', 'gs://acme-co-backup/database-bank-2017-03-29-nightly';

Point-in-time restore from incremental backups

  1. > RESTORE bank.customers \
  2. FROM 'gs://acme-co-backup/database-bank-2017-03-27-weekly', 'gs://acme-co-backup/database-bank-2017-03-28-nightly', 'gs://acme-co-backup/database-bank-2017-03-29-nightly' \
  3. AS OF SYSTEM TIME '2017-02-28 10:00:00';

Restore into a different database

By default, tables and views are restored to the database they originally belonged to. However, using the into_db option, you can control the target database.

  1. > RESTORE bank.customers \
  2. FROM 'gs://acme-co-backup/database-bank-2017-03-27-weekly' \
  3. WITH into_db = 'newdb';

Remove the foreign key before restore

By default, tables with Foreign Key constraints must be restored at the same time as the tables they reference. However, using the skip_missing_foreign_keys option you can remove the Foreign Key constraint from the table and then restore it.

  1. > RESTORE bank.accounts \
  2. FROM 'gs://acme-co-backup/database-bank-2017-03-27-weekly' \
  3. WITH skip_missing_foreign_keys;

Restoring users from system.users backup

Every full backup contains the system.users table, which you can use to restore your cluster's usernames and their hashed passwords. However, to restore them, you must restore the system.users table into a new database because you cannot drop the existing system.users table.

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

  1. > RESTORE system.users \
  2. FROM 'azure://acme-co-backup/table-users-2017-03-27-full?AZURE_ACCOUNT_KEY=hash&AZURE_ACCOUNT_NAME=acme-co' \
  3. WITH into_db = 'newdb';
  1. > INSERT INTO system.users SELECT * FROM newdb.users;
  1. > DROP TABLE newdb.users;

See also

Was this page helpful?
YesNo