The PAUSE JOB statement lets you pause the following types of jobs:
IMPORTjobsBACKUPandRESTOREjobs- User-created table statistics jobs
- Automatic table statistics jobs
- Changefeeds
- Schema change jobs
- Scheduled backup 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 parameter set.
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. |
Examples
Pause a single job
> SHOW JOBS;
job_id | job_type | description |...
-----------------+-----------+------------------------------------------------+...
27536791415282 | RESTORE | RESTORE db.* FROM 'azure://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 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
