1 -- collectd - contrib/postgresql/collectd_insert.sql
2 -- Copyright (C) 2012 Sebastian 'tokkee' Harl
3 -- All rights reserved.
5 -- Redistribution and use in source and binary forms, with or without
6 -- modification, are permitted provided that the following conditions
9 -- - Redistributions of source code must retain the above copyright
10 -- notice, this list of conditions and the following disclaimer.
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.
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.
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
36 -- The 'values' table is partitioned to improve performance and maintainance.
37 -- Please note that additional maintainance scripts are required in order to
38 -- keep the setup running -- see the comments below for details.
40 -- The function 'collectd_insert' may be used to actually insert values
41 -- submitted by collectd into those tables.
43 -- Sample configuration:
44 -- ---------------------
46 -- <Plugin postgresql>
48 -- Statement "SELECT collectd_insert($1, $2, $3, $4, $5, $6, $7, $8, $9);"
56 CREATE TABLE identifiers (
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
64 CREATE SEQUENCE identifiers_id_seq
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);
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);
86 CREATE TABLE "values" (
88 tstamp timestamp without time zone NOT NULL,
89 name character varying(64) NOT NULL,
90 value double precision NOT NULL
93 -- partition "values" by day (or week, month, ...)
94 -- make sure the CHECKs don't overlap!
95 CREATE TABLE "values$2012-08-19" (
96 CHECK (tstamp >= TIMESTAMP '2012-08-19' AND tstamp < TIMESTAMP '2012-08-20')
98 CREATE TABLE "values$2012-08-20" (
99 CHECK (tstamp >= TIMESTAMP '2012-08-20' AND tstamp < TIMESTAMP '2012-08-21')
102 -- set up a daily (weekly, monthly, ...) cron-job (or similar) to create the
103 -- tables for the next day (week, month, ...)
105 ALTER TABLE ONLY "values$2012-08-19"
106 ADD CONSTRAINT "values_2012-08-19_pkey" PRIMARY KEY (id, tstamp, name, value);
107 ALTER TABLE ONLY "values$2012-08-19"
108 ADD CONSTRAINT "values_2012-08-19_id_fkey" FOREIGN KEY (id) REFERENCES identifiers(id);
110 ALTER TABLE ONLY "values$2012-08-20"
111 ADD CONSTRAINT "values_2012-08-20_pkey" PRIMARY KEY (id, tstamp, name, value);
112 ALTER TABLE ONLY "values$2012-08-20"
113 ADD CONSTRAINT "values_2012-08-20_id_fkey" FOREIGN KEY (id) REFERENCES identifiers(id);
116 CREATE OR REPLACE FUNCTION values_insert_trigger()
121 child_tbl character varying;
123 SELECT 'values$' || CAST (NEW.tstamp AS DATE) INTO child_tbl;
124 -- Rather than using 'EXECUTE', some if-cascade checking the date may also
125 -- be used. However, this would require frequent updates of the trigger
126 -- function while this example works automatically.
127 EXECUTE 'INSERT INTO "' || child_tbl || '" VALUES ($1.*)' USING NEW;
132 CREATE TRIGGER insert_values_trigger
133 BEFORE INSERT ON values
134 FOR EACH ROW EXECUTE PROCEDURE values_insert_trigger();
136 -- when querying values make sure to enable constraint exclusion
137 -- SET constraint_exclusion = on;
139 CREATE OR REPLACE FUNCTION collectd_insert(
140 timestamp, character varying,
141 character varying, character varying,
142 character varying, character varying,
143 character varying[], character varying[], double precision[]
150 p_plugin alias for $3;
151 p_plugin_instance alias for $4;
153 p_type_instance alias for $6;
154 p_value_names alias for $7;
155 -- don't use the type info; for 'StoreRates true' it's 'gauge' anyway
156 -- p_type_names alias for $8;
157 p_values alias for $9;
164 AND plugin = p_plugin
165 AND COALESCE(plugin_inst, '') = COALESCE(p_plugin_instance, '')
167 AND COALESCE(type_inst, '') = COALESCE(p_type_instance, '');
169 INSERT INTO identifiers (host, plugin, plugin_inst, type, type_inst)
170 VALUES (p_host, p_plugin, p_plugin_instance, p_type, p_type_instance)
171 RETURNING id INTO ds_id;
175 EXIT WHEN i > array_upper(p_value_names, 1);
176 INSERT INTO values (id, tstamp, name, value)
177 VALUES (ds_id, p_time, p_value_names[i], p_values[i]);