On this page 
Warning:
As of May 10, 2022, CockroachDB v20.2 is no longer supported. For more details, refer to the Release Support Policy.
New in v20.2: The CREATE SCHEDULE FOR BACKUP statement creates a schedule for periodic backups.
For more information about creating, managing, monitoring, and restoring from a scheduled backup, see Manage a Backup Schedule.
Note:
Core users can only use backup scheduling for full backups of clusters, databases, or tables.
To use the other backup features, you need an Enterprise license.
Required privileges
- Only members of the
adminrole can runCREATE SCHEDULE FOR BACKUP. By default, therootuser belongs to theadminrole. BACKUPrequires full read and write (including delete and overwrite) permissions to its target destination.
Synopsis
CREATE SCHEDULE <label>
FOR BACKUP [<targets>] INTO <location>
[WITH <backup_options>[=<value>] [, ...]]
RECURRING [crontab] [FULL BACKUP <crontab|ALWAYS>]
[WITH SCHEDULE OPTIONS <schedule_option>[= <value>] [, ...] ]
Targets:
Empty targets list: backup full cluster.
TABLE <table_pattern> [, ...]
DATABASE <database_name> [, ...]
Parameters
| Parameter | Description |
|---|---|
label |
The name used to identify the backup schedule. This is optional and does not need to be unique. If not provided, the schedule will be assigned the name BACKUP. |
table_pattern |
The table(s) or view(s) you want to back up. |
database_name |
The name of the database(s) you want to back up (i.e., create backups of all tables and views in the database). |
location |
The URI where you want to store the backup. The backup files will be stored in year > month > day subdirectories. The location can be cloud storage, or nodelocal.Note: If you want to schedule a backup using temporary credentials, we recommend that you use implicit authentication; otherwise, you'll need to drop and then recreate schedules each time you need to update the credentials. |
backup_options |
Control the backup behavior with a comma-separated list of options. |
RECURRING crontab |
Specifies when the backup should be taken. By default, these are incremental backups that capture changes since the last backup and append to the current full backup. The schedule is specified as a STRING in crontab format. All times in UTC. Example: '@daily' (run daily at midnight) |
FULL BACKUP crontab |
Specifies when to take a new full backup. The schedule is specified as a STRING in crontab format or as ALWAYS. If FULL BACKUP ALWAYS is specified, then the backups triggered by the RECURRING clause will always be full backups. For free users, ALWAYS is the only accepted value of FULL BACKUP.If the FULL BACKUP clause is omitted, CockroachDB will default to the following full backup schedule:
|
WITH SCHEDULE OPTIONS schedule_option |
Experimental feature. Control the schedule behavior with a comma-separated list of these options. |
Note:
For schedules that include both full and incremental backups, CockroachDB will create two schedules (one for each type).
Backup options
| Option | Value | Description |
|---|---|---|
revision_history |
N/A | Create a backup with full revision history, which records every change made to the cluster within the garbage collection period leading up to and including the given timestamp. |
encryption_passphrase |
STRING |
The passphrase used to encrypt the files (BACKUP manifest and data files) that the BACKUP statement generates. This same passphrase is needed to decrypt the file when it is used to restore and to list the contents of the backup when using SHOW BACKUP. There is no practical limit on the length of the passphrase. |
DETACHED |
N/A | New in v20.2: When a backup runs in DETACHED mode, it will execute asynchronously. The job ID will be returned after the backup job creation completes. Note that with DETACHED specified, further job information and the job completion status will not be returned. For more on the differences between the returned job data, see the example below. To check on the job status, use the SHOW JOBS statement. To run a backup within a transaction, use the DETACHED option. |
kms |
STRING |
New in v20.2: The key management service (KMS) URI (or a comma-separated list of URIs) used to encrypt the files (BACKUP manifest and data files) that the BACKUP statement generates. This same KMS URI is needed to decrypt the file when it is used to restore and to list the contents of the backup when using SHOW BACKUP. Currently, only AWS KMS is supported. |
Schedule options
Warning:
This is an experimental feature. Its interface, options, and outputs are subject to change, and there may be bugs.
If you encounter a bug, please file an issue.
| Option | Value | Description |
|---|---|---|
first_run |
TIMESTAMPTZ / now |
Execute the schedule at the specified time in the future. If not specified, the default behavior is to execute the schedule based on its next RECURRING time. |
on_execution_failure |
retry / reschedule / pause |
If an error occurs during the backup execution, do the following:
Default: reschedule |
on_previous_running |
start / skip / wait |
If the previous backup started by the schedule is still running, do the following:
Default: wait |
ignore_existing_backups |
N/A | If backups were already created in the destination that the new schedule references, this option must be passed to acknowledge that the new schedule may be backing up different objects. |
Considerations
- We recommend that you schedule your backups at a cadence that your cluster can keep up with; for example, if a previous backup is still running when it is time to start the next one, adjust the schedule so the backups do not end up falling behind or update the
on_previous_runningoption. - To prevent scheduled backups from falling behind, first determine how long a single backup takes and use that as your starting point for the schedule's cadence.
- Ensure you are monitoring your backup schedule (e.g., Prometheus) and alerting metrics that will confirm that your backups are completing, but also that they're not running more concurrently than you expect.
- Ensure that your GC window is long enough to accommodate your backup schedule, otherwise your incremental backups will throw an error. For example, if you set up your schedule to be
RECURRING '@daily'but your GC window is less than 1 day, all your incremental backups will fail. - The
AS OF SYSTEM TIMEclause cannot be set on scheduled backups. Scheduled backups are started shortly after the scheduled time has passed by an internal polling mechanism and are automatically run withAS OF SYSTEM TIMEset to the time at which the backup was scheduled to run. - If you want to schedule a backup using temporary credentials, we recommend that you use
implicitauthentication; otherwise, you'll need to drop and then recreate schedules each time you need to update the credentials.
View and control backup schedules
Once a backup schedule is successfully created, you can do the following:
| Action | SQL Statement |
|---|---|
| View the schedule | SHOW SCHEDULES |
| Pause the schedule | PAUSE SCHEDULES |
| Resume the schedule | RESUME SCHEDULES |
| Drop the schedule | DROP SCHEDULES |
View and control a backup initiated by a schedule
After CockroachDB successfully initiates a scheduled backup, it registers the backup as a job. You can do the following with each individual backup job:
| Action | SQL Statement |
|---|---|
| View the backup status | SHOW JOBS |
| Pause the backup | PAUSE JOB |
| Resume the backup | RESUME JOB |
| Cancel the backup | CANCEL JOB |
You can also visit the Jobs page of the DB Console to view job details. The BACKUP statement will return when the backup is finished or if it encounters an error.
Examples
Create a schedule for full backups only (core)
Core users can only use backup scheduling for full backups of clusters, databases, or tables. Full backups are taken with the FULL BACKUP ALWAYS clause, for example:
> CREATE SCHEDULE core_schedule_label
FOR BACKUP INTO 's3://test/schedule-test-core?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x'
RECURRING '@daily'
FULL BACKUP ALWAYS
WITH SCHEDULE OPTIONS first_run = 'now';
schedule_id | name | status | first_run | schedule | backup_stmt
---------------------+---------------------+--------+---------------------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
588799238330220545 | core_schedule_label | ACTIVE | 2020-09-11 00:00:00+00:00 | @daily | BACKUP INTO 's3://test/schedule-test-core?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x' WITH detached
(1 row)
To use the other backup features, you need an Enterprise license.
Create a scheduled backup for a cluster
This example creates a schedule for a cluster backup with revision history that's taken every day at midnight:
> CREATE SCHEDULE schedule_label
FOR BACKUP INTO 's3://test/backups/schedule_test?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x'
WITH revision_history
RECURRING '@daily';
schedule_id | name | status | first_run | schedule | backup_stmt
---------------------+----------------+------------------------------------------------+----------------------------------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------
588796190000218113 | schedule_label | PAUSED: Waiting for initial backup to complete | NULL | @daily | BACKUP INTO LATEST IN 's3://test/schedule-test?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x' WITH revision_history, detached
588796190012702721 | schedule_label | ACTIVE | 2020-09-10 16:52:17.280821+00:00 | @weekly | BACKUP INTO 's3://test/schedule-test?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x' WITH revision_history, detached
(2 rows)
Because the FULL BACKUP clause was not included, CockroachDB also scheduled a full backup to run @weekly. This is the default cadence for incremental backups RECURRING > 1 hour but <= 1 day.
Create a scheduled backup for a database
This example creates a schedule for a backup of the database movr with revision history that's taken every day 1 minute past midnight (00:00:01):
> CREATE SCHEDULE schedule_database
FOR BACKUP DATABASE movr INTO 's3://test/schedule-database?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x'
WITH revision_history
RECURRING '1 0 * * *';
schedule_id | name | status | first_run | schedule | backup_stmt
---------------------+-------------------+------------------------------------------------+----------------------------------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------
588819866656997377 | schedule_database | PAUSED: Waiting for initial backup to complete | NULL | 1 0 * * * | BACKUP DATABASE movr INTO LATEST IN 's3://test/schedule-database?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x' WITH revision_history, detached
588819866674233345 | schedule_database | ACTIVE | 2020-09-10 18:52:42.823003+00:00 | @weekly | BACKUP DATABASE movr INTO 's3://test/schedule-database?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x' WITH revision_history, detached
(2 rows)
Because the FULL BACKUP clause was not included, CockroachDB also scheduled a full backup to run @weekly. This is the default cadence for incremental backups RECURRING > 1 hour but <= 1 day.
Create a scheduled backup for a table
This example creates a schedule for a backup of the table movr.vehicles with revision history that's taken every hour:
> CREATE SCHEDULE schedule_table
FOR BACKUP TABLE movr.vehicles INTO 's3://test/schedule-table?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x'
WITH revision_history
RECURRING '@hourly';
schedule_id | name | status | first_run | schedule | backup_stmt
---------------------+----------------+------------------------------------------------+----------------------------------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------
588820615348027393 | schedule_table | PAUSED: Waiting for initial backup to complete | NULL | @hourly | BACKUP TABLE movr.vehicles INTO LATEST IN 's3://test/schedule-table?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x' WITH revision_history, detached
588820615382302721 | schedule_table | ACTIVE | 2020-09-10 18:56:31.305782+00:00 | @daily | BACKUP TABLE movr.vehicles INTO 's3://test/schedule-table?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x' WITH revision_history, detached
(2 rows)
Because the FULL BACKUP clause was not included, CockroachDB also scheduled a full backup to run @daily. This is the default cadence for incremental backups RECURRING <= 1 hour.
Create a scheduled backup with a scheduled first run
This example creates a schedule for a backup of the table movr.vehicles with revision history that's taken every hour, with its first run scheduled for 2020-09-15 00:00:00.00 (UTC):
> CREATE SCHEDULE scheduled_first_run
FOR BACKUP TABLE movr.vehicles INTO 's3://test/schedule-table?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x'
WITH revision_history
RECURRING '@hourly'
WITH SCHEDULE OPTIONS first_run = '2020-09-15 00:00:00.00';
schedule_id | name | status | first_run | schedule | backup_stmt
---------------------+---------------------+------------------------------------------------+---------------------------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------
589963390457741313 | scheduled_first_run | PAUSED: Waiting for initial backup to complete | NULL | @hourly | BACKUP TABLE movr.vehicles INTO LATEST IN 's3://test/scheduled-first-run?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x' WITH revision_history, detached
589963390487363585 | scheduled_first_run | ACTIVE | 2020-09-15 00:00:00+00:00 | @daily | BACKUP TABLE movr.vehicles INTO 's3://test/scheduled-first-run?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x' WITH revision_history, detached
(2 rows)
Because the FULL BACKUP clause was not included, CockroachDB also scheduled a full backup to run @daily. This is the default cadence for incremental backups RECURRING <= 1 hour.
View scheduled backup details
When a backup is created by a schedule, it is stored within a collection of backups in the given location. To view details for a backup created by a schedule, you can use the following:
SHOW BACKUPS IN ystatement to view a list of the full backup's subdirectories.SHOW BACKUP x IN ystatement to view a list of the full and incremental backups that are stored in a specific full backup's subdirectory.
For more details, see SHOW BACKUP.
View a list of the full backup's subdirectories
> SHOW BACKUPS IN 's3://test/backup-test?AWS_ACCESS_KEY_ID=[placeholder]&AWS_SECRET_ACCESS_KEY=[placeholder]';
path
------------------------
2020/09/24-204152.88
2020/09/24-204623.44
2020/09/24-205612.40
2020/09/24-207328.36
(4 rows)
The path format is <year>/<month>/<day>-<timestamp>.
View a list of the full and incremental backups in a specific full backup subdirectory
> SHOW BACKUP '2020/09/24-204152.88' IN 's3://test/backup-test?AWS_ACCESS_KEY_ID=[placeholder]&AWS_SECRET_ACCESS_KEY=[placeholder]';
database_name | parent_schema_name | object_name | object_type | start_time | end_time | size_bytes | rows | is_full_cluster
----------------+--------------------+----------------------------+-------------+----------------------------------+----------------------------------+------------+------+------------------
NULL | NULL | system | database | NULL | 2020-09-24 20:41:52.880553+00:00 | NULL | NULL | true
system | public | users | table | NULL | 2020-09-24 20:41:52.880553+00:00 | 144 | 3 | true
system | public | zones | table | NULL | 2020-09-24 20:41:52.880553+00:00 | 201 | 7 | true
system | public | settings | table | NULL | 2020-09-24 20:41:52.880553+00:00 | 875 | 6 | true
system | public | ui | table | NULL | 2020-09-24 20:41:52.880553+00:00 | 0 | 0 | true
system | public | jobs | table | NULL | 2020-09-24 20:41:52.880553+00:00 | 795117 | 80 | true
system | public | locations | table | NULL | 2020-09-24 20:41:52.880553+00:00 | 261 | 5 | true
system | public | role_members | table | NULL | 2020-09-24 20:41:52.880553+00:00 | 184 | 2 | true
system | public | comments | table | NULL | 2020-09-24 20:41:52.880553+00:00 | 0 | 0 | true
system | public | scheduled_jobs | table | NULL | 2020-09-24 20:41:52.880553+00:00 | 1013 | 2 | true
NULL | NULL | defaultdb | database | NULL | 2020-09-24 20:41:52.880553+00:00 | NULL | NULL | true
NULL | NULL | postgres | database | NULL | 2020-09-24 20:41:52.880553+00:00 | NULL | NULL | true
NULL | NULL | movr | database | NULL | 2020-09-24 20:41:52.880553+00:00 | NULL | NULL | true
movr | public | users | table | NULL | 2020-09-24 20:41:52.880553+00:00 | 4911 | 50 | true
movr | public | vehicles | table | NULL | 2020-09-24 20:41:52.880553+00:00 | 3182 | 15 | true
movr | public | rides | table | NULL | 2020-09-24 20:41:52.880553+00:00 | 156387 | 500 | true
movr | public | vehicle_location_histories | table | NULL | 2020-09-24 20:41:52.880553+00:00 | 73918 | 1000 | true
movr | public | promo_codes | table | NULL | 2020-09-24 20:41:52.880553+00:00 | 216083 | 1000 | true
movr | public | user_promo_codes | table | NULL | 2020-09-24 20:41:52.880553+00:00 | 0 | 0 | true
defaultdb | NULL | org_one | schema | NULL | 2020-09-24 20:41:52.880553+00:00 | NULL | NULL | true
NULL | NULL | system | database | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00 | NULL | NULL | true
system | public | users | table | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00 | 0 | 0 | true
system | public | zones | table | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00 | 0 | 0 | true
system | public | settings | table | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00 | 0 | 0 | true
system | public | ui | table | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00 | 0 | 0 | true
system | public | jobs | table | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00 | 102381 | 1 | true
system | public | locations | table | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00 | 0 | 0 | true
system | public | role_members | table | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00 | 0 | 0 | true
system | public | comments | table | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00 | 0 | 0 | true
system | public | scheduled_jobs | table | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00 | 1347 | 2 | true
NULL | NULL | defaultdb | database | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00 | NULL | NULL | true
NULL | NULL | postgres | database | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00 | NULL | NULL | true
NULL | NULL | movr | database | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00 | NULL | NULL | true
movr | public | users | table | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00 | 0 | 0 | true
movr | public | vehicles | table | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00 | 0 | 0 | true
movr | public | rides | table | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00 | 0 | 0 | true
movr | public | vehicle_location_histories | table | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00 | 0 | 0 | true
movr | public | promo_codes | table | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00 | 0 | 0 | true
movr | public | user_promo_codes | table | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00 | 0 | 0 | true
defaultdb | NULL | org_one | schema | 2020-09-24 20:41:52.880553+00:00 | 2020-09-24 20:50:00+00:00 | NULL | NULL | true
(40 rows)
See also
- Manage a Backup Schedule
BACKUPRESTORESHOW BACKUPSHOW SCHEDULESPAUSE SCHEDULESRESUME SCHEDULESDROP SCHEDULESPAUSE JOBRESUME JOBCANCEL JOB- Take Full and Incremental Backups
- Use the Built-in SQL Client
- Other Cockroach Commands
Was this helpful?
