Zabbix : partitionnement des tables PostgreSQL

Boris HUISGEN January 13, 2014

administration base de données monitoring postgresql zabbix

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 !

See also

PostgreSQL : migration d’un cluster 9.1 vers 9.3
Read more
PostgreSQL : créer un utilisateur avec droits en lecture seule
Read more
PostgreSQL : afficher les requêtes SQL en cours d’exécution
Read more