| 1 |
-- collectd - contrib/postgresql/collectd_insert.sql |
| 2 |
-- Copyright (C) 2012 Sebastian 'tokkee' Harl |
| 3 |
-- All rights reserved. |
| 4 |
-- |
| 5 |
-- Redistribution and use in source and binary forms, with or without |
| 6 |
-- modification, are permitted provided that the following conditions |
| 7 |
-- are met: |
| 8 |
-- |
| 9 |
-- - Redistributions of source code must retain the above copyright |
| 10 |
-- notice, this list of conditions and the following disclaimer. |
| 11 |
-- |
| 12 |
-- - Redistributions in binary form must reproduce the above copyright |
| 13 |
-- notice, this list of conditions and the following disclaimer in the |
| 14 |
-- documentation and/or other materials provided with the distribution. |
| 15 |
-- |
| 16 |
-- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" |
| 17 |
-- AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE |
| 18 |
-- IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE |
| 19 |
-- ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE |
| 20 |
-- LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR |
| 21 |
-- CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF |
| 22 |
-- SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS |
| 23 |
-- INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN |
| 24 |
-- CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) |
| 25 |
-- ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE |
| 26 |
-- POSSIBILITY OF SUCH DAMAGE. |
| 27 |
|
| 28 |
-- Description: |
| 29 |
-- ------------ |
| 30 |
-- |
| 31 |
-- This is a sample database setup that may be used to write data collected by |
| 32 |
-- collectd to a PostgreSQL database. We're using two tables, 'identifiers' |
| 33 |
-- and 'values' to store the value-list identifier and the actual values |
| 34 |
-- respectively. |
| 35 |
-- |
| 36 |
-- The 'values' table is partitioned to improve performance and maintenance. |
| 37 |
-- Please note that additional maintenance scripts are required in order to |
| 38 |
-- keep the setup running -- see the comments below for details. |
| 39 |
-- |
| 40 |
-- The function 'collectd_insert' may be used to actually insert values |
| 41 |
-- submitted by collectd into those tables. |
| 42 |
-- |
| 43 |
-- Sample configuration: |
| 44 |
-- --------------------- |
| 45 |
-- |
| 46 |
-- <Plugin postgresql> |
| 47 |
-- <Writer sqlstore> |
| 48 |
-- Statement "SELECT collectd_insert($1, $2, $3, $4, $5, $6, $7, $8, $9);" |
| 49 |
-- </Writer> |
| 50 |
-- <Database foo> |
| 51 |
-- # ... |
| 52 |
-- Writer sqlstore |
| 53 |
-- </Database> |
| 54 |
-- </Plugin> |
| 55 |
|
| 56 |
CREATE TABLE identifiers ( |
| 57 |
id integer NOT NULL, |
| 58 |
host character varying(64) NOT NULL, |
| 59 |
plugin character varying(64) NOT NULL, |
| 60 |
plugin_inst character varying(64) DEFAULT NULL::character varying, |
| 61 |
type character varying(64) NOT NULL, |
| 62 |
type_inst character varying(64) DEFAULT NULL::character varying |
| 63 |
); |
| 64 |
CREATE SEQUENCE identifiers_id_seq |
| 65 |
START WITH 1 |
| 66 |
INCREMENT BY 1 |
| 67 |
NO MINVALUE |
| 68 |
NO MAXVALUE |
| 69 |
CACHE 1; |
| 70 |
ALTER SEQUENCE identifiers_id_seq OWNED BY identifiers.id; |
| 71 |
ALTER TABLE ONLY identifiers |
| 72 |
ALTER COLUMN id SET DEFAULT nextval('identifiers_id_seq'::regclass); |
| 73 |
ALTER TABLE ONLY identifiers |
| 74 |
ADD CONSTRAINT identifiers_host_plugin_plugin_inst_type_type_inst_key |
| 75 |
UNIQUE (host, plugin, plugin_inst, type, type_inst); |
| 76 |
ALTER TABLE ONLY identifiers |
| 77 |
ADD CONSTRAINT identifiers_pkey PRIMARY KEY (id); |
| 78 |
|
| 79 |
-- optionally, create indexes for the identifier fields |
| 80 |
CREATE INDEX identifiers_host ON identifiers USING btree (host); |
| 81 |
CREATE INDEX identifiers_plugin ON identifiers USING btree (plugin); |
| 82 |
CREATE INDEX identifiers_plugin_inst ON identifiers USING btree (plugin_inst); |
| 83 |
CREATE INDEX identifiers_type ON identifiers USING btree (type); |
| 84 |
CREATE INDEX identifiers_type_inst ON identifiers USING btree (type_inst); |
| 85 |
|
| 86 |
CREATE TABLE "values" ( |
| 87 |
id integer NOT NULL, |
| 88 |
tstamp timestamp with time zone NOT NULL, |
| 89 |
name character varying(64) NOT NULL, |
| 90 |
value double precision NOT NULL |
| 91 |
); |
| 92 |
|
| 93 |
CREATE OR REPLACE VIEW collectd |
| 94 |
AS SELECT host, plugin, plugin_inst, type, type_inst, |
| 95 |
host |
| 96 |
|| '/' || plugin |
| 97 |
|| CASE |
| 98 |
WHEN plugin_inst IS NOT NULL THEN '-' |
| 99 |
ELSE '' |
| 100 |
END |
| 101 |
|| coalesce(plugin_inst, '') |
| 102 |
|| '/' || type |
| 103 |
|| CASE |
| 104 |
WHEN type_inst IS NOT NULL THEN '-' |
| 105 |
ELSE '' |
| 106 |
END |
| 107 |
|| coalesce(type_inst, '') AS identifier, |
| 108 |
tstamp, name, value |
| 109 |
FROM identifiers |
| 110 |
JOIN values |
| 111 |
ON values.id = identifiers.id; |
| 112 |
|
| 113 |
-- partition "values" by day (or week, month, ...) |
| 114 |
|
| 115 |
-- create the child tables for today and the next 'days' days: |
| 116 |
-- this may, for example, be used in a daily cron-job (or similar) to create |
| 117 |
-- the tables for the next couple of days |
| 118 |
CREATE OR REPLACE FUNCTION values_update_childs( |
| 119 |
integer |
| 120 |
) RETURNS SETOF text |
| 121 |
LANGUAGE plpgsql |
| 122 |
AS $_$ |
| 123 |
DECLARE |
| 124 |
days alias for $1; |
| 125 |
cur_day date; |
| 126 |
next_day date; |
| 127 |
i integer; |
| 128 |
BEGIN |
| 129 |
IF days < 1 THEN |
| 130 |
RAISE EXCEPTION 'Cannot have negative number of days'; |
| 131 |
END IF; |
| 132 |
|
| 133 |
i := 0; |
| 134 |
LOOP |
| 135 |
EXIT WHEN i > days; |
| 136 |
|
| 137 |
SELECT CAST ('now'::date + i * '1day'::interval AS date) INTO cur_day; |
| 138 |
SELECT CAST ('now'::date + (i + 1) * '1day'::interval AS date) INTO next_day; |
| 139 |
|
| 140 |
i := i + 1; |
| 141 |
|
| 142 |
BEGIN |
| 143 |
EXECUTE 'CREATE TABLE "values$' || cur_day || '" ( |
| 144 |
CHECK (tstamp >= TIMESTAMP ''' || cur_day || ''' ' |
| 145 |
|| 'AND tstamp < TIMESTAMP ''' || next_day || ''') |
| 146 |
) INHERITS (values)'; |
| 147 |
EXCEPTION WHEN duplicate_table THEN |
| 148 |
CONTINUE; |
| 149 |
END; |
| 150 |
|
| 151 |
RETURN NEXT 'values$' || cur_day::text; |
| 152 |
|
| 153 |
EXECUTE 'ALTER TABLE ONLY "values$' || cur_day || '" |
| 154 |
ADD CONSTRAINT "values_' || cur_day || '_pkey" |
| 155 |
PRIMARY KEY (id, tstamp, name, value)'; |
| 156 |
EXECUTE 'ALTER TABLE ONLY "values$' || cur_day || '" |
| 157 |
ADD CONSTRAINT "values_' || cur_day || '_id_fkey" |
| 158 |
FOREIGN KEY (id) REFERENCES identifiers(id)'; |
| 159 |
END LOOP; |
| 160 |
RETURN; |
| 161 |
END; |
| 162 |
$_$; |
| 163 |
|
| 164 |
-- create initial child tables |
| 165 |
SELECT values_update_childs(2); |
| 166 |
|
| 167 |
CREATE OR REPLACE FUNCTION values_insert_trigger() |
| 168 |
RETURNS trigger |
| 169 |
LANGUAGE plpgsql |
| 170 |
AS $_$ |
| 171 |
DECLARE |
| 172 |
child_tbl character varying; |
| 173 |
BEGIN |
| 174 |
SELECT 'values$' || CAST (NEW.tstamp AS DATE) INTO child_tbl; |
| 175 |
-- Rather than using 'EXECUTE', some if-cascade checking the date may also |
| 176 |
-- be used. However, this would require frequent updates of the trigger |
| 177 |
-- function while this example works automatically. |
| 178 |
EXECUTE 'INSERT INTO "' || child_tbl || '" VALUES ($1.*)' USING NEW; |
| 179 |
RETURN NULL; |
| 180 |
END; |
| 181 |
$_$; |
| 182 |
|
| 183 |
CREATE TRIGGER insert_values_trigger |
| 184 |
BEFORE INSERT ON values |
| 185 |
FOR EACH ROW EXECUTE PROCEDURE values_insert_trigger(); |
| 186 |
|
| 187 |
-- when querying values make sure to enable constraint exclusion |
| 188 |
-- SET constraint_exclusion = on; |
| 189 |
|
| 190 |
CREATE OR REPLACE FUNCTION collectd_insert( |
| 191 |
timestamp with time zone, character varying, |
| 192 |
character varying, character varying, |
| 193 |
character varying, character varying, |
| 194 |
character varying[], character varying[], double precision[] |
| 195 |
) RETURNS void |
| 196 |
LANGUAGE plpgsql |
| 197 |
AS $_$ |
| 198 |
DECLARE |
| 199 |
p_time alias for $1; |
| 200 |
p_host alias for $2; |
| 201 |
p_plugin alias for $3; |
| 202 |
p_plugin_instance alias for $4; |
| 203 |
p_type alias for $5; |
| 204 |
p_type_instance alias for $6; |
| 205 |
p_value_names alias for $7; |
| 206 |
-- don't use the type info; for 'StoreRates true' it's 'gauge' anyway |
| 207 |
-- p_type_names alias for $8; |
| 208 |
p_values alias for $9; |
| 209 |
ds_id integer; |
| 210 |
i integer; |
| 211 |
BEGIN |
| 212 |
SELECT id INTO ds_id |
| 213 |
FROM identifiers |
| 214 |
WHERE host = p_host |
| 215 |
AND plugin = p_plugin |
| 216 |
AND COALESCE(plugin_inst, '') = COALESCE(p_plugin_instance, '') |
| 217 |
AND type = p_type |
| 218 |
AND COALESCE(type_inst, '') = COALESCE(p_type_instance, ''); |
| 219 |
IF NOT FOUND THEN |
| 220 |
INSERT INTO identifiers (host, plugin, plugin_inst, type, type_inst) |
| 221 |
VALUES (p_host, p_plugin, p_plugin_instance, p_type, p_type_instance) |
| 222 |
RETURNING id INTO ds_id; |
| 223 |
END IF; |
| 224 |
i := 1; |
| 225 |
LOOP |
| 226 |
EXIT WHEN i > array_upper(p_value_names, 1); |
| 227 |
INSERT INTO values (id, tstamp, name, value) |
| 228 |
VALUES (ds_id, p_time, p_value_names[i], p_values[i]); |
| 229 |
i := i + 1; |
| 230 |
END LOOP; |
| 231 |
END; |
| 232 |
$_$; |
| 233 |
|
| 234 |
-- vim: set expandtab : |