1 /**
2 * collectd - src/postgresql.c
3 * Copyright (C) 2008 Sebastian Harl
4 *
5 * This program is free software; you can redistribute it and/or modify it
6 * under the terms of the GNU General Public License as published by the
7 * Free Software Foundation; only version 2 of the License is applicable.
8 *
9 * This program is distributed in the hope that it will be useful, but
10 * WITHOUT ANY WARRANTY; without even the implied warranty of
11 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
12 * General Public License for more details.
13 *
14 * You should have received a copy of the GNU General Public License along
15 * with this program; if not, write to the Free Software Foundation, Inc.,
16 * 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
17 *
18 * Author:
19 * Sebastian Harl <sh at tokkee.org>
20 **/
22 /*
23 * This module collects PostgreSQL database statistics.
24 */
26 #include "collectd.h"
27 #include "common.h"
29 #include "configfile.h"
30 #include "plugin.h"
32 #include "utils_complain.h"
34 #include <pg_config_manual.h>
35 #include <libpq-fe.h>
37 #define log_err(...) ERROR ("postgresql: " __VA_ARGS__)
38 #define log_warn(...) WARNING ("postgresql: " __VA_ARGS__)
39 #define log_info(...) INFO ("postgresql: " __VA_ARGS__)
41 #ifndef C_PSQL_DEFAULT_CONF
42 # define C_PSQL_DEFAULT_CONF PKGDATADIR "/postgresql_default.conf"
43 #endif
45 /* Appends the (parameter, value) pair to the string
46 * pointed to by 'buf' suitable to be used as argument
47 * for PQconnectdb(). If value equals NULL, the pair
48 * is ignored. */
49 #define C_PSQL_PAR_APPEND(buf, buf_len, parameter, value) \
50 if ((0 < (buf_len)) && (NULL != (value)) && ('\0' != *(value))) { \
51 int s = ssnprintf (buf, buf_len, " %s = '%s'", parameter, value); \
52 if (0 < s) { \
53 buf += s; \
54 buf_len -= s; \
55 } \
56 }
58 /* Returns the tuple (major, minor, patchlevel)
59 * for the given version number. */
60 #define C_PSQL_SERVER_VERSION3(server_version) \
61 (server_version) / 10000, \
62 (server_version) / 100 - (int)((server_version) / 10000) * 100, \
63 (server_version) - (int)((server_version) / 100) * 100
65 /* Returns true if the given host specifies a
66 * UNIX domain socket. */
67 #define C_PSQL_IS_UNIX_DOMAIN_SOCKET(host) \
68 ((NULL == (host)) || ('\0' == *(host)) || ('/' == *(host)))
70 /* Returns the tuple (host, delimiter, port) for a
71 * given (host, port) pair. Depending on the value of
72 * 'host' a UNIX domain socket or a TCP socket is
73 * assumed. */
74 #define C_PSQL_SOCKET3(host, port) \
75 ((NULL == (host)) || ('\0' == *(host))) ? DEFAULT_PGSOCKET_DIR : host, \
76 C_PSQL_IS_UNIX_DOMAIN_SOCKET (host) ? "/.s.PGSQL." : ":", \
77 port
79 typedef enum {
80 C_PSQL_PARAM_HOST = 1,
81 C_PSQL_PARAM_DB,
82 C_PSQL_PARAM_USER,
83 } c_psql_param_t;
85 typedef struct {
86 char *type;
87 char *type_instance;
88 int ds_type;
89 } c_psql_col_t;
91 typedef struct {
92 char *name;
93 char *query;
95 c_psql_param_t *params;
96 int params_num;
98 c_psql_col_t *cols;
99 int cols_num;
100 } c_psql_query_t;
102 typedef struct {
103 PGconn *conn;
104 c_complain_t conn_complaint;
106 int proto_version;
108 int max_params_num;
110 /* user configuration */
111 c_psql_query_t **queries;
112 int queries_num;
114 char *host;
115 char *port;
116 char *database;
117 char *user;
118 char *password;
120 char *sslmode;
122 char *krbsrvname;
124 char *service;
125 } c_psql_database_t;
127 static char *def_queries[] = {
128 "backends",
129 "transactions",
130 "queries",
131 "query_plans",
132 "table_states",
133 "disk_io",
134 "disk_usage"
135 };
136 static int def_queries_num = STATIC_ARRAY_SIZE (def_queries);
138 static c_psql_query_t *queries = NULL;
139 static int queries_num = 0;
141 static c_psql_database_t *databases = NULL;
142 static int databases_num = 0;
144 static c_psql_query_t *c_psql_query_new (const char *name)
145 {
146 c_psql_query_t *query;
148 ++queries_num;
149 if (NULL == (queries = (c_psql_query_t *)realloc (queries,
150 queries_num * sizeof (*queries)))) {
151 log_err ("Out of memory.");
152 exit (5);
153 }
154 query = queries + queries_num - 1;
156 query->name = sstrdup (name);
157 query->query = NULL;
159 query->params = NULL;
160 query->params_num = 0;
162 query->cols = NULL;
163 query->cols_num = 0;
164 return query;
165 } /* c_psql_query_new */
167 static void c_psql_query_delete (c_psql_query_t *query)
168 {
169 int i;
171 sfree (query->name);
172 sfree (query->query);
174 sfree (query->params);
175 query->params_num = 0;
177 for (i = 0; i < query->cols_num; ++i) {
178 sfree (query->cols[i].type);
179 sfree (query->cols[i].type_instance);
180 }
181 sfree (query->cols);
182 query->cols_num = 0;
183 return;
184 } /* c_psql_query_delete */
186 static c_psql_query_t *c_psql_query_get (const char *name)
187 {
188 int i;
190 for (i = 0; i < queries_num; ++i)
191 if (0 == strcasecmp (name, queries[i].name))
192 return queries + i;
193 return NULL;
194 } /* c_psql_query_get */
196 static c_psql_database_t *c_psql_database_new (const char *name)
197 {
198 c_psql_database_t *db;
200 ++databases_num;
201 if (NULL == (databases = (c_psql_database_t *)realloc (databases,
202 databases_num * sizeof (*databases)))) {
203 log_err ("Out of memory.");
204 exit (5);
205 }
207 db = databases + (databases_num - 1);
209 db->conn = NULL;
211 db->conn_complaint.last = 0;
212 db->conn_complaint.interval = 0;
214 db->proto_version = 0;
216 db->max_params_num = 0;
218 db->queries = NULL;
219 db->queries_num = 0;
221 db->database = sstrdup (name);
222 db->host = NULL;
223 db->port = NULL;
224 db->user = NULL;
225 db->password = NULL;
227 db->sslmode = NULL;
229 db->krbsrvname = NULL;
231 db->service = NULL;
232 return db;
233 } /* c_psql_database_new */
235 static void c_psql_database_delete (c_psql_database_t *db)
236 {
237 PQfinish (db->conn);
239 sfree (db->queries);
240 db->queries_num = 0;
242 sfree (db->database);
243 sfree (db->host);
244 sfree (db->port);
245 sfree (db->user);
246 sfree (db->password);
248 sfree (db->sslmode);
250 sfree (db->krbsrvname);
252 sfree (db->service);
253 return;
254 } /* c_psql_database_delete */
256 static void submit (const c_psql_database_t *db,
257 const char *type, const char *type_instance,
258 value_t *values, size_t values_len)
259 {
260 value_list_t vl = VALUE_LIST_INIT;
262 vl.values = values;
263 vl.values_len = values_len;
264 vl.time = time (NULL);
266 if (C_PSQL_IS_UNIX_DOMAIN_SOCKET (db->host)
267 || (0 == strcmp (db->host, "localhost")))
268 sstrncpy (vl.host, hostname_g, sizeof (vl.host));
269 else
270 sstrncpy (vl.host, db->host, sizeof (vl.host));
272 sstrncpy (vl.plugin, "postgresql", sizeof (vl.plugin));
273 sstrncpy (vl.plugin_instance, db->database, sizeof (vl.plugin_instance));
275 sstrncpy (vl.type, type, sizeof (vl.type));
277 if (NULL != type_instance)
278 sstrncpy (vl.type_instance, type_instance, sizeof (vl.type_instance));
280 plugin_dispatch_values (&vl);
281 return;
282 } /* submit */
284 static void submit_counter (const c_psql_database_t *db,
285 const char *type, const char *type_instance,
286 const char *value)
287 {
288 value_t values[1];
290 if ((NULL == value) || ('\0' == *value))
291 return;
293 values[0].counter = atoll (value);
294 submit (db, type, type_instance, values, 1);
295 return;
296 } /* submit_counter */
298 static void submit_gauge (const c_psql_database_t *db,
299 const char *type, const char *type_instance,
300 const char *value)
301 {
302 value_t values[1];
304 if ((NULL == value) || ('\0' == *value))
305 return;
307 values[0].gauge = atof (value);
308 submit (db, type, type_instance, values, 1);
309 return;
310 } /* submit_gauge */
312 static int c_psql_check_connection (c_psql_database_t *db)
313 {
314 /* "ping" */
315 PQclear (PQexec (db->conn, "SELECT 42;"));
317 if (CONNECTION_OK != PQstatus (db->conn)) {
318 PQreset (db->conn);
320 /* trigger c_release() */
321 if (0 == db->conn_complaint.interval)
322 db->conn_complaint.interval = 1;
324 if (CONNECTION_OK != PQstatus (db->conn)) {
325 c_complain (LOG_ERR, &db->conn_complaint,
326 "Failed to connect to database %s: %s",
327 db->database, PQerrorMessage (db->conn));
328 return -1;
329 }
331 db->proto_version = PQprotocolVersion (db->conn);
332 if (3 > db->proto_version)
333 log_warn ("Protocol version %d does not support parameters.",
334 db->proto_version);
335 }
337 c_release (LOG_INFO, &db->conn_complaint,
338 "Successfully reconnected to database %s", PQdb (db->conn));
339 return 0;
340 } /* c_psql_check_connection */
342 static PGresult *c_psql_exec_query_params (c_psql_database_t *db,
343 c_psql_query_t *query)
344 {
345 char *params[db->max_params_num];
346 int i;
348 assert (db->max_params_num >= query->params_num);
350 for (i = 0; i < query->params_num; ++i) {
351 switch (query->params[i]) {
352 case C_PSQL_PARAM_HOST:
353 params[i] = C_PSQL_IS_UNIX_DOMAIN_SOCKET (db->host)
354 ? "localhost" : db->host;
355 break;
356 case C_PSQL_PARAM_DB:
357 params[i] = db->database;
358 break;
359 case C_PSQL_PARAM_USER:
360 params[i] = db->user;
361 break;
362 default:
363 assert (0);
364 }
365 }
367 return PQexecParams (db->conn, query->query, query->params_num, NULL,
368 (const char *const *)((0 == query->params_num) ? NULL : params),
369 NULL, NULL, /* return text data */ 0);
370 } /* c_psql_exec_query_params */
372 static PGresult *c_psql_exec_query_noparams (c_psql_database_t *db,
373 c_psql_query_t *query)
374 {
375 return PQexec (db->conn, query->query);
376 } /* c_psql_exec_query_noparams */
378 static int c_psql_exec_query (c_psql_database_t *db, int idx)
379 {
380 c_psql_query_t *query;
381 PGresult *res;
383 int rows, cols;
384 int i;
386 if (idx >= db->queries_num)
387 return -1;
389 query = db->queries[idx];
391 if (3 <= db->proto_version)
392 res = c_psql_exec_query_params (db, query);
393 else if (0 == query->params_num)
394 res = c_psql_exec_query_noparams (db, query);
395 else {
396 log_err ("Connection to database \"%s\" does not support parameters "
397 "(protocol version %d) - cannot execute query \"%s\".",
398 db->database, db->proto_version, query->name);
399 return -1;
400 }
402 if (PGRES_TUPLES_OK != PQresultStatus (res)) {
403 log_err ("Failed to execute SQL query: %s",
404 PQerrorMessage (db->conn));
405 log_info ("SQL query was: %s", query->query);
406 PQclear (res);
407 return -1;
408 }
410 rows = PQntuples (res);
411 if (1 > rows)
412 return 0;
414 cols = PQnfields (res);
415 if (query->cols_num != cols) {
416 log_err ("SQL query returned wrong number of fields "
417 "(expected: %i, got: %i)", query->cols_num, cols);
418 log_info ("SQL query was: %s", query->query);
419 return -1;
420 }
422 for (i = 0; i < rows; ++i) {
423 int j;
425 for (j = 0; j < cols; ++j) {
426 c_psql_col_t col = query->cols[j];
428 char *value = PQgetvalue (res, i, j);
430 if (col.ds_type == DS_TYPE_COUNTER)
431 submit_counter (db, col.type, col.type_instance, value);
432 else if (col.ds_type == DS_TYPE_GAUGE)
433 submit_gauge (db, col.type, col.type_instance, value);
434 }
435 }
436 return 0;
437 } /* c_psql_exec_query */
439 static int c_psql_read (void)
440 {
441 int success = 0;
442 int i;
444 for (i = 0; i < databases_num; ++i) {
445 c_psql_database_t *db = databases + i;
447 int j;
449 assert (NULL != db->database);
451 if (0 != c_psql_check_connection (db))
452 continue;
454 for (j = 0; j < db->queries_num; ++j)
455 c_psql_exec_query (db, j);
457 ++success;
458 }
460 if (! success)
461 return -1;
462 return 0;
463 } /* c_psql_read */
465 static int c_psql_shutdown (void)
466 {
467 int i;
469 if ((NULL == databases) || (0 == databases_num))
470 return 0;
472 plugin_unregister_read ("postgresql");
473 plugin_unregister_shutdown ("postgresql");
475 for (i = 0; i < databases_num; ++i) {
476 c_psql_database_t *db = databases + i;
477 c_psql_database_delete (db);
478 }
480 sfree (databases);
481 databases_num = 0;
483 for (i = 0; i < queries_num; ++i) {
484 c_psql_query_t *query = queries + i;
485 c_psql_query_delete (query);
486 }
488 sfree (queries);
489 queries_num = 0;
490 return 0;
491 } /* c_psql_shutdown */
493 static int c_psql_init (void)
494 {
495 int i;
497 if ((NULL == databases) || (0 == databases_num))
498 return 0;
500 for (i = 0; i < queries_num; ++i) {
501 c_psql_query_t *query = queries + i;
502 int j;
504 for (j = 0; j < query->cols_num; ++j) {
505 c_psql_col_t *col = query->cols + j;
506 const data_set_t *ds;
508 ds = plugin_get_ds (col->type);
509 if (NULL == ds) {
510 log_err ("Column: Unknown type \"%s\".", col->type);
511 c_psql_shutdown ();
512 return -1;
513 }
515 if (1 != ds->ds_num) {
516 log_err ("Column: Invalid type \"%s\" - types defining "
517 "one data source are supported only (got: %i).",
518 col->type, ds->ds_num);
519 c_psql_shutdown ();
520 return -1;
521 }
523 col->ds_type = ds->ds[0].type;
524 }
525 }
527 for (i = 0; i < databases_num; ++i) {
528 c_psql_database_t *db = databases + i;
530 char conninfo[4096];
531 char *buf = conninfo;
532 int buf_len = sizeof (conninfo);
533 int status;
535 char *server_host;
536 int server_version;
538 status = ssnprintf (buf, buf_len, "dbname = '%s'", db->database);
539 if (0 < status) {
540 buf += status;
541 buf_len -= status;
542 }
544 C_PSQL_PAR_APPEND (buf, buf_len, "host", db->host);
545 C_PSQL_PAR_APPEND (buf, buf_len, "port", db->port);
546 C_PSQL_PAR_APPEND (buf, buf_len, "user", db->user);
547 C_PSQL_PAR_APPEND (buf, buf_len, "password", db->password);
548 C_PSQL_PAR_APPEND (buf, buf_len, "sslmode", db->sslmode);
549 C_PSQL_PAR_APPEND (buf, buf_len, "krbsrvname", db->krbsrvname);
550 C_PSQL_PAR_APPEND (buf, buf_len, "service", db->service);
552 db->conn = PQconnectdb (conninfo);
553 if (0 != c_psql_check_connection (db))
554 continue;
556 db->proto_version = PQprotocolVersion (db->conn);
558 server_host = PQhost (db->conn);
559 server_version = PQserverVersion (db->conn);
560 log_info ("Sucessfully connected to database %s (user %s) "
561 "at server %s%s%s (server version: %d.%d.%d, "
562 "protocol version: %d, pid: %d)",
563 PQdb (db->conn), PQuser (db->conn),
564 C_PSQL_SOCKET3 (server_host, PQport (db->conn)),
565 C_PSQL_SERVER_VERSION3 (server_version),
566 db->proto_version, PQbackendPID (db->conn));
568 if (3 > db->proto_version)
569 log_warn ("Protocol version %d does not support parameters.",
570 db->proto_version);
571 }
573 plugin_register_read ("postgresql", c_psql_read);
574 plugin_register_shutdown ("postgresql", c_psql_shutdown);
575 return 0;
576 } /* c_psql_init */
578 static int config_set (char *name, char **var, const oconfig_item_t *ci)
579 {
580 if ((0 != ci->children_num) || (1 != ci->values_num)
581 || (OCONFIG_TYPE_STRING != ci->values[0].type)) {
582 log_err ("%s expects a single string argument.", name);
583 return 1;
584 }
586 sfree (*var);
587 *var = sstrdup (ci->values[0].value.string);
588 return 0;
589 } /* config_set */
591 static int config_set_param (c_psql_query_t *query, const oconfig_item_t *ci)
592 {
593 c_psql_param_t param;
594 char *param_str;
596 if ((0 != ci->children_num) || (1 != ci->values_num)
597 || (OCONFIG_TYPE_STRING != ci->values[0].type)) {
598 log_err ("Param expects a single string argument.");
599 return 1;
600 }
602 param_str = ci->values[0].value.string;
603 if (0 == strcasecmp (param_str, "hostname"))
604 param = C_PSQL_PARAM_HOST;
605 else if (0 == strcasecmp (param_str, "database"))
606 param = C_PSQL_PARAM_DB;
607 else if (0 == strcasecmp (param_str, "username"))
608 param = C_PSQL_PARAM_USER;
609 else {
610 log_err ("Invalid parameter \"%s\".", param_str);
611 return 1;
612 }
614 ++query->params_num;
615 if (NULL == (query->params = (c_psql_param_t *)realloc (query->params,
616 query->params_num * sizeof (*query->params)))) {
617 log_err ("Out of memory.");
618 exit (5);
619 }
621 query->params[query->params_num - 1] = param;
622 return 0;
623 } /* config_set_param */
625 static int config_set_column (c_psql_query_t *query, const oconfig_item_t *ci)
626 {
627 c_psql_col_t *col;
629 int i;
631 if ((0 != ci->children_num)
632 || (1 > ci->values_num) || (2 < ci->values_num)) {
633 log_err ("Column expects either one or two arguments.");
634 return 1;
635 }
637 for (i = 0; i < ci->values_num; ++i) {
638 if (OCONFIG_TYPE_STRING != ci->values[i].type) {
639 log_err ("Column expects either one or two string arguments.");
640 return 1;
641 }
642 }
644 ++query->cols_num;
645 if (NULL == (query->cols = (c_psql_col_t *)realloc (query->cols,
646 query->cols_num * sizeof (*query->cols)))) {
647 log_err ("Out of memory.");
648 exit (5);
649 }
651 col = query->cols + query->cols_num - 1;
653 col->ds_type = -1;
655 col->type = sstrdup (ci->values[0].value.string);
656 col->type_instance = (2 == ci->values_num)
657 ? sstrdup (ci->values[1].value.string) : NULL;
658 return 0;
659 } /* config_set_column */
661 static int set_query (c_psql_database_t *db, const char *name)
662 {
663 c_psql_query_t *query;
665 query = c_psql_query_get (name);
666 if (NULL == query) {
667 log_err ("Query \"%s\" not found - please check your configuration.",
668 name);
669 return 1;
670 }
672 ++db->queries_num;
673 if (NULL == (db->queries = (c_psql_query_t **)realloc (db->queries,
674 db->queries_num * sizeof (*db->queries)))) {
675 log_err ("Out of memory.");
676 exit (5);
677 }
679 if (query->params_num > db->max_params_num)
680 db->max_params_num = query->params_num;
682 db->queries[db->queries_num - 1] = query;
683 return 0;
684 } /* set_query */
686 static int config_set_query (c_psql_database_t *db, const oconfig_item_t *ci)
687 {
688 if ((0 != ci->children_num) || (1 != ci->values_num)
689 || (OCONFIG_TYPE_STRING != ci->values[0].type)) {
690 log_err ("Query expects a single string argument.");
691 return 1;
692 }
693 return set_query (db, ci->values[0].value.string);
694 } /* config_set_query */
696 static int c_psql_config_query (oconfig_item_t *ci)
697 {
698 c_psql_query_t *query;
700 int i;
702 if ((1 != ci->values_num)
703 || (OCONFIG_TYPE_STRING != ci->values[0].type)) {
704 log_err ("<Query> expects a single string argument.");
705 return 1;
706 }
708 query = c_psql_query_new (ci->values[0].value.string);
710 for (i = 0; i < ci->children_num; ++i) {
711 oconfig_item_t *c = ci->children + i;
713 if (0 == strcasecmp (c->key, "Query"))
714 config_set ("Query", &query->query, c);
715 else if (0 == strcasecmp (c->key, "Param"))
716 config_set_param (query, c);
717 else if (0 == strcasecmp (c->key, "Column"))
718 config_set_column (query, c);
719 else
720 log_warn ("Ignoring unknown config key \"%s\".", c->key);
721 }
722 return 0;
723 } /* c_psql_config_query */
725 static int c_psql_config_database (oconfig_item_t *ci)
726 {
727 c_psql_database_t *db;
729 int i;
731 if ((1 != ci->values_num)
732 || (OCONFIG_TYPE_STRING != ci->values[0].type)) {
733 log_err ("<Database> expects a single string argument.");
734 return 1;
735 }
737 db = c_psql_database_new (ci->values[0].value.string);
739 for (i = 0; i < ci->children_num; ++i) {
740 oconfig_item_t *c = ci->children + i;
742 if (0 == strcasecmp (c->key, "Host"))
743 config_set ("Host", &db->host, c);
744 else if (0 == strcasecmp (c->key, "Port"))
745 config_set ("Port", &db->port, c);
746 else if (0 == strcasecmp (c->key, "User"))
747 config_set ("User", &db->user, c);
748 else if (0 == strcasecmp (c->key, "Password"))
749 config_set ("Password", &db->password, c);
750 else if (0 == strcasecmp (c->key, "SSLMode"))
751 config_set ("SSLMode", &db->sslmode, c);
752 else if (0 == strcasecmp (c->key, "KRBSrvName"))
753 config_set ("KRBSrvName", &db->krbsrvname, c);
754 else if (0 == strcasecmp (c->key, "Service"))
755 config_set ("Service", &db->service, c);
756 else if (0 == strcasecmp (c->key, "Query"))
757 config_set_query (db, c);
758 else
759 log_warn ("Ignoring unknown config key \"%s\".", c->key);
760 }
762 if (NULL == db->queries) {
763 for (i = 0; i < def_queries_num; ++i)
764 set_query (db, def_queries[i]);
765 }
766 return 0;
767 }
769 static int c_psql_config (oconfig_item_t *ci)
770 {
771 static int have_def_config = 0;
773 int i;
775 if (0 == have_def_config) {
776 oconfig_item_t *c;
778 have_def_config = 1;
780 c = oconfig_parse_file (C_PSQL_DEFAULT_CONF);
781 if (NULL == c)
782 log_err ("Failed to read default config ("C_PSQL_DEFAULT_CONF").");
783 else
784 c_psql_config (c);
786 if (NULL == queries)
787 log_err ("Default config ("C_PSQL_DEFAULT_CONF") did not define "
788 "any queries - please check your installation.");
789 }
791 for (i = 0; i < ci->children_num; ++i) {
792 oconfig_item_t *c = ci->children + i;
794 if (0 == strcasecmp (c->key, "Query"))
795 c_psql_config_query (c);
796 else if (0 == strcasecmp (c->key, "Database"))
797 c_psql_config_database (c);
798 else
799 log_warn ("Ignoring unknown config key \"%s\".", c->key);
800 }
801 return 0;
802 } /* c_psql_config */
804 void module_register (void)
805 {
806 plugin_register_complex_config ("postgresql", c_psql_config);
807 plugin_register_init ("postgresql", c_psql_init);
808 } /* module_register */
810 /* vim: set sw=4 ts=4 tw=78 noexpandtab : */