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.
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
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:
|
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. |
New in v23.1: EXECUTION LOCALITY |
Key-value pairs | Restricts the execution of the backup to nodes that match the defined locality filter requirements. For example, WITH EXECUTION LOCALITY = 'region=us-west-1a,cloud=aws'. Refer to Take Locality-restricted backups for usage and reference detail. |
kms |
STRING |
The URI of the cryptographic key stored in a key management service (KMS), or a comma-separated list of key URIs, used to take and restore encrypted backups. Refer to URI Formats. The key or keys are used to encrypt the manifest and data files that the BACKUP statement generates and to decrypt them during a restore operation, and to list the contents of the backup when using SHOW BACKUP. AWS KMS, Google Cloud KMS, and Azure Key Vault 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
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 |
|---|---|---|
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. The option affects backups started by the full backup schedule only. Incremental backups are always set to wait. |
updates_cluster_last_backup_time_metric |
N/A | (admin privileges required) When set during backup schedule creation, this option updates the schedules_backup_last_completed_time metric for the scheduled backup. |
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:
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 nextRECURRINGtime.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:
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)
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:
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
CREATE SCHEDULE FOR BACKUP- Take Full and Incremental Backups
- Manage a Backup Schedule
BACKUP- Use Cloud Storage
CREATE EXTERNAL CONNECTION
Was this helpful?
