PAUSE JOB

The PAUSE JOB statement lets you pause the following types of jobs:

After pausing jobs, you can resume them with RESUME JOB.

If a schema change job is paused, any jobs waiting on that schema change will stop waiting and return an error.

Required privileges

To pause a job, the user must be a member of the admin role or must have the CONTROLJOB role option set. Non-admin users cannot pause admin users’ jobs. For changefeeds, users with the CHANGEFEED privilege on a set of tables can pause changefeed jobs running on those tables.

Synopsis

Pause all jobs by type

Parameters

Parameter	Description
`job_id`	The ID of the job you want to pause, which can be found with `SHOW JOBS`.
`select_stmt`	A selection query that returns `job_id`(s) to pause.
`for_schedules_clause`	The schedule you want to pause jobs for. You can pause jobs for a specific schedule (`FOR SCHEDULE id`) or pause jobs for multiple schedules by nesting a `SELECT` clause in the statement (`FOR SCHEDULES` ). See the examples below.
`WITH REASON = ...`	The reason to pause the job. CockroachDB stores the reason in the job’s metadata, but there is no way to display it.
`BACKUP`, `CHANGEFEED`, `RESTORE`, `IMPORT`	The job type to pause.

Monitoring paused jobs

We recommend monitoring paused jobs. Jobs that are paused for a long period of time can start to affect the cluster in the following ways:

A paused backup, restore, or index backfill job ( schema change ) will continue to hold a protected timestamp record on the data the job is operating on. This could result in data accumulation as the older versions of the keys cannot be garbage collected. In turn, this may cause increased disk usage and degraded performance for some workloads. See Protected timestamps and scheduled backups for more detail.
A paused changefeed job, if protect_data_from_gc_on_pause is set, will also hold a protected timestamp record on the data the job is operating on. Depending on the value of gc_protect_expires_after, this can lead to data accumulation. Once gc_protect_expires_after elapses, the protected timestamp record will be released and the changefeed job will be canceled. See Garbage collection and changefeeds for more detail.

To avoid these issues, use the jobs.{job_type}.currently_paused metric to track the number of jobs (for each job type) that are currently considered paused. You can monitor protected timestamps relating to particular CockroachDB jobs with the following metrics:

jobs.{job_type}.protected_age_sec tracks the oldest protected timestamp record protecting jobs. As this metric increases, garbage accumulation increases. Garbage collection will not progress on a table, database, or cluster if the protected timestamp record is present.
jobs.{job_type}.protected_record_count tracks the number of protected timestamp records held by jobs.

For a full list of the available job types, access your cluster’s /_status/vars endpoint. See the following pages for details on metrics:

Examples

Pause a single job

> SHOW JOBS;

      job_id     |  job_type |               description                      |...
-----------------+-----------+------------------------------------------------+...
  27536791415282 |  RESTORE  | RESTORE db.* FROM 'azure-blob://backup/db/tbl' |...

> PAUSE JOB 27536791415282;

Pause multiple jobs

To pause multiple jobs, nest a SELECT clause that retrieves job_id(s) inside the PAUSE JOBS statement:

> PAUSE JOBS (WITH x AS (SHOW JOBS) SELECT job_id FROM x
      WHERE user_name = 'maxroach');

All jobs created by maxroach will be paused.

Pause by job type

To pause all jobs by the type of job, use the PAUSE ALL {job} JOBS statement. You can pause all BACKUP, RESTORE, CHANGEFEED, IMPORT jobs using this statement, for example:

PAUSE ALL BACKUP JOBS;

Pause automatic table statistics jobs

> SHOW AUTOMATIC JOBS;

        job_id       |       job_type      |                    description                      |...
---------------------+---------------------+-----------------------------------------------------+...
  438235476849557505 |  AUTO CREATE STATS  | Table statistics refresh for defaultdb.public.users |...
(1 row)

> PAUSE JOB 438235476849557505;

To permanently disable automatic table statistics jobs, disable the sql.stats.automatic_collection.enabled cluster setting:

> SET CLUSTER SETTING sql.stats.automatic_collection.enabled = false;

Pause jobs for a schedule

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 WITH x AS (SHOW SCHEDULES) SELECT id FROM x WHERE label = 'test_schedule';

PAUSE JOBS FOR SCHEDULES 2

Statements

Syntax

Data Types

Spatial Data

Constraints

Required privileges

Synopsis

Pause all jobs by type

Parameters

Monitoring paused jobs

Examples

Pause a single job

Pause multiple jobs

Pause by job type

Pause automatic table statistics jobs

Pause jobs for a schedule

See also

​Required privileges

​Synopsis

​Pause all jobs by type

​Parameters

​Monitoring paused jobs

​Examples

​Pause a single job

​Pause multiple jobs

​Pause by job type

​Pause automatic table statistics jobs

​Pause jobs for a schedule

​See also

Required privileges

Synopsis

Pause all jobs by type

Parameters

Monitoring paused jobs

Examples

Pause a single job

Pause multiple jobs

Pause by job type

Pause automatic table statistics jobs

Pause jobs for a schedule

See also