Zabbix : partitionnement des tables PostgreSQL

Boris HUISGEN
|
Petit résumé sur la configuration du partitionnement des tables Zabbix (version 2.2) avec une base de données PostgreSQL 9.3. Le partionnement des tables est automatique ; il peut être désactivé à tout moment. La suppression des tables est quant à elle à automatiser en cron.
Trigger de gestion du partitionnement journalier
-- Function: trg_daily_partition()
-- DROP FUNCTION trg_daily_partition();
CREATE OR REPLACE FUNCTION trg_daily_partition()
RETURNS trigger AS
$BODY$
DECLARE
tablename text;
partname text;
startdate text;
enddate text;
query text;
BEGIN
tablename := TG_ARGV[0];
partname := tablename || '_' || TO_CHAR(TO_TIMESTAMP(NEW.clock), 'YYYYMMDD');
EXECUTE 'INSERT INTO ' || 'partitions.' || quote_ident(partname) || ' SELECT ($1).*' USING NEW;
RETURN NULL;
EXCEPTION
WHEN undefined_table THEN
startdate := EXTRACT(EPOCH FROM date_trunc('day', TO_TIMESTAMP(NEW.clock)));
enddate := EXTRACT(EPOCH FROM date_trunc('day', TO_TIMESTAMP(NEW.clock) + ('1 day')::interval));
EXECUTE 'CREATE TABLE IF NOT EXISTS ' || 'partitions.' || quote_ident(partname) || ' (CHECK ((clock >= ' || quote_literal(startdate) || ' AND clock < ' || quote_literal(enddate) || '))) INHERITS ( ' || tablename || ' )';
EXECUTE 'CREATE INDEX ' || quote_ident(partname) || '_1 on ' || 'partitions.' || quote_ident(partname) || '(itemid,clock)';
EXECUTE 'INSERT INTO ' || 'partitions.' || quote_ident(partname) || ' SELECT($1).*' USING NEW;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION trg_daily_partition()
OWNER TO zabbix;
Trigger de gestion du partitionnement mensuel
-- Function: trg_monthly_partition()
-- DROP FUNCTION trg_monthly_partition();
CREATE OR REPLACE FUNCTION trg_monthly_partition()
RETURNS trigger AS
$BODY$
DECLARE
tablename text;
partname text;
startdate text;
enddate text;
query text;
BEGIN
tablename := TG_ARGV[0];
partname := tablename || '_' || TO_CHAR(TO_TIMESTAMP(NEW.clock), 'YYYYMM');
EXECUTE 'INSERT INTO ' || 'partitions.' || quote_ident(partname) || ' SELECT ($1).*' USING NEW;
RETURN NULL;
EXCEPTION
WHEN undefined_table THEN
startdate := EXTRACT(EPOCH FROM date_trunc('month', TO_TIMESTAMP(NEW.clock)));
enddate := EXTRACT(EPOCH FROM date_trunc('month', TO_TIMESTAMP(NEW.clock) + ('1 month')::interval));
EXECUTE 'CREATE TABLE IF NOT EXISTS ' || 'partitions.' || quote_ident(partname) || ' (CHECK ((clock >= ' || quote_literal(startdate) || ' AND clock < ' || quote_literal(enddate) || '))) INHERITS ( ' || tablename || ' )';
EXECUTE 'CREATE INDEX ' || quote_ident(partname) || '_1 on ' || 'partitions.' || quote_ident(partname) || '(itemid,clock)';
EXECUTE 'INSERT INTO ' || 'partitions.' || quote_ident(partname) || ' SELECT($1).*' USING NEW;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION trg_monthly_partition()
OWNER TO zabbix;
Création du schéma
CREATE SCHEMA partitions AUTHORIZATION zabbix
Ajout des triggers sur les tables parentes
CREATE TRIGGER partition_trg BEFORE INSERT ON history FOR EACH ROW EXECUTE PROCEDURE trg_insert_daily('history');
CREATE TRIGGER partition_trg BEFORE INSERT ON history_sync FOR EACH ROW EXECUTE PROCEDURE trg_daily_partition('history_sync');
CREATE TRIGGER partition_trg BEFORE INSERT ON history_uint FOR EACH ROW EXECUTE PROCEDURE trg_daily_partition('history_uint');
CREATE TRIGGER partition_trg BEFORE INSERT ON history_str_sync FOR EACH ROW EXECUTE PROCEDURE trg_daily_partition('history_str_sync');
CREATE TRIGGER partition_trg BEFORE INSERT ON history_log FOR EACH ROW EXECUTE PROCEDURE trg_daily_partition('history_log');
CREATE TRIGGER partition_trg BEFORE INSERT ON trends FOR EACH ROW EXECUTE PROCEDURE trg_monthly_partition('trends');
CREATE TRIGGER partition_trg BEFORE INSERT ON trends_uint FOR EACH ROW EXECUTE PROCEDURE trg_monthly_partition('trends_uint');
Fonctions de suppression des partitions
-- Function: delete_daily_partitions(integer)
-- DROP FUNCTION delete_daily_partitions(integer);
CREATE OR REPLACE FUNCTION delete_daily_partitions(max integer)
RETURNS text AS
$BODY$
DECLARE
result RECORD;
tmp text;
tmstp TIMESTAMP;
maxdate DATE;
BEGIN
maxdate := date_trunc('day', NOW() - (max || ' day')::interval);
FOR result IN SELECT * FROM pg_tables WHERE schemaname = 'partitions' LOOP
tmp := SUBSTRING(result.tablename FROM '[0-9_]*$');
IF LENGTH(tmp) <> 9 THEN
CONTINUE;
END IF;
tmstp := TO_TIMESTAMP(tmp, '_YYYYMMDD');
IF tmstp <= maxdate THEN
RAISE NOTICE 'Deleting daily table %', result.tablename;
EXECUTE 'DROP TABLE ' || result.schemaname || '.' || quote_ident(result.tablename);
END IF;
END LOOP;
RETURN 1;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION delete_daily_partitions(integer)
OWNER TO zabbix;
-- Function: delete_monthly_partitions(integer)
-- DROP FUNCTION delete_monthly_partitions(integer);
CREATE OR REPLACE FUNCTION delete_monthly_partitions(max integer)
RETURNS text AS
$BODY$
DECLARE
prefix text;
result RECORD;
tmp text;
tmstp TIMESTAMP;
maxdate DATE;
BEGIN
maxdate := date_trunc('day', NOW() - (max || ' month')::interval);
FOR result IN SELECT * FROM pg_tables WHERE schemaname = 'partitions' LOOP
tmp := SUBSTRING(result.tablename FROM '[0-9_]*$');
IF LENGTH(tmp) <> 7 THEN
CONTINUE;
END IF;
tmstp := TO_TIMESTAMP(tmp, '_YYYYMM');
IF tmstp <= maxdate THEN
RAISE NOTICE 'Deleting monthly table %', result.tablename;
EXECUTE 'DROP TABLE ' || result.schemaname || '.' || quote_ident(result.tablename);
END IF;
END LOOP;
RETURN 'OK';
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION delete_monthly_partitions(integer)
OWNER TO zabbix;
Exemple d’utilisation :
SELECT delete_daily_partitions(31);
SELECT delete_monthly_partitions(12);
N’oubliez pas de désactiver le housekeeper sur les items/trends !