2 * collectd - src/postgresql.c
3 * Copyright (C) 2008 Sebastian Harl
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.
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.
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
19 * Sebastian Harl <sh at tokkee.org>
23 * This module collects PostgreSQL database statistics.
29 #include "configfile.h"
32 #include "utils_complain.h"
34 #include <pg_config_manual.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"
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
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); \
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
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." : ":", \
80 C_PSQL_PARAM_HOST = 1,
95 c_psql_param_t *params;
104 c_complain_t conn_complaint;
108 /* user configuration */
109 c_psql_query_t **queries;
125 static char *def_queries[] = {
130 static int def_queries_num = STATIC_ARRAY_SIZE (def_queries);
132 static c_psql_query_t *queries = NULL;
133 static int queries_num = 0;
135 static c_psql_database_t *databases = NULL;
136 static int databases_num = 0;
138 static c_psql_query_t *c_psql_query_new (const char *name)
140 c_psql_query_t *query;
143 if (NULL == (queries = (c_psql_query_t *)realloc (queries,
144 queries_num * sizeof (*queries)))) {
145 log_err ("Out of memory.");
148 query = queries + queries_num - 1;
150 query->name = sstrdup (name);
153 query->params = NULL;
154 query->params_num = 0;
159 } /* c_psql_query_new */
161 static void c_psql_query_delete (c_psql_query_t *query)
166 sfree (query->query);
168 sfree (query->params);
169 query->params_num = 0;
171 for (i = 0; i < query->cols_num; ++i) {
172 sfree (query->cols[i].type);
173 sfree (query->cols[i].type_instance);
178 } /* c_psql_query_delete */
180 static c_psql_query_t *c_psql_query_get (const char *name)
184 for (i = 0; i < queries_num; ++i)
185 if (0 == strcasecmp (name, queries[i].name))
188 } /* c_psql_query_get */
190 static c_psql_database_t *c_psql_database_new (const char *name)
192 c_psql_database_t *db;
195 if (NULL == (databases = (c_psql_database_t *)realloc (databases,
196 databases_num * sizeof (*databases)))) {
197 log_err ("Out of memory.");
201 db = databases + (databases_num - 1);
205 db->conn_complaint.last = 0;
206 db->conn_complaint.interval = 0;
208 db->max_params_num = 0;
213 db->database = sstrdup (name);
221 db->krbsrvname = NULL;
225 } /* c_psql_database_new */
227 static void c_psql_database_delete (c_psql_database_t *db)
234 sfree (db->database);
238 sfree (db->password);
242 sfree (db->krbsrvname);
246 } /* c_psql_database_delete */
248 static void submit (const c_psql_database_t *db,
249 const char *type, const char *type_instance,
250 value_t *values, size_t values_len)
252 value_list_t vl = VALUE_LIST_INIT;
255 vl.values_len = values_len;
256 vl.time = time (NULL);
258 if (C_PSQL_IS_UNIX_DOMAIN_SOCKET (db->host)
259 || (0 == strcmp (db->host, "localhost")))
260 sstrncpy (vl.host, hostname_g, sizeof (vl.host));
262 sstrncpy (vl.host, db->host, sizeof (vl.host));
264 sstrncpy (vl.plugin, "postgresql", sizeof (vl.plugin));
265 sstrncpy (vl.plugin_instance, db->database, sizeof (vl.plugin_instance));
267 sstrncpy (vl.type, type, sizeof (vl.type));
269 if (NULL != type_instance)
270 sstrncpy (vl.type_instance, type_instance, sizeof (vl.type_instance));
272 plugin_dispatch_values (&vl);
276 static void submit_counter (const c_psql_database_t *db,
277 const char *type, const char *type_instance,
282 if ((NULL == value) || ('\0' == *value))
285 values[0].counter = atoll (value);
286 submit (db, type, type_instance, values, 1);
288 } /* submit_counter */
290 static void submit_gauge (const c_psql_database_t *db,
291 const char *type, const char *type_instance,
296 if ((NULL == value) || ('\0' == *value))
299 values[0].gauge = atof (value);
300 submit (db, type, type_instance, values, 1);
304 static int c_psql_check_connection (c_psql_database_t *db)
307 PQclear (PQexec (db->conn, "SELECT 42;"));
309 if (CONNECTION_OK != PQstatus (db->conn)) {
312 /* trigger c_release() */
313 if (0 == db->conn_complaint.interval)
314 db->conn_complaint.interval = 1;
316 if (CONNECTION_OK != PQstatus (db->conn)) {
317 c_complain (LOG_ERR, &db->conn_complaint,
318 "Failed to connect to database %s: %s",
319 db->database, PQerrorMessage (db->conn));
324 c_release (LOG_INFO, &db->conn_complaint,
325 "Successfully reconnected to database %s", PQdb (db->conn));
327 } /* c_psql_check_connection */
329 static int c_psql_exec_query (c_psql_database_t *db, int idx)
331 c_psql_query_t *query;
334 char *params[db->max_params_num];
339 if (idx >= db->queries_num)
342 query = db->queries[idx];
344 assert (db->max_params_num >= query->params_num);
346 for (i = 0; i < query->params_num; ++i) {
347 switch (query->params[i]) {
348 case C_PSQL_PARAM_HOST:
349 params[i] = C_PSQL_IS_UNIX_DOMAIN_SOCKET (db->host)
350 ? "localhost" : db->host;
352 case C_PSQL_PARAM_DB:
353 params[i] = db->database;
355 case C_PSQL_PARAM_USER:
356 params[i] = db->user;
363 res = PQexecParams (db->conn, query->query, query->params_num, NULL,
364 (const char *const *)((0 == query->params_num) ? NULL : params),
365 NULL, NULL, /* return text data */ 0);
367 if (PGRES_TUPLES_OK != PQresultStatus (res)) {
368 log_err ("Failed to execute SQL query: %s",
369 PQerrorMessage (db->conn));
370 log_info ("SQL query was: %s", query->query);
375 rows = PQntuples (res);
379 cols = PQnfields (res);
380 if (query->cols_num != cols) {
381 log_err ("SQL query returned wrong number of fields "
382 "(expected: %i, got: %i)", query->cols_num, cols);
383 log_info ("SQL query was: %s", query->query);
387 for (i = 0; i < rows; ++i) {
390 for (j = 0; j < cols; ++j) {
391 c_psql_col_t col = query->cols[j];
393 char *value = PQgetvalue (res, i, j);
395 if (col.ds_type == DS_TYPE_COUNTER)
396 submit_counter (db, col.type, col.type_instance, value);
397 else if (col.ds_type == DS_TYPE_GAUGE)
398 submit_gauge (db, col.type, col.type_instance, value);
402 } /* c_psql_exec_query */
404 static int c_psql_read (void)
409 for (i = 0; i < databases_num; ++i) {
410 c_psql_database_t *db = databases + i;
414 assert (NULL != db->database);
416 if (0 != c_psql_check_connection (db))
419 for (j = 0; j < db->queries_num; ++j)
420 c_psql_exec_query (db, j);
430 static int c_psql_shutdown (void)
434 if ((NULL == databases) || (0 == databases_num))
437 plugin_unregister_read ("postgresql");
438 plugin_unregister_shutdown ("postgresql");
440 for (i = 0; i < databases_num; ++i) {
441 c_psql_database_t *db = databases + i;
442 c_psql_database_delete (db);
448 for (i = 0; i < queries_num; ++i) {
449 c_psql_query_t *query = queries + i;
450 c_psql_query_delete (query);
456 } /* c_psql_shutdown */
458 static int c_psql_init (void)
462 if ((NULL == databases) || (0 == databases_num))
465 for (i = 0; i < queries_num; ++i) {
466 c_psql_query_t *query = queries + i;
469 for (j = 0; j < query->cols_num; ++j) {
470 c_psql_col_t *col = query->cols + j;
471 const data_set_t *ds;
473 ds = plugin_get_ds (col->type);
475 log_err ("Column: Unknown type \"%s\".", col->type);
480 if (1 != ds->ds_num) {
481 log_err ("Column: Invalid type \"%s\" - types defining "
482 "one data source are supported only (got: %i).",
483 col->type, ds->ds_num);
488 col->ds_type = ds->ds[0].type;
492 for (i = 0; i < databases_num; ++i) {
493 c_psql_database_t *db = databases + i;
496 char *buf = conninfo;
497 int buf_len = sizeof (conninfo);
503 status = ssnprintf (buf, buf_len, "dbname = '%s'", db->database);
509 C_PSQL_PAR_APPEND (buf, buf_len, "host", db->host);
510 C_PSQL_PAR_APPEND (buf, buf_len, "port", db->port);
511 C_PSQL_PAR_APPEND (buf, buf_len, "user", db->user);
512 C_PSQL_PAR_APPEND (buf, buf_len, "password", db->password);
513 C_PSQL_PAR_APPEND (buf, buf_len, "sslmode", db->sslmode);
514 C_PSQL_PAR_APPEND (buf, buf_len, "krbsrvname", db->krbsrvname);
515 C_PSQL_PAR_APPEND (buf, buf_len, "service", db->service);
517 db->conn = PQconnectdb (conninfo);
518 if (0 != c_psql_check_connection (db))
521 server_host = PQhost (db->conn);
522 server_version = PQserverVersion (db->conn);
523 log_info ("Sucessfully connected to database %s (user %s) "
524 "at server %s%s%s (server version: %d.%d.%d, "
525 "protocol version: %d, pid: %d)",
526 PQdb (db->conn), PQuser (db->conn),
527 C_PSQL_SOCKET3 (server_host, PQport (db->conn)),
528 C_PSQL_SERVER_VERSION3 (server_version),
529 PQprotocolVersion (db->conn), PQbackendPID (db->conn));
532 plugin_register_read ("postgresql", c_psql_read);
533 plugin_register_shutdown ("postgresql", c_psql_shutdown);
537 static int config_set (char *name, char **var, const oconfig_item_t *ci)
539 if ((0 != ci->children_num) || (1 != ci->values_num)
540 || (OCONFIG_TYPE_STRING != ci->values[0].type)) {
541 log_err ("%s expects a single string argument.", name);
546 *var = sstrdup (ci->values[0].value.string);
550 static int config_set_param (c_psql_query_t *query, const oconfig_item_t *ci)
552 c_psql_param_t param;
555 if ((0 != ci->children_num) || (1 != ci->values_num)
556 || (OCONFIG_TYPE_STRING != ci->values[0].type)) {
557 log_err ("Param expects a single string argument.");
561 param_str = ci->values[0].value.string;
562 if (0 == strcasecmp (param_str, "hostname"))
563 param = C_PSQL_PARAM_HOST;
564 else if (0 == strcasecmp (param_str, "database"))
565 param = C_PSQL_PARAM_DB;
566 else if (0 == strcasecmp (param_str, "username"))
567 param = C_PSQL_PARAM_USER;
569 log_err ("Invalid parameter \"%s\".", param_str);
574 if (NULL == (query->params = (c_psql_param_t *)realloc (query->params,
575 query->params_num * sizeof (*query->params)))) {
576 log_err ("Out of memory.");
580 query->params[query->params_num - 1] = param;
582 } /* config_set_param */
584 static int config_set_column (c_psql_query_t *query, const oconfig_item_t *ci)
590 if ((0 != ci->children_num)
591 || (1 > ci->values_num) || (2 < ci->values_num)) {
592 log_err ("Column expects either one or two arguments.");
596 for (i = 0; i < ci->values_num; ++i) {
597 if (OCONFIG_TYPE_STRING != ci->values[i].type) {
598 log_err ("Column expects either one or two string arguments.");
604 if (NULL == (query->cols = (c_psql_col_t *)realloc (query->cols,
605 query->cols_num * sizeof (*query->cols)))) {
606 log_err ("Out of memory.");
610 col = query->cols + query->cols_num - 1;
614 col->type = sstrdup (ci->values[0].value.string);
615 col->type_instance = (2 == ci->values_num)
616 ? sstrdup (ci->values[1].value.string) : NULL;
618 } /* config_set_column */
620 static int config_set_query (c_psql_database_t *db, const oconfig_item_t *ci)
622 c_psql_query_t *query;
624 if ((0 != ci->children_num) || (1 != ci->values_num)
625 || (OCONFIG_TYPE_STRING != ci->values[0].type)) {
626 log_err ("Query expects a single string argument.");
630 query = c_psql_query_get (ci->values[0].value.string);
632 log_err ("Query \"%s\" not found - please check your configuration.",
633 ci->values[0].value.string);
638 if (NULL == (db->queries = (c_psql_query_t **)realloc (db->queries,
639 db->queries_num * sizeof (*db->queries)))) {
640 log_err ("Out of memory.");
644 if (query->params_num > db->max_params_num)
645 db->max_params_num = query->params_num;
647 db->queries[db->queries_num - 1] = query;
649 } /* config_set_query */
651 static int c_psql_config_query (oconfig_item_t *ci)
653 c_psql_query_t *query;
657 if ((1 != ci->values_num)
658 || (OCONFIG_TYPE_STRING != ci->values[0].type)) {
659 log_err ("<Query> expects a single string argument.");
663 query = c_psql_query_new (ci->values[0].value.string);
665 for (i = 0; i < ci->children_num; ++i) {
666 oconfig_item_t *c = ci->children + i;
668 if (0 == strcasecmp (c->key, "Query"))
669 config_set ("Query", &query->query, c);
670 else if (0 == strcasecmp (c->key, "Param"))
671 config_set_param (query, c);
672 else if (0 == strcasecmp (c->key, "Column"))
673 config_set_column (query, c);
675 log_warn ("Ignoring unknown config key \"%s\".", c->key);
678 } /* c_psql_config_query */
680 static int c_psql_config_database (oconfig_item_t *ci)
682 c_psql_database_t *db;
686 if ((1 != ci->values_num)
687 || (OCONFIG_TYPE_STRING != ci->values[0].type)) {
688 log_err ("<Database> expects a single string argument.");
692 db = c_psql_database_new (ci->values[0].value.string);
694 for (i = 0; i < ci->children_num; ++i) {
695 oconfig_item_t *c = ci->children + i;
697 if (0 == strcasecmp (c->key, "Host"))
698 config_set ("Host", &db->host, c);
699 else if (0 == strcasecmp (c->key, "Port"))
700 config_set ("Port", &db->port, c);
701 else if (0 == strcasecmp (c->key, "User"))
702 config_set ("User", &db->user, c);
703 else if (0 == strcasecmp (c->key, "Password"))
704 config_set ("Password", &db->password, c);
705 else if (0 == strcasecmp (c->key, "SSLMode"))
706 config_set ("SSLMode", &db->sslmode, c);
707 else if (0 == strcasecmp (c->key, "KRBSrvName"))
708 config_set ("KRBSrvName", &db->krbsrvname, c);
709 else if (0 == strcasecmp (c->key, "Service"))
710 config_set ("Service", &db->service, c);
711 else if (0 == strcasecmp (c->key, "Query"))
712 config_set_query (db, c);
714 log_warn ("Ignoring unknown config key \"%s\".", c->key);
717 if (NULL == db->queries) {
718 db->queries = (c_psql_query_t **)malloc (def_queries_num
719 * sizeof (*db->queries));
721 for (i = 0; i < def_queries_num; ++i) {
722 db->queries[i] = c_psql_query_get (def_queries[i]);
723 if (NULL == db->queries[i])
724 log_err ("Query \"%s\" not found - "
725 "please check your installation.",
734 static int c_psql_config (oconfig_item_t *ci)
736 static int have_def_config = 0;
740 if (0 == have_def_config) {
745 c = oconfig_parse_file (C_PSQL_DEFAULT_CONF);
747 log_err ("Failed to read default config ("C_PSQL_DEFAULT_CONF").");
752 log_err ("Default config ("C_PSQL_DEFAULT_CONF") did not define "
753 "any queries - please check your installation.");
756 for (i = 0; i < ci->children_num; ++i) {
757 oconfig_item_t *c = ci->children + i;
759 if (0 == strcasecmp (c->key, "Query"))
760 c_psql_config_query (c);
761 else if (0 == strcasecmp (c->key, "Database"))
762 c_psql_config_database (c);
764 log_warn ("Ignoring unknown config key \"%s\".", c->key);
767 } /* c_psql_config */
769 void module_register (void)
771 plugin_register_complex_config ("postgresql", c_psql_config);
772 plugin_register_init ("postgresql", c_psql_init);
773 } /* module_register */
775 /* vim: set sw=4 ts=4 tw=78 noexpandtab : */