Z3005, TimescaleDB and zabbix 6

[atef.haloui]

Hi,
A couple of weeks ago, I've upgraded from 5.x to 6.x. This implies an upgrading of my postgresql installation from 11 to 13 and timescaledb from 1.7 to 2.6.
This worked fine but I didn't pay attention that housekeeping in no longer working and I finally got an error from zabbix about my database disk that is 90% full.
Looking at zabbix logs, I can see the following:

10538:20220721:081753.219 executing housekeeper
10538:20220721:081753.227 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: "history" is not a hypertable or a continuous aggregate
HINT: The operation is only possible on a hypertable or continuous aggregate.
[select drop_chunks(relation=>'history',older_than=>165708 8273)]
10538:20220721:081753.227 cannot drop chunks for history
10538:20220721:081753.227 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: "history_str" is not a hypertable or a continuous aggregate
HINT: The operation is only possible on a hypertable or continuous aggregate.
[select drop_chunks(relation=>'history_str',older_than=>16 57088273)]
10538:20220721:081753.227 cannot drop chunks for history_str
10538:20220721:081753.227 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: "history_log" is not a hypertable or a continuous aggregate
HINT: The operation is only possible on a hypertable or continuous aggregate.
[select drop_chunks(relation=>'history_log',older_than=>16 57088273)]
10538:20220721:081753.227 cannot drop chunks for history_log
10538:20220721:081753.228 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: "history_text" is not a hypertable or a continuous aggregate
HINT: The operation is only possible on a hypertable or continuous aggregate.
[select drop_chunks(relation=>'history_text',older_than=>1 657088273)]
10538:20220721:081753.228 cannot drop chunks for history_text
10538:20220721:081753.305 housekeeper [deleted 0 hist/trends, 0 items/triggers, 4 events, 3 problems, 0 sessions, 0 alarms, 2087 audit, 0 records in 0.078804 sec, idle for 1 hour(s)]

On postgresql side, I have the following errors:

2022-07-21 07:17:53.063 CEST [18515] zabbix@zabbix HINT: The operation is only possible on a hypertable or continuous aggregate.
2022-07-21 07:17:53.063 CEST [18515] zabbix@zabbix STATEMENT: select drop_chunks(relation=>'history_log',older_than=>16 57084673)
2022-07-21 07:17:53.063 CEST [18515] zabbix@zabbix ERROR: "history_text" is not a hypertable or a continuous aggregate
2022-07-21 07:17:53.063 CEST [18515] zabbix@zabbix HINT: The operation is only possible on a hypertable or continuous aggregate.
2022-07-21 07:17:53.063 CEST [18515] zabbix@zabbix STATEMENT: select drop_chunks(relation=>'history_text',older_than=>1 657084673)
2022-07-21 08:17:53.227 CEST [24400] zabbix@zabbix ERROR: "history" is not a hypertable or a continuous aggregate
2022-07-21 08:17:53.227 CEST [24400] zabbix@zabbix HINT: The operation is only possible on a hypertable or continuous aggregate.
2022-07-21 08:17:53.227 CEST [24400] zabbix@zabbix STATEMENT: select drop_chunks(relation=>'history',older_than=>165708 8273)
2022-07-21 08:17:53.227 CEST [24400] zabbix@zabbix ERROR: "history_str" is not a hypertable or a continuous aggregate
2022-07-21 08:17:53.227 CEST [24400] zabbix@zabbix HINT: The operation is only possible on a hypertable or continuous aggregate.
2022-07-21 08:17:53.227 CEST [24400] zabbix@zabbix STATEMENT: select drop_chunks(relation=>'history_str',older_than=>16 57088273)
2022-07-21 08:17:53.227 CEST [24400] zabbix@zabbix ERROR: "history_log" is not a hypertable or a continuous aggregate
2022-07-21 08:17:53.227 CEST [24400] zabbix@zabbix HINT: The operation is only possible on a hypertable or continuous aggregate.
2022-07-21 08:17:53.227 CEST [24400] zabbix@zabbix STATEMENT: select drop_chunks(relation=>'history_log',older_than=>16 57088273)
2022-07-21 08:17:53.228 CEST [24400] zabbix@zabbix ERROR: "history_text" is not a hypertable or a continuous aggregate
2022-07-21 08:17:53.228 CEST [24400] zabbix@zabbix HINT: The operation is only possible on a hypertable or continuous aggregate.
2022-07-21 08:17:53.228 CEST [24400] zabbix@zabbix STATEMENT: select drop_chunks(relation=>'history_text',older_than=>1 657088273)

As I'm not expert on TimescaleDB, I've tried to run again the timescaledb.sql --> it worked fine without errors.
However, as soon as housekeeping run again I got the same errors.

How could I correct the problem please ?
Would it be possible to drop timescaledb from my installation and use a normal postgresql ?
I'm not interested in keeping history and trends so I can drops all data from these tables.

Thank you very much for your help

[vladimir_lv]

Hi!
check if these tables exist in the hypertables list:

select * from _timescaledb_catalog.hypertable;

Did you complete all steps described here/

https://www.zabbix.com/documentation/6.2/en/manual/appendix/install/timescaledb?hl=TimescaleDB%2Ctimescaledb

[atef.haloui]

Hi,
As I've made an upgrade, I didn't take all required steps. I will execute them.
In the meanwhile, this is the result of the select statement:

l$ echo "select * from _timescaledb_catalog.hypertable;" | sudo -u postgres psql zabbix
id | schema_name | table_name | associated_schema_name | associated_table_prefix | num_dimensions | chunk_sizing_func_schema | chunk_sizing_func_name | chunk_target_size | compression_state | compressed_hypertable_id | replication_factor
----+-----------------------+---------------------------+------------------------+-------------------------+----------------+--------------------------+--------------------------+-------------------+-------------------+--------------------------+--------------------
13 | _timescaledb_internal | _compressed_hypertable_13 | _timescaledb_internal | _hyper_13 | 0 | _timescaledb_internal | calculate_chunk_interval | 0 | 2 | |
6 | public | trends | _timescaledb_internal | _hyper_6 | 1 | _timescaledb_internal | calculate_chunk_interval | 0 | 1 | 13 |
14 | _timescaledb_internal | _compressed_hypertable_14 | _timescaledb_internal | _hyper_14 | 0 | _timescaledb_internal | calculate_chunk_interval | 0 | 2 | |
7 | public | trends_uint | _timescaledb_internal | _hyper_7 | 1 | _timescaledb_internal | calculate_chunk_interval | 0 | 1 | 14 |
19 | _timescaledb_internal | _compressed_hypertable_19 | _timescaledb_internal | _hyper_19 | 0 | _timescaledb_internal | calculate_chunk_interval | 0 | 2 | |
15 | public | history_uint | _timescaledb_internal | _hyper_15 | 1 | _timescaledb_internal | calculate_chunk_interval | 0 | 1 | 19 |
(6 rows)

Thank you

[atef.haloui]

Hi,
I've resolved the problem by simply deleting tables and recreating them:

DROP TABLE history;
DROP TABLE history_uint;
DROP TABLE history_log;
DROP TABLE history_text;
DROP TABLE history_str;
DROP TABLE trends;
DROP TABLE trends_uint;

CREATE TABLE history (
    itemid                   bigint                                    NOT NULL,
    clock                    integer         DEFAULT '0'               NOT NULL,
    value                    DOUBLE PRECISION DEFAULT '0.0000'          NOT NULL,
    ns                       integer         DEFAULT '0'               NOT NULL,
    PRIMARY KEY (itemid,clock,ns)
);
CREATE TABLE history_uint (
    itemid                   bigint                                    NOT NULL,
    clock                    integer         DEFAULT '0'               NOT NULL,
    value                    numeric(20)     DEFAULT '0'               NOT NULL,
    ns                       integer         DEFAULT '0'               NOT NULL,
    PRIMARY KEY (itemid,clock,ns)
);
CREATE TABLE history_str (
    itemid                   bigint                                    NOT NULL,
    clock                    integer         DEFAULT '0'               NOT NULL,
    value                    varchar(255)    DEFAULT ''                NOT NULL,
    ns                       integer         DEFAULT '0'               NOT NULL,
    PRIMARY KEY (itemid,clock,ns)
);
CREATE TABLE history_log (
    itemid                   bigint                                    NOT NULL,
    clock                    integer         DEFAULT '0'               NOT NULL,
    timestamp                integer         DEFAULT '0'               NOT NULL,
    source                   varchar(64)     DEFAULT ''                NOT NULL,
    severity                 integer         DEFAULT '0'               NOT NULL,
    value                    text            DEFAULT ''                NOT NULL,
    logeventid               integer         DEFAULT '0'               NOT NULL,
    ns                       integer         DEFAULT '0'               NOT NULL,
    PRIMARY KEY (itemid,clock,ns)
);
CREATE TABLE history_text (
    itemid                   bigint                                    NOT NULL,
    clock                    integer         DEFAULT '0'               NOT NULL,
    value                    text            DEFAULT ''                NOT NULL,
    ns                       integer         DEFAULT '0'               NOT NULL,
    PRIMARY KEY (itemid,clock,ns)
);
CREATE TABLE trends (
    itemid                   bigint                                    NOT NULL,
    clock                    integer         DEFAULT '0'               NOT NULL,
    num                      integer         DEFAULT '0'               NOT NULL,
    value_min                DOUBLE PRECISION DEFAULT '0.0000'          NOT NULL,
    value_avg                DOUBLE PRECISION DEFAULT '0.0000'          NOT NULL,
    value_max                DOUBLE PRECISION DEFAULT '0.0000'          NOT NULL,
    PRIMARY KEY (itemid,clock)
);
CREATE TABLE trends_uint (
    itemid                   bigint                                    NOT NULL,
    clock                    integer         DEFAULT '0'               NOT NULL,
    num                      integer         DEFAULT '0'               NOT NULL,
    value_min                numeric(20)     DEFAULT '0'               NOT NULL,
    value_avg                numeric(20)     DEFAULT '0'               NOT NULL,
    value_max                numeric(20)     DEFAULT '0'               NOT NULL,
    PRIMARY KEY (itemid,clock)
);

ALTER TABLE history OWNER TO zabbix;
ALTER TABLE history_uint OWNER TO zabbix;
ALTER TABLE history_log OWNER TO zabbix;
ALTER TABLE history_text OWNER TO zabbix;
ALTER TABLE history_str OWNER TO zabbix;
ALTER TABLE trends OWNER TO zabbix;
ALTER TABLE trends_uint OWNER TO zabbix;

cat /usr/share/doc/zabbix-sql-scripts/postgresql/timescaledb.sql | sudo -u zabbix psql zabbix

Thank you for your help

[atef.haloui]

$ echo "select * from _timescaledb_catalog.hypertable;" | sudo -u postgres psql zabbix
id | schema_name | table_name | associated_schema_name | associated_table_prefix | num_dimensions | chunk_sizing_func_schema | chunk_sizing_func_name | chunk_target_size | compression_state | compressed_hypertable_id | replication_factor
----+-----------------------+---------------------------+------------------------+-------------------------+----------------+--------------------------+--------------------------+-------------------+-------------------+--------------------------+--------------------
31 | _timescaledb_internal | _compressed_hypertable_31 | _timescaledb_internal | _hyper_31 | 0 | _timescaledb_internal | calculate_chunk_interval | 0 | 2 | |
24 | public | history | _timescaledb_internal | _hyper_24 | 1 | _timescaledb_internal | calculate_chunk_interval | 0 | 1 | 31 |
32 | _timescaledb_internal | _compressed_hypertable_32 | _timescaledb_internal | _hyper_32 | 0 | _timescaledb_internal | calculate_chunk_interval | 0 | 2 | |
25 | public | history_uint | _timescaledb_internal | _hyper_25 | 1 | _timescaledb_internal | calculate_chunk_interval | 0 | 1 | 32 |
33 | _timescaledb_internal | _compressed_hypertable_33 | _timescaledb_internal | _hyper_33 | 0 | _timescaledb_internal | calculate_chunk_interval | 0 | 2 | |
28 | public | history_str | _timescaledb_internal | _hyper_28 | 1 | _timescaledb_internal | calculate_chunk_interval | 0 | 1 | 33 |
34 | _timescaledb_internal | _compressed_hypertable_34 | _timescaledb_internal | _hyper_34 | 0 | _timescaledb_internal | calculate_chunk_interval | 0 | 2 | |
27 | public | history_text | _timescaledb_internal | _hyper_27 | 1 | _timescaledb_internal | calculate_chunk_interval | 0 | 1 | 34 |
35 | _timescaledb_internal | _compressed_hypertable_35 | _timescaledb_internal | _hyper_35 | 0 | _timescaledb_internal | calculate_chunk_interval | 0 | 2 | |
26 | public | history_log | _timescaledb_internal | _hyper_26 | 1 | _timescaledb_internal | calculate_chunk_interval | 0 | 1 | 35 |
36 | _timescaledb_internal | _compressed_hypertable_36 | _timescaledb_internal | _hyper_36 | 0 | _timescaledb_internal | calculate_chunk_interval | 0 | 2 | |
29 | public | trends | _timescaledb_internal | _hyper_29 | 1 | _timescaledb_internal | calculate_chunk_interval | 0 | 1 | 36 |
37 | _timescaledb_internal | _compressed_hypertable_37 | _timescaledb_internal | _hyper_37 | 0 | _timescaledb_internal | calculate_chunk_interval | 0 | 2 | |
30 | public | trends_uint | _timescaledb_internal | _hyper_30 | 1 | _timescaledb_internal | calculate_chunk_interval | 0 | 1 | 37 |
(14 rows)

[Rudlafik]

THX this thread is wery helpfuly.
I have little diferent problem - half migration proces.

Output from control of TS DB tables was there:

l$ echo "select * from _timescaledb_catalog.hypertable;" | sudo -u postgres psql zabbix
id | schema_name | table_name | associated_schema_name | associated_table_prefix | num_dimensions | chunk_sizing_func_schema | chunk_sizing_func_name | chunk_target_size | compression_state | compressed_hypertable_id | replication_factor
----+-----------------------+---------------------------+------------------------+-------------------------+----------------+--------------------------+--------------------------+-------------------+-------------------+--------------------------+--------------------
13 | _timescaledb_internal | _compressed_hypertable_13 | _timescaledb_internal | _hyper_13 | 0 | _timescaledb_internal | calculate_chunk_interval | 0 | 2 | |
6 | public | trends | _timescaledb_internal | _hyper_6 | 1 | _timescaledb_internal | calculate_chunk_interval | 0 | 1 | 13 |
14 | _timescaledb_internal | _compressed_hypertable_14 | _timescaledb_internal | _hyper_14 | 0 | _timescaledb_internal | calculate_chunk_interval | 0 | 2 | |
7 | public | trends_uint | _timescaledb_internal | _hyper_7 | 1 | _timescaledb_internal | calculate_chunk_interval | 0 | 1 | 14 |
(4 rows)

I leaves this tables in TS and edit migration script:

DO $$
DECLARE
        minimum_postgres_version_major          INTEGER;
        minimum_postgres_version_minor          INTEGER;
        current_postgres_version_major          INTEGER;
        current_postgres_version_minor          INTEGER;
        current_postgres_version_full           VARCHAR;

        minimum_timescaledb_version_major       INTEGER;
        minimum_timescaledb_version_minor       INTEGER;
        current_timescaledb_version_major       INTEGER;
        current_timescaledb_version_minor       INTEGER;
        current_timescaledb_version_full        VARCHAR;
BEGIN
        SELECT 10 INTO minimum_postgres_version_major;
        SELECT 2 INTO minimum_postgres_version_minor;
        SELECT 1 INTO minimum_timescaledb_version_major;
        SELECT 5 INTO minimum_timescaledb_version_minor;

        SHOW server_version INTO current_postgres_version_full;

        IF NOT found THEN
                RAISE EXCEPTION 'Cannot determine PostgreSQL version, aborting';
        END IF;

        SELECT substring(current_postgres_version_full, '^(\d+).') INTO current_postgres_version_major;
        SELECT substring(current_postgres_version_full, '^\d+.(\d+)') INTO current_postgres_version_minor;

        IF (current_postgres_version_major < minimum_postgres_version_major OR
                        (current_postgres_version_major = minimum_postgres_version_major AND
                        current_postgres_version_minor < minimum_postgres_version_minor)) THEN
                        RAISE EXCEPTION 'PostgreSQL version % is NOT SUPPORTED (with TimescaleDB)! Minimum is %.%.0 !',
                                        current_postgres_version_full, minimum_postgres_version_major,
                                        minimum_postgres_version_minor;
        ELSE
                RAISE NOTICE 'PostgreSQL version % is valid', current_postgres_version_full;
        END IF;

        SELECT extversion INTO current_timescaledb_version_full FROM pg_extension WHERE extname = 'timescaledb';

        IF NOT found THEN
                RAISE EXCEPTION 'TimescaleDB extension is not installed';
        ELSE
                RAISE NOTICE 'TimescaleDB extension is detected';
        END IF;

        SELECT substring(current_timescaledb_version_full, '^(\d+).') INTO current_timescaledb_version_major;
        SELECT substring(current_timescaledb_version_full, '^\d+.(\d+)') INTO current_timescaledb_version_minor;

        IF (current_timescaledb_version_major < minimum_timescaledb_version_major OR
                        (current_timescaledb_version_major = minimum_timescaledb_version_major AND
                        current_timescaledb_version_minor < minimum_timescaledb_version_minor)) THEN
                RAISE EXCEPTION 'TimescaleDB version % is UNSUPPORTED! Minimum is %.%.0!',
                                current_timescaledb_version_full, minimum_timescaledb_version_major,
                                minimum_timescaledb_version_minor;
        ELSE
                RAISE NOTICE 'TimescaleDB version % is valid', current_timescaledb_version_full;
        END IF;
        PERFORM create_hypertable('history', 'clock', chunk_time_interval => 86400, migrate_data => true);
        PERFORM create_hypertable('history_uint', 'clock', chunk_time_interval => 86400, migrate_data => true);
        PERFORM create_hypertable('history_log', 'clock', chunk_time_interval => 86400, migrate_data => true);
        PERFORM create_hypertable('history_text', 'clock', chunk_time_interval => 86400, migrate_data => true);
        PERFORM create_hypertable('history_str', 'clock', chunk_time_interval => 86400, migrate_data => true);
        UPDATE config SET db_extension='timescaledb',hk_history_global=1;
        UPDATE config SET compression_status=1,compress_older='7d';
        RAISE NOTICE 'TimescaleDB is configured successfully';
END $$;

Next:

sudo cat timescaledb2.sql | sudo -u zabbix psql zabbix

And than perfect control:

select * from _timescaledb_catalog.hypertable;

zabbix=> select * from _timescaledb_catalog.hypertable;
 id |      schema_name      |        table_name         | associated_schema_name | associated_table_prefix | num_dimensions | chunk_sizing_func_schema |  chunk_sizing_func_name  | chunk_target_size | compression_state | compressed_hypertable_id | replication_factor
----+-----------------------+---------------------------+------------------------+-------------------------+----------------+--------------------------+--------------------------+-------------------+-------------------+--------------------------+--------------------
 13 | _timescaledb_internal | _compressed_hypertable_13 | _timescaledb_internal  | _hyper_13               |              0 | _timescaledb_internal    | calculate_chunk_interval |                 0 |                 2 |                          |
  6 | public                | trends                    | _timescaledb_internal  | _hyper_6                |              1 | _timescaledb_internal    | calculate_chunk_interval |                 0 |                 1 |                       13 |
 14 | _timescaledb_internal | _compressed_hypertable_14 | _timescaledb_internal  | _hyper_14               |              0 | _timescaledb_internal    | calculate_chunk_interval |                 0 |                 2 |                          |
  7 | public                | trends_uint               | _timescaledb_internal  | _hyper_7                |              1 | _timescaledb_internal    | calculate_chunk_interval |                 0 |                 1 |                       14 |
 28 | _timescaledb_internal | _compressed_hypertable_28 | _timescaledb_internal  | _hyper_28               |              0 | _timescaledb_internal    | calculate_chunk_interval |                 0 |                 2 |                          |
 23 | public                | history                   | _timescaledb_internal  | _hyper_23               |              1 | _timescaledb_internal    | calculate_chunk_interval |                 0 |                 1 |                       28 |
 29 | _timescaledb_internal | _compressed_hypertable_29 | _timescaledb_internal  | _hyper_29               |              0 | _timescaledb_internal    | calculate_chunk_interval |                 0 |                 2 |                          |
 24 | public                | history_uint              | _timescaledb_internal  | _hyper_24               |              1 | _timescaledb_internal    | calculate_chunk_interval |                 0 |                 1 |                       29 |
 30 | _timescaledb_internal | _compressed_hypertable_30 | _timescaledb_internal  | _hyper_30               |              0 | _timescaledb_internal    | calculate_chunk_interval |                 0 |                 2 |                          |
 27 | public                | history_str               | _timescaledb_internal  | _hyper_27               |              1 | _timescaledb_internal    | calculate_chunk_interval |                 0 |                 1 |                       30 |
 31 | _timescaledb_internal | _compressed_hypertable_31 | _timescaledb_internal  | _hyper_31               |              0 | _timescaledb_internal    | calculate_chunk_interval |                 0 |                 2 |                          |
 26 | public                | history_text              | _timescaledb_internal  | _hyper_26               |              1 | _timescaledb_internal    | calculate_chunk_interval |                 0 |                 1 |                       31 |
 32 | _timescaledb_internal | _compressed_hypertable_32 | _timescaledb_internal  | _hyper_32               |              0 | _timescaledb_internal    | calculate_chunk_interval |                 0 |                 2 |                          |
 25 | public                | history_log               | _timescaledb_internal  | _hyper_25               |              1 | _timescaledb_internal    | calculate_chunk_interval |                 0 |                 1 |                       32 |
(14 rows)

Notice:

I upgrade system from PSQL 12 ->13 and TS 1.7 -> 2.3 than ZBX 5.4.X -> 6.0.2