How to "safely" truncate history tables in Zabbix 6 before enabling timescale.

The script was tested against Zabbix 6.0 and 6.2. I'll add a proper introduction soon.

If you don't fully understand every single line better don't run it in production.

--RECREATING HISTORY TABLES WITH DATA. INSTEAD OF "WHERE FALSE" YOU CAN USE "WHERE CLOCK > X" IF YOU WANT TO PRESERVE SOME DATA

ALTER TABLE history RENAME TO history_big;

CREATE TABLE history as SELECT * FROM history_big where false;

ALTER TABLE history_uint RENAME TO history_uint_big;

CREATE TABLE history_uint as SELECT * FROM history_uint_big where false;

ALTER TABLE history_str RENAME TO history_str_big;

CREATE TABLE history_str as SELECT * FROM history_str_big where false;

ALTER TABLE history_log RENAME TO history_log_big;

CREATE TABLE history_log as SELECT * FROM history_log_big where false;

ALTER TABLE history_text RENAME TO history_text_big;

CREATE TABLE history_text as SELECT * FROM history_text_big where false;

ALTER TABLE history_text RENAME TO history_text_big;

CREATE TABLE history_text as SELECT * FROM history_text_big where false;

ALTER TABLE history_text RENAME TO history_text_big;

CREATE TABLE history_text as SELECT * FROM history_text_big where false;

--ENABLING PRIMARY KEYS. AT THIS POINT YOUR PRIMARY KEYS STOPPED WORKING. TO ENABLE TIMESCALE YOU SHOULD ENABLE PRIMARY KEYS.

ALTER TABLE history RENAME TO history_old;

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)

);

ALTER TABLE history_uint RENAME TO history_uint_old;

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)

);

ALTER TABLE history_str RENAME TO history_str_old;

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)

);

ALTER TABLE history_log RENAME TO history_log_old;

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)

);

ALTER TABLE history_text RENAME TO history_text_old;

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)

);

--POPULATING TABLES WITH DATA. IF YOU RECREATED THE TABLES WITHOUT DATA, THIS WILL TAKE SECONDS

INSERT INTO history SELECT * FROM history_old ON CONFLICT (itemid,clock,ns) DO NOTHING;

       

INSERT INTO history_uint SELECT * FROM history_uint_old ON CONFLICT (itemid,clock,ns) DO NOTHING;

       

INSERT INTO history_str SELECT * FROM history_str_old ON CONFLICT (itemid,clock,ns) DO NOTHING;

       

INSERT INTO history_log SELECT * FROM history_log_old ON CONFLICT (itemid,clock,ns) DO NOTHING;

       

INSERT INTO history_text SELECT * FROM history_text_old ON CONFLICT (itemid,clock,ns) DO NOTHING;

--FIXING PERMISSIONS. IN CASE YOU RAN THE SCRIPT AS POSTGRES

ALTER TABLE history OWNER TO zabbix;

ALTER TABLE history_uint OWNER TO zabbix;

ALTER TABLE history_str OWNER TO zabbix;

ALTER TABLE history_log OWNER TO zabbix;

ALTER TABLE history_text OWNER TO zabbix;

At this point you can start zabbix to test if everything went well. If so, you can stop server and frontend again and continue with Timescale setup.

--CREATING EXTENSION / as root

echo "CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;" | sudo -u postgres psql zabbix

--ENABLING TIMESCALE / as root

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

--DROPPING OLD TEMP TABLES

drop table history_big;

drop table history_uint_big;

drop table history_str_big;

drop table history_log_big;

drop table history_text_big;

drop table history_old;

drop table history_uint_old;

drop table history_str_old;

drop table history_log_old;

drop table history_text_old;

Now you can start server and frontend with your fresh Timescale setup.