CREATE SCHEDULE FOR BACKUP

On this page Carat arrow pointing down

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 admin role can run CREATE SCHEDULE FOR BACKUP. By default, the root user belongs to the admin role.
  • BACKUP requires full read and write (including delete and overwrite) permissions to its target destination.

Synopsis

CREATE SCHEDULE [IF NOT EXISTS] <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
IF NOT EXISTS New in v21.2: Use to specify that a scheduled backup should not be created if the label already exists. Produces an error if the schedule label already exists, or if the label is not specified.
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.
targets The targets you want to back up:
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:
  • RECURRING <= 1 hour: Default to FULL BACKUP '@daily'
  • RECURRING <= 1 day: Default to FULL BACKUP '@weekly'
  • Otherwise: Default to FULL BACKUP ALWAYS
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 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 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

Note:

This feature is experimental.This feature is subject to change. To share feedback and/or issues, contact Support.

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:
  • retry: Retry the backup right away.
  • reschedule: Retry the backup by rescheduling it based on the RECURRING expression.
  • pause: Pause the schedule. This requires manual intervention to resume the schedule.

Default: reschedule
on_previous_running start / skip / wait If the previous backup started by the schedule is still running, do the following:
  • start: Start the new backup anyway, even if the previous one still running.
  • skip: Skip the new backup and run the next backup based on the RECURRING expression.
  • wait: Wait for the previous backup to complete.

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_running option.
  • 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 TIME clause 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 with AS OF SYSTEM TIME set 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 implicit authentication; 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:

icon/buttons/copy 
> 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:

icon/buttons/copy 
> 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):

icon/buttons/copy 
> 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:

icon/buttons/copy 
> 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):

icon/buttons/copy 
> 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:

For more details, see SHOW BACKUP.

See also


Yes No
On this page

Yes No

Product

Resources

Learn

Support Channels

Company

Get developer news