RESTORE

On this page

The RESTORE statement restores your cluster's schemas and data from a 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.

You can restore:

• A full cluster
• Databases
• Tables

Required privileges

• Full cluster restores can only be run by members of the admin role. By default, the root user belongs to the admin role.
• For all other restores, the user must have write access (CREATE or INSERT) on all objects affected.

Source privileges

The source file URL does not require the ADMIN role in the following scenarios:

• S3 and GS using SPECIFIED (and not IMPLICIT) credentials. Azure is always SPECIFIED by default.
• Userfile

The source file URL does require the ADMIN role in the following scenarios:

• S3 or GS using IMPLICIT credentials
• Use of a custom endpoint on S3
• Nodelocal, HTTP or HTTPS

We recommend using cloud storage for bulk operations.

Synopsis

Parameters

Options

You can control RESTORE behavior using any of the following in the restore_options_list. To set multiple RESTORE options, use a comma-separated list:

Backup file URLs

CockroachDB uses the URL provided to construct a secure API call to the service you specify. The URL structure depends on the type of file storage you are using. For more information, see the following:

• Use Cloud Storage for Bulk Operations
• Use a Local File Server for Bulk Operations

Functional details

You can restore:

• A full cluster
• Databases
• Tables

Full cluster

A full cluster restore can only be run on a target cluster with no user-created databases or tables. Restoring a full cluster includes:

• All user tables
• Relevant system tables
• All databases
• All tables (which automatically includes their indexes)
• All views

Databases

The database cannot already exist in the target cluster. Restoring a database will create a new database and restore all of its tables and views. The created database will have the name of the database in the backup.

RESTORE DATABASE backup_database_name FROM 'your_backup_location';

Tables

You can also restore individual 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.

By default, tables and views are restored into a target database matching the name of the database from which they were backed up. If the target database does not exist, you must create it. You can choose to change the target database with the into_db option.

The target database must not have tables or views with the same name as the tables or views you're restoring. If any of the restore target's names are being used, you can:

• DROP TABLE, DROP VIEW, or DROP SEQUENCE and then restore them. Note that a sequence cannot be dropped while it is being used in a column's DEFAULT expression, so those expressions must be dropped before the sequence is dropped, and recreated after the sequence is recreated. The setval function can be used to set the value of the sequence to what it was previously.
• Restore the table or view into a different database.

New in v20.2: When restoring an individual table that references a user-defined type (e.g., ENUM), CockroachDB will first check to see if the type already exists. The restore will attempt the following for each user-defined type within a table backup:

• If there is not an existing type in the cluster with the same name, CockroachDB will create the user-defined type as it exists in the backup.
• If there is an existing type in the cluster with the same name that is compatible with the type in the backup, CockroachDB will map the type in the backup to the type in the cluster.
• If there is an existing type in the cluster with the same name but it is not compatible with the type in the backup, the restore will not succeed and you will be asked to resolve the naming conflict. You can do this by either dropping or renaming the existing user-defined type.

In general, two types are compatible if they are the same kind (e.g., an enum is only compatible with other enums). Additionally, enums are only compatible if they have the same ordered set of elements that have also been created in the same way. For example:

• CREATE TYPE t1 AS ENUM ('yes', 'no') and CREATE TYPE t2 AS ENUM ('yes', 'no') are compatible.
• CREATE TYPE t1 AS ENUM ('yes', 'no') and CREATE TYPE t2 AS ENUM ('no', 'yes') are not compatible.
• CREATE TYPE t1 AS ENUM ('yes', 'no') and CREATE TYPE t2 AS ENUM ('yes'); ALTER TYPE t2 ADD VALUE ('no') are not compatible because they were not created in the same way.

Object dependencies

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

Users and privileges

To restore your users and privilege grants, you can do a cluster backup and restore the cluster to a fresh cluster with no user data.

If you are not doing a full cluster restore, the table-level privileges need to be granted to the users after the restore is complete. To do this, backup the system.users table, restore users and their passwords, and then grant the table-level privileges.

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:

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.

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.

Known limitations

An object's historical data is not preserved upon RESTORE. This means that if an AS OF SYSTEM TIME query is issued on a restored object, the query will fail or the response will be incorrect because there is no historical data to query.

Examples

Restore a cluster

To restore a full cluster:

> RESTORE FROM 'gs://acme-co-backup/test-cluster';

Restore a database

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

Restore a table

To restore a single table:

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

To restore multiple tables:

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

Restore from incremental backups

Restoring from incremental backups requires previous full and incremental backups. To restore from a destination containing the full backup, as well as the incremental backups (stored as subdirectories):

> RESTORE FROM 'gs://acme-co-backup/test-cluster';

To explicitly point to where your incremental backups are, provide the previous full and incremental backup locations in a comma-separated list. In this example, -weekly is the full backup and the two -nightly are incremental backups.

> RESTORE bank.customers \
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';

Restore a backup asynchronously

New in v20.2: Use the DETACHED option to execute the restore job asynchronously:

> RESTORE FROM \
'gs://acme-co-backup/test-cluster' \
WITH DETACHED;

The job ID is returned after the restore job creation completes:

        job_id
----------------------
  592786066399264769
(1 row)

Without the DETACHED option, RESTORE will block the SQL connection until the job completes. Once finished, the job status and more detailed job data is returned:

job_id             |  status   | fraction_completed | rows | index_entries | bytes
---------------------+-----------+--------------------+------+---------------+--------
652471804772712449 | succeeded |                  1 |   50 |             0 |  4911
(1 row)

Other restore usages

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

> RESTORE bank.customers \
FROM 'gs://acme-co-backup/database-bank-2017-03-27-weekly' \
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.

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

Restoring users from system.users backup

The system.users table stores your cluster's usernames and their hashed passwords. 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.

> CREATE DATABASE newdb;

> RESTORE system.users \
FROM 'azure://acme-co-backup/table-users-2017-03-27-full?AZURE_ACCOUNT_KEY=hash&AZURE_ACCOUNT_NAME=acme-co' \
WITH into_db = 'newdb';

> INSERT INTO system.users SELECT * FROM newdb.users;

> DROP TABLE newdb.users;

See also

• BACKUP
• Take Full and Incremental Backups
• Take and Restore Encrypted Backups
• Take and Restore Locality-aware Backups
• Take Backups with Revision History and Restore from a Point-in-time
• New in v20.2: Manage a Backup Schedule
• Configure Replication Zones
• ENUM
• CREATE TYPE
• DROP TYPE

Was this helpful?