PAUSE JOB

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

• jobs
• and jobs
• jobs
• jobs
• jobs
• jobs

After pausing jobs, you can resume them with .

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 set. Non-admin users cannot pause admin users’ jobs.

For changefeeds, users with the 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 .
select_stmt	A 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 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.
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 , , or index backfill job () will continue to hold a record on the data the job is operating on. This could result in data accumulation as the older versions of the keys cannot be . In turn, this may cause increased disk usage and degraded performance for some workloads. See for more detail.
• A paused job, if is set, will also hold a protected timestamp record on the data the job is operating on. Depending on the value of , 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 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 .

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 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 :

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

Pause jobs for a schedule

To pause jobs for a specific , use the schedule’s id:

> PAUSE JOBS FOR SCHEDULE 590204387299262465;

PAUSE JOBS FOR SCHEDULES 1

You can also pause multiple schedules by nesting a 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

See also