e477348836a98a081400eacd959fe52eee2f88b7
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 /* 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
44 * is ignored. */
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); \
48 if (0 < s) { \
49 buf += s; \
50 buf_len -= s; \
51 } \
52 }
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
69 * assumed. */
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." : ":", \
73 port
75 typedef struct {
76 PGconn *conn;
77 c_complain_t conn_complaint;
79 /* user configuration */
80 char *host;
81 char *port;
82 char *database;
83 char *user;
84 char *password;
86 char *sslmode;
88 char *krbsrvname;
90 char *service;
91 } c_psql_database_t;
93 static c_psql_database_t *databases = NULL;
94 static int databases_num = 0;
96 static c_psql_database_t *c_psql_database_new (const char *name)
97 {
98 c_psql_database_t *db;
100 ++databases_num;
101 if (NULL == (databases = (c_psql_database_t *)realloc (databases,
102 databases_num * sizeof (*databases)))) {
103 log_err ("Out of memory.");
104 exit (5);
105 }
107 db = databases + (databases_num - 1);
109 db->conn = NULL;
111 db->conn_complaint.last = 0;
112 db->conn_complaint.interval = 0;
114 db->database = sstrdup (name);
115 db->host = NULL;
116 db->port = NULL;
117 db->user = NULL;
118 db->password = NULL;
120 db->sslmode = NULL;
122 db->krbsrvname = NULL;
124 db->service = NULL;
125 return db;
126 } /* c_psql_database_new */
128 static void c_psql_database_delete (c_psql_database_t *db)
129 {
130 PQfinish (db->conn);
132 sfree (db->database);
133 sfree (db->host);
134 sfree (db->port);
135 sfree (db->user);
136 sfree (db->password);
138 sfree (db->sslmode);
140 sfree (db->krbsrvname);
142 sfree (db->service);
143 return;
144 } /* c_psql_database_delete */
146 static void submit (const c_psql_database_t *db,
147 const char *type, const char *type_instance,
148 value_t *values, size_t values_len)
149 {
150 value_list_t vl = VALUE_LIST_INIT;
152 vl.values = values;
153 vl.values_len = values_len;
154 vl.time = time (NULL);
156 if (C_PSQL_IS_UNIX_DOMAIN_SOCKET (db->host)
157 || (0 == strcmp (db->host, "localhost")))
158 sstrncpy (vl.host, hostname_g, sizeof (vl.host));
159 else
160 sstrncpy (vl.host, db->host, sizeof (vl.host));
162 sstrncpy (vl.plugin, "postgresql", sizeof (vl.plugin));
163 sstrncpy (vl.plugin_instance, db->database, sizeof (vl.plugin_instance));
165 sstrncpy (vl.type, type, sizeof (vl.type));
167 if (NULL != type_instance)
168 sstrncpy (vl.type_instance, type_instance, sizeof (vl.type_instance));
170 plugin_dispatch_values (&vl);
171 return;
172 } /* submit */
174 static void submit_counter (const c_psql_database_t *db,
175 const char *type, const char *type_instance,
176 const char *value)
177 {
178 value_t values[1];
180 if ((NULL == value) || ('\0' == *value))
181 return;
183 values[0].counter = atoll (value);
184 submit (db, type, type_instance, values, 1);
185 return;
186 } /* submit_counter */
188 static void submit_gauge (const c_psql_database_t *db,
189 const char *type, const char *type_instance,
190 const char *value)
191 {
192 value_t values[1];
194 if ((NULL == value) || ('\0' == *value))
195 return;
197 values[0].gauge = atof (value);
198 submit (db, type, type_instance, values, 1);
199 return;
200 } /* submit_gauge */
202 static int c_psql_check_connection (c_psql_database_t *db)
203 {
204 /* "ping" */
205 PQclear (PQexec (db->conn, "SELECT 42;"));
207 if (CONNECTION_OK != PQstatus (db->conn)) {
208 PQreset (db->conn);
210 /* trigger c_release() */
211 if (0 == db->conn_complaint.interval)
212 db->conn_complaint.interval = 1;
214 if (CONNECTION_OK != PQstatus (db->conn)) {
215 c_complain (LOG_ERR, &db->conn_complaint,
216 "Failed to connect to database %s: %s",
217 db->database, PQerrorMessage (db->conn));
218 return -1;
219 }
220 }
222 c_release (LOG_INFO, &db->conn_complaint,
223 "Successfully reconnected to database %s", PQdb (db->conn));
224 return 0;
225 } /* c_psql_check_connection */
227 static int c_psql_stat_database (c_psql_database_t *db)
228 {
229 const char *const query =
230 "SELECT numbackends, xact_commit, xact_rollback "
231 "FROM pg_stat_database "
232 "WHERE datname = $1;";
234 PGresult *res;
236 int n;
238 res = PQexecParams (db->conn, query, /* number of parameters */ 1,
239 NULL, (const char *const *)&db->database, NULL, NULL,
240 /* return text data */ 0);
242 if (PGRES_TUPLES_OK != PQresultStatus (res)) {
243 log_err ("Failed to execute SQL query: %s",
244 PQerrorMessage (db->conn));
245 log_info ("SQL query was: %s", query);
246 PQclear (res);
247 return -1;
248 }
250 n = PQntuples (res);
251 if (1 < n) {
252 log_warn ("pg_stat_database has more than one entry "
253 "for database %s - ignoring additional results.",
254 db->database);
255 }
256 else if (1 > n) {
257 log_err ("pg_stat_database has no entry for database %s",
258 db->database);
259 PQclear (res);
260 return -1;
261 }
263 submit_gauge (db, "pg_numbackends", NULL, PQgetvalue (res, 0, 0));
265 submit_counter (db, "pg_xact", "commit", PQgetvalue (res, 0, 1));
266 submit_counter (db, "pg_xact", "rollback", PQgetvalue (res, 0, 2));
268 PQclear (res);
269 return 0;
270 } /* c_psql_stat_database */
272 static int c_psql_stat_user_tables (c_psql_database_t *db)
273 {
274 const char *const query =
275 "SELECT sum(seq_scan), sum(seq_tup_read), "
276 "sum(idx_scan), sum(idx_tup_fetch), "
277 "sum(n_tup_ins), sum(n_tup_upd), sum(n_tup_del), "
278 "sum(n_tup_hot_upd), sum(n_live_tup), sum(n_dead_tup) "
279 "FROM pg_stat_user_tables;";
281 PGresult *res;
283 int n;
285 res = PQexec (db->conn, query);
287 if (PGRES_TUPLES_OK != PQresultStatus (res)) {
288 log_err ("Failed to execute SQL query: %s",
289 PQerrorMessage (db->conn));
290 log_info ("SQL query was: %s", query);
291 PQclear (res);
292 return -1;
293 }
295 n = PQntuples (res);
296 assert (1 >= n);
298 if (1 > n) /* no user tables */
299 return 0;
301 submit_counter (db, "pg_scan", "seq", PQgetvalue (res, 0, 0));
302 submit_counter (db, "pg_scan", "seq_tup_read", PQgetvalue (res, 0, 1));
303 submit_counter (db, "pg_scan", "idx", PQgetvalue (res, 0, 2));
304 submit_counter (db, "pg_scan", "idx_tup_fetch", PQgetvalue (res, 0, 3));
306 submit_counter (db, "pg_n_tup_c", "ins", PQgetvalue (res, 0, 4));
307 submit_counter (db, "pg_n_tup_c", "upd", PQgetvalue (res, 0, 5));
308 submit_counter (db, "pg_n_tup_c", "del", PQgetvalue (res, 0, 6));
309 submit_counter (db, "pg_n_tup_c", "hot_upd", PQgetvalue (res, 0, 7));
311 submit_gauge (db, "pg_n_tup_g", "live", PQgetvalue (res, 0, 8));
312 submit_gauge (db, "pg_n_tup_g", "dead", PQgetvalue (res, 0, 9));
314 PQclear (res);
315 return 0;
316 } /* c_psql_stat_user_tables */
318 static int c_psql_statio_user_tables (c_psql_database_t *db)
319 {
320 const char *const query =
321 "SELECT sum(heap_blks_read), sum(heap_blks_hit), "
322 "sum(idx_blks_read), sum(idx_blks_hit), "
323 "sum(toast_blks_read), sum(toast_blks_hit), "
324 "sum(tidx_blks_read), sum(tidx_blks_hit) "
325 "FROM pg_statio_user_tables;";
327 PGresult *res;
329 int n;
331 res = PQexec (db->conn, query);
333 if (PGRES_TUPLES_OK != PQresultStatus (res)) {
334 log_err ("Failed to execute SQL query: %s",
335 PQerrorMessage (db->conn));
336 log_info ("SQL query was: %s", query);
337 PQclear (res);
338 return -1;
339 }
341 n = PQntuples (res);
342 assert (1 >= n);
344 if (1 > n) /* no user tables */
345 return 0;
347 submit_counter (db, "pg_blks", "heap_read", PQgetvalue (res, 0, 0));
348 submit_counter (db, "pg_blks", "heap_hit", PQgetvalue (res, 0, 1));
350 submit_counter (db, "pg_blks", "idx_read", PQgetvalue (res, 0, 2));
351 submit_counter (db, "pg_blks", "idx_hit", PQgetvalue (res, 0, 3));
353 submit_counter (db, "pg_blks", "toast_read", PQgetvalue (res, 0, 4));
354 submit_counter (db, "pg_blks", "toast_hit", PQgetvalue (res, 0, 5));
356 submit_counter (db, "pg_blks", "tidx_read", PQgetvalue (res, 0, 6));
357 submit_counter (db, "pg_blks", "tidx_hit", PQgetvalue (res, 0, 7));
359 PQclear (res);
360 return 0;
361 } /* c_psql_statio_user_tables */
363 static int c_psql_read (void)
364 {
365 int success = 0;
366 int i;
368 for (i = 0; i < databases_num; ++i) {
369 c_psql_database_t *db = databases + i;
371 assert (NULL != db->database);
373 if (0 != c_psql_check_connection (db))
374 continue;
376 c_psql_stat_database (db);
377 c_psql_stat_user_tables (db);
378 c_psql_statio_user_tables (db);
380 ++success;
381 }
383 if (! success)
384 return -1;
385 return 0;
386 } /* c_psql_read */
388 static int c_psql_shutdown (void)
389 {
390 int i;
392 if ((NULL == databases) || (0 == databases_num))
393 return 0;
395 plugin_unregister_read ("postgresql");
396 plugin_unregister_shutdown ("postgresql");
398 for (i = 0; i < databases_num; ++i) {
399 c_psql_database_t *db = databases + i;
400 c_psql_database_delete (db);
401 }
403 sfree (databases);
404 databases_num = 0;
405 return 0;
406 } /* c_psql_shutdown */
408 static int c_psql_init (void)
409 {
410 int i;
412 if ((NULL == databases) || (0 == databases_num))
413 return 0;
415 for (i = 0; i < databases_num; ++i) {
416 c_psql_database_t *db = databases + i;
418 char conninfo[4096];
419 char *buf = conninfo;
420 int buf_len = sizeof (conninfo);
421 int status;
423 char *server_host;
424 int server_version;
426 status = ssnprintf (buf, buf_len, "dbname = '%s'", db->database);
427 if (0 < status) {
428 buf += status;
429 buf_len -= status;
430 }
432 C_PSQL_PAR_APPEND (buf, buf_len, "host", db->host);
433 C_PSQL_PAR_APPEND (buf, buf_len, "port", db->port);
434 C_PSQL_PAR_APPEND (buf, buf_len, "user", db->user);
435 C_PSQL_PAR_APPEND (buf, buf_len, "password", db->password);
436 C_PSQL_PAR_APPEND (buf, buf_len, "sslmode", db->sslmode);
437 C_PSQL_PAR_APPEND (buf, buf_len, "krbsrvname", db->krbsrvname);
438 C_PSQL_PAR_APPEND (buf, buf_len, "service", db->service);
440 db->conn = PQconnectdb (conninfo);
441 if (0 != c_psql_check_connection (db))
442 continue;
444 server_host = PQhost (db->conn);
445 server_version = PQserverVersion (db->conn);
446 log_info ("Sucessfully connected to database %s (user %s) "
447 "at server %s%s%s (server version: %d.%d.%d, "
448 "protocol version: %d, pid: %d)",
449 PQdb (db->conn), PQuser (db->conn),
450 C_PSQL_SOCKET3(server_host, PQport (db->conn)),
451 C_PSQL_SERVER_VERSION3 (server_version),
452 PQprotocolVersion (db->conn), PQbackendPID (db->conn));
453 }
455 plugin_register_read ("postgresql", c_psql_read);
456 plugin_register_shutdown ("postgresql", c_psql_shutdown);
457 return 0;
458 } /* c_psql_init */
460 static int config_set (char *name, char **var, const oconfig_item_t *ci)
461 {
462 if ((0 != ci->children_num) || (1 != ci->values_num)
463 || (OCONFIG_TYPE_STRING != ci->values[0].type)) {
464 log_err ("%s expects a single string argument.", name);
465 return 1;
466 }
468 sfree (*var);
469 *var = sstrdup (ci->values[0].value.string);
470 return 0;
471 } /* config_set */
473 static int c_psql_config_database (oconfig_item_t *ci)
474 {
475 c_psql_database_t *db;
477 int i;
479 if ((1 != ci->values_num)
480 || (OCONFIG_TYPE_STRING != ci->values[0].type)) {
481 log_err ("<Database> expects a single string argument.");
482 return 1;
483 }
485 db = c_psql_database_new (ci->values[0].value.string);
487 for (i = 0; i < ci->children_num; ++i) {
488 oconfig_item_t *c = ci->children + i;
490 if (0 == strcasecmp (c->key, "Host"))
491 config_set ("Host", &db->host, c);
492 else if (0 == strcasecmp (c->key, "Port"))
493 config_set ("Port", &db->port, c);
494 else if (0 == strcasecmp (c->key, "User"))
495 config_set ("User", &db->user, c);
496 else if (0 == strcasecmp (c->key, "Password"))
497 config_set ("Password", &db->password, c);
498 else if (0 == strcasecmp (c->key, "SSLMode"))
499 config_set ("SSLMode", &db->sslmode, c);
500 else if (0 == strcasecmp (c->key, "KRBSrvName"))
501 config_set ("KRBSrvName", &db->krbsrvname, c);
502 else if (0 == strcasecmp (c->key, "Service"))
503 config_set ("Service", &db->service, c);
504 else
505 log_warn ("Ignoring unknown config key \"%s\".", c->key);
506 }
507 return 0;
508 }
510 static int c_psql_config (oconfig_item_t *ci)
511 {
512 int i;
514 for (i = 0; i < ci->children_num; ++i) {
515 oconfig_item_t *c = ci->children + i;
517 if (0 == strcasecmp (c->key, "Database"))
518 c_psql_config_database (c);
519 else
520 log_warn ("Ignoring unknown config key \"%s\".", c->key);
521 }
522 return 0;
523 } /* c_psql_config */
525 void module_register (void)
526 {
527 plugin_register_complex_config ("postgresql", c_psql_config);
528 plugin_register_init ("postgresql", c_psql_init);
529 } /* module_register */
531 /* vim: set sw=4 ts=4 tw=78 noexpandtab : */