Question about PostgreSQL partitioning

[fdavidcn]

Hi guys,

I am a newbie with zabbix and i was recently partitioned my PostgreSQL database following this manual in zabbix wiki: https://www.zabbix.org/wiki/Docs/how...topartitioning

I disabled "Enable internal maintenaince" under "Housekeeper" options for History and Trends also.

I think that all is working good because i can see under "Schemas" a new schema called "partitions" and inside tables, 2 new tables was appeared "history_p2018_06_28" and "history_uint_p2018_06_28". This 2 tables has now about 50Mb and growing and i think that partitioning was made correctly and is working. Please correct me if i am wrong way.

My questions are:
1. Can i remove all "old data" from old schema "public" and recover now this space (about 120 Gb)?? If its possible, how can i do it manually?
2. Zabbix is still "autoremoving" old data automatically after partitioning database or i need to do some config?

Zabbix version is 3.4
PostgreSql version is 9.5


Thanks in advance!!

[kernbug]

Hi guys,


My questions are:
1. Can i remove all "old data" from old schema "public" and recover now this space (about 120 Gb)?? If its possible, how can i do it manually?
2. Zabbix is still "autoremoving" old data automatically after partitioning database or i need to do some config?

Hi,

1. Short answer is no. Why: https://www.postgresql.org/docs/10/s...titioning.html. Unlike the MySQL, partitions can be performed without a break in the service, but this means that only new data will be partitioned. You must manually transfer the information to the partition, and only then delete the unnecessary partitions.
2. When using partitioning, you must disable the cleanup of the specific tables (partitioned) with the housekeeper. Also, you must take care of the partitions rotation with DBMS.

[fdavidcn]

Hi,

1. Short answer is no. Why: https://www.postgresql.org/docs/10/s...titioning.html. Unlike the MySQL, partitions can be performed without a break in the service, but this means that only new data will be partitioned. You must manually transfer the information to the partition, and only then delete the unnecessary partitions.
2. When using partitioning, you must disable the cleanup of the specific tables (partitioned) with the housekeeper. Also, you must take care of the partitions rotation with DBMS.

Hi friend Kern!

This is my situation... I was installed zabbix two weeks ago and i started to monitor about 350 hosts with "no partitions" and now DB size is too big for the server (i dont have so much space on this server) and housekeeper takes several hours to clean database (+12h)..

Now i have database partitioned and i can decide when remove old data and i want to clean all old data from "public table" to "start from scratch" but keeping hosts, items, graphs,...

Is there any way to acomplish this without deleting database or i need to export all configs, hosts, templates... and then reinstall database again?

Thanks!!

[leandros]

Hi Guys,

I followed the partitioning process as is described by the following link : https://github.com/robbrucks/zabbix-...o-partitioning
which is mentioned that adapts the above auto partitioning method with zabbix and Postgresql : https://www.zabbix.org/wiki/Docs/how...topartitioning

As a result I ran the install_partitioning.sh as postgres superuser on my zabbix database which executes the next functions :

1. create_partition_schema : succeed

2. create_trigger_function : succeed

3. create_cleanup_function : succeed

4. add_partition_triggers : hanging !!!

As I can see from the log everything are created expect for the triggers. The process is hanging while trying to create the first trigger for the history table as reported below.

CREATE FUNCTION
==================================================
Partition Cleanup Function Successfully Created.
==================================================
add_partition_triggers -----------------------------------------
2022-04-05 16:58:39 EEST
SET ROLE zabbix;
SET
CREATE TRIGGER zbx_partition_trg BEFORE INSERT ON history FOR EACH ROW EXECUTE PROCEDURE zbx_part_trigger_func('day');

The create trigger process is running for a long time and as i notice is in waiting status as depicted watching the htop.

postgres 16806 0.0 0.0 3542436 5872 ? Ss 16:58 0:00 postgres: postgres zabbix [local] CREATE TRIGGER waiting

Its worth to mention also that my database is very large ~ 6.0T size , i do not know if this matters and the history table 1.3T size.

public | history | table | zabbix | 1314 GB |
public | history_log | table | zabbix | 80 GB

/dev/mapper/db--01-lv_db 6.0T 5.2T 806G 87% /database

I appreciate your assistance in advance