+=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. A database is configured by
+specifying a B<Database> block as described below. The default 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.
+
+By specifying custom database queries using a B<Query> block as described
+below, you may collect any data that is available from some PostgreSQL
+database. This way, you are able to access statistics of external daemons
+which are available in a PostgreSQL database or use future or special
+statistics provided by PostgreSQL without the need to upgrade your collectd
+installation.
+
+The B<PostgreSQL Documentation> manual can be found at
+L<http://www.postgresql.org/docs/manuals/>.
+
+ <Plugin postgresql>
+ <Query magic>
+ Query "SELECT magic, spells FROM wizard WHERE host = $1;"
+ Param hostname
+ Column gauge magic
+ Column counter spells
+ </Query>
+
+ <Database foo>
+ Host "hostname"
+ Port "5432"
+ User "username"
+ Password "secret"
+ SSLMode "prefer"
+ KRBSrvName "kerberos_service_name"
+ Query magic
+ </Database>
+ <Database bar>
+ Service "service_name"
+ </Database>
+ </Plugin>
+
+The B<Query> block defines one database query which may later be used by a
+database definition. It accepts a single mandatory argument which specifies
+the name of the query. The names of all queries have to be unique. The
+following configuration options are available to define the query:
+
+=over 4
+
+=item B<Query> I<sql query>
+
+Specify the I<sql query> which the plugin should execute. The string may
+contain the tokens B<$1>, B<$2>, etc. which are used to reference the first,
+second, etc. parameter. The value of the parameters is specified by the
+B<Param> configuration option - see below for details. To include a literal
+B<$> character followed by a number, surround it with single quotes (B<'>).
+
+Any SQL command which may return data (such as C<SELECT> or C<SHOW>) is
+allowed. Note, however, that only a single command may be used. Semicolons are
+allowed as long as a single non-empty command has been specified only.
+
+=item B<Param> I<hostname>|I<database>|I<username>
+
+Specify the parameters which should be passed to the SQL query. The parameters
+are referred to in the SQL query as B<$1>, B<$2>, etc. in the same order as
+they appear in the configuration file. The value of the parameter is
+determined depending on the value of the B<Param> option as follows:
+
+=over 4
+
+=item I<hostname>
+
+The configured hostname of the database connection. If a UNIX domain socket is
+used, the parameter expands to "localhost".
+
+=item I<database>
+
+The name of the database of the current connection.
+
+=item I<username>
+
+The username used to connect to the database.
+
+=back
+
+=item B<Column> I<type> [I<type instance>]
+
+Specify the I<type> and optional I<type instance> used to dispatch the value
+of each result column. Detailed information about types and their
+configuration can be found in L<types.db(5)>. The number and order of the
+B<Column> options has to match the columns of the query result.
+
+=back
+
+The following predefined queries are available (the definitions can be found
+in the F<postgresql_default.conf> file which, by default, is available at
+C<I<prefix>/share/collectd/>):
+
+=over 4
+
+=item B<backends>
+
+This query collects the number of backends, i.E<nbsp>e. the number of
+connected clients.
+
+=item B<transactions>
+
+This query collects the numbers of committed and rolled-back transactions of
+the user tables.
+
+=item B<queries>
+
+This query collects the numbers of various table modifications (i.E<nbsp>e.
+insertions, updates, deletions) of the user tables.
+
+=item B<query_plans>
+
+This query collects the numbers of various table scans and returned tuples of
+the user tables.
+
+=item B<table_states>
+
+This query collects the numbers of live and dead rows in the user tables.
+
+=item B<disk_io>
+
+This query collects disk block access counts for user tables.
+
+=item B<disk_usage>
+
+This query collects the on-disk size of the database in bytes.
+
+=back
+
+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.
+
+=over 4
+
+=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.
+
+=item B<Query> I<query>
+
+Specify a I<query> which should be executed for the database connection. This
+may be any of the predefined or user-defined queries. If no such option is
+given, it defaults to "backends", "transactions", "queries", "query_plans",
+"table_states", "disk_io" and "disk_usage". Else, the specified queries are
+used only.
+
+=back
+
projects / collectd.git / blobdiff