I have created the following WIKI page with the latest update for this script
Please refer to the wiki page rather than this thread.
Most partitioning solutions for Zabbix require a cron/scheduled job to create the partitions.
I have created this function to simplify the partitioning under Postgresql so that maintenance if kept to the minimum. The function will automatically create the new partition when it needs it.
A trigger is attached to each table that you want to partition. The trigger needs to have a parameter as either "day" or "month". The trigger will attempt to insert the record into a table called
table_p2012_10_08 (for daily partitions) or
table_p2012_10 (for monthly partitions)
If the table is not there, it will create it and attempt another insert.
To make it work, all you need to do is to add the following triggers to the "CLOCK" based tables you want to partition.
To disable the partitioning, simply remove the trigger and reimport the data in the main table
The function itself is
Please report any problem to me.
Please refer to the wiki page rather than this thread.
Most partitioning solutions for Zabbix require a cron/scheduled job to create the partitions.
I have created this function to simplify the partitioning under Postgresql so that maintenance if kept to the minimum. The function will automatically create the new partition when it needs it.
A trigger is attached to each table that you want to partition. The trigger needs to have a parameter as either "day" or "month". The trigger will attempt to insert the record into a table called
table_p2012_10_08 (for daily partitions) or
table_p2012_10 (for monthly partitions)
If the table is not there, it will create it and attempt another insert.
To make it work, all you need to do is to add the following triggers to the "CLOCK" based tables you want to partition.
Code:
CREATE TRIGGER partition_trg BEFORE INSERT ON history FOR EACH ROW EXECUTE PROCEDURE trg_partition('day');
CREATE TRIGGER partition_trg BEFORE INSERT ON history_sync FOR EACH ROW EXECUTE PROCEDURE trg_partition('day');
CREATE TRIGGER partition_trg BEFORE INSERT ON history_uint FOR EACH ROW EXECUTE PROCEDURE trg_partition('day');
CREATE TRIGGER partition_trg BEFORE INSERT ON history_str_sync FOR EACH ROW EXECUTE PROCEDURE trg_partition('day');
CREATE TRIGGER partition_trg BEFORE INSERT ON history_log FOR EACH ROW EXECUTE PROCEDURE trg_partition('day');
CREATE TRIGGER partition_trg BEFORE INSERT ON trends FOR EACH ROW EXECUTE PROCEDURE trg_partition('month');
CREATE TRIGGER partition_trg BEFORE INSERT ON trends_uint FOR EACH ROW EXECUTE PROCEDURE trg_partition('month');
-- Do not create a trigger for these tables. See my comment later in this thread
-- CREATE TRIGGER partition_trg BEFORE INSERT ON acknowledges FOR EACH ROW EXECUTE PROCEDURE trg_partition('month');
-- CREATE TRIGGER partition_trg BEFORE INSERT ON alerts FOR EACH ROW EXECUTE PROCEDURE trg_partition('month');
-- CREATE TRIGGER partition_trg BEFORE INSERT ON auditlog FOR EACH ROW EXECUTE PROCEDURE trg_partition('month');
-- CREATE TRIGGER partition_trg BEFORE INSERT ON events FOR EACH ROW EXECUTE PROCEDURE trg_partition('month');
-- CREATE TRIGGER partition_trg BEFORE INSERT ON service_alarms FOR EACH ROW EXECUTE PROCEDURE trg_partition('month');
The function itself is
Code:
-- Function: trg__partition() /* This function makes it easy to partition Zabbix tables on PostgresSQL 9.x (tested on 9.2 only) It will attempt an insert on the partitioned table, and if the table does not exist it will create a new one. Inspiration and code taken from: http://zabbixzone.com/zabbix/partitioning-tables/ http://stackoverflow.com/questions/7891320/self-managing-postgresql-partition-tables-plpgsql Alessandro De Maria <[email protected]> */ -- DROP FUNCTION trg__partition(); CREATE OR REPLACE FUNCTION trg_partition() RETURNS trigger AS $BODY$ DECLARE timeformat text; selector text; _interval interval; tablename text; startdate text; enddate text; create_table_part text; create_index_part text; BEGIN selector = TG_ARGV[0]; IF selector = 'day' THEN timeformat := 'YYYY_MM_DD'; ELSIF selector = 'month' THEN timeformat := 'YYYY_MM'; END IF; _interval := '1 ' || selector; tablename := TG_TABLE_NAME || '_p' || to_char(to_timestamp(NEW.clock), timeformat); EXECUTE 'INSERT INTO ' || quote_ident(tablename) || ' SELECT ($1).*' USING NEW; RETURN NULL; EXCEPTION WHEN undefined_table THEN startdate := extract(epoch FROM date_trunc(selector, to_timestamp(NEW.clock))); enddate := extract(epoch FROM date_trunc(selector, to_timestamp(NEW.clock) + _interval )); create_table_part:= 'CREATE TABLE IF NOT EXISTS '|| quote_ident(tablename) || ' (CHECK ((clock >= ' || quote_literal(startdate) || ' AND clock < ' || quote_literal(enddate) || '))) INHERITS ('|| TG_TABLE_NAME || ')'; create_index_part:= 'CREATE INDEX '|| quote_ident(tablename) || '_1 on ' || quote_ident(tablename) || '(itemid,clock)'; EXECUTE create_table_part; EXECUTE create_index_part; --insert it again EXECUTE 'INSERT INTO ' || quote_ident(tablename) || ' SELECT ($1).*' USING NEW; RETURN NULL; END; $BODY$ LANGUAGE plpgsql
Comment