timescaledb

Timescaledb: How to stop all continuous aggregation jobs in a database

I am creating test and static analysis copies from TimescaleDB by dumping and reimporting the data. These databases are read-only and do not have new data coming in. However, as they are copies of the original TimescaleDB database, they still have their continuous aggregation jobs running.

These jobs, as they have nothing to do, still seem to cause unnecessary wake ups and background CPU usage.

How can I disable all continuous aggregation jobs in a TimescaleDB?

Solution

  • The jobs are managed through automation policies. You should be able to identify the jobs that are scheduled with this

    SELECT * FROM timescaledb_information.jobs;

    You can either alter the job using the job id (example uses id 1000):

    SELECT alter_job(1000, scheduled => false);

    ... or alter all the continuous aggregate jobs...

    SELECT alter_job(job_id, scheduled => false)
FROM timescaledb_information.jobs
WHERE proc_name = 'policy_refresh_continuous_aggregate' 
AND hypertable_name = 'yourhypertablename'

    ALTERNATIVELY if you want to completely delete them then you can use

    SELECT delete_job(1000);

    NOTE: this would stop any currently running job too. In case you want to restart them then alter might be better than delete.

    You can get this from the TimescaleDB documentation by looking for automation policy, and it would be good to read through that so that you understand any impact this may have.

    Transparency: I work for Timescale