Force zabbix housekeeping with batched DELETE. Sample use: PGPASSWORD=password ./zabbix_housekeeping.sh history history_str history_text history_log history_uint trends trends_uint"

zabbix_housekeeping.sh

#!/bin/sh
psql="psql -h localhost -U zabbix zabbix"
interval="90 days"
limit=1000000

housekeeping()
{
	t="$1"

	echo "--> $t"
	while :; do
		t1=`date +%s`
		result=`cat << EOF | $psql 2>&1
--
-- Generic statement
--
-- WITH rows AS
-- (SELECT itemid, clock FROM $t WHERE age(to_timestamp(clock)) > INTERVAL '$interval' LIMIT $limit)
-- DELETE FROM $t h WHERE EXISTS (SELECT 1 FROM rows WHERE rows.itemid = h.itemid AND rows.clock = h.clock)

--
-- PostgreSQL-optimized query that does not use an index to do DELETE
-- FOR UPDATE SKIP LOCKED is mandatory so that ctid does not change while in transaction
--
WITH rows AS
(SELECT ctid FROM $t WHERE age(to_timestamp(clock)) > INTERVAL '$interval' LIMIT $limit FOR UPDATE SKIP LOCKED)
DELETE FROM $t WHERE ctid IN (TABLE rows)
EOF`
		t2=`date +%s`
		case "$result" in
		DELETE*)
			num=${result#DELETE }
			echo "Deleted $num records in $(($t2 - $t1)) sec"
                        if [ "$num" -lt "$limit" ]; then
                                break
                        fi
			;;
		*)
			echo $result
			break
			;;
		esac
	done
}

for t in $*; do
	housekeeping $t
done