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 with time zone NOT NULL,
89 name character varying(64) NOT NULL,
90 value double precision NOT NULL
93 CREATE OR REPLACE VIEW collectd
94 AS SELECT host, plugin, plugin_inst, type, type_inst,
98 WHEN plugin_inst IS NOT NULL THEN '-'
101 || coalesce(plugin_inst, '')
104 WHEN type_inst IS NOT NULL THEN '-'
107 || coalesce(plugin_inst, '') AS identifier,
111 ON values.id = identifiers.id;
113 -- partition "values" by day (or week, month, ...)
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(
130 RAISE EXCEPTION 'Cannot have negative number of days';
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;
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
151 RETURN NEXT 'values$' || cur_day::text;
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)';
164 -- create initial child tables
165 SELECT values_update_childs(2);
167 CREATE OR REPLACE FUNCTION values_insert_trigger()
172 child_tbl character varying;
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;
183 CREATE TRIGGER insert_values_trigger
184 BEFORE INSERT ON values
185 FOR EACH ROW EXECUTE PROCEDURE values_insert_trigger();
187 -- when querying values make sure to enable constraint exclusion
188 -- SET constraint_exclusion = on;
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[]
201 p_plugin alias for $3;
202 p_plugin_instance alias for $4;
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;
215 AND plugin = p_plugin
216 AND COALESCE(plugin_inst, '') = COALESCE(p_plugin_instance, '')
218 AND COALESCE(type_inst, '') = COALESCE(p_type_instance, '');
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;
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]);
234 -- vim: set expandtab :