zabbix-pgsql-partitioning

zabbix-pgsql-partitioning copied to clipboard

It'd be great to have README improved with what are common operational tasks. I'm willing to create a PR if someone can confirm my thoughts.

Partitions needs to be present before data INSERT: To deal with that, run provision command regularly. SELECT zbx_provision_partitions('history', 'day', 14); should be run at least each 13 days. Each 7 days seems more sensible, but why not to run it daily.

Drop partitions older than 14 days: To substitute zabbix built-in housekeeper (reason why most people partition) run drop partitions regularly. SELECT zbx_drop_old_partitions('history', SELECT (NOW() - INTERVAL '2 weeks')::DATE::TIMESTAMP); It's also ok to run daily.

Set recurring tasks in cron Above SQL commands can be set to run periodically e.g. using cron with record like 0 3 * * * echo '<SQL_command>' | PGOPTIONS='--client-min-messages=warning' PGPASSWORD=<zabbix_db_password> psql -U zabbix -d zabbix --quiet (run every night at 3 am, avoid NOTICE messages being reported as cron errors)

PR is very welcomed! Also, if you wanted to write up a blog post or something, I could link to it from the readme.