Skip to content

Latest commit

 

History

History
282 lines (186 loc) · 14.3 KB

backups-page.md

File metadata and controls

282 lines (186 loc) · 14.3 KB
title summary toc docs_area
Restore Data from a Backup
Restore data from a backup in CockroachDB Cloud.
true
manage
{{ site.data.products.serverless }} {{ site.data.products.dedicated }}

This page describes the Backups page and how to restore your data.

The backups that Cockroach Labs runs for you can be viewed on the Backups page.

Cockroach Labs runs [full cluster backups](../{{site.versions["stable"]}}/take-full-and-incremental-backups.html#full-backups) hourly for every {{ site.data.products.serverless }} cluster. The full backups are retained for 30 days.
Cockroach Labs runs [full backups](../{{site.versions["stable"]}}/take-full-and-incremental-backups.html#full-backups) daily and [incremental backups](../{{site.versions["stable"]}}/take-full-and-incremental-backups.html#incremental-backups) hourly for every {{ site.data.products.db }} cluster. The full backups are retained for 30 days, while incremental backups are retained for 7 days.

{{site.data.alerts.callout_info}} Currently, you can only restore databases and tables to the same cluster that the backup was taken from.

In the meantime, you can back up and restore data manually or back up from a self-hosted CockroachDB cluster and restore into a {{ site.data.products.db }} cluster. Note that you cannot restore a backup of a multi-region database into a single-region database. {{site.data.alerts.end}}

Backups page

A list of your full cluster backups displays on your cluster's Backups page.

For each backup, the following details display:

- The date and time the backup was taken (**Data From**) - The **Status** of the backup - The **Type** of backup - The **Size** of the backup - The remaining number of days the backup will be retained (**Expires In**) - The number of [**Databases**](#databases) included in the backup 
To view the databases included in the backup, click the number in the [**Databases**](#databases) column.
- The date and time the backup was taken (**Data From**) - The **Status** of the backup - The remaining number of days the backup will be retained (**Expires In**)

Databases

To view the databases included in the backup, click the number in the Databases column on the cluster view of the Backups page.

For each database in the backup, the following details display:

  • The Name of the database

  • The Size of the database data captured in the backup

    {{site.data.alerts.callout_info}} If the Size listed for a database in an incremental backup is 0 B, it means no changes were made in the database since the last full backup. {{site.data.alerts.end}}

  • The number of Tables in the database

    To view the tables in the database, click the number in the Tables column.

To restore a database, click Restore in the corresponding row.

{{site.data.alerts.callout_info}} If a database does not contain tables, it will not display in the Databases view. {{site.data.alerts.end}}

Tables

To view the tables in a database, click the number in the Tables column on the Databases page.

For each table in the database, the following details display:

  • The Name of the table

  • The Size of the table data captured in the backup

    {{site.data.alerts.callout_info}} If the Size listed for a table in an incremental backup is 0.00 B, it means no changes were made in the table since the last full backup. {{site.data.alerts.end}}

  • The number of Rows captured in the backup

Ways to restore data

Console Admin can perform the following from the Console:

Additional ways to restore data:

Restore a database

To restore a database:

  1. Find the cluster backup containing the database you want to restore, and click the number in the corresponding Databases column.

  2. In the Databases view, click Restore for the database you want to restore.

    The Restore database module displays with backup details.

  3. In the Restore to field, enter the name of the destination database. For multi-region databases on v21.2, see Restore a multi-region database.

    {{site.data.alerts.callout_info}} Resolve any naming conflicts by using DROP or RENAME on the existing database. If you enter a unique name in the Restore to field, a new database will be created. {{site.data.alerts.end}}

  4. Select any of the Dependency options to skip. You can:

    • Skip missing foreign keys, which will remove missing foreign key constraints (i.e., when the referenced table is not in the backup or is not being restored) before restoring.
    • Skip missing sequences, which will ignore sequence dependencies (i.e., the DEFAULT expression that uses the sequence).
    • Skip missing views, which will skip restoring views that cannot be restored because their dependencies are not being restored at the same time.

  5. Click Continue

  6. Once you have reviewed the restore details, click Restore.

    When the restore job has been created successfully, you will be taken to the Restore Jobs tab, which will show you the status of your restore.

When the restore is complete, be sure to set any database-specific zone configurations and, if applicable, grant privileges.

Restore a multi-region database to a new database in v21.2

{{site.data.alerts.callout_info}} The following procedure for restoring a multi-region database to a different database name is only applicable to clusters running version v21.2. Clusters running v22.1+ can restore a multi-region database following Restore a database in the previous section. {{site.data.alerts.end}}

{% include cockroachcloud/restore-multiregion-dedicated.md %}

Restore a table

To restore a table:

  1. Find the cluster backup containing the table you want to restore, and click the number in the corresponding Databases column.

  2. In the Databases view, find the database containing the table you want to restore, and click the number in the corresponding Tables column.

    The Tables view displays.

  3. Click Restore for the table you want to restore.

    The Restore table module displays with backup details.

  4. In the Restore to field, enter the name of the destination database.

    {{site.data.alerts.callout_info}} If you enter the name of an existing database, the table will be restored into that existing database. To use the name of an existing database, first resolve any naming conflicts by using DROP or RENAME on the existing database. If you enter a unique name in the Restore to field, a new database will be created. {{site.data.alerts.end}}

  5. Select any of the Dependency options to skip. You can:

    • Skip missing foreign keys, which will remove missing foreign key constraints (i.e., when the referenced table is not in the backup or is not being restored) before restoring.
    • Skip missing sequences, which will ignore sequence dependencies (i.e., the DEFAULT expression that uses the sequence).
    • Skip missing views, which will skip restoring views that cannot be restored because their dependencies are not being restored at the same time.

  6. Click Continue

  7. Once you have reviewed the restore details, click Restore.

    When the restore job has been created successfully, you will be taken to the Restore Jobs tab, which will show you the status of your restore.

Back up a self-hosted CockroachDB cluster and restore into a {{ site.data.products.db }} cluster

To back up a self-hosted CockroachDB cluster into a {{ site.data.products.db }} cluster:

  1. While connected to your self-hosted CockroachDB cluster, back up your databases and/or tables to an external location:

    {% include_cached copy-clipboard.html %}

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

    {{site.data.alerts.callout_danger}} If you are backing up the data to AWS or GCP, use the specified option for the AUTH parameter, as {{ site.data.products.db }} will need the specified credentials upon RESTORE. For more information on authentication parameters to cloud storage providers, see Use Cloud Storage for Bulk Operations. {{site.data.alerts.end}}

  2. Connect to your {{ site.data.products.db }} cluster:

    Mac Linux Windows

    {% include cockroachcloud/sql-connection-string.md %}

  3. Restore to your {{ site.data.products.db }} cluster.

    Use SHOW BACKUPS with your external location to find the backup's subdirectory:

    {% include_cached copy-clipboard.html %}

    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)

    Use the subdirectory to specify the backup to restore:

    {% include_cached copy-clipboard.html %}

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

Troubleshooting

Resolve a database naming conflict

The databases you want to restore cannot have the same name as an existing database in the target cluster. Before you restore a database, verify that the database name is not already in use. To do this, connect to the target cluster with the CockroachDB SQL client and run the following:

{% include_cached copy-clipboard.html %}

> SHOW DATABASES;

If the database's name is already in use, either drop the existing database:

{% include_cached copy-clipboard.html %}

> DROP DATABASE example_database;

Or change the existing database's name:

{% include_cached copy-clipboard.html %}

> ALTER DATABASE example_database RENAME TO archived_example_database;

Resolve a table naming conflict

The table you want to restore cannot have the same name as an existing table in the target database. Before you restore a table, verify that the table name is not already in use. To do this, connect to the target cluster with the CockroachDB SQL client and run the following:

{% include_cached copy-clipboard.html %}

> SHOW TABLES FROM database_name;

If the table's name is already in use, either drop the existing table:

{% include_cached copy-clipboard.html %}

> DROP TABLE target_database.example_table;

Or change the existing table's name:

{% include_cached copy-clipboard.html %}

> ALTER TABLE target_database.example_table RENAME TO target_database.archived_example_table;

Restore a cluster

Find the cluster backup you want to restore, and click Restore.

Performing a restore will cause your cluster to be unavailable for the duration of the restore. All current data is deleted, and the cluster will be restored to the state it was in at the time of the backup. There are no automatic incremental backups, and no automatic database or table level backups.

You can manage your own backups, including incremental, database, and table level backups. To perform manual backups, you must configure either a userfile location or a cloud storage location, which requires billing information for your organization even if you don't set a spend limit.