ALTER BACKUP SCHEDULE

On this page Carat arrow pointing down
Note:

Core users can only use backup scheduling for full backups of clusters, databases, or tables. If you do not specify the FULL BACKUP ALWAYS clause when you schedule a backup, you will receive a warning that the schedule will only run full backups.

To use the other backup features, you need an Enterprise license.

New in v22.2: The ALTER BACKUP SCHEDULE statement modifies an existing backup schedule. You can use ALTER BACKUP SCHEDULE to do the following:

  • Set a different name for a backup schedule.
  • Change a scheduled backup's storage location.
  • Apply additional backup options or schedule options to backups and schedules.
  • Adjust the cadence and type of scheduled backups.

Required privileges

To alter a backup schedule, you must be the owner of the backup schedule, i.e., the user that created the backup schedule.

Synopsis

ALTER BACKUP SCHEDULE schedule_id SET LABEL schedule_label INTO collectionURI WITH option RECURRING crontab FULL BACKUP crontab ALWAYS SCHEDULE OPTION schedule_option ,

Parameters

Parameter Description
schedule_id The schedule's ID that CREATE SCHEDULE FOR BACKUP and SHOW SCHEDULES display.
schedule_label The name or label given to the backup schedule.
collectionURI The URI where you want to store the backup. See Backup file URLs for detail on forming the URI.
option Control the backup behavior with a comma-separated list of these options.
RECURRING crontab Specify when the backup should be taken. By default, these are incremental backups. A separate schedule may be created automatically to write full backups at a regular cadence, depending on the frequency of the incremental backups. You can likewise modify this separate schedule with ALTER BACKUP SCHEDULE. Define the schedule as a STRING in crontab format. All times in UTC.

Example: '@daily' (run daily at midnight)
FULL BACKUP crontab / ALWAYS Specify when to take a new full backup. Define the schedule as a STRING in crontab format or as ALWAYS.

FULL BACKUP ALWAYS will trigger RECURRING to always take full backups.
Note: If you do not have an Enterprise license then you can only take full backups. ALWAYS is the only accepted value of FULL BACKUP.

If you omit the FULL BACKUP clause, the default backup schedule will be as follows:
  • If RECURRING <= 1 hour: Default to FULL BACKUP '@daily'
  • If RECURRING <= 1 day: Default to FULL BACKUP '@weekly'
  • Otherwise: Default to FULL BACKUP ALWAYS
schedule_option Control the schedule behavior with a comma-separated list of these schedule options.

Backup options

You can use the backup options in this table to control the behavior of your backups. See Apply different options to scheduled backups for an example.

Option Value Description
revision_history BOOL / None 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.

You can specify a backup with revision history without any value e.g., WITH revision_history. Or, you can explicitly define WITH revision_history = 'true' / 'false'. The revision_history option defaults to true when used with BACKUP or CREATE SCHEDULE FOR BACKUP. A value is required when using ALTER BACKUP SCHEDULE to apply different options to scheduled backups.
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 BOOL / None Note: Backups running on a schedule have the detached option applied implicitly. Therefore, you cannot modify this option for scheduled backups.

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. To check on the job status, use the SHOW JOBS statement.
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, AWS KMS and Google Cloud KMS are supported.
incremental_location STRING Create an incremental backup in a different location than the default incremental backup location.

WITH incremental_location = 'explicit_incrementals_URI'

See Incremental backups with explicitly specified destinations for usage.

Schedule options

Warning:

The following schedule options are in preview. Their interface, options, and outputs are subject to change, and there may be bugs.

If you encounter a bug, please file an issue.

You can use the schedule options in this table to control the behavior of your backup schedule. See Apply different options to scheduled backups for an example.

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 is 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.

Examples

The examples in this section start with the following created backup schedule. Each section follows on from the previous example's schedule state.

First, create a schedule that will take daily full backups of the cluster:

icon/buttons/copy
CREATE SCHEDULE aws_backups
  FOR BACKUP INTO 'external://s3_storage'
    RECURRING '@daily'
    FULL BACKUP ALWAYS
    WITH SCHEDULE OPTIONS first_run = 'now', ignore_existing_backups;

This statement specifies:

  • 'external://s3_storage': Use the storage location represented by this external connection URI.
  • first_run = 'now': Take the first full backup immediately rather than wait for its next RECURRING time.
  • ignore_existing_backups: Ignore any existing backups already present in the storage location.

The command returns the following output. Note that the detached option is implicitly added, because this backup has been configured to run on a schedule:

     schedule_id     |   label     | status |           first_run           | schedule |                    backup_stmt
---------------------+-------------+--------+-------------------------------+----------+----------------------------------------------------
  814155335856521217 | aws_backups | ACTIVE | 2022-11-15 16:48:10.667767+00 | @daily   | BACKUP INTO 'external://s3_storage' WITH detached
(1 row)

Change the storage location for scheduled backups

You can change the storage location to which your backup schedule is taking backups with the SET INTO command. Use the schedule ID to specify the schedule to modify and the new storage location URI. This statement also changes the schedule's label to match the change in backup location:

icon/buttons/copy
ALTER BACKUP SCHEDULE 814155335856521217 SET INTO 'external://gcs_storage', SET LABEL gcs_backups;
     schedule_id     |    label    | status |       first_run        | schedule |                    backup_stmt
---------------------+-------------+--------+------------------------+----------+-----------------------------------------------------
  814155335856521217 | gcs_backups | ACTIVE | 2022-11-16 00:00:00+00 | @daily   | BACKUP INTO 'external://gcs_storage' WITH detached
(1 row)
Note:

Incremental backups require a full backup in the storage location. Therefore, when you change the storage location for a backup schedule, CockroachDB will pause any scheduled incremental backups until the next full backup runs on its regular schedule cadence. Consider that if you change the storage location and then adjust the frequency of your scheduled backups before the next full backup, any newly added incremental backups will not be part of the pause after a storage location change. This could result in a reported error state for the incremental backups, which will not resolve until the next scheduled full backup.

Adjust frequency of scheduled backups

To adjust the frequency of your scheduled backups, use SET with FULL BACKUP and RECURRING for full and incremental backups. You can either define the frequency as a STRING or in crontab format. See the Parameters table for more detail.

The following command adds incremental backups to the schedule occurring hourly:

icon/buttons/copy
ALTER BACKUP SCHEDULE 814155335856521217 SET FULL BACKUP '@daily', SET RECURRING '@hourly';

The output shows the two scheduled jobs:

     schedule_id     |    label    | status |       first_run        | schedule |                         backup_stmt
---------------------+-------------+--------+------------------------+----------+---------------------------------------------------------------
  814168045421199361 | gcs_backups | ACTIVE | 2022-11-15 18:00:00+00 | @hourly  | BACKUP INTO LATEST IN 'external://gcs_storage' WITH detached
  814155335856521217 | gcs_backups | ACTIVE | 2022-11-16 00:00:00+00 | @daily   | BACKUP INTO 'external://gcs_storage' WITH detached
(2 rows)

You can use the SHOW SCHEDULE statement with one of the schedule IDs to show details for each of the jobs. For the full backup job:

SHOW SCHEDULE 814155335856521217;

This shows that the full backup has a dependent schedule, which lists the incremental backup's schedule ID:

          id         |    label    | schedule_status |        next_run        | state | recurrence | jobsrunning | owner |            created            |                                                                                       command
---------------------+-------------+-----------------+------------------------+-------+------------+-------------+-------+-------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  814155335856521217 | gcs_backups | ACTIVE          | 2022-11-18 00:00:00+00 | NULL  | @daily     |           0 | root  | 2022-11-15 16:48:10.667783+00 | {"backup_statement": "BACKUP INTO 'external://gcs_storage' WITH detached, "dependent_schedule_id": 814168045421199361}
(1 row)

For the incremental backup:

SHOW SCHEDULE 814168045421199361;

The following includes the backup_type as 1. This signifies that this schedule is for an incremental backup:

          id         |    label    | schedule_status |        next_run        | state | recurrence | jobsrunning | owner |            created            |                                                                       command
---------------------+-------------+-----------------+------------------------+-------+------------+-------------+-------+-------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------
  814168045421199361 | gcs_backups | ACTIVE          | 2022-11-15 19:00:00+00 | NULL  | @hourly    |           0 | root  | 2022-11-15 17:52:49.327263+00 | {"backup_statement": "BACKUP INTO LATEST IN 'external://gcs_storage' WITH detached", "backup_type": 1, "dependent_schedule_id": 814155335856521217}
(1 row)

Full backups are implicitly of backup_type 0, and so does not display in the schedule details.

Apply different options to scheduled backups

You can modify the behavior of your backup schedule and the backup jobs with SET SCHEDULE OPTION and SET WITH. See the Schedule options table and the Backup options table for a list of the available options.

This statement changes the default wait value for the on_previous_running schedule option to start. If a previous backup started by the schedule is still running, the scheduled job will now start the new backup anyway, rather than waiting. The backup option incremental_location modifies the storage location for incremental backups:

ALTER BACKUP SCHEDULE 814168045421199361 SET SCHEDULE OPTION on_previous_running = 'start', SET WITH incremental_location = 'external://gcs_incremental_storage';

The incremental backup schedule's BACKUP statement shows that it will read files in the full backup location 'external://gcs_storage' and ultimately store the incremental backup in 'external://gcs_incremental_storage' on an hourly basis:

     schedule_id     |    label    | status |       first_run        | schedule |                                                        backup_stmt
---------------------+-------------+--------+------------------------+----------+----------------------------------------------------------------------------------------------------------------------------
  814168045421199361 | gcs_backups | ACTIVE | 2022-11-15 21:00:00+00 | @hourly  | BACKUP INTO LATEST IN 'external://gcs_storage' WITH detached, incremental_location = 'external://gcs_incremental_storage'
  814155335856521217 | gcs_backups | ACTIVE | 2022-11-16 00:00:00+00 | @daily   | BACKUP INTO 'external://gcs_storage' WITH detached, incremental_location = 'external://gcs_incremental_storage'
(2 rows)

See also


Yes No
On this page

Yes No