[collectd] [PATCH] postgresql: Added a plugin to collect PostgreSQL database statistics.
Sebastian Harl
sh at tokkee.org
Sat Jul 5 12:03:51 CEST 2008
This plugin collects active server connections, transaction numbers, block IO
and table row manipulations of PostgreSQL databases. For this purpose it keeps
persistent connections to all configured databases. The statistics are
collected from PostgreSQL's "statistics collector" subsystem.
Currently, statistics are collected from the pg_stat_database,
pg_stat_user_tables and pg_statio_user_tables views. The following new types
have been introduced: pg_blks, pg_n_tup_c, pg_n_tup_g, pg_numbackends, pg_scan
and pg_xact. The type names are basically the same as the column names in the
queried views to keep things consistent. For the number of tuples, counter and
gauge values exist, so the type names have been prefixed with "_c" and "_g"
respectively.
Sample plugin configuration:
<Plugin postgresql>
<Database foo>
Host "hostname"
Port "5432"
User "username"
Password "secret"
SSLMode "prefer"
KRBSrvName "kerberos_service_name"
</Database>
<Database bar>
Service "service_name"
</Database>
</Plugin>
Signed-off-by: Sebastian Harl <sh at tokkee.org>
---
So far, I've just done some basic tests with version 8.3 of PostgreSQL.
So, any feedback would be appreciated. I will test with version 8.1 some
time during the next days.
---
README | 7 +
configure.in | 90 +++++++++
src/Makefile.am | 10 +
src/collectd.conf.in | 17 ++
src/collectd.conf.pod | 105 ++++++++++
src/postgresql.c | 519 +++++++++++++++++++++++++++++++++++++++++++++++++
src/types.db | 6 +
7 files changed, 754 insertions(+), 0 deletions(-)
create mode 100644 src/postgresql.c
diff --git a/README b/README
index ca2bdf6..97119ac 100644
--- a/README
+++ b/README
@@ -141,6 +141,10 @@ Features
Network latency: Time to reach the default gateway or another given
host.
+ - postgresql
+ PostgreSQL database statistics: active server connections, transaction
+ numbers, block IO, table row manipulations.
+
- processes
Process counts: Number of running, sleeping, zombie, ... processes.
@@ -364,6 +368,9 @@ Prerequisites
Obviously used by the `perl' plugin. The library has to be compiled with
ithread support (introduced in Perl 5.6.0).
+ * libpq (optional)
+ The PostgreSQL C client library used by the `postgresql' plugin.
+
* librrd (optional; headers and library; rrdtool 1.0 and 1.2 both work fine)
If built without `librrd' the resulting binary will be `client only', i.e.
will send its values via multicast and not create any RRD files itself.
diff --git a/configure.in b/configure.in
index 73fa841..6873d8f 100644
--- a/configure.in
+++ b/configure.in
@@ -2113,6 +2113,93 @@ then
AC_SUBST(BUILD_WITH_OPENIPMI_LIBS)
fi
+dnl Check for libpq.
+with_pg_config="pg_config"
+with_libpq_includedir=""
+with_libpq_libdir=""
+with_libpq_cppflags=""
+with_libpq_ldflags=""
+AC_ARG_WITH(libpq, [AS_HELP_STRING([--with-libpq@<:@=PREFIX@:>@],
+ [Path to libpq.])],
+[
+ if test "x$withval" = "xno"
+ then
+ with_libpq="no"
+ else if test "x$withval" = "xyes"
+ then
+ with_libpq="yes"
+ else
+ if test -f "$withval" && test -x "$withval";
+ then
+ with_pg_config="$withval"
+ else if test -x "$withval/bin/pg_config"
+ then
+ with_pg_config="$withval/bin/pg_config"
+ fi; fi
+ with_libpq="yes"
+ fi; fi
+],
+[
+ with_libpq="yes"
+])
+if test "x$with_libpq" = "xyes"
+then
+ with_libpq_includedir=`$with_pg_config --includedir 2> /dev/null`
+ pg_config_status=$?
+
+ if test $pg_config_status -eq 0
+ then
+ if test -n "$with_libpq_includedir"; then
+ for dir in $with_libpq_includedir; do
+ with_libpq_cppflags="$with_libpq_cppflags -I$dir"
+ done
+ fi
+ else
+ AC_MSG_WARN([$with_pg_config returned with status $pg_config_status])
+ fi
+
+ SAVE_CPPFLAGS="$CPPFLAGS"
+ CPPFLAGS="$CPPFLAGS $with_libpq_cppflags"
+
+ AC_CHECK_HEADERS(libpq-fe.h, [],
+ [with_libpq="no (libpq-fe.h not found)"], [])
+
+ CPPFLAGS="$SAVE_CPPFLAGS"
+fi
+if test "x$with_libpq" = "xyes"
+then
+ with_libpq_libdir=`$with_pg_config --libdir 2> /dev/null`
+ pg_config_status=$?
+
+ if test $pg_config_status -eq 0
+ then
+ if test -n "$with_libpq_libdir"; then
+ for dir in $with_libpq_libdir; do
+ with_libpq_ldflags="$with_libpq_ldflags -L$dir"
+ done
+ fi
+ else
+ AC_MSG_WARN([$with_pg_config returned with status $pg_config_status])
+ fi
+
+ SAVE_LDFLAGS="$LDFLAGS"
+ LDFLAGS="$LDFLAGS $with_libpq_ldflags"
+
+ AC_CHECK_LIB(pq, PQconnectdb,
+ [with_libpq="yes"],
+ [with_libpq="no (symbol 'PQconnectdb' not found)"])
+
+ LDFLAGS="$SAVE_LDFLAGS"
+fi
+if test "x$with_libpq" = "xyes"
+then
+ BUILD_WITH_LIBPQ_CPPFLAGS="$with_libpq_cppflags"
+ BUILD_WITH_LIBPQ_LDFLAGS="$with_libpq_ldflags"
+ AC_SUBST(BUILD_WITH_LIBPQ_CPPFLAGS)
+ AC_SUBST(BUILD_WITH_LIBPQ_LDFLAGS)
+fi
+AM_CONDITIONAL(BUILD_WITH_LIBPQ, test "x$with_libpq" = "xyes")
+
dnl Check for libvirt and libxml2 libraries.
with_libxml2="no (pkg-config isn't available)"
with_libxml2_cflags=""
@@ -2544,6 +2631,7 @@ AC_PLUGIN([ntpd], [yes], [NTPd statistics])
AC_PLUGIN([nut], [$with_libupsclient], [Network UPS tools statistics])
AC_PLUGIN([perl], [$plugin_perl], [Embed a Perl interpreter])
AC_PLUGIN([ping], [$with_liboping], [Network latency statistics])
+AC_PLUGIN([postgresql], [$with_libpq], [PostgreSQL database statistics])
AC_PLUGIN([powerdns], [yes], [PowerDNS statistics])
AC_PLUGIN([processes], [$plugin_processes], [Process statistics])
AC_PLUGIN([qmail], [yes], [QMail queue statistics])
@@ -2655,6 +2743,7 @@ Configuration:
libpcap . . . . . . . $with_libpcap
libperl . . . . . . . $with_libperl
libpthread . . . . . $with_libpthread
+ libpq . . . . . . . . $with_libpq
librrd . . . . . . . $with_rrdtool
libsensors . . . . . $with_lm_sensors
libstatgrab . . . . . $with_libstatgrab
@@ -2709,6 +2798,7 @@ Configuration:
nut . . . . . . . . . $enable_nut
perl . . . . . . . . $enable_perl
ping . . . . . . . . $enable_ping
+ postgresql . . . . . $enable_postgresql
powerdns . . . . . . $enable_powerdns
processes . . . . . . $enable_processes
qmail . . . . . . . . $enable_qmail
diff --git a/src/Makefile.am b/src/Makefile.am
index 4f0496d..3d8b146 100644
--- a/src/Makefile.am
+++ b/src/Makefile.am
@@ -536,6 +536,16 @@ collectd_LDADD += "-dlopen" ping.la
collectd_DEPENDENCIES += ping.la
endif
+if BUILD_PLUGIN_POSTGRESQL
+pkglib_LTLIBRARIES += postgresql.la
+postgresql_la_SOURCES = postgresql.c
+postgresql_la_CPPFLAGS = $(BUILD_WITH_LIBPQ_CPPFLAGS)
+postgresql_la_LDFLAGS = -module -avoid-version \
+ $(BUILD_WITH_LIBPQ_LDFLAGS) -lpq
+collectd_LDADD += "-dlopen" postgresql.la
+collectd_DEPENDENCIES += postgresql.la
+endif
+
if BUILD_PLUGIN_POWERDNS
pkglib_LTLIBRARIES += powerdns.la
powerdns_la_SOURCES = powerdns.c
diff --git a/src/collectd.conf.in b/src/collectd.conf.in
index e76f997..f73ac99 100644
--- a/src/collectd.conf.in
+++ b/src/collectd.conf.in
@@ -51,6 +51,7 @@ FQDNLookup true
@BUILD_PLUGIN_NUT_TRUE at LoadPlugin nut
@BUILD_PLUGIN_PERL_TRUE at LoadPlugin perl
@BUILD_PLUGIN_PING_TRUE at LoadPlugin ping
+ at BUILD_PLUGIN_POSTGRESQL_TRUE@LoadPlugin postgresql
@BUILD_PLUGIN_POWERDNS_TRUE at LoadPlugin powerdns
@BUILD_PLUGIN_PROCESSES_TRUE at LoadPlugin processes
@BUILD_PLUGIN_RRDTOOL_TRUE at LoadPlugin rrdtool
@@ -247,6 +248,22 @@ FQDNLookup true
# TTL 255
#</Plugin>
+#<Plugin postgresql>
+# <Database foo>
+# Host "hostname"
+# Port 5432
+# User "username"
+# Password "secret"
+#
+# SSLMode "prefer"
+# KRBSrvName "kerberos_service_name"
+# </Database>
+#
+# <Database bar>
+# Service "service_name"
+# </Database>
+#</Plugin>
+
#<Plugin powerdns>
# <Server "server_name">
# Collect "latency"
diff --git a/src/collectd.conf.pod b/src/collectd.conf.pod
index 107b3af..faff2ff 100644
--- a/src/collectd.conf.pod
+++ b/src/collectd.conf.pod
@@ -948,6 +948,110 @@ Sets the Time-To-Live of generated ICMP packets.
=back
+=head2 Plugin C<postgresql>
+
+The C<postgresql> plugin queries statistics from PostgreSQL databases. It
+keeps a persistent connection to all configured databases and tries to
+reconnect if the connection has been interrupted. The statistics are collected
+from PostgreSQL's B<statistics collector> which thus has to be enabled for
+this plugin to work correctly. This should usually be the case by default.
+See the section "The Statistics Collector" of the B<PostgreSQL Documentation>
+for details.
+
+The B<PostgreSQL Documentation> manual can be found at
+L<http://www.postgresql.org/docs/manuals/>.
+
+ <Plugin postgresql>
+ <Database foo>
+ Host "hostname"
+ Port "5432"
+ User "username"
+ Password "secret"
+ SSLMode "prefer"
+ KRBSrvName "kerberos_service_name"
+ </Database>
+ <Database bar>
+ Service "service_name"
+ </Database>
+ </Plugin>
+
+=over 4
+
+=item B<Database> block
+
+The B<Database> block defines one PostgreSQL database for which to collect
+statistics. It accepts a single mandatory argument which specifies the
+database name. None of the other options are required. PostgreSQL will use
+default values as documented in the section "CONNECTING TO A DATABASE" in the
+L<psql(1)> manpage. However, be aware that those defaults may be influenced by
+the user collectd is run as and special environment variables. See the manpage
+for details.
+
+=item B<Host> I<hostname>
+
+Specify the hostname or IP of the PostgreSQL server to connect to. If the
+value begins with a slash, it is interpreted as the directory name in which to
+look for the UNIX domain socket.
+
+This option is also used to determine the hostname that is associated with a
+collected data set. If it has been omitted or either begins with with a slash
+or equals B<localhost> it will be replaced with the global hostname definition
+of collectd. Any other value will be passed literally to collectd when
+dispatching values. Also see the global B<Hostname> and B<FQDNLookup> options.
+
+=item B<Port> I<port>
+
+Specify the TCP port or the local UNIX domain socket file extension of the
+server.
+
+=item B<User> I<username>
+
+Specify the username to be used when connecting to the server.
+
+=item B<Password> I<password>
+
+Specify the password to be used when connecting to the server.
+
+=item B<SSLMode> I<disable>|I<allow>|I<prefer>|I<require>
+
+Specify whether to use an SSL connection when contacting the server. The
+following modes are supported:
+
+=over 4
+
+=item I<disable>
+
+Do not use SSL at all.
+
+=item I<allow>
+
+First, try to connect without using SSL. If that fails, try using SSL.
+
+=item I<prefer> (default)
+
+First, try to connect using SSL. If that fails, try without using SSL.
+
+=item I<require>
+
+Use SSL only.
+
+=back
+
+=item B<KRBSrvName> I<kerberos_service_name>
+
+Specify the Kerberos service name to use when authenticating with Kerberos 5
+or GSSAPI. See the sections "Kerberos authentication" and "GSSAPI" of the
+B<PostgreSQL Documentation> for details.
+
+=item B<Service> I<service_name>
+
+Specify the PostgreSQL service name to use for additional parameters. That
+service has to be defined in F<pg_service.conf> and holds additional
+connection parameters. See the section "The Connection Service File" in the
+B<PostgreSQL Documentation> for details.
+
+=back
+
=head2 Plugin C<powerdns>
The C<powerdns> plugin queries statistics from an authoritative PowerDNS
@@ -1652,6 +1756,7 @@ L<types.db(5)>,
L<hddtemp(8)>,
L<kstat(3KSTAT)>,
L<mbmon(1)>,
+L<psql(1)>,
L<rrdtool(1)>,
L<sensors(1)>
diff --git a/src/postgresql.c b/src/postgresql.c
new file mode 100644
index 0000000..40f8ec5
--- /dev/null
+++ b/src/postgresql.c
@@ -0,0 +1,519 @@
+/**
+ * collectd - src/postgresql.c
+ * Copyright (C) 2008 Sebastian Harl
+ *
+ * This program is free software; you can redistribute it and/or modify it
+ * under the terms of the GNU General Public License as published by the
+ * Free Software Foundation; only version 2 of the License is applicable.
+ *
+ * This program is distributed in the hope that it will be useful, but
+ * WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
+ * General Public License for more details.
+ *
+ * You should have received a copy of the GNU General Public License along
+ * with this program; if not, write to the Free Software Foundation, Inc.,
+ * 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
+ *
+ * Author:
+ * Sebastian Harl <sh at tokkee.org>
+ **/
+
+/*
+ * This module collects PostgreSQL database statistics.
+ */
+
+#include "collectd.h"
+#include "common.h"
+
+#include "configfile.h"
+#include "plugin.h"
+
+#include "utils_complain.h"
+
+#include <pg_config_manual.h>
+#include <libpq-fe.h>
+
+#define log_err(...) ERROR ("postgresql: " __VA_ARGS__)
+#define log_warn(...) WARNING ("postgresql: " __VA_ARGS__)
+#define log_info(...) INFO ("postgresql: " __VA_ARGS__)
+
+/* Appends the (parameter, value) pair to the string
+ * pointed to by 'buf' suitable to be used as argument
+ * for PQconnectdb(). If value equals NULL, the pair
+ * is ignored. */
+#define C_PSQL_PAR_APPEND(buf, buf_len, parameter, value) \
+ if ((0 < (buf_len)) && (NULL != (value)) && ('\0' != *(value))) { \
+ int s = ssnprintf (buf, buf_len, " %s = '%s'", parameter, value); \
+ if (0 < s) { \
+ buf += s; \
+ buf_len -= s; \
+ } \
+ }
+
+/* Returns the tuple (major, minor, patchlevel)
+ * for the given version number. */
+#define C_PSQL_SERVER_VERSION3(server_version) \
+ (server_version) / 10000, \
+ (server_version) / 100 - (int)((server_version) / 10000) * 100, \
+ (server_version) - (int)((server_version) / 100) * 100
+
+/* Returns true if the given host specifies a
+ * UNIX domain socket. */
+#define C_PSQL_IS_UNIX_DOMAIN_SOCKET(host) \
+ ((NULL == (host)) || ('\0' == *(host)) || ('/' == *(host)))
+
+/* Returns the tuple (host, delimiter, port) for a
+ * given (host, port) pair. Depending on the value of
+ * 'host' a UNIX domain socket or a TCP socket is
+ * assumed. */
+#define C_PSQL_SOCKET3(host, port) \
+ ((NULL == (host)) || ('\0' == *(host))) ? DEFAULT_PGSOCKET_DIR : host, \
+ C_PSQL_IS_UNIX_DOMAIN_SOCKET (host) ? "/.s.PGSQL." : ":", \
+ port
+
+typedef struct {
+ PGconn *conn;
+ c_complain_t conn_complaint;
+
+ /* user configuration */
+ char *host;
+ char *port;
+ char *database;
+ char *user;
+ char *password;
+
+ char *sslmode;
+
+ char *krbsrvname;
+
+ char *service;
+} c_psql_database_t;
+
+static c_psql_database_t *databases = NULL;
+static int databases_num = 0;
+
+static void submit (const c_psql_database_t *db,
+ const char *type, const char *type_instance,
+ value_t *values, size_t values_len)
+{
+ value_list_t vl = VALUE_LIST_INIT;
+
+ vl.values = values;
+ vl.values_len = values_len;
+ vl.time = time (NULL);
+
+ if (C_PSQL_IS_UNIX_DOMAIN_SOCKET (db->host)
+ || (0 == strcmp (db->host, "localhost")))
+ sstrncpy (vl.host, hostname_g, sizeof (vl.host));
+ else
+ sstrncpy (vl.host, db->host, sizeof (vl.host));
+
+ sstrncpy (vl.plugin, "postgresql", sizeof (vl.plugin));
+ sstrncpy (vl.plugin_instance, db->database, sizeof (vl.plugin_instance));
+
+ sstrncpy (vl.type, type, sizeof (vl.type));
+
+ if (NULL != type_instance)
+ sstrncpy (vl.type_instance, type_instance, sizeof (vl.type_instance));
+
+ plugin_dispatch_values (&vl);
+ return;
+} /* submit */
+
+static void submit_counter (const c_psql_database_t *db,
+ const char *type, const char *type_instance,
+ const char *value)
+{
+ value_t values[1];
+
+ if ((NULL == value) || ('\0' == *value))
+ return;
+
+ values[0].counter = atoll (value);
+ submit (db, type, type_instance, values, 1);
+ return;
+} /* submit_counter */
+
+static void submit_gauge (const c_psql_database_t *db,
+ const char *type, const char *type_instance,
+ const char *value)
+{
+ value_t values[1];
+
+ if ((NULL == value) || ('\0' == *value))
+ return;
+
+ values[0].gauge = atof (value);
+ submit (db, type, type_instance, values, 1);
+ return;
+} /* submit_gauge */
+
+static int c_psql_check_connection (c_psql_database_t *db)
+{
+ /* "ping" */
+ PQclear (PQexec (db->conn, "SELECT 42;"));
+
+ if (CONNECTION_OK != PQstatus (db->conn)) {
+ PQreset (db->conn);
+
+ /* trigger c_release() */
+ if (0 == db->conn_complaint.interval)
+ db->conn_complaint.interval = 1;
+
+ if (CONNECTION_OK != PQstatus (db->conn)) {
+ c_complain (LOG_ERR, &db->conn_complaint,
+ "Failed to connect to database %s: %s",
+ db->database, PQerrorMessage (db->conn));
+ return -1;
+ }
+ }
+
+ c_release (LOG_INFO, &db->conn_complaint,
+ "Successfully reconnected to database %s", PQdb (db->conn));
+ return 0;
+} /* c_psql_check_connection */
+
+static int c_psql_stat_database (c_psql_database_t *db)
+{
+ const char *const query =
+ "SELECT numbackends, xact_commit, xact_rollback "
+ "FROM pg_stat_database "
+ "WHERE datname = $1;";
+
+ PGresult *res;
+
+ int n;
+
+ res = PQexecParams (db->conn, query, /* number of parameters */ 1,
+ NULL, (const char *const *)&db->database, NULL, NULL,
+ /* return text data */ 0);
+
+ if (PGRES_TUPLES_OK != PQresultStatus (res)) {
+ log_err ("Failed to execute SQL query: %s",
+ PQerrorMessage (db->conn));
+ log_info ("SQL query was: %s", query);
+ PQclear (res);
+ return -1;
+ }
+
+ n = PQntuples (res);
+ if (1 < n) {
+ log_warn ("pg_stat_database has more than one entry "
+ "for database %s - ignoring additional results.",
+ db->database);
+ }
+ else if (1 > n) {
+ log_err ("pg_stat_database has no entry for database %s",
+ db->database);
+ PQclear (res);
+ return -1;
+ }
+
+ submit_gauge (db, "pg_numbackends", NULL, PQgetvalue (res, 0, 0));
+
+ submit_counter (db, "pg_xact", "commit", PQgetvalue (res, 0, 1));
+ submit_counter (db, "pg_xact", "rollback", PQgetvalue (res, 0, 2));
+
+ PQclear (res);
+ return 0;
+} /* c_psql_stat_database */
+
+static int c_psql_stat_user_tables (c_psql_database_t *db)
+{
+ const char *const query =
+ "SELECT sum(seq_scan), sum(seq_tup_read), "
+ "sum(idx_scan), sum(idx_tup_fetch), "
+ "sum(n_tup_ins), sum(n_tup_upd), sum(n_tup_del), "
+ "sum(n_tup_hot_upd), sum(n_live_tup), sum(n_dead_tup) "
+ "FROM pg_stat_user_tables;";
+
+ PGresult *res;
+
+ int n;
+
+ res = PQexec (db->conn, query);
+
+ if (PGRES_TUPLES_OK != PQresultStatus (res)) {
+ log_err ("Failed to execute SQL query: %s",
+ PQerrorMessage (db->conn));
+ log_info ("SQL query was: %s", query);
+ PQclear (res);
+ return -1;
+ }
+
+ n = PQntuples (res);
+ assert (1 >= n);
+
+ if (1 > n) /* no user tables */
+ return 0;
+
+ submit_counter (db, "pg_scan", "seq", PQgetvalue (res, 0, 0));
+ submit_counter (db, "pg_scan", "seq_tup_read", PQgetvalue (res, 0, 1));
+ submit_counter (db, "pg_scan", "idx", PQgetvalue (res, 0, 2));
+ submit_counter (db, "pg_scan", "idx_tup_fetch", PQgetvalue (res, 0, 3));
+
+ submit_counter (db, "pg_n_tup_c", "ins", PQgetvalue (res, 0, 4));
+ submit_counter (db, "pg_n_tup_c", "upd", PQgetvalue (res, 0, 5));
+ submit_counter (db, "pg_n_tup_c", "del", PQgetvalue (res, 0, 6));
+ submit_counter (db, "pg_n_tup_c", "hot_upd", PQgetvalue (res, 0, 7));
+
+ submit_gauge (db, "pg_n_tup_g", "live", PQgetvalue (res, 0, 8));
+ submit_gauge (db, "pg_n_tup_g", "dead", PQgetvalue (res, 0, 9));
+
+ PQclear (res);
+ return 0;
+} /* c_psql_stat_user_tables */
+
+static int c_psql_statio_user_tables (c_psql_database_t *db)
+{
+ const char *const query =
+ "SELECT sum(heap_blks_read), sum(heap_blks_hit), "
+ "sum(idx_blks_read), sum(idx_blks_hit), "
+ "sum(toast_blks_read), sum(toast_blks_hit), "
+ "sum(tidx_blks_read), sum(tidx_blks_hit) "
+ "FROM pg_statio_user_tables;";
+
+ PGresult *res;
+
+ int n;
+
+ res = PQexec (db->conn, query);
+
+ if (PGRES_TUPLES_OK != PQresultStatus (res)) {
+ log_err ("Failed to execute SQL query: %s",
+ PQerrorMessage (db->conn));
+ log_info ("SQL query was: %s", query);
+ PQclear (res);
+ return -1;
+ }
+
+ n = PQntuples (res);
+ assert (1 >= n);
+
+ if (1 > n) /* no user tables */
+ return 0;
+
+ submit_counter (db, "pg_blks", "heap_read", PQgetvalue (res, 0, 0));
+ submit_counter (db, "pg_blks", "heap_hit", PQgetvalue (res, 0, 1));
+
+ submit_counter (db, "pg_blks", "idx_read", PQgetvalue (res, 0, 2));
+ submit_counter (db, "pg_blks", "idx_hit", PQgetvalue (res, 0, 3));
+
+ submit_counter (db, "pg_blks", "toast_read", PQgetvalue (res, 0, 4));
+ submit_counter (db, "pg_blks", "toast_hit", PQgetvalue (res, 0, 5));
+
+ submit_counter (db, "pg_blks", "tidx_read", PQgetvalue (res, 0, 6));
+ submit_counter (db, "pg_blks", "tidx_hit", PQgetvalue (res, 0, 7));
+
+ PQclear (res);
+ return 0;
+} /* c_psql_statio_user_tables */
+
+static int c_psql_read (void)
+{
+ int success = 0;
+ int i;
+
+ for (i = 0; i < databases_num; ++i) {
+ c_psql_database_t *db = databases + i;
+
+ assert (NULL != db->database);
+
+ if (0 != c_psql_check_connection (db))
+ continue;
+
+ c_psql_stat_database (db);
+ c_psql_stat_user_tables (db);
+ c_psql_statio_user_tables (db);
+
+ ++success;
+ }
+
+ if (! success)
+ return -1;
+ return 0;
+} /* c_psql_read */
+
+static int c_psql_shutdown (void)
+{
+ int i;
+
+ if ((NULL == databases) || (0 == databases_num))
+ return 0;
+
+ plugin_unregister_read ("postgresql");
+ plugin_unregister_shutdown ("postgresql");
+
+ for (i = 0; i < databases_num; ++i) {
+ c_psql_database_t *db = databases + i;
+
+ PQfinish (db->conn);
+
+ sfree (db->database);
+ sfree (db->host);
+ sfree (db->port);
+ sfree (db->user);
+ sfree (db->password);
+
+ sfree (db->sslmode);
+
+ sfree (db->krbsrvname);
+
+ sfree (db->service);
+ }
+
+ sfree (databases);
+ databases_num = 0;
+ return 0;
+} /* c_psql_shutdown */
+
+static int c_psql_init (void)
+{
+ int i;
+
+ if ((NULL == databases) || (0 == databases_num))
+ return 0;
+
+ for (i = 0; i < databases_num; ++i) {
+ c_psql_database_t *db = databases + i;
+
+ char conninfo[4096];
+ char *buf = conninfo;
+ int buf_len = sizeof (conninfo);
+ int status;
+
+ char *server_host;
+ int server_version;
+
+ status = ssnprintf (buf, buf_len, "dbname = '%s'", db->database);
+ if (0 < status) {
+ buf += status;
+ buf_len -= status;
+ }
+
+ C_PSQL_PAR_APPEND (buf, buf_len, "host", db->host);
+ C_PSQL_PAR_APPEND (buf, buf_len, "port", db->port);
+ C_PSQL_PAR_APPEND (buf, buf_len, "user", db->user);
+ C_PSQL_PAR_APPEND (buf, buf_len, "password", db->password);
+ C_PSQL_PAR_APPEND (buf, buf_len, "sslmode", db->sslmode);
+ C_PSQL_PAR_APPEND (buf, buf_len, "krbsrvname", db->krbsrvname);
+ C_PSQL_PAR_APPEND (buf, buf_len, "service", db->service);
+
+ db->conn = PQconnectdb (conninfo);
+ if (0 != c_psql_check_connection (db))
+ continue;
+
+ server_host = PQhost (db->conn);
+ server_version = PQserverVersion (db->conn);
+ log_info ("Sucessfully connected to database %s (user %s) "
+ "at server %s%s%s (server version: %d.%d.%d, "
+ "protocol version: %d, pid: %d)",
+ PQdb (db->conn), PQuser (db->conn),
+ C_PSQL_SOCKET3(server_host, PQport (db->conn)),
+ C_PSQL_SERVER_VERSION3 (server_version),
+ PQprotocolVersion (db->conn), PQbackendPID (db->conn));
+ }
+
+ plugin_register_read ("postgresql", c_psql_read);
+ plugin_register_shutdown ("postgresql", c_psql_shutdown);
+ return 0;
+} /* c_psql_init */
+
+static int config_set (char *name, char **var, const oconfig_item_t *ci)
+{
+ if ((0 != ci->children_num) || (1 != ci->values_num)
+ || (OCONFIG_TYPE_STRING != ci->values[0].type)) {
+ log_err ("%s expects a single string argument.", name);
+ return 1;
+ }
+
+ sfree (*var);
+ *var = sstrdup (ci->values[0].value.string);
+ return 0;
+} /* config_set */
+
+static int c_psql_config_database (oconfig_item_t *ci)
+{
+ c_psql_database_t *db;
+
+ int i;
+
+ if ((1 != ci->values_num)
+ || (OCONFIG_TYPE_STRING != ci->values[0].type)) {
+ log_err ("<Database> expects a single string argument.");
+ return 1;
+ }
+
+ ++databases_num;
+ if (NULL == (databases = (c_psql_database_t *)realloc (databases,
+ databases_num * sizeof (*databases)))) {
+ log_err ("Out of memory.");
+ exit (5);
+ }
+
+ db = databases + (databases_num - 1);
+
+ db->conn = NULL;
+
+ db->conn_complaint.last = 0;
+ db->conn_complaint.interval = 0;
+
+ db->database = sstrdup (ci->values[0].value.string);
+ db->host = NULL;
+ db->port = NULL;
+ db->user = NULL;
+ db->password = NULL;
+
+ db->sslmode = NULL;
+
+ db->krbsrvname = NULL;
+
+ db->service = NULL;
+
+ for (i = 0; i < ci->children_num; ++i) {
+ oconfig_item_t *c = ci->children + i;
+
+ if (0 == strcasecmp (c->key, "Host"))
+ config_set ("Host", &db->host, c);
+ else if (0 == strcasecmp (c->key, "Port"))
+ config_set ("Port", &db->port, c);
+ else if (0 == strcasecmp (c->key, "User"))
+ config_set ("User", &db->user, c);
+ else if (0 == strcasecmp (c->key, "Password"))
+ config_set ("Password", &db->password, c);
+ else if (0 == strcasecmp (c->key, "SSLMode"))
+ config_set ("SSLMode", &db->sslmode, c);
+ else if (0 == strcasecmp (c->key, "KRBSrvName"))
+ config_set ("KRBSrvName", &db->krbsrvname, c);
+ else if (0 == strcasecmp (c->key, "Service"))
+ config_set ("Service", &db->service, c);
+ else
+ log_warn ("Ignoring unknown config key \"%s\".", c->key);
+ }
+ return 0;
+}
+
+static int c_psql_config (oconfig_item_t *ci)
+{
+ int i;
+
+ for (i = 0; i < ci->children_num; ++i) {
+ oconfig_item_t *c = ci->children + i;
+
+ if (0 == strcasecmp (c->key, "Database"))
+ c_psql_config_database (c);
+ else
+ log_warn ("Ignoring unknown config key \"%s\".", c->key);
+ }
+ return 0;
+} /* c_psql_config */
+
+void module_register (void)
+{
+ plugin_register_complex_config ("postgresql", c_psql_config);
+ plugin_register_init ("postgresql", c_psql_init);
+} /* module_register */
+
+/* vim: set sw=4 ts=4 tw=78 noexpandtab : */
+
diff --git a/src/types.db b/src/types.db
index 8350fb4..fabae12 100644
--- a/src/types.db
+++ b/src/types.db
@@ -63,6 +63,12 @@ nfs_procedure value:COUNTER:0:4294967295
nginx_connections value:GAUGE:0:U
nginx_requests value:COUNTER:0:134217728
percent percent:GAUGE:0:100.1
+pg_blks value:COUNTER:0:U
+pg_n_tup_c value:COUNTER:0:U
+pg_n_tup_g value:GAUGE:0:U
+pg_numbackends value:GAUGE:0:U
+pg_scan value:COUNTER:0:U
+pg_xact value:COUNTER:0:U
ping ping:GAUGE:0:65535
players value:GAUGE:0:1000000
power value:GAUGE:0:U
--
1.5.6.1.156.ge903b
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: Digital signature
Url : http://mailman.verplant.org/pipermail/collectd/attachments/20080705/d59a86b3/attachment.pgp
More information about the collectd
mailing list