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 /* Appends the (parameter, value) pair to the string
42 * pointed to by 'buf' suitable to be used as argument
43 * for PQconnectdb(). If value equals NULL, the pair
45 #define C_PSQL_PAR_APPEND(buf, buf_len, parameter, value) \
46 if ((0 < (buf_len)) && (NULL != (value)) && ('\0' != *(value))) { \
47 int s = ssnprintf (buf, buf_len, " %s = '%s'", parameter, value); \
54 /* Returns the tuple (major, minor, patchlevel)
55 * for the given version number. */
56 #define C_PSQL_SERVER_VERSION3(server_version) \
57 (server_version) / 10000, \
58 (server_version) / 100 - (int)((server_version) / 10000) * 100, \
59 (server_version) - (int)((server_version) / 100) * 100
61 /* Returns true if the given host specifies a
62 * UNIX domain socket. */
63 #define C_PSQL_IS_UNIX_DOMAIN_SOCKET(host) \
64 ((NULL == (host)) || ('\0' == *(host)) || ('/' == *(host)))
66 /* Returns the tuple (host, delimiter, port) for a
67 * given (host, port) pair. Depending on the value of
68 * 'host' a UNIX domain socket or a TCP socket is
70 #define C_PSQL_SOCKET3(host, port) \
71 ((NULL == (host)) || ('\0' == *(host))) ? DEFAULT_PGSOCKET_DIR : host, \
72 C_PSQL_IS_UNIX_DOMAIN_SOCKET (host) ? "/.s.PGSQL." : ":", \
91 c_complain_t conn_complaint;
93 /* user configuration */
94 c_psql_query_t **queries;
110 static c_psql_query_t *queries = NULL;
111 static int queries_num = 0;
113 static c_psql_database_t *databases = NULL;
114 static int databases_num = 0;
116 static c_psql_query_t *c_psql_query_new (const char *name)
118 c_psql_query_t *query;
121 if (NULL == (queries = (c_psql_query_t *)realloc (queries,
122 queries_num * sizeof (*queries)))) {
123 log_err ("Out of memory.");
126 query = queries + queries_num - 1;
128 query->name = sstrdup (name);
134 } /* c_psql_query_new */
136 static void c_psql_query_delete (c_psql_query_t *query)
141 sfree (query->query);
143 for (i = 0; i < query->cols_num; ++i) {
144 sfree (query->cols[i].type);
145 sfree (query->cols[i].type_instance);
150 } /* c_psql_query_delete */
152 static c_psql_query_t *c_psql_query_get (const char *name)
156 for (i = 0; i < queries_num; ++i)
157 if (0 == strcasecmp (name, queries[i].name))
160 } /* c_psql_query_get */
162 static c_psql_database_t *c_psql_database_new (const char *name)
164 c_psql_database_t *db;
167 if (NULL == (databases = (c_psql_database_t *)realloc (databases,
168 databases_num * sizeof (*databases)))) {
169 log_err ("Out of memory.");
173 db = databases + (databases_num - 1);
177 db->conn_complaint.last = 0;
178 db->conn_complaint.interval = 0;
183 db->database = sstrdup (name);
191 db->krbsrvname = NULL;
195 } /* c_psql_database_new */
197 static void c_psql_database_delete (c_psql_database_t *db)
204 sfree (db->database);
208 sfree (db->password);
212 sfree (db->krbsrvname);
216 } /* c_psql_database_delete */
218 static void submit (const c_psql_database_t *db,
219 const char *type, const char *type_instance,
220 value_t *values, size_t values_len)
222 value_list_t vl = VALUE_LIST_INIT;
225 vl.values_len = values_len;
226 vl.time = time (NULL);
228 if (C_PSQL_IS_UNIX_DOMAIN_SOCKET (db->host)
229 || (0 == strcmp (db->host, "localhost")))
230 sstrncpy (vl.host, hostname_g, sizeof (vl.host));
232 sstrncpy (vl.host, db->host, sizeof (vl.host));
234 sstrncpy (vl.plugin, "postgresql", sizeof (vl.plugin));
235 sstrncpy (vl.plugin_instance, db->database, sizeof (vl.plugin_instance));
237 sstrncpy (vl.type, type, sizeof (vl.type));
239 if (NULL != type_instance)
240 sstrncpy (vl.type_instance, type_instance, sizeof (vl.type_instance));
242 plugin_dispatch_values (&vl);
246 static void submit_counter (const c_psql_database_t *db,
247 const char *type, const char *type_instance,
252 if ((NULL == value) || ('\0' == *value))
255 values[0].counter = atoll (value);
256 submit (db, type, type_instance, values, 1);
258 } /* submit_counter */
260 static void submit_gauge (const c_psql_database_t *db,
261 const char *type, const char *type_instance,
266 if ((NULL == value) || ('\0' == *value))
269 values[0].gauge = atof (value);
270 submit (db, type, type_instance, values, 1);
274 static int c_psql_check_connection (c_psql_database_t *db)
277 PQclear (PQexec (db->conn, "SELECT 42;"));
279 if (CONNECTION_OK != PQstatus (db->conn)) {
282 /* trigger c_release() */
283 if (0 == db->conn_complaint.interval)
284 db->conn_complaint.interval = 1;
286 if (CONNECTION_OK != PQstatus (db->conn)) {
287 c_complain (LOG_ERR, &db->conn_complaint,
288 "Failed to connect to database %s: %s",
289 db->database, PQerrorMessage (db->conn));
294 c_release (LOG_INFO, &db->conn_complaint,
295 "Successfully reconnected to database %s", PQdb (db->conn));
297 } /* c_psql_check_connection */
299 static int c_psql_exec_query (c_psql_database_t *db, int idx)
301 c_psql_query_t *query;
307 if (idx >= db->queries_num)
310 query = db->queries[idx];
312 res = PQexec (db->conn, query->query);
314 if (PGRES_TUPLES_OK != PQresultStatus (res)) {
315 log_err ("Failed to execute SQL query: %s",
316 PQerrorMessage (db->conn));
317 log_info ("SQL query was: %s", query->query);
322 rows = PQntuples (res);
326 cols = PQnfields (res);
327 if (query->cols_num != cols) {
328 log_err ("SQL query returned wrong number of fields "
329 "(expected: %i, got: %i)", query->cols_num, cols);
330 log_info ("SQL query was: %s", query->query);
334 for (i = 0; i < rows; ++i) {
337 for (j = 0; j < cols; ++j) {
338 c_psql_col_t col = query->cols[j];
340 char *value = PQgetvalue (res, i, j);
342 if (col.ds_type == DS_TYPE_COUNTER)
343 submit_counter (db, col.type, col.type_instance, value);
344 else if (col.ds_type == DS_TYPE_GAUGE)
345 submit_gauge (db, col.type, col.type_instance, value);
349 } /* c_psql_exec_query */
351 static int c_psql_stat_database (c_psql_database_t *db)
353 const char *const query =
354 "SELECT numbackends, xact_commit, xact_rollback "
355 "FROM pg_stat_database "
356 "WHERE datname = $1;";
362 res = PQexecParams (db->conn, query, /* number of parameters */ 1,
363 NULL, (const char *const *)&db->database, NULL, NULL,
364 /* return text data */ 0);
366 if (PGRES_TUPLES_OK != PQresultStatus (res)) {
367 log_err ("Failed to execute SQL query: %s",
368 PQerrorMessage (db->conn));
369 log_info ("SQL query was: %s", query);
376 log_warn ("pg_stat_database has more than one entry "
377 "for database %s - ignoring additional results.",
381 log_err ("pg_stat_database has no entry for database %s",
387 submit_gauge (db, "pg_numbackends", NULL, PQgetvalue (res, 0, 0));
389 submit_counter (db, "pg_xact", "commit", PQgetvalue (res, 0, 1));
390 submit_counter (db, "pg_xact", "rollback", PQgetvalue (res, 0, 2));
394 } /* c_psql_stat_database */
396 static int c_psql_stat_user_tables (c_psql_database_t *db)
398 const char *const query =
399 "SELECT sum(seq_scan), sum(seq_tup_read), "
400 "sum(idx_scan), sum(idx_tup_fetch), "
401 "sum(n_tup_ins), sum(n_tup_upd), sum(n_tup_del), "
402 "sum(n_tup_hot_upd), sum(n_live_tup), sum(n_dead_tup) "
403 "FROM pg_stat_user_tables;";
409 res = PQexec (db->conn, query);
411 if (PGRES_TUPLES_OK != PQresultStatus (res)) {
412 log_err ("Failed to execute SQL query: %s",
413 PQerrorMessage (db->conn));
414 log_info ("SQL query was: %s", query);
422 if (1 > n) /* no user tables */
425 submit_counter (db, "pg_scan", "seq", PQgetvalue (res, 0, 0));
426 submit_counter (db, "pg_scan", "seq_tup_read", PQgetvalue (res, 0, 1));
427 submit_counter (db, "pg_scan", "idx", PQgetvalue (res, 0, 2));
428 submit_counter (db, "pg_scan", "idx_tup_fetch", PQgetvalue (res, 0, 3));
430 submit_counter (db, "pg_n_tup_c", "ins", PQgetvalue (res, 0, 4));
431 submit_counter (db, "pg_n_tup_c", "upd", PQgetvalue (res, 0, 5));
432 submit_counter (db, "pg_n_tup_c", "del", PQgetvalue (res, 0, 6));
433 submit_counter (db, "pg_n_tup_c", "hot_upd", PQgetvalue (res, 0, 7));
435 submit_gauge (db, "pg_n_tup_g", "live", PQgetvalue (res, 0, 8));
436 submit_gauge (db, "pg_n_tup_g", "dead", PQgetvalue (res, 0, 9));
440 } /* c_psql_stat_user_tables */
442 static int c_psql_statio_user_tables (c_psql_database_t *db)
444 const char *const query =
445 "SELECT sum(heap_blks_read), sum(heap_blks_hit), "
446 "sum(idx_blks_read), sum(idx_blks_hit), "
447 "sum(toast_blks_read), sum(toast_blks_hit), "
448 "sum(tidx_blks_read), sum(tidx_blks_hit) "
449 "FROM pg_statio_user_tables;";
455 res = PQexec (db->conn, query);
457 if (PGRES_TUPLES_OK != PQresultStatus (res)) {
458 log_err ("Failed to execute SQL query: %s",
459 PQerrorMessage (db->conn));
460 log_info ("SQL query was: %s", query);
468 if (1 > n) /* no user tables */
471 submit_counter (db, "pg_blks", "heap_read", PQgetvalue (res, 0, 0));
472 submit_counter (db, "pg_blks", "heap_hit", PQgetvalue (res, 0, 1));
474 submit_counter (db, "pg_blks", "idx_read", PQgetvalue (res, 0, 2));
475 submit_counter (db, "pg_blks", "idx_hit", PQgetvalue (res, 0, 3));
477 submit_counter (db, "pg_blks", "toast_read", PQgetvalue (res, 0, 4));
478 submit_counter (db, "pg_blks", "toast_hit", PQgetvalue (res, 0, 5));
480 submit_counter (db, "pg_blks", "tidx_read", PQgetvalue (res, 0, 6));
481 submit_counter (db, "pg_blks", "tidx_hit", PQgetvalue (res, 0, 7));
485 } /* c_psql_statio_user_tables */
487 static int c_psql_read (void)
492 for (i = 0; i < databases_num; ++i) {
493 c_psql_database_t *db = databases + i;
497 assert (NULL != db->database);
499 if (0 != c_psql_check_connection (db))
502 c_psql_stat_database (db);
503 c_psql_stat_user_tables (db);
504 c_psql_statio_user_tables (db);
506 for (j = 0; j < db->queries_num; ++j)
507 c_psql_exec_query (db, j);
517 static int c_psql_shutdown (void)
521 if ((NULL == databases) || (0 == databases_num))
524 plugin_unregister_read ("postgresql");
525 plugin_unregister_shutdown ("postgresql");
527 for (i = 0; i < databases_num; ++i) {
528 c_psql_database_t *db = databases + i;
529 c_psql_database_delete (db);
535 for (i = 0; i < queries_num; ++i) {
536 c_psql_query_t *query = queries + i;
537 c_psql_query_delete (query);
543 } /* c_psql_shutdown */
545 static int c_psql_init (void)
549 if ((NULL == databases) || (0 == databases_num))
552 for (i = 0; i < queries_num; ++i) {
553 c_psql_query_t *query = queries + i;
556 for (j = 0; j < query->cols_num; ++j) {
557 c_psql_col_t *col = query->cols + j;
558 const data_set_t *ds;
560 ds = plugin_get_ds (col->type);
562 log_err ("Column: Unknown type \"%s\".", col->type);
567 if (1 != ds->ds_num) {
568 log_err ("Column: Invalid type \"%s\" - types defining "
569 "one data source are supported only (got: %i).",
570 col->type, ds->ds_num);
575 col->ds_type = ds->ds[0].type;
579 for (i = 0; i < databases_num; ++i) {
580 c_psql_database_t *db = databases + i;
583 char *buf = conninfo;
584 int buf_len = sizeof (conninfo);
590 status = ssnprintf (buf, buf_len, "dbname = '%s'", db->database);
596 C_PSQL_PAR_APPEND (buf, buf_len, "host", db->host);
597 C_PSQL_PAR_APPEND (buf, buf_len, "port", db->port);
598 C_PSQL_PAR_APPEND (buf, buf_len, "user", db->user);
599 C_PSQL_PAR_APPEND (buf, buf_len, "password", db->password);
600 C_PSQL_PAR_APPEND (buf, buf_len, "sslmode", db->sslmode);
601 C_PSQL_PAR_APPEND (buf, buf_len, "krbsrvname", db->krbsrvname);
602 C_PSQL_PAR_APPEND (buf, buf_len, "service", db->service);
604 db->conn = PQconnectdb (conninfo);
605 if (0 != c_psql_check_connection (db))
608 server_host = PQhost (db->conn);
609 server_version = PQserverVersion (db->conn);
610 log_info ("Sucessfully connected to database %s (user %s) "
611 "at server %s%s%s (server version: %d.%d.%d, "
612 "protocol version: %d, pid: %d)",
613 PQdb (db->conn), PQuser (db->conn),
614 C_PSQL_SOCKET3 (server_host, PQport (db->conn)),
615 C_PSQL_SERVER_VERSION3 (server_version),
616 PQprotocolVersion (db->conn), PQbackendPID (db->conn));
619 plugin_register_read ("postgresql", c_psql_read);
620 plugin_register_shutdown ("postgresql", c_psql_shutdown);
624 static int config_set (char *name, char **var, const oconfig_item_t *ci)
626 if ((0 != ci->children_num) || (1 != ci->values_num)
627 || (OCONFIG_TYPE_STRING != ci->values[0].type)) {
628 log_err ("%s expects a single string argument.", name);
633 *var = sstrdup (ci->values[0].value.string);
637 static int config_set_column (c_psql_query_t *query, const oconfig_item_t *ci)
643 if ((0 != ci->children_num)
644 || (1 > ci->values_num) || (2 < ci->values_num)) {
645 log_err ("Column expects either one or two arguments.");
649 for (i = 0; i < ci->values_num; ++i) {
650 if (OCONFIG_TYPE_STRING != ci->values[i].type) {
651 log_err ("Column expects either one or two string arguments.");
657 if (NULL == (query->cols = (c_psql_col_t *)realloc (query->cols,
658 query->cols_num * sizeof (*query->cols)))) {
659 log_err ("Out of memory.");
663 col = query->cols + query->cols_num - 1;
667 col->type = sstrdup (ci->values[0].value.string);
668 col->type_instance = (2 == ci->values_num)
669 ? sstrdup (ci->values[1].value.string) : NULL;
671 } /* config_set_column */
673 static int config_set_query (c_psql_database_t *db, const oconfig_item_t *ci)
675 c_psql_query_t *query;
677 if ((0 != ci->children_num) || (1 != ci->values_num)
678 || (OCONFIG_TYPE_STRING != ci->values[0].type)) {
679 log_err ("Query expects a single string argument.");
683 query = c_psql_query_get (ci->values[0].value.string);
685 log_err ("Query \"%s\" not found - please check your configuration.",
686 ci->values[0].value.string);
691 if (NULL == (db->queries = (c_psql_query_t **)realloc (db->queries,
692 db->queries_num * sizeof (*db->queries)))) {
693 log_err ("Out of memory.");
697 db->queries[db->queries_num - 1] = query;
699 } /* config_set_query */
701 static int c_psql_config_query (oconfig_item_t *ci)
703 c_psql_query_t *query;
707 if ((1 != ci->values_num)
708 || (OCONFIG_TYPE_STRING != ci->values[0].type)) {
709 log_err ("<Query> expects a single string argument.");
713 query = c_psql_query_new (ci->values[0].value.string);
715 for (i = 0; i < ci->children_num; ++i) {
716 oconfig_item_t *c = ci->children + i;
718 if (0 == strcasecmp (c->key, "Query"))
719 config_set ("Query", &query->query, c);
720 else if (0 == strcasecmp (c->key, "Column"))
721 config_set_column (query, c);
723 log_warn ("Ignoring unknown config key \"%s\".", c->key);
726 } /* c_psql_config_query */
728 static int c_psql_config_database (oconfig_item_t *ci)
730 c_psql_database_t *db;
734 if ((1 != ci->values_num)
735 || (OCONFIG_TYPE_STRING != ci->values[0].type)) {
736 log_err ("<Database> expects a single string argument.");
740 db = c_psql_database_new (ci->values[0].value.string);
742 for (i = 0; i < ci->children_num; ++i) {
743 oconfig_item_t *c = ci->children + i;
745 if (0 == strcasecmp (c->key, "Host"))
746 config_set ("Host", &db->host, c);
747 else if (0 == strcasecmp (c->key, "Port"))
748 config_set ("Port", &db->port, c);
749 else if (0 == strcasecmp (c->key, "User"))
750 config_set ("User", &db->user, c);
751 else if (0 == strcasecmp (c->key, "Password"))
752 config_set ("Password", &db->password, c);
753 else if (0 == strcasecmp (c->key, "SSLMode"))
754 config_set ("SSLMode", &db->sslmode, c);
755 else if (0 == strcasecmp (c->key, "KRBSrvName"))
756 config_set ("KRBSrvName", &db->krbsrvname, c);
757 else if (0 == strcasecmp (c->key, "Service"))
758 config_set ("Service", &db->service, c);
759 else if (0 == strcasecmp (c->key, "Query"))
760 config_set_query (db, c);
762 log_warn ("Ignoring unknown config key \"%s\".", c->key);
767 static int c_psql_config (oconfig_item_t *ci)
771 for (i = 0; i < ci->children_num; ++i) {
772 oconfig_item_t *c = ci->children + i;
774 if (0 == strcasecmp (c->key, "Query"))
775 c_psql_config_query (c);
776 else if (0 == strcasecmp (c->key, "Database"))
777 c_psql_config_database (c);
779 log_warn ("Ignoring unknown config key \"%s\".", c->key);
782 } /* c_psql_config */
784 void module_register (void)
786 plugin_register_complex_config ("postgresql", c_psql_config);
787 plugin_register_init ("postgresql", c_psql_init);
788 } /* module_register */
790 /* vim: set sw=4 ts=4 tw=78 noexpandtab : */