RESTORE SNAPSHOT

Restore a snapshot into the cluster.

Table of contents

Synopsis

RESTORE SNAPSHOT repository_name.snapshot_name
{ ALL |
  METADATA |
  TABLE table_ident [ PARTITION (partition_column = value [, ...])] [, ...] |
  data_section [, ...] }
[ WITH (restore_parameter [= value], [, ...]) ]

where data_section:

{  TABLES |
   VIEWS |
   USERS |      -- Deprecated, use USERMANAGEMENT instead
   PRIVILEGES | -- Deprecated, use USERMANAGEMENT instead
   USERMANAGEMENT |
   ANALYZERS |
   UDFS }

Description

Restore one or more tables, partitions, or metadata from an existing snapshot into the cluster. The snapshot must be given as fully qualified reference with repository_name and snapshot_name.

To restore everything, use the ALL keyword.

Single tables (or table partitions) can be restored by using TABLE together with a table_ident and a optional partition reference given the partition_column values.

It is possible to restore all tables using the TABLES keyword. This will restore all tables but will not restore metadata.

To restore only the metadata (including views, users, roles, privileges, analyzers, user-defined-functions, and all cluster settings), instead use the METADATA keyword.

A single metadata group can be restored by using the related data_section keyword.

Additionally, multiple data_section keywords can be used to restore multiple concrete sections at once.

To cancel a restore operation simply drop the tables that are being restored.

Caution

If you try to restore a table that already exists, CrateDB will return an error. However, if you try to restore metadata or cluster settings that already exist, they will be overwritten.

Parameters

repository_name:

The name of the repository of the snapshot to restore as ident.

snapshot_name:

The name of the snapshot as ident.

table_ident:

The name (optionally schema-qualified) of an existing table that is to be restored from the snapshot.

data_section:

The section name of the data to be restored. Multiple sections can be selected. A section cannot be combined with the ALL, METADATA, or TABLE keywords.

Clauses

PARTITION

If the table is partitioned, the optional PARTITION clause can be used to restore a snapshot from one partition exclusively.

[ PARTITION ( partition_column = value [, ...] ) ]

partition_column:

Column name used for table partitioning.

value:

The respective column value.

All partition columns (specified by the PARTITIONED BY clause) must be listed inside the parentheses in the order matching the PARTITION BY definition along with their respective values using the partition_column = value syntax (separated by commas).

Because each partition corresponds to a unique set of partition column row values, this clause uniquely identifies a single partition to restore.

Tip

The SHOW CREATE TABLE statement will show you the complete list of partition columns specified by the PARTITIONED BY clause.

WITH

[ WITH (restore_parameter [= value], [, ...]) ]

The following configuration parameters can be used to modify how the snapshot is restored to the cluster:

ignore_unavailable:

(Default false) Per default the restore command fails if a table is given that does not exist in the snapshot. If set to true those missing tables are ignored.

wait_for_completion:

(Default: false) By default the request returns once the restore operation started. If set to true the request returns after all selected tables from the snapshot are restored or an error occurred. In order to monitor the restore operation the * sys.shards table can be queried.

schema_rename_pattern:

(Default (.+)) Regular expression matching schemas of restored tables. Used to restore table into a different schema. Capture groups () can be used to reuse portions of the table schema and then used in schema_rename_replacement. Default value matches the entire schema name.

schema_rename_replacement:

(Default $1) Replacement pattern used to restore table into a different schema. Can include groups, captured in schema_rename_pattern. By default no replacement is happening and tables are restored into their original schemas.

Example: prefix_$1 combined with default schema_rename_pattern adds ‘prefix’ to all restored table schemas.

Example: target combined with default schema_rename_pattern restores all tables into the target schema.

table_rename_pattern:

(Default (.+)) Regular expression matching names of restored tables. Used to rename tables on restoring. Capture groups () can be used to reuse portions of the table name and then used in table_rename_replacement. Default value matches the entire table name.

table_rename_replacement:

(Default $1) Replacement pattern used to rename tables on restoring. Can include groups, captured in table_rename_pattern. By default no replacement is happening and tables are restored with their original names. Example: prefix_$1 combined with default table_rename_pattern adds ‘prefix’ to all restored table names.

Caution

Restore will abort with a failure if there is a name collision after evaluating the rename operations, or if a table with the same name as the rename target already exists.