Installing Zabbix 6.x and partitioning PostgreSQL 14.x

[elekgeek]

Hi @Doctorbal,

I read that Zabbix has changed few things concerning database:

So, when following your instructions for partitioning PostgreSQL 14.x, I think "Create Empty history* and trends* Tables" has to be modified, unfortunately I am not even good with DB stuff.

Thank you for your time.

Omar

[Doctorbal]

@elekgeek ,

I have not had the time to do this and will most likely not get to it for a while.

I still have an outstanding issue that addresses upgrading Zabbix to 5.x and using PostgreSQL v12 that I haven't got to!

I would warmly get some feedback and see others commit changes to this repository though! So if you are going down this route right now I would be very grateful for your insight and feedback on how what needs changing in PostgreSQL v14 with Zabbix 6.x. This would save myself a lot of time in the future!

Thanks & Best Regards,
Andreas

[elekgeek]

I will try to install it myself and see how it goes :- )

[ents-hqx]

This worked for me. zabbix 6.2 and PostgreSQL 14

-- history
CREATE TABLE public.history
(
itemid bigint NOT NULL,
clock integer DEFAULT 0 NOT NULL,
value double precision DEFAULT '0'::double precision NOT NULL,
ns integer DEFAULT 0 NOT NULL,
CONSTRAINT history_pkey PRIMARY KEY (itemid, clock, ns)
) PARTITION BY RANGE (clock);

-- history_log
CREATE TABLE public.history_log
(
itemid bigint NOT NULL,
clock integer DEFAULT 0 NOT NULL,
"timestamp" integer DEFAULT 0 NOT NULL,
source character varying(64) DEFAULT ''::character varying NOT NULL,
severity integer DEFAULT 0 NOT NULL,
value text DEFAULT ''::text NOT NULL,
logeventid integer DEFAULT 0 NOT NULL,
ns integer DEFAULT 0 NOT NULL,
CONSTRAINT history_log_pkey PRIMARY KEY (itemid, clock, ns)
) PARTITION BY RANGE (clock);

-- history_str
CREATE TABLE public.history_str
(
itemid bigint NOT NULL,
clock integer DEFAULT 0 NOT NULL,
value character varying(255) DEFAULT ''::character varying NOT NULL,
ns integer DEFAULT 0 NOT NULL,
CONSTRAINT history_str_pkey PRIMARY KEY (itemid, clock, ns)
) PARTITION BY RANGE (clock);

-- history_text
CREATE TABLE public.history_text
(
itemid bigint NOT NULL,
clock integer DEFAULT 0 NOT NULL,
value text DEFAULT ''::text NOT NULL,
ns integer DEFAULT 0 NOT NULL,
CONSTRAINT history_text_pkey PRIMARY KEY (itemid, clock, ns)
) PARTITION BY RANGE (clock);

-- history_uint
CREATE TABLE public.history_uint
(
itemid bigint NOT NULL,
clock integer DEFAULT 0 NOT NULL,
value numeric(20,0) DEFAULT '0'::numeric NOT NULL,
ns integer DEFAULT 0 NOT NULL,
CONSTRAINT history_uint_pkey PRIMARY KEY (itemid, clock, ns)
) PARTITION BY RANGE (clock);

-- trends
CREATE TABLE public.trends
(
itemid bigint NOT NULL,
clock integer NOT NULL DEFAULT 0,
num integer NOT NULL DEFAULT 0,
value_min double precision DEFAULT '0'::double precision NOT NULL,
value_avg double precision DEFAULT '0'::double precision NOT NULL,
value_max double precision DEFAULT '0'::double precision NOT NULL,
CONSTRAINT trends_pkey PRIMARY KEY (itemid, clock)
) PARTITION BY RANGE (clock);

-- trends_uint
CREATE TABLE public.trends_uint
(
itemid bigint NOT NULL,
clock integer NOT NULL DEFAULT 0,
num integer NOT NULL DEFAULT 0,
value_min numeric(20,0) DEFAULT (0)::numeric NOT NULL,
value_avg numeric(20,0) DEFAULT (0)::numeric NOT NULL,
value_max numeric(20,0) DEFAULT (0)::numeric NOT NULL,
CONSTRAINT trends_uint_pkey PRIMARY KEY (itemid, clock)
) PARTITION BY RANGE (clock);

[Doctorbal]

Fantastic @ents-hqx ! Thanks for sharing!