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 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(
131 RAISE EXCEPTION 'Cannot have negative number of days';
139 SELECT CAST ('now'::date + i * '1day'::interval AS date) INTO cur_day;
140 SELECT CAST ('now'::date + (i + 1) * '1day'::interval AS date) INTO next_day;
145 EXECUTE 'CREATE TABLE "values$' || cur_day || '" (
146 CHECK (tstamp >= TIMESTAMP ''' || cur_day || ''' '
147 || 'AND tstamp < TIMESTAMP ''' || next_day || ''')
148 ) INHERITS (values)';
149 EXCEPTION WHEN duplicate_table THEN
153 RAISE INFO 'Created table "values$%"', cur_day;
156 EXECUTE 'ALTER TABLE ONLY "values$' || cur_day || '"
157 ADD CONSTRAINT "values_' || cur_day || '_pkey"
158 PRIMARY KEY (id, tstamp, name, value)';
159 EXECUTE 'ALTER TABLE ONLY "values$' || cur_day || '"
160 ADD CONSTRAINT "values_' || cur_day || '_id_fkey"
161 FOREIGN KEY (id) REFERENCES identifiers(id)';
167 -- create initial child tables
168 SELECT values_update_childs(2);
170 CREATE OR REPLACE FUNCTION values_insert_trigger()
175 child_tbl character varying;
177 SELECT 'values$' || CAST (NEW.tstamp AS DATE) INTO child_tbl;
178 -- Rather than using 'EXECUTE', some if-cascade checking the date may also
179 -- be used. However, this would require frequent updates of the trigger
180 -- function while this example works automatically.
181 EXECUTE 'INSERT INTO "' || child_tbl || '" VALUES ($1.*)' USING NEW;
186 CREATE TRIGGER insert_values_trigger
187 BEFORE INSERT ON values
188 FOR EACH ROW EXECUTE PROCEDURE values_insert_trigger();
190 -- when querying values make sure to enable constraint exclusion
191 -- SET constraint_exclusion = on;
193 CREATE OR REPLACE FUNCTION collectd_insert(
194 timestamp, character varying,
195 character varying, character varying,
196 character varying, character varying,
197 character varying[], character varying[], double precision[]
204 p_plugin alias for $3;
205 p_plugin_instance alias for $4;
207 p_type_instance alias for $6;
208 p_value_names alias for $7;
209 -- don't use the type info; for 'StoreRates true' it's 'gauge' anyway
210 -- p_type_names alias for $8;
211 p_values alias for $9;
218 AND plugin = p_plugin
219 AND COALESCE(plugin_inst, '') = COALESCE(p_plugin_instance, '')
221 AND COALESCE(type_inst, '') = COALESCE(p_type_instance, '');
223 INSERT INTO identifiers (host, plugin, plugin_inst, type, type_inst)
224 VALUES (p_host, p_plugin, p_plugin_instance, p_type, p_type_instance)
225 RETURNING id INTO ds_id;
229 EXIT WHEN i > array_upper(p_value_names, 1);
230 INSERT INTO values (id, tstamp, name, value)
231 VALUES (ds_id, p_time, p_value_names[i], p_values[i]);
237 -- vim: set expandtab :