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 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 <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 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_pauseis set, will also hold a protected timestamp record on the data the job is operating on. Depending on the value ofgc_protect_expires_after, this can lead to data accumulation. Oncegc_protect_expires_afterelapses, the protected timestamp record will be released and the changefeed job will be canceled. See Garbage collection and changefeeds for more detail.
New in v23.1:
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_sectracks 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_counttracks 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
> 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
See also
Was this helpful?
