New in v20.2: You can create schedules in CockroachDB for periodic backups. Once a backup schedule is created, you can do the following:
- Set up monitoring for the backup schedule
- View scheduled backup details
- View and control the backup schedule
- View and control a backup initiated by a schedule
- Restore from a scheduled backup
Create a new backup schedule
To create a new backup schedule, use the CREATE SCHEDULE FOR BACKUP statement. For example:
> CREATE SCHEDULE schedule_label
FOR BACKUP INTO 's3://test/backups/test_schedule_1?AWS_ACCESS_KEY_ID=123&AWS_SECRET_ACCESS_KEY=123'
WITH revision_history
RECURRING '@daily'
WITH SCHEDULE OPTIONS first_run = 'now';
In this example, a schedule labeled schedule_label is created to take daily (incremental) backups with revision history in AWS S3, with the first backup being taken now. A second schedule for weekly full backups is also created by default. Both schedules have the same label (i.e., schedule_label).
For more information about the different options available when creating a backup schedule, see CREATE SCHEDULE FOR BACKUP.
Set up monitoring for the backup schedule
We recommend that you monitor your backup schedule with Prometheus, and alert when there are anomalies such as backups that have failed or no backups succeeding over a certain amount of time— at which point, you can inspect schedules by running SHOW SCHEDULES.
Metrics for scheduled backups fall into two categories:
Backup schedule-specific metrics, aggregated across all schedules:
schedules_BACKUP_started: A counter for the total number of backups started by a scheduleschedules_BACKUP_succeeded: A counter for the number of backups started by a schedule that succeededschedules_BACKUP_failed: A counter for the number of backups started by a schedule that failedWhen
schedules_BACKUP_failedincrements, runSHOW SCHEDULESto check which schedule is affected and to inspect the error in thestatuscolumn.
Scheduler-specific metrics:
schedules.round.reschedule-wait: The number of schedules that were rescheduled due to a currently running job. A value greater than 0 indicates that a previous backup was still running when a new scheduled backup was supposed to start. This corresponds to theon_previous_running=waitschedule option.schedules.round.reschedule-skip: The number of schedules that were skipped due to a currently running job. A value greater than 0 indicates that a previous backup was still running when a new scheduled backup was supposed to start. This corresponds to theon_previous_running=skipschedule option.
schedules.round.reschedule-wait and schedules.round.reschedule-skip are gauge metrics and can be graphed. A continual positive value for either of these metrics may indicate a misconfigured backup cadence, and you should consider adjusting the cadence to avoid waiting for or skipping the next backup.
For a tutorial on how to use Prometheus to set up monitoring and alerting, see Monitor CockroachDB with Prometheus.
View scheduled backup details
New in v20.2: 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)
View and control the backup schedule
Once a backup schedule is successfully created, you can view the schedule, pause the schedule, resume the schedule, or drop the schedule.
View the schedule
> SHOW SCHEDULES;
For more information, see SHOW SCHEDULES.
Pause the schedule
To pause a schedule, you can either specify the schedule's id:
> PAUSE SCHEDULE 589963390487363585;
Or nest a SELECT clause that retrieves id(s) inside the PAUSE SCHEDULES statement:
> PAUSE SCHEDULES SELECT id FROM [SHOW SCHEDULES] WHERE label = 'schedule_database';
For more information, see PAUSE SCHEDULES.
Resume the schedule
To resume a paused schedule, you can either specify the schedule's id:
> RESUME SCHEDULE 589963390487363585;
Or nest a SELECT clause that retrieves id(s) inside the RESUME SCHEDULES statement:
> RESUME SCHEDULES SELECT id FROM [SHOW SCHEDULES] WHERE label = 'schedule_database';
For more information, see RESUME SCHEDULES.
Drop the schedule
To drop a schedule, you can either specify the schedule's id:
> DROP SCHEDULE 589963390487363585;
Or nest a SELECT clause that retrieves id(s) inside the DROP SCHEDULES statement:
> DROP SCHEDULES SELECT id FROM [SHOW SCHEDULES] WHERE label = 'schedule_database';
For more information, see DROP SCHEDULES.
DROP SCHEDULE does not cancel any in-progress jobs started by the schedule. Before you drop a schedule, cancel any in-progress jobs first, as you will not be able to look up the job ID once the schedule is dropped.
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 view, pause, resume, or cancel each individual backup job.
View the backup job
To view jobs for a specific backup schedule, use the schedule's id:
> SHOW JOBS FOR SCHEDULE 590204387299262465;
job_id | job_type | description | statement | user_name | status | running_status | created | started | finished | modified | fraction_completed | error | coordinator_id
---------------------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-----------+---------+----------------+----------------------------------+---------+----------+----------------------------------+--------------------+-------+-----------------
590205481558802434 | BACKUP | BACKUP INTO '/2020/09/15-161444.99' IN 's3://test/scheduled-backup-0915?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=redacted' AS OF SYSTEM TIME '2020-09-15 16:20:00+00:00' WITH revision_history, detached | | root | running | NULL | 2020-09-15 16:20:18.347383+00:00 | NULL | NULL | 2020-09-15 16:20:18.347383+00:00 | 0 | | 0
(1 row)
You can also view multiple schedules by nesting a SELECT clause that retrieves id(s) inside the SHOW JOBS statement:
> SHOW JOBS FOR SCHEDULES SELECT id FROM [SHOW SCHEDULES] WHERE label = 'test_schedule';
job_id | job_type | description | statement | user_name | status | running_status | created | started | finished | modified | fraction_completed | error | coordinator_id
---------------------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-----------+-----------+----------------+----------------------------------+---------+----------------------------------+----------------------------------+--------------------+-------+-----------------
590204496007299074 | BACKUP | BACKUP INTO '/2020/09/15-161444.99' IN 's3://test/scheduled-backup-0915?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=redacted' AS OF SYSTEM TIME '2020-09-15 16:14:44.991631+00:00' WITH revision_history, detached | | root | succeeded | NULL | 2020-09-15 16:15:17.720725+00:00 | NULL | 2020-09-15 16:15:20.913789+00:00 | 2020-09-15 16:15:20.910594+00:00 | 1 | | 0
590205481558802434 | BACKUP | BACKUP INTO '/2020/09/15-161444.99' IN 's3://test/scheduled-backup-0915?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=redacted' AS OF SYSTEM TIME '2020-09-15 16:20:00+00:00' WITH revision_history, detached | | root | succeeded | NULL | 2020-09-15 16:20:18.347383+00:00 | NULL | 2020-09-15 16:20:48.37873+00:00 | 2020-09-15 16:20:48.374256+00:00 | 1 | | 0
(2 rows)
For more information, see SHOW JOBS.
Pause the backup job
To pause jobs for a specific backup schedule, use the schedule's id:
> PAUSE JOBS FOR SCHEDULE 590204387299262465;
PAUSE JOBS FOR SCHEDULES 1
You can also pause multiple schedules by nesting a SELECT clause that retrieves id(s) inside the PAUSE JOBS statement:
> PAUSE JOBS FOR SCHEDULES SELECT id FROM [SHOW SCHEDULES] WHERE label = 'test_schedule';
PAUSE JOBS FOR SCHEDULES 2
For more information, see PAUSE JOB.
Resume the backup job
To resume jobs for a specific backup schedule, use the schedule's id:
> RESUME JOBS FOR SCHEDULE 590204387299262465;
RESUME JOBS FOR SCHEDULES 1
You can also resume multiple schedules by nesting a SELECT clause that retrieves id(s) inside the PAUSE JOBS statement:
> RESUME JOBS FOR SCHEDULES SELECT id FROM [SHOW SCHEDULES] WHERE label = 'test_schedule';
RESUME JOBS FOR SCHEDULES 2
For more information, see RESUME JOB.
Cancel the backup job
To cancel jobs for a specific backup schedule, use the schedule's id:
> CANCEL JOBS FOR SCHEDULE 590204387299262465;
CANCEL JOBS FOR SCHEDULES 1
You can also CANCEL multiple schedules by nesting a SELECT clause that retrieves id(s) inside the CANCEL JOBS statement:
> CANCEL JOBS FOR SCHEDULES SELECT id FROM [SHOW SCHEDULES] WHERE label = 'test_schedule';
CANCEL JOBS FOR SCHEDULES 2
For more information, see CANCEL JOB.
Restore from a scheduled backup
To restore from a scheduled backup, use the RESTORE statement:
> RESTORE
FROM 's3://test/backups/test_schedule_1/2020/08/19-035600.00?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x'
AS OF SYSTEM TIME '2020-08-19 03:50:00+00:00';
To view the backups stored within a collection, use the SHOW BACKUP statement.
See also
CREATE SCHEDULE FOR BACKUPBACKUPRESTORESHOW 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?
