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;
107 c_complain_t conn_complaint;
113 /* user configuration */
114 c_psql_query_t **queries;
131 static char *def_queries[] = {
140 static int def_queries_num = STATIC_ARRAY_SIZE (def_queries);
142 static c_psql_query_t *queries = NULL;
143 static int queries_num = 0;
145 static c_psql_database_t *databases = NULL;
146 static int databases_num = 0;
148 static c_psql_query_t *c_psql_query_new (const char *name)
150 c_psql_query_t *query;
153 if (NULL == (queries = (c_psql_query_t *)realloc (queries,
154 queries_num * sizeof (*queries)))) {
155 log_err ("Out of memory.");
158 query = queries + queries_num - 1;
160 query->name = sstrdup (name);
163 query->params = NULL;
164 query->params_num = 0;
169 query->min_pg_version = 0;
170 query->max_pg_version = INT_MAX;
172 } /* c_psql_query_new */
174 static void c_psql_query_delete (c_psql_query_t *query)
181 sfree (query->params);
182 query->params_num = 0;
184 for (i = 0; i < query->cols_num; ++i) {
185 sfree (query->cols[i].type);
186 sfree (query->cols[i].type_instance);
191 } /* c_psql_query_delete */
193 static c_psql_query_t *c_psql_query_get (const char *name, int server_version)
197 for (i = 0; i < queries_num; ++i)
198 if (0 == strcasecmp (name, queries[i].name)
199 && ((-1 == server_version)
200 || ((queries[i].min_pg_version <= server_version)
201 && (server_version <= queries[i].max_pg_version))))
204 } /* c_psql_query_get */
206 static c_psql_database_t *c_psql_database_new (const char *name)
208 c_psql_database_t *db;
211 if (NULL == (databases = (c_psql_database_t *)realloc (databases,
212 databases_num * sizeof (*databases)))) {
213 log_err ("Out of memory.");
217 db = databases + (databases_num - 1);
221 C_COMPLAIN_INIT (&db->conn_complaint);
223 db->proto_version = 0;
225 db->max_params_num = 0;
228 db->hidden_queries = NULL;
231 db->database = sstrdup (name);
239 db->krbsrvname = NULL;
243 } /* c_psql_database_new */
245 static void c_psql_database_init (c_psql_database_t *db, int server_version)
249 /* Get the right version of each query definition. */
250 for (i = 0; i < db->queries_num; ++i) {
253 tmp = c_psql_query_get (db->queries[i]->name, server_version);
255 if (tmp == db->queries[i])
259 log_err ("Query \"%s\" not found for server version %i - "
260 "please check your configuration.",
261 db->queries[i]->name, server_version);
262 /* By hiding the query (rather than removing it from the list) we
263 * don't lose it in case a reconnect to an available version
264 * happens at a later time. */
265 db->hidden_queries[i] = 1;
269 db->hidden_queries[i] = 0;
270 db->queries[i] = tmp;
272 } /* c_psql_database_init */
274 static void c_psql_database_delete (c_psql_database_t *db)
280 sfree (db->hidden_queries);
283 sfree (db->database);
287 sfree (db->password);
291 sfree (db->krbsrvname);
295 } /* c_psql_database_delete */
297 static void submit (const c_psql_database_t *db,
298 const char *type, const char *type_instance,
299 value_t *values, size_t values_len)
301 value_list_t vl = VALUE_LIST_INIT;
304 vl.values_len = values_len;
305 vl.time = time (NULL);
307 if (C_PSQL_IS_UNIX_DOMAIN_SOCKET (db->host)
308 || (0 == strcmp (db->host, "localhost")))
309 sstrncpy (vl.host, hostname_g, sizeof (vl.host));
311 sstrncpy (vl.host, db->host, sizeof (vl.host));
313 sstrncpy (vl.plugin, "postgresql", sizeof (vl.plugin));
314 sstrncpy (vl.plugin_instance, db->database, sizeof (vl.plugin_instance));
316 sstrncpy (vl.type, type, sizeof (vl.type));
318 if (NULL != type_instance)
319 sstrncpy (vl.type_instance, type_instance, sizeof (vl.type_instance));
321 plugin_dispatch_values (&vl);
325 static void submit_counter (const c_psql_database_t *db,
326 const char *type, const char *type_instance,
331 if ((NULL == value) || ('\0' == *value))
334 values[0].counter = atoll (value);
335 submit (db, type, type_instance, values, 1);
337 } /* submit_counter */
339 static void submit_gauge (const c_psql_database_t *db,
340 const char *type, const char *type_instance,
345 if ((NULL == value) || ('\0' == *value))
348 values[0].gauge = atof (value);
349 submit (db, type, type_instance, values, 1);
353 static int c_psql_check_connection (c_psql_database_t *db)
356 PQclear (PQexec (db->conn, "SELECT 42;"));
358 if (CONNECTION_OK != PQstatus (db->conn)) {
361 /* trigger c_release() */
362 if (0 == db->conn_complaint.interval)
363 db->conn_complaint.interval = 1;
365 if (CONNECTION_OK != PQstatus (db->conn)) {
366 c_complain (LOG_ERR, &db->conn_complaint,
367 "Failed to connect to database %s: %s",
368 db->database, PQerrorMessage (db->conn));
372 db->proto_version = PQprotocolVersion (db->conn);
373 if (3 > db->proto_version)
374 log_warn ("Protocol version %d does not support parameters.",
378 /* We might have connected to a different PostgreSQL version, so we
379 * need to reinitialize stuff. */
380 if (c_would_release (&db->conn_complaint))
381 c_psql_database_init (db, PQserverVersion (db->conn));
383 c_release (LOG_INFO, &db->conn_complaint,
384 "Successfully reconnected to database %s", PQdb (db->conn));
386 } /* c_psql_check_connection */
388 static PGresult *c_psql_exec_query_params (c_psql_database_t *db,
389 c_psql_query_t *query)
391 char *params[db->max_params_num];
394 assert (db->max_params_num >= query->params_num);
396 for (i = 0; i < query->params_num; ++i) {
397 switch (query->params[i]) {
398 case C_PSQL_PARAM_HOST:
399 params[i] = C_PSQL_IS_UNIX_DOMAIN_SOCKET (db->host)
400 ? "localhost" : db->host;
402 case C_PSQL_PARAM_DB:
403 params[i] = db->database;
405 case C_PSQL_PARAM_USER:
406 params[i] = db->user;
413 return PQexecParams (db->conn, query->stmt, query->params_num, NULL,
414 (const char *const *)((0 == query->params_num) ? NULL : params),
415 NULL, NULL, /* return text data */ 0);
416 } /* c_psql_exec_query_params */
418 static PGresult *c_psql_exec_query_noparams (c_psql_database_t *db,
419 c_psql_query_t *query)
421 return PQexec (db->conn, query->stmt);
422 } /* c_psql_exec_query_noparams */
424 static int c_psql_exec_query (c_psql_database_t *db, int idx)
426 c_psql_query_t *query;
432 if (idx >= db->queries_num)
435 if (0 != db->hidden_queries[idx])
438 query = db->queries[idx];
440 if (3 <= db->proto_version)
441 res = c_psql_exec_query_params (db, query);
442 else if (0 == query->params_num)
443 res = c_psql_exec_query_noparams (db, query);
445 log_err ("Connection to database \"%s\" does not support parameters "
446 "(protocol version %d) - cannot execute query \"%s\".",
447 db->database, db->proto_version, query->name);
451 if (PGRES_TUPLES_OK != PQresultStatus (res)) {
452 log_err ("Failed to execute SQL query: %s",
453 PQerrorMessage (db->conn));
454 log_info ("SQL query was: %s", query->stmt);
459 rows = PQntuples (res);
465 cols = PQnfields (res);
466 if (query->cols_num != cols) {
467 log_err ("SQL query returned wrong number of fields "
468 "(expected: %i, got: %i)", query->cols_num, cols);
469 log_info ("SQL query was: %s", query->stmt);
474 for (i = 0; i < rows; ++i) {
477 for (j = 0; j < cols; ++j) {
478 c_psql_col_t col = query->cols[j];
480 char *value = PQgetvalue (res, i, j);
482 if (col.ds_type == DS_TYPE_COUNTER)
483 submit_counter (db, col.type, col.type_instance, value);
484 else if (col.ds_type == DS_TYPE_GAUGE)
485 submit_gauge (db, col.type, col.type_instance, value);
490 } /* c_psql_exec_query */
492 static int c_psql_read (void)
497 for (i = 0; i < databases_num; ++i) {
498 c_psql_database_t *db = databases + i;
502 assert (NULL != db->database);
504 if (0 != c_psql_check_connection (db))
507 for (j = 0; j < db->queries_num; ++j)
508 c_psql_exec_query (db, j);
518 static int c_psql_shutdown (void)
522 if ((NULL == databases) || (0 == databases_num))
525 plugin_unregister_read ("postgresql");
526 plugin_unregister_shutdown ("postgresql");
528 for (i = 0; i < databases_num; ++i) {
529 c_psql_database_t *db = databases + i;
530 c_psql_database_delete (db);
536 for (i = 0; i < queries_num; ++i) {
537 c_psql_query_t *query = queries + i;
538 c_psql_query_delete (query);
544 } /* c_psql_shutdown */
546 static int c_psql_init (void)
550 if ((NULL == databases) || (0 == databases_num))
553 for (i = 0; i < queries_num; ++i) {
554 c_psql_query_t *query = queries + i;
557 for (j = 0; j < query->cols_num; ++j) {
558 c_psql_col_t *col = query->cols + j;
559 const data_set_t *ds;
561 ds = plugin_get_ds (col->type);
563 log_err ("Column: Unknown type \"%s\".", col->type);
568 if (1 != ds->ds_num) {
569 log_err ("Column: Invalid type \"%s\" - types defining "
570 "one data source are supported only (got: %i).",
571 col->type, ds->ds_num);
576 col->ds_type = ds->ds[0].type;
580 for (i = 0; i < databases_num; ++i) {
581 c_psql_database_t *db = databases + i;
584 char *buf = conninfo;
585 int buf_len = sizeof (conninfo);
591 /* this will happen during reinitialization */
592 if (NULL != db->conn) {
593 c_psql_check_connection (db);
597 status = ssnprintf (buf, buf_len, "dbname = '%s'", db->database);
603 C_PSQL_PAR_APPEND (buf, buf_len, "host", db->host);
604 C_PSQL_PAR_APPEND (buf, buf_len, "port", db->port);
605 C_PSQL_PAR_APPEND (buf, buf_len, "user", db->user);
606 C_PSQL_PAR_APPEND (buf, buf_len, "password", db->password);
607 C_PSQL_PAR_APPEND (buf, buf_len, "sslmode", db->sslmode);
608 C_PSQL_PAR_APPEND (buf, buf_len, "krbsrvname", db->krbsrvname);
609 C_PSQL_PAR_APPEND (buf, buf_len, "service", db->service);
611 db->conn = PQconnectdb (conninfo);
612 if (0 != c_psql_check_connection (db))
615 db->proto_version = PQprotocolVersion (db->conn);
617 server_host = PQhost (db->conn);
618 server_version = PQserverVersion (db->conn);
619 log_info ("Sucessfully connected to database %s (user %s) "
620 "at server %s%s%s (server version: %d.%d.%d, "
621 "protocol version: %d, pid: %d)",
622 PQdb (db->conn), PQuser (db->conn),
623 C_PSQL_SOCKET3 (server_host, PQport (db->conn)),
624 C_PSQL_SERVER_VERSION3 (server_version),
625 db->proto_version, PQbackendPID (db->conn));
627 if (3 > db->proto_version)
628 log_warn ("Protocol version %d does not support parameters.",
631 c_psql_database_init (db, server_version);
634 plugin_register_read ("postgresql", c_psql_read);
635 plugin_register_shutdown ("postgresql", c_psql_shutdown);
639 static int config_set_s (char *name, char **var, const oconfig_item_t *ci)
641 if ((0 != ci->children_num) || (1 != ci->values_num)
642 || (OCONFIG_TYPE_STRING != ci->values[0].type)) {
643 log_err ("%s expects a single string argument.", name);
648 *var = sstrdup (ci->values[0].value.string);
652 static int config_set_i (char *name, int *var, const oconfig_item_t *ci)
654 if ((0 != ci->children_num) || (1 != ci->values_num)
655 || (OCONFIG_TYPE_NUMBER != ci->values[0].type)) {
656 log_err ("%s expects a single number argument.", name);
660 *var = (int)ci->values[0].value.number;
664 static int config_set_param (c_psql_query_t *query, const oconfig_item_t *ci)
666 c_psql_param_t param;
669 if ((0 != ci->children_num) || (1 != ci->values_num)
670 || (OCONFIG_TYPE_STRING != ci->values[0].type)) {
671 log_err ("Param expects a single string argument.");
675 param_str = ci->values[0].value.string;
676 if (0 == strcasecmp (param_str, "hostname"))
677 param = C_PSQL_PARAM_HOST;
678 else if (0 == strcasecmp (param_str, "database"))
679 param = C_PSQL_PARAM_DB;
680 else if (0 == strcasecmp (param_str, "username"))
681 param = C_PSQL_PARAM_USER;
683 log_err ("Invalid parameter \"%s\".", param_str);
688 if (NULL == (query->params = (c_psql_param_t *)realloc (query->params,
689 query->params_num * sizeof (*query->params)))) {
690 log_err ("Out of memory.");
694 query->params[query->params_num - 1] = param;
696 } /* config_set_param */
698 static int config_set_column (c_psql_query_t *query, const oconfig_item_t *ci)
704 if ((0 != ci->children_num)
705 || (1 > ci->values_num) || (2 < ci->values_num)) {
706 log_err ("Column expects either one or two arguments.");
710 for (i = 0; i < ci->values_num; ++i) {
711 if (OCONFIG_TYPE_STRING != ci->values[i].type) {
712 log_err ("Column expects either one or two string arguments.");
718 if (NULL == (query->cols = (c_psql_col_t *)realloc (query->cols,
719 query->cols_num * sizeof (*query->cols)))) {
720 log_err ("Out of memory.");
724 col = query->cols + query->cols_num - 1;
728 col->type = sstrdup (ci->values[0].value.string);
729 col->type_instance = (2 == ci->values_num)
730 ? sstrdup (ci->values[1].value.string) : NULL;
732 } /* config_set_column */
734 static int set_query (c_psql_database_t *db, const char *name)
736 c_psql_query_t *query;
738 query = c_psql_query_get (name, -1);
740 log_err ("Query \"%s\" not found - please check your configuration.",
746 if (NULL == (db->queries = (c_psql_query_t **)realloc (db->queries,
747 db->queries_num * sizeof (*db->queries)))) {
748 log_err ("Out of memory.");
752 if (query->params_num > db->max_params_num)
753 db->max_params_num = query->params_num;
755 db->queries[db->queries_num - 1] = query;
759 static int config_set_query (c_psql_database_t *db, const oconfig_item_t *ci)
761 if ((0 != ci->children_num) || (1 != ci->values_num)
762 || (OCONFIG_TYPE_STRING != ci->values[0].type)) {
763 log_err ("Query expects a single string argument.");
766 return set_query (db, ci->values[0].value.string);
767 } /* config_set_query */
769 static int c_psql_config_query (oconfig_item_t *ci)
771 c_psql_query_t *query;
775 if ((1 != ci->values_num)
776 || (OCONFIG_TYPE_STRING != ci->values[0].type)) {
777 log_err ("<Query> expects a single string argument.");
781 query = c_psql_query_new (ci->values[0].value.string);
783 for (i = 0; i < ci->children_num; ++i) {
784 oconfig_item_t *c = ci->children + i;
786 if (0 == strcasecmp (c->key, "Statement"))
787 config_set_s ("Statement", &query->stmt, c);
788 /* backwards compat for versions < 4.6 */
789 else if (0 == strcasecmp (c->key, "Query")) {
790 log_warn ("<Query>: 'Query' is deprecated - use 'Statement' instead.");
791 config_set_s ("Query", &query->stmt, c);
793 else if (0 == strcasecmp (c->key, "Param"))
794 config_set_param (query, c);
795 else if (0 == strcasecmp (c->key, "Column"))
796 config_set_column (query, c);
797 else if (0 == strcasecmp (c->key, "MinPGVersion"))
798 config_set_i ("MinPGVersion", &query->min_pg_version, c);
799 else if (0 == strcasecmp (c->key, "MaxPGVersion"))
800 config_set_i ("MaxPGVersion", &query->max_pg_version, c);
802 log_warn ("Ignoring unknown config key \"%s\".", c->key);
805 for (i = 0; i < queries_num - 1; ++i) {
806 c_psql_query_t *q = queries + i;
808 if ((0 == strcasecmp (q->name, query->name))
809 && (q->min_pg_version <= query->max_pg_version)
810 && (query->min_pg_version <= q->max_pg_version)) {
811 log_err ("Ignoring redefinition (with overlapping version ranges) "
812 "of query \"%s\".", query->name);
818 if (query->min_pg_version > query->max_pg_version) {
819 log_err ("Query \"%s\": MinPGVersion > MaxPGVersion.",
824 if (NULL == query->stmt) {
825 log_err ("Query \"%s\" does not include an SQL query statement - "
826 "please check your configuration.", query->name);
831 c_psql_query_delete (query);
836 } /* c_psql_config_query */
838 static int c_psql_config_database (oconfig_item_t *ci)
840 c_psql_database_t *db;
844 if ((1 != ci->values_num)
845 || (OCONFIG_TYPE_STRING != ci->values[0].type)) {
846 log_err ("<Database> expects a single string argument.");
850 db = c_psql_database_new (ci->values[0].value.string);
852 for (i = 0; i < ci->children_num; ++i) {
853 oconfig_item_t *c = ci->children + i;
855 if (0 == strcasecmp (c->key, "Host"))
856 config_set_s ("Host", &db->host, c);
857 else if (0 == strcasecmp (c->key, "Port"))
858 config_set_s ("Port", &db->port, c);
859 else if (0 == strcasecmp (c->key, "User"))
860 config_set_s ("User", &db->user, c);
861 else if (0 == strcasecmp (c->key, "Password"))
862 config_set_s ("Password", &db->password, c);
863 else if (0 == strcasecmp (c->key, "SSLMode"))
864 config_set_s ("SSLMode", &db->sslmode, c);
865 else if (0 == strcasecmp (c->key, "KRBSrvName"))
866 config_set_s ("KRBSrvName", &db->krbsrvname, c);
867 else if (0 == strcasecmp (c->key, "Service"))
868 config_set_s ("Service", &db->service, c);
869 else if (0 == strcasecmp (c->key, "Query"))
870 config_set_query (db, c);
872 log_warn ("Ignoring unknown config key \"%s\".", c->key);
875 if (NULL == db->queries) {
876 for (i = 0; i < def_queries_num; ++i)
877 set_query (db, def_queries[i]);
880 db->hidden_queries = (int *)calloc (db->queries_num,
881 sizeof (*db->hidden_queries));
882 if (NULL == db->hidden_queries) {
883 log_err ("Out of memory.");
887 } /* c_psql_config_database */
889 static int c_psql_config (oconfig_item_t *ci)
891 static int have_def_config = 0;
895 if (0 == have_def_config) {
900 c = oconfig_parse_file (C_PSQL_DEFAULT_CONF);
902 log_err ("Failed to read default config ("C_PSQL_DEFAULT_CONF").");
907 log_err ("Default config ("C_PSQL_DEFAULT_CONF") did not define "
908 "any queries - please check your installation.");
911 for (i = 0; i < ci->children_num; ++i) {
912 oconfig_item_t *c = ci->children + i;
914 if (0 == strcasecmp (c->key, "Query"))
915 c_psql_config_query (c);
916 else if (0 == strcasecmp (c->key, "Database"))
917 c_psql_config_database (c);
919 log_warn ("Ignoring unknown config key \"%s\".", c->key);
922 } /* c_psql_config */
924 void module_register (void)
926 plugin_register_complex_config ("postgresql", c_psql_config);
927 plugin_register_init ("postgresql", c_psql_init);
928 } /* module_register */
930 /* vim: set sw=4 ts=4 tw=78 noexpandtab : */