Zabbix 6.0 upgrade - PostgreSQL history primary keys when using partitions

[Arne Henrik Segtnan]

Hi everyone,


We have a Zabbix environment currently running version 5.0. Database in current version: PostgreSQL 12.0.

The Zabbix dev-env has been successfully upgraded to 6.0. Database in Zabbix 6.0: PostgreSQL 15.0.




Database has been set up with history and trend partitioning using Zabbix script 'partition_setup.sql'. We now would like to implement primary key for history tables in the upgraded version, as recommended by Zabbix. However, procedure for this task seems to have been created for non-partitioned systems only?


https://www.zabbix.com/documentation...b_primary_keys


'Important notes' states: If the database uses partitions, contact the DB administrator or Zabbix support team for help.

What would be the recommended procedure for this task, when using partitions?



From the Zabbix DB:

SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 40;

relname | relpages

----------------------------+----------

events events_1

events

events_pkey

event_recovery_2

housekeeper

event_recovery_pkey

event_recovery_1

items

sessions_pkey

event_recovery

sessions_1

housekeeper_pkey

trends_uint_p2023_02

history_uint_p2023_08_17

trends_uint_p2023_06

trends_uint_p2023_01

trends_uint_p2023_07

history_uint_p2023_08_18

history_uint_p2023_08_19

alerts

history_uint_p2023_08_20

history_uint_p2023_08_16

history_uint_p2023_08_17_1

history_uint_p2023_08_15

history_uint_p2023_08_19_1

history_uint_p2023_08_18_1

history_uint_p2023_08_20_1

history_uint_p2023_08_16_1

trends_uint_p2023_08

trends_uint_p2023_05

trends_uint_p2023_02_1

history_uint_p2023_08_10

history_uint_p2023_08_15_1

history_uint_p2023_08_08

trends_uint_p2023_01_1

history_uint_p2023_08_09

trends_uint_p2023_06_1

history_uint_p2023_08_12

history_uint_p2023_08_13

(40 rows)

[cyber]

partition_setup.sql does not seem to be part of Zabbix installation... Something created locally? I guess that's why you need a DBA...
For me partitioning PG instance is a new concept... I can see why it is done with mysql/mariadb (space usage and cleanup), but there is no such need for it in PG...

[Arne Henrik Segtnan]

Thanks for the reply. This is probably something locally made by the previous admin in the company, as you say - inherited from our Zabbix 4.x (and now 5.x) setup, starting on PG 9. In our internal documentation, it is claimed that PG partitioning had significant positive impact on history table cleanup. Could that have been the case in older PG-versions, and that this is no longer relevant?

[cyber]

Never used PG9. My old 4.4 was on 10.5+ timescale IIRC and I never had issues with cleanup... But it may aswell be the question of environment size... Currently running 14.5+timescale, same story.
You don't seem to be using timescale?

[Arne Henrik Segtnan]

Nope, we're not using timescale, probably something to consider to avoid the custom partitioning setup currently being used. Prod-env (PG 12) DB-size approx. 1.4TB.

[cyber]

1.4T is pretty much, where I was... after enabling timescale compression also, DB size dropped to ~500G... And I don't see any perf issues.

[Arne Henrik Segtnan]

Enabling timescale seems to be the right way to go, then - thanks a lot Just need to find out if it's possible to 'un-partition' the current PG database prior to timescale implementation, I suppose.