Removing old data - Postgresql

[ppaciek]

Hello,
How can I delete old data from the database? I set the housekeeper option in zabbix for 90 days for all logs (previously it was 365 days), then I did Vacuum on postgresql and cut only a few gb (I suspect that from indexes). I want to delete my old data and regain some disk space as the base currently weighs 600gb. I have an application server, database (postgres), and several zabbix proxies - all on separate virtual machines. I am surprised that after setting the retention to 90 days, I still see the data on the charts from the beginning (when I started zabbix) Any suggestions? I would be very grateful for your help.

[[email protected]]

Hi ppaciek,

If you check the zabbix server logs you will see how many records are being deleted per housekeeper run (i.e. grep 'housekeeper \[deleted' /var/log/zabbix/zabbix_server.log) for example:

6548:20210526:040635.152 housekeeper [deleted 0 hist/trends, 0 items/triggers, 12848 events, 1360 problems, 0 sessions, 0 alarms, 0 audit, 0 records in 11.111402 sec, idle for 1 hour(s)]
26548:20210526:050643.928 housekeeper [deleted 0 hist/trends, 0 items/triggers, 12752 events, 1367 problems, 0 sessions, 0 alarms, 0 audit, 0 records in 7.440068 sec, idle for 1 hour(s)]
26548:20210526:060654.160 housekeeper [deleted 0 hist/trends, 0 items/triggers, 12777 events, 1362 problems, 0 sessions, 0 alarms, 0 audit, 0 records in 9.343487 sec, idle for 1 hour(s)]

In our case we use database partitioning so the history syncer is disabled for history and trends data.

From what I understand when you change the retention by a large amount the housekeeper will take time to catch up based on the Zabbix server settings (HousekeepingFrequency/MaxHousekeeperDelete).

One thing you can do is force housekeeper runs manually to speed up the process rather than waiting for once per hour: 'zabbix_server -c /usr/local/etc/zabbix_server.conf -R housekeeper_execute'

Cheers
James

[Jennifer64]

PostgreSQL does not currently have a built-in cron-like functionality, so I'd use the system's cron to run a suitable delete statement. If you already have a btree index on the timestamp column, you might as well run the delete much more frequently than nightly, taking out small chunks at a time.


My Pasco Connect