how to recreate timescaledb housekeeper compression jobs

[tonitsh]

After database restoration i see my timescaledb_information.jobs. There are some jobs with proc_schema _timescaledb_internal and some jobs with proc_schema _timescaledb_functions.
Editing the period in zabbix ui housekeeper , changes the _timescaledb_functions values, but initial_start and next_start values are empty. Even if i fill them up with TIMESTAMPTZ, they wont run.
Is there a correct way of removing and recreating housekeeper compression jobs ? ( running the job manually, compresses the chunks as expected, though no log is stored in timescaledb_information.job_stats of this run)

running on postgres14 + timescaledb 2.13.1
Any help would be appriciated.

[cyber]

I just tried... but my DB-fu was weak... as it was a test system I just dropped DB and recreated it..:P

There are jobs and settings, I had none of those in some reason... Maybe rerunning that script that converts things to TS might help (/usr/share/zabbix-sql-scripts/postgresql/timescaledb/schema.sql)... In some reason I did not try it.. Tried to mess around with adding those missing things one by one.. got frustrated and blew things up and recreated from scratch...

[tonitsh]

Re running the skritp doesn 't do it, it checks, and says, that timescale is installed and hypertables are already hypertables.
i wonder if diableing housekeeper and re-enableing would do it, but i have quite large database with loads of data, im hoping someone has actualy had to do it before, or has a skript for recreating jobs.

[tonitsh]

Im wondering if i should edit the sql skript provided for timescaledb, to delete the current jobs and then create them, without recreating the hypertables?!
though the skripts describe how to create a new table and migrate from old table, copy a job from the old table and then alter the job to set the next start time to now().
When i run that command, to set it to now () then i see it in the schedule, but it does not run, in job_stats, i see runned 0 times. in job error i see empty rows.
If i use call (jobid) , zabbix returns CALL, but still nothing happens. chunks are still not compressed, history is empty.

[cyber]

timescale docs say https://docs.timescale.com/api/latest/actions/add_job/

Register the user_defined_action procedure to run at midnight every Sunday. The initial_start provided must satisfy these requirements, so it must be a Sunday midnight

-- December 4, 2022 is a Sunday
SELECT add_job('user_defined_action','1 week', initial_start => '2022-12-04 00:00:00+00'::timestamptz);
-- if subject to DST
SELECT add_job('user_defined_action','1 week', initial_start => '2022-12-04 00:00:00+00'::timestamptz, timezone => 'Europe/Berlin');

:

So maybe you can try to set it to when it has to start next time instead of now? To run it right away its "CALL run_job(jobid)"

[tonitsh]

https://docs.timescale.com/use-timescale/latest/compression/troubleshooting/

SELECT _timescaledb_functions.start_background_workers();

Basicly what was wrong in my case, was that backgroundworkers had stopped.

after starting them the jobs continued to run. hope it helpes others aswell...

About the recreating part:
Delete old jobs, turn off housekeeper in Zabbix ui ( let the housekeeper run for 2 consecutive times: Checking or unchecking the checkbox does not activate/deactivate compression immediately. Because compression is handled by the Housekeeper, the changes will take effect in up to 2 times HousekeepingFrequency hours (set in zabbix_server.conf) )
then re enable it - the jobs are created automaticly.

Hope it helps someone else aswell..

[cyber]

you can force housekeeper run from command line, if you dont want to wait...:P

[tonitsh]

i can force the job, but how can i recreate it in a situation where i see that my compressing job is trying to comress items that are in chuncs which are already compressed ? ( i limited the job max_runtime to 1 hour, so it wouldn't hinder my production state.

zabbix=# select * from timescaledb_information.job_stats ;
hypertable_schema | hypertable_name | job_id | last_run_started_at | last_successful_finish | last_run_status | job_status | last_run_duration | next_start | total_runs | total_successes | total_failures
-------------------+-----------------+--------+-------------------------------+-------------------------------+-----------------+------------+-------------------+-------------------------------+------------+-----------------+----------------
public | auditlog | 1053 | 2024-10-09 ... | 2024-10-09 ... | Success | Scheduled | 00:00:00.03472 | 2024-10-10 16:48:21.455854+03 | 7 | 7 | 0
public | history | 1045 | 2024-10-09 ... | -infinity | Failed | Scheduled | 01:00:00.090013 | 2024-10-10 15:22:10.429871+03 | 17 | 0 | 17
public | history_bin | 1050 | 2024-10-09 ... | 2024-10-09 ... | Success | Scheduled | 00:00:00.020546 | 2024-10-10 16:48:21.480052+03 | 16 | 7 | 9
public | history_log | 1049 | 2024-10-09 ... | 2024-10-09 ... | Success | Scheduled | 00:00:00.038596 | 2024-10-10 16:48:21.462184+03 | 8 | 7 | 1
public | history_str | 1047 | 2024-10-09 ... | 2024-10-09 ... | Success | Scheduled | 00:00:00.035945 | 2024-10-10 16:48:21.461607+03 | 8 | 7 | 1
public | history_text | 1048 | 2024-10-09 ... | 2024-10-09 ... | Success | Scheduled | 00:00:01.816822 | 2024-10-10 16:48:28.44988+03 | 11 | 7 | 4
public | history_uint | 1046 | 2024-10-09 ... | -infinity | Failed | Scheduled | 01:00:00.060985 | 2024-10-10 16:57:37.532135+03 | 17 | 0 | 17
public | trends | 1051 | 2024-10-09 ... | 2024-10-09 ... | Success | Scheduled | 00:00:00.035488 | 2024-10-10 16:48:21.454515+03 | 7 | 7 | 0
public | trends_uint | 1052 | 2024-10-09 ... | 2024-10-09 ... | Success | Scheduled | 00:00:00.031877 | 2024-10-10 16:48:21.453944+03 | 7 | 7 | 0
| | 1 | 2024-10-10 ... | -infinity | Failed | Scheduled | 00:00:03.152325 | 2024-10-11 02:27:58.146346+03 | 77 | 0 | 77
| | 2 | 2024-10-02 ... | 2024-10-02 ... | Success | Scheduled | 00:00:00.038449 | 2024-11-01 02:00:00+02 | 72 | 2 | 70
(11 rows)
zabbix=# select * from timescaledb_information.job ;
zabbix=# select * from timescaledb_information.job_errors ;
job_id | proc_schema | proc_name | pid | start_time | finish_time | sqlerrcode | err_message
--------+-------------+-----------+-----+------------+-------------+------------+-------------
(0 rows)
zabbix=# select count(*) from timescaledb_information.chunks where chunk_creation_time < now() - interval '14 days' and is_compressed = 'f' and hypertable_name = 'history_uint' ;
count
-------
8
(1 row)
zabbix=# select count(*) from timescaledb_information.chunks where chunk_creation_time < now() - interval '14 days' and is_compressed = 'f' and hypertable_name = 'history' ;
count
-------
8
(1 row)
zabbix=# select config, owner from _timescaledb_config.bgw_job;
config | owner
---------------------------------------------------------------+----------
{"drop_after": "1 month"} | postgres
| postgres
{"hypertable_id": 5, "compress_after": 1216800} | zabbix
{"hypertable_id": 4, "compress_after": 1216800} | zabbix
{"hypertable_id": 3, "compress_after": 1216800} | zabbix
{"hypertable_id": 15, "compress_after": 1216800} | zabbix
{"hypertable_id": 6, "compress_after": 1216800} | zabbix
{"hypertable_id": 7, "compress_after": 1216800} | zabbix
{"hypertable_id": 16, "compress_created_before": "338:00:00"} | zabbix
{"hypertable_id": 1, "compress_after": 1216800} | zabbix
{"hypertable_id": 2, "compress_after": 1216800} | zabbix