This is an old revision of the document!
Table of Contents
Adding DB table partitioning to Zabbix DB
Note from the Author
First off, the servers that I'm using in my setup are OpenBSD. Therefore the CLI commands shown below will most likely be different then what you need to be able to run the same command on your system. For example, I'll be using 'doas' in place of 'sudo' and 'rcctl' in place of 'service' or 'systemctl'. Be sure to pay attention to this when issue these commands.
Summary
The current Zabbix monitoring server I'm working on is a development server that I have been working on to build for a client. The issue that I have run into is that the partitioning containing the PostgreSQL database is nearly full. And there is not enough space to properly VACUUM the larger tables to reclaim the DB space. When I originally installed the system, I did not choose to use partitioning within PostgreSQL DB tables. I am regretting that now.
What I'm going to attempt here is to retain the history collected thus far. I plan to DELETE some of the oldest data from the largest tables.
I will dump the entire zabbix database, drop the zabbix database, “re-install” the database via the Zabbix installer process, then re-import the old data from the previously made backup.
Getting things ready to perform the work
Shutdown the Zabbix server data collection processes
I happen to be working with two different servers. One is the Zabbix process server and the second is the PostgreSQL database server.
Issue the following command to shutdown your Zabbix server data collection processes.
doas rcctl stop zabbix_server
Delete older data [optional]
If you have a large amount of historical data that isn't relevant, you can delete some of it by issuing these SQL commands:
DELETE FROM history_uint WHERE to_timestamp(clock) < NOW() - INTERVAL '40 days'; DELETE FROM history_text WHERE to_timestamp(clock) < NOW() - INTERVAL '40 days'; DELETE FROM history_str WHERE to_timestamp(clock) < NOW() - INTERVAL '40 days'; DELETE FROM history WHERE to_timestamp(clock) < NOW() - INTERVAL '40 days';
Additional tables that get a little big that could have some:
DELETE FROM alerts WHERE to_timestamp(clock) < NOW() - INTERVAL '40 days'; DELETE FROM events WHERE to_timestamp(clock) < NOW() - INTERVAL '40 days'; DELETE FROM trends_uint WHERE to_timestamp(clock) < NOW() - INTERVAL '40 days'; DELETE FROM trends WHERE to_timestamp(clock) < NOW() - INTERVAL '40 days';
Dump the entire Zabbix DB
Use this to dump the entire Zabbix database + schema structure.
sudo su _postgresql pg_dump -U zabbix zabbix | gzip -1 > ./zabdb.zabbix.Pg.gz
Triggering the Zabbix install screen
Initial install & configuration instructions:
https://www.zabbix.com/documentation/4.0/manual/installation/install
Testing…
The history table is by far the largest
pg_dump -U zabbix -t history_uint zabbix | gzip -1 > ./zabdb.zabbix.history_uint.Pg.gz