Adding DB table partitioning to Zabbix DB

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.

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.

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

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 database

pg_dump -U zabbix zabbix | gzip -1 > ./zabdb.zabbix.Pg.gz

pg_dump -U postgres postgres | psql -U zabbix zabbix

The following use of pg_dump and pg_restore performs a data-only backup using the custom-form output option and using INSERTs instead of COPY statements:

pg_dump -U zabbix -a --inserts -t history_str -Fc zabbix | gzip -1 > /var/postgresql/zabdb.zabbix.history_str.Pg.gz   

Once the backup is done, drop all data from the table:

psql -U zabbix -c 'truncate history_str;' zabbix

Now restore the data to the table:

gunzip -c zabdb.zabbix.history_str.Pg.gz | pg_restore -U zabbix -d zabbix