CODEWITHSUNDEEP
sqlpostgresqltime-seriespostgresql-9.1timescaledb
Ann
Ann

Reputation: 165

TimescaleDB how to alter the retention policy's time interval

In timescale DB, it is not allowed to alter the retention time interval of a hyper table. Is there any work around or other ways to alter the time interval after a retention policy is set for a hypertable?

Upvotes: 0

Views: 1702

Answers (3)

ravi.zombie
ravi.zombie

Reputation: 1570

Have to only remove and add the policy back

select remove_retention_policy('tablename');
select add_retention_policy('tablename', INTERVAL '54 days');

link: timescale data retention

Upvotes: 0

0xh8h
0xh8h

Reputation: 3509

  1. Update retention policy
SELECT add_retention_policy('<table_name>', INTERVAL '1 hour');
  1. Use this query to see scheduled data retention jobs
SELECT j.hypertable_name,
       j.job_id,
       config,
       schedule_interval,
       job_status,
       last_run_status,
       last_run_started_at,
       js.next_start,
       total_runs,
       total_successes,
       total_failures
  FROM timescaledb_information.jobs j
  JOIN timescaledb_information.job_stats js
    ON j.job_id = js.job_id
  WHERE j.proc_name = 'policy_retention';

  1. Get the job_id from the above table

  2. Run this query to alter the job's schedule_interval

SELECT alter_job(1000, schedule_interval => INTERVAL '1 hour');

Upvotes: 0

Optimum
Optimum

Reputation: 146

Just remove the retention policy using remove_retention_policy and then add it back with the desired interval

Upvotes: 1

Related Questions