collectd-postgresql.md

collectd/postgresql

Monitors a PostgreSQL database server using collectd's PostgreSQL plugin.

You have to specify each database you want to monitor individually under the databases key. If you have a common authentication to all databases being monitored, you can specify that in the top-level username/password options, otherwise they can be specified at the database level.

Sample YAML configuration:

monitors:
- type: collectd/postgresql
  host: 127.0.0.1
  port: 5432
  username: "username1"
  password: "password1"
  databases:
  - name: "testdb"
    username: "test_user"
    password: "test_pwd"

Sample YAML configuration with custom query:

monitors:
- type: collectd/postgresql
  host: 127.0.0.1
  port: 5432
  username: "username1"
  password: "password1"
  queries:
  - name: "exampleQuery"
    params:
    - "hostname"
    statement: "Select * From test Where host = $1;"
    results:
    - type: "gauge"
      valuesFrom:
      - "test"
      instancePrefix: "test"
 databases:
 - name: "test"
   username: "username2"
   password: "password2"
   queries:
   - "exampleQuery"

metricsToInclude:
   - metricNames:
     - gauge.test
     monitorType: collectd/postgresql

Note that the metric names for the additional metrics picked up from the queries provided depend on the type, instancePrefix and/or instancesFrom parameters being passed in. See PostgreSQL plugin for details.

Monitor Type: collectd/postgresql

Monitor Source Code

Accepts Endpoints: Yes

Multiple Instances Allowed: Yes

Configuration

Config option	Required	Type	Description
host	yes	string
port	yes	integer
databases	yes	list of objects (see below)	A list of databases along with optional authentication credentials.
queries	no	list of objects (see below)	PostgreSQL queries and metric mappings
username	no	string	A username that serves as a default for all databases if not overridden
password	no	string	A password that serves as a default for all databases if not overridden
reportHost	no	bool	A SignalFx extension to the plugin that allows us to disable the normal behavior of the PostgreSQL collectd plugin where the host dimension is set to the hostname of the PostgreSQL database server. When false (the recommended and default setting), the globally configured hostname config is used instead. (default: false)

The nested databases config object has the following fields:

Config option	Required	Type	Description
name	yes	string	The name of the database
username	no	string	Username used to access the database
password	no	string	Password used to access the database
interval	no	integer	Interval to query the database in seconds (default: 0)
expireDelay	no	integer	Skip expired values in query output (default: 0)
sslMode	no	string	Specify whether to use an ssl connection with PostgreSQL. (prefer(default), disable, allow, require)
krbSrvName	no	string	Specify the Kerberos service name used to authenticate with kerberos 5 or GSSAPI
queries	no	list of strings	Queries used to generate metrics. These will override the default set. If no queries are specified, the default set will be used [custom_deadlocks, backends, transactions, queries, queries_by_table, query_plans, table_states, query_plans_by_table, table_states_by_table, disk_io, disk_io_by_table, disk_usage]

The nested queries config object has the following fields:

Config option	Required	Type	Description
name	yes	string	Name used to refer to the query in the database block
statement	yes	string	Statement is a SQL statement to execute
results	yes	list of objects (see below)	Result blocks that define mappings of SQL query results to metrics
params	no	list of strings	Parameters used to fill in $1,$2,$... tokens in the SQL statement. Acceptable values are hostname, database, instance, username, interval
pluginInstanceFrom	no	string	Specifies the column that should be used to populate plugin instance
minVersion	no	integer	The minimum version of PostgreSQL that the query is compatible with. The version must be specified as a two decimal digit. Ex. 7.2.3 -> 70203 (default: 0)
maxVersion	no	integer	The maximum version of PostgreSQL that the query is compatible with. The version must be specified as a two decimal digit. Ex. 7.2.3 -> 70203 (default: 0)

The nested results config object has the following fields:

Config option	Required	Type	Description
type	yes	string	Type defines a metric type
valuesFrom	yes	list of strings	Specifies columns in the SQL result to use as the metric value. The number of columns must match the expected number of values for the metric type.
instancePrefix	no	string	A prefix for the type instance
instancesFrom	no	list of strings	Specifies columns in the SQL result to uses for the type instance. Multiple columns are joined with a hyphen "-".

Metrics

The following table lists the metrics available for this monitor. Metrics that are marked as Included are standard metrics and are monitored by default.

Name	Type	Included	Description
pg_blks.heap_hit	gauge	✔	Number of buffer hits
pg_blks.heap_read	gauge	✔	Number of disk blocks read
pg_blks.idx_hit	gauge	✔	Number of index buffer hits
pg_blks.idx_read	gauge	✔	Number of index blocks read
pg_blks.tidx_hit	gauge		Number of TOAST index buffer hits
pg_blks.tidx_read	gauge		Number of TOAST index blocks read
pg_blks.toast_hit	gauge		Number of TOAST buffer hits
pg_blks.toast_read	gauge		Number of disk blocks read
pg_db_size	gauge	✔	Size of the database on disk, in bytes
pg_n_tup_c.del	gauge	✔	Number of delete operations
pg_n_tup_c.hot_upd	gauge		Number of update operations not requiring index update
pg_n_tup_c.ins	gauge	✔	Number of insert operations
pg_n_tup_c.upd	gauge	✔	Number of update operations
pg_n_tup_g.dead	gauge		Number of dead rows in the database
pg_n_tup_g.live	gauge	✔	Number of live rows in the database
pg_numbackends	gauge	✔	Number of server processes
pg_scan.idx	gauge	✔	Number of index scans
pg_scan.idx_tup_fetch	gauge	✔	Number of rows read from index scans
pg_scan.seq	gauge	✔	Number of sequential scans
pg_scan.seq_tup_read	gauge	✔	Number of rows read from sequential scans
pg_xact.commit	gauge	✔	Number of commits
pg_xact.num_deadlocks	gauge		Number of deadlocks detected by the database
pg_xact.rollback	gauge	✔	Number of rollbacks

To specify custom metrics you want to monitor, add a metricsToInclude filter to the agent configuration, as shown in the code snippet below. The snippet lists all available custom metrics. You can copy and paste the snippet into your configuration file, then delete any custom metrics that you do not want sent.

Note that some of the custom metrics require you to set a flag as well as add them to the list. Check the monitor configuration file to see if a flag is required for gathering additional metrics.

metricsToInclude:
  - metricNames:
    - pg_blks.tidx_hit
    - pg_blks.tidx_read
    - pg_blks.toast_hit
    - pg_blks.toast_read
    - pg_n_tup_c.hot_upd
    - pg_n_tup_g.dead
    - pg_xact.num_deadlocks
    monitorType: collectd/postgresql