Názory k článku
Nová verze Zabbix 7.0.2: Opravy chyb a automatická konverze tabulky history_bin

Konverze se asi pri aktualizaci z 7.0.0 na 7.0.2 nepovedla:

zabbix=# SELECT * FROM chunks_detailed_size('history_bin') ORDER BY chunk_name, node_name;
ERROR:  function chunks_detailed_size(unknown) does not exist
LINE 1: SELECT * FROM chunks_detailed_size('history_bin') ORDER BY ...
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
zabbix=#

Kdyz jsem zkusil naimportovat rucne schema, opet to skoncilo s chybou:

root@zabbix / # dpkg -l | grep zabbix-server
ii  zabbix-server-pgsql                1:7.0.2-1+debian12                             amd64        Zabbix network monitoring solution - server (PostgreSQL)
root@zabbix / #
root@zabbix / # cat /usr/share/zabbix-sql-scripts/postgresql/timescaledb/schema.sql | sudo -i -u postgres psql zabbix
DROP FUNCTION
CREATE FUNCTION
ERROR:  cannot drop function cuid_timestamp(character varying) because other objects depend on it
DETAIL:  constraint constraint_2571 on table _timescaledb_internal._hyper_15_5103_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2572 on table _timescaledb_internal._hyper_15_5104_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2573 on table _timescaledb_internal._hyper_15_5105_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2574 on table _timescaledb_internal._hyper_15_5106_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2575 on table _timescaledb_internal._hyper_15_5107_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2576 on table _timescaledb_internal._hyper_15_5108_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2577 on table _timescaledb_internal._hyper_15_5109_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2578 on table _timescaledb_internal._hyper_15_5110_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2579 on table _timescaledb_internal._hyper_15_5111_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2580 on table _timescaledb_internal._hyper_15_5112_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2581 on table _timescaledb_internal._hyper_15_5113_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2582 on table _timescaledb_internal._hyper_15_5114_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2583 on table _timescaledb_internal._hyper_15_5115_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2584 on table _timescaledb_internal._hyper_15_5116_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2585 on table _timescaledb_internal._hyper_15_5117_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2586 on table _timescaledb_internal._hyper_15_5118_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2587 on table _timescaledb_internal._hyper_15_5119_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2588 on table _timescaledb_internal._hyper_15_5120_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2589 on table _timescaledb_internal._hyper_15_5121_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2590 on table _timescaledb_internal._hyper_15_5122_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2591 on table _timescaledb_internal._hyper_15_5123_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2592 on table _timescaledb_internal._hyper_15_5124_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2593 on table _timescaledb_internal._hyper_15_5125_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2594 on table _timescaledb_internal._hyper_15_5126_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2595 on table _timescaledb_internal._hyper_15_5127_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2596 on table _timescaledb_internal._hyper_15_5128_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2597 on table _timescaledb_internal._hyper_15_5129_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2598 on table _timescaledb_internal._hyper_15_5130_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2599 on table _timescaledb_internal._hyper_15_5131_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2600 on table _timescaledb_internal._hyper_15_5132_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2601 on table _timescaledb_internal._hyper_15_5133_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2602 on table _timescaledb_internal._hyper_15_5134_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2607 on table _timescaledb_internal._hyper_15_5143_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2637 on table _timescaledb_internal._hyper_15_5201_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2669 on table _timescaledb_internal._hyper_15_5264_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2699 on table _timescaledb_internal._hyper_15_5323_chunk depends on function cuid_timestamp(character varying)
constraint constraint_2728 on table _timescaledb_internal._hyper_15_5381_chunk depends on function cuid_timestamp(character varying)
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
CREATE FUNCTION
NOTICE:  PostgreSQL version 14.12 (Debian 14.12-1.pgdg120+1) is valid
NOTICE:  TimescaleDB extension is detected
NOTICE:  TimescaleDB version 2.15.3 is valid
NOTICE:  table "history" is already a hypertable, skipping
NOTICE:  table "history_uint" is already a hypertable, skipping
NOTICE:  table "history_log" is already a hypertable, skipping
NOTICE:  table "history_text" is already a hypertable, skipping
NOTICE:  table "history_str" is already a hypertable, skipping
NOTICE:  table "history_bin" is already a hypertable, skipping
NOTICE:  table "auditlog" is already a hypertable, skipping
NOTICE:  table "trends" is already a hypertable, skipping
NOTICE:  table "trends_uint" is already a hypertable, skipping
NOTICE:  TimescaleDB extension is already installed; not changing configuration
NOTICE:  TimescaleDB is configured successfully
DO

Muzete mi prosim poradit, jak to resit?

Kdyz jsem psal zpravicku tak jsem aktualizoval Zabbix 7.0.2 z 7.0.1 na Debianu a provedlo se to automaticky.

Dnes jsem upgrade provadel na FreeBSD 14.1 a samo se to neprovedlo.

88606:2024080­5:163410.738 [Z3005] query failed: [0] PGRES_FATAL_ER­ROR:ERROR: table "history_bin" is not a hypertable
[select set_integer_now_fun­c('history_bin', 'zbx_ts_unix_now', true)]
88606:2024080­5:163410.738 Table "history_bin" is not a hypertable. Execute TimescaleDB configuration step as described in Zabbix documentation to upgrade schema.

Opravite to tak ze opetovne importujeje timescaledb schema
psql zabbix < /usr/local/sha­re/zabbix7/ser­ver/database/pos­tgresql/times­caledb/schema­.sql

Ja mam PostgreSQL 16.3 z logu vidim ze mate PostgreSQL 14.12

Podpora by tam mela byt:
https://docs.timescale.com/self-hosted/latest/upgrades/upgrade-pg/

To presne jsem delal a neslo to:

root@zabbix / # cat /usr/share/zabbix-sql-scripts/postgresql/timescaledb/schema.sql | sudo -i -u postgres psql zabbix
DROP FUNCTION
CREATE FUNCTION
ERROR:  cannot drop function cuid_timestamp(character varying) because other objects depend on it

:(

# cat /usr/share/zabbix-sql-scripts/postgres­ql/timescaled­b/schema.sql | sudo -i -u postgres psql zabbix
DROP FUNCTION
CREATE FUNCTION
ERROR: cannot drop function cuid_timestam­p(character varying) because other objects depend on it
DETAIL: constraint constraint_361 on table _timescaledb_in­ternal._hyper_15_439_c­hunk depends on function cuid_timestam­p(character varying)
constraint constraint_390 on table _timescaledb_in­ternal._hyper_15_468_c­hunk depends on function cuid_timestam­p(character varying)
DOPORUČENÍ: Use DROP ... CASCADE to drop the dependent objects too.
CREATE FUNCTION
NOTICE: PostgreSQL version 15.7 (Debian 15.7-0+deb12u1) is valid
NOTICE: TimescaleDB extension is detected
NOTICE: TimescaleDB version 2.15.3 is valid
NOTICE: table "history" is already a hypertable, skipping
NOTICE: table "history_uint" is already a hypertable, skipping
NOTICE: table "history_log" is already a hypertable, skipping
NOTICE: table "history_text" is already a hypertable, skipping
NOTICE: table "history_str" is already a hypertable, skipping
NOTICE: table "history_bin" is already a hypertable, skipping
NOTICE: table "auditlog" is already a hypertable, skipping
NOTICE: table "trends" is already a hypertable, skipping
NOTICE: table "trends_uint" is already a hypertable, skipping
NOTICE: TimescaleDB extension is already installed; not changing configuration
NOTICE: TimescaleDB is configured successfully
DO


Overte zda skutecne se tabulka neprevedla na hypertable.

NOTICE: table "history_bin" is already a hypertable, skipping

SELECT * FROM chunks_detailed_si­ze('history_bin') ORDER BY chunk_name, node_name;

Pokud si zabbix server po restartu nestenuje tak je tabulka history_bin jiz prevedena na hypertable.

Zabbix běží, ale je to OK?

PostgreSQL version 15.7 (Debian 15.7-0+deb12u1)
TimescaleDB version 2.15.3


zabbix=> SELECT * FROM chunks_detailed_si­ze('history_bin') ORDER BY chunk_name, node_name;
chunk_schema | chunk_name | table_bytes | index_bytes | toast_bytes | total_bytes | node_name
--------------+------------+-------------+-------------+-------------+-------------+-----------
(0 rows)


Podle me ano, dotazem se ptate chunks v hypertable jen tam aktualne neni zadny zaznam. Nevim presne co se do history_bin zapisuje. Cekal bych datovy typ, ktery asi nepouzivate a proto tam nic neni. Ale konverze klasicke tabulky na hypertable v ramci Zabbix 7.0.2 je v poradku.