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!