PAUSE JOB

On this page Carat arrow pointing down

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

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

Note:

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

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 <select_clause>). 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.

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:

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 {job_type} 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 {job_type} 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

icon/buttons/copy
> SHOW JOBS;
      job_id     |  job_type |               description                      |...
-----------------+-----------+------------------------------------------------+...
  27536791415282 |  RESTORE  | RESTORE db.* FROM 'azure-blob://backup/db/tbl' |...
icon/buttons/copy
> PAUSE JOB 27536791415282;

Pause multiple jobs

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

icon/buttons/copy
> 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 automatic table statistics jobs

icon/buttons/copy
> SHOW AUTOMATIC JOBS;
        job_id       |       job_type      |                    description                      |...
---------------------+---------------------+-----------------------------------------------------+...
  438235476849557505 |  AUTO CREATE STATS  | Table statistics refresh for defaultdb.public.users |...
(1 row)
icon/buttons/copy
> PAUSE JOB 438235476849557505;

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

icon/buttons/copy
> 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:

icon/buttons/copy
> 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:

icon/buttons/copy
> PAUSE JOBS FOR SCHEDULES WITH x AS (SHOW SCHEDULES) SELECT id FROM x WHERE label = 'test_schedule';
PAUSE JOBS FOR SCHEDULES 2

See also


Yes No
On this page

Yes No