From e0d76f0aa2258b01a85f32b7bb3e0f0f2342c537 Mon Sep 17 00:00:00 2001 From: Sebastian Harl Date: Sun, 19 Aug 2012 14:25:41 +0200 Subject: [PATCH] contrib/postgresql/collectd_insert.sql: Added sample writer setup. --- contrib/postgresql/collectd_insert.sql | 182 +++++++++++++++++++++++++ 1 file changed, 182 insertions(+) create mode 100644 contrib/postgresql/collectd_insert.sql diff --git a/contrib/postgresql/collectd_insert.sql b/contrib/postgresql/collectd_insert.sql new file mode 100644 index 00000000..67a106e4 --- /dev/null +++ b/contrib/postgresql/collectd_insert.sql @@ -0,0 +1,182 @@ +-- collectd - contrib/postgresql/collectd_insert.sql +-- Copyright (C) 2012 Sebastian 'tokkee' Harl +-- All rights reserved. +-- +-- Redistribution and use in source and binary forms, with or without +-- modification, are permitted provided that the following conditions +-- are met: +-- +-- - Redistributions of source code must retain the above copyright +-- notice, this list of conditions and the following disclaimer. +-- +-- - Redistributions in binary form must reproduce the above copyright +-- notice, this list of conditions and the following disclaimer in the +-- documentation and/or other materials provided with the distribution. +-- +-- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" +-- AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE +-- IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE +-- ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE +-- LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR +-- CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF +-- SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS +-- INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN +-- CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) +-- ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE +-- POSSIBILITY OF SUCH DAMAGE. + +-- Description: +-- ------------ +-- +-- This is a sample database setup that may be used to write data collected by +-- collectd to a PostgreSQL database. We're using two tables, 'identifiers' +-- and 'values' to store the value-list identifier and the actual values +-- respectively. +-- +-- The 'values' table is partitioned to improve performance and maintainance. +-- Please note that additional maintainance scripts are required in order to +-- keep the setup running -- see the comments below for details. +-- +-- The function 'collectd_insert' may be used to actually insert values +-- submitted by collectd into those tables. +-- +-- Sample configuration: +-- --------------------- +-- +-- +-- +-- Statement "SELECT collectd_insert($1, $2, $3, $4, $5, $6, $7, $8, $9);" +-- +-- +-- # ... +-- Writer sqlstore +-- +-- + +CREATE TABLE identifiers ( + id integer NOT NULL, + host character varying(64) NOT NULL, + plugin character varying(64) NOT NULL, + plugin_inst character varying(64) DEFAULT NULL::character varying, + type character varying(64) NOT NULL, + type_inst character varying(64) DEFAULT NULL::character varying +); +CREATE SEQUENCE identifiers_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; +ALTER SEQUENCE identifiers_id_seq OWNED BY identifiers.id; +ALTER TABLE ONLY identifiers + ALTER COLUMN id SET DEFAULT nextval('identifiers_id_seq'::regclass); +ALTER TABLE ONLY identifiers + ADD CONSTRAINT identifiers_host_plugin_plugin_inst_type_type_inst_key + UNIQUE (host, plugin, plugin_inst, type, type_inst); +ALTER TABLE ONLY identifiers + ADD CONSTRAINT identifiers_pkey PRIMARY KEY (id); + +-- optionally, create indexes for the identifier fields +CREATE INDEX identifiers_host ON identifiers USING btree (host); +CREATE INDEX identifiers_plugin ON identifiers USING btree (plugin); +CREATE INDEX identifiers_plugin_inst ON identifiers USING btree (plugin_inst); +CREATE INDEX identifiers_type ON identifiers USING btree (type); +CREATE INDEX identifiers_type_inst ON identifiers USING btree (type_inst); + +CREATE TABLE "values" ( + id integer NOT NULL, + tstamp timestamp without time zone NOT NULL, + name character varying(64) NOT NULL, + value double precision NOT NULL +); + +-- partition "values" by day (or week, month, ...) +-- make sure the CHECKs don't overlap! +CREATE TABLE "values$2012-08-19" ( + CHECK (tstamp >= TIMESTAMP '2012-08-19' AND tstamp < TIMESTAMP '2012-08-20') +) INHERITS (values); +CREATE TABLE "values$2012-08-20" ( + CHECK (tstamp >= TIMESTAMP '2012-08-20' AND tstamp < TIMESTAMP '2012-08-21') +) INHERITS (values); +-- ... +-- set up a daily (weekly, monthly, ...) cron-job (or similar) to create the +-- tables for the next day (week, month, ...) + +ALTER TABLE ONLY "values$2012-08-19" + ADD CONSTRAINT "values_2012-08-19_pkey" PRIMARY KEY (id, tstamp, name, value); +ALTER TABLE ONLY "values$2012-08-19" + ADD CONSTRAINT "values_2012-08-19_id_fkey" FOREIGN KEY (id) REFERENCES identifiers(id); + +ALTER TABLE ONLY "values$2012-08-20" + ADD CONSTRAINT "values_2012-08-20_pkey" PRIMARY KEY (id, tstamp, name, value); +ALTER TABLE ONLY "values$2012-08-20" + ADD CONSTRAINT "values_2012-08-20_id_fkey" FOREIGN KEY (id) REFERENCES identifiers(id); +-- ... + +CREATE OR REPLACE FUNCTION values_insert_trigger() + RETURNS trigger + LANGUAGE plpgsql + AS $_$ +DECLARE + child_tbl character varying; +BEGIN + SELECT 'values$' || CAST (NEW.tstamp AS DATE) INTO child_tbl; + -- Rather than using 'EXECUTE', some if-cascade checking the date may also + -- be used. However, this would require frequent updates of the trigger + -- function while this example works automatically. + EXECUTE 'INSERT INTO "' || child_tbl || '" VALUES ($1.*)' USING NEW; + RETURN NULL; +END; +$_$; + +CREATE TRIGGER insert_values_trigger + BEFORE INSERT ON values + FOR EACH ROW EXECUTE PROCEDURE values_insert_trigger(); + +-- when querying values make sure to enable constraint exclusion +-- SET constraint_exclusion = on; + +CREATE OR REPLACE FUNCTION collectd_insert( + timestamp, character varying, + character varying, character varying, + character varying, character varying, + character varying[], character varying[], double precision[] + ) RETURNS void + LANGUAGE plpgsql + AS $_$ +DECLARE + p_time alias for $1; + p_host alias for $2; + p_plugin alias for $3; + p_plugin_instance alias for $4; + p_type alias for $5; + p_type_instance alias for $6; + p_value_names alias for $7; + -- don't use the type info; for 'StoreRates true' it's 'gauge' anyway + -- p_type_names alias for $8; + p_values alias for $9; + ds_id integer; + i integer; +BEGIN + SELECT id INTO ds_id + FROM identifiers + WHERE host = p_host + AND plugin = p_plugin + AND COALESCE(plugin_inst, '') = COALESCE(p_plugin_instance, '') + AND type = p_type + AND COALESCE(type_inst, '') = COALESCE(p_type_instance, ''); + IF NOT FOUND THEN + INSERT INTO identifiers (host, plugin, plugin_inst, type, type_inst) + VALUES (p_host, p_plugin, p_plugin_instance, p_type, p_type_instance) + RETURNING id INTO ds_id; + END IF; + i := 1; + LOOP + EXIT WHEN i > array_upper(p_value_names, 1); + INSERT INTO values (id, tstamp, name, value) + VALUES (ds_id, p_time, p_value_names[i], p_values[i]); + i := i + 1; + END LOOP; +END; +$_$; + -- 2.30.2