CREATE SCHEDULE FOR BACKUP

On this page

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.

Required privileges

You can grant the BACKUP privilege to a user or role depending on the type of backup:

The listed privileges do not cascade to objects lower in the schema tree. For example, if you are granted database-level BACKUP privileges, this does not give you the privilege to back up a table. If you need the BACKUP privilege on a database to apply to all newly created tables in that database, use DEFAULT PRIVILEGES. You can add BACKUP to the user or role's default privileges with ALTER DEFAULT PRIVILEGES.

Members of the admin role can run all three types of backups (cluster, database, and table) without the need to grant a specific BACKUP privilege. However, we recommend using the BACKUP privilege model to create users or roles and grant them BACKUP privileges as necessary for stronger access control.

Privileges for managing a backup job

To manage a backup job with PAUSE JOB, RESUME JOB, or CANCEL JOB, users must have at least one of the following:

• Be a member of the admin role.
• The CONTROLJOB role option.

To view a backup job with SHOW JOB, users must have at least one of the following:

• New in v23.1: The VIEWJOB privilege, which allows you to view all jobs (including admin-owned jobs).
• Be a member of the admin role.
• The CONTROLJOB role option.

See GRANT for detail on granting privileges to a role or user.

Required privileges using the legacy privilege model

The following details the legacy privilege model that CockroachDB supports in v22.2 and earlier. Support for this privilege model will be removed in a future release of CockroachDB:

• Full cluster backups can only be run by members of the admin role. By default, the root user belongs to the admin role.
• For all other backups, the user must have read access on all objects being backed up. Database backups require CONNECT privileges, and table backups require SELECT privileges. Backups of user-defined schemas, or backups containing user-defined types, require USAGE privileges.

See the Required privileges section for the updated privilege model.

Destination privileges

You can grant a user the EXTERNALIOIMPLICITACCESS system-level privilege.

Either the EXTERNALIOIMPLICITACCESS system-level privilege or the admin role is required for the following scenarios:

• Interacting with a cloud storage resource using IMPLICIT authentication.
• Using a custom endpoint on S3.
• Using the cockroach nodelocal upload command.

No special privilege is required for:

• Interacting with an Amazon S3 and Google Cloud Storage resource using SPECIFIED credentials. Azure Storage is always SPECIFIED by default.
• Using Userfile storage.

We recommend using cloud storage. You also need to ensure that the permissions at your storage destination are configured for the operation. See Storage Permissions for a list of the necessary permissions that each bulk operation requires.

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

Backup options

Schedule options

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

Protected timestamps and scheduled backups

Scheduled backups ensure that the data to be backed up is protected from garbage collection until it has been successfully backed up. This active management of protected timestamps means that you can run scheduled backups at a cadence independent from the GC TTL of the data. This is unlike non-scheduled backups that are tightly coupled to the GC TTL. See Garbage collection and backups for more detail.

The data being backed up will not be eligible for garbage collection until a successful backup completes. At this point, the schedule will release the existing protected timestamp record and write a new one to protect data for the next backup that is scheduled to run. It is important to consider that when a scheduled backup fails there will be an accumulation of data until the next successful backup. Resolving the backup failure or dropping the backup schedule will make the data eligible for garbage collection once again.

You can also use the exclude_data_from_backup option with a scheduled backup as a way to prevent protected timestamps from prolonging garbage collection on a table. See the example Exclude a table's data from backups for usage information.

We recommend monitoring your backup schedule to alert for failed backups:

• See the Backup and Restore Monitoring page for a general overview and list of metrics available for backup, scheduled backup, and restore jobs.
• See Set up monitoring for the backup schedule for metrics and monitoring backup schedules specifically.

Incremental backup schedules

The incremental backup schedule is created in a paused state, and is only un-paused on completion of the first, scheduled full backup. This ensures that the first incremental backup is only executed once it has a full backup to build a chain from. Thereafter, the incremental backups are scheduled to run at its specified cadence.

Incremental backups always append to the latest, complete full backup. An incremental backup can run concurrently with a full backup, but in such a situation it will continue to append to the previous full backup that has already completed.

An incremental backup will always wait for another incremental backup started by the same schedule to complete before running. This prevents incremental backups from backing up overlapping spans of time in the same backup chain. To enforce this, backup schedules created or altered using the on_previous_running option will have the full backup schedule created with the user specified option, but will always default the incremental backup schedule option to on_previous_running = wait.

View and control backup schedules

Once a backup schedule is successfully created, you can do the following:

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:

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 is 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 is 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 is 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 is 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 schedule options

This example creates a schedule for a cluster backup with the on_previous_running option:

> CREATE SCHEDULE schedule_option
  FOR BACKUP INTO 's3://test/backups/schedule_test?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x'
    RECURRING '@daily'
    WITH SCHEDULE OPTIONS on_previous_running = 'start';

     schedule_id     |      label      |                     status                     |          first_run           | schedule |                                                         backup_stmt
---------------------+-----------------+------------------------------------------------+------------------------------+----------+------------------------------------------------------------------------------------------------------------------------------
  866226603264475137 | schedule_option | PAUSED: Waiting for initial backup to complete | NULL                         | @daily   | BACKUP INTO LATEST IN 's3://test/backups/schedule_test?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x' WITH detached
  866226603270635521 | schedule_option | ACTIVE                                         | 2023-05-18 14:56:20.39198+00 | @weekly  | BACKUP INTO 's3://test/backups/schedule_test?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=x' WITH detached
(2 rows)

The schedule starts a new backup, even if the previous one is still running because the user specified option for on_previous_running = 'start'. The incremental backup remains PAUSED until the initial full backup is complete.

Because the FULL BACKUP clause is not included, CockroachDB also schedules 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 collectionURI statement to view a list of the full backup's subdirectories.
• SHOW BACKUP FROM subdirectory IN collectionURI statement to view a list of the full and incremental backups that are stored in a specific full backup's subdirectory.
• Use the Schedules page in the DB Console to view a list of created backup schedules and their individual details.

For more details, see SHOW BACKUP.

See also

• Manage a Backup Schedule
• BACKUP
• RESTORE
• SHOW BACKUP
• SHOW SCHEDULES
• PAUSE SCHEDULES
• RESUME SCHEDULES
• DROP SCHEDULES
• ALTER BACKUP SCHEDULE
• PAUSE JOB
• RESUME JOB
• CANCEL JOB
• Take Full and Incremental Backups
• Use the Built-in SQL Client
• cockroach Commands Overview

Was this helpful?