Join us at the Dash conference! July 16-17, NYC

Postgres Custom Metric Collection

To collect custom metrics with the Postgres integration, use the custom_queries option in the conf.d/postgres.d/conf.yaml file at the root of your Agent’s configuration directory. See the sample postgres.d/conf.yaml for more details.

Configuration

custom_queries has the following options:

Option Required Description
metric_prefix Yes Each metric starts with the chosen prefix.
query Yes This is the SQL to execute. It can be a simple statement or a multi-line script. All of the rows of the results are evaluated. Use the pipe if you require a multi-line script.
columns Yes This is a list representing each column ordered sequentially from left to right. The number of columns must equal the number of columns returned in the query. There are 2 required pieces of data:
- name: This is the suffix to append to the metric_prefix to form the full metric name. If the type is specified as tag, the column is instead applied as a tag to every metric collected by this query.
- type: This is the submission method (gauge, count, rate, etc.). This can also be set to ‘tag’ to tag each metric in the row with the name and value of the item in this column.
tags No A list of tags to apply to each metric (as specified above).

Example

Database and table

Below is the company table from testdb database. The table contains 3 employee records:

testdb=# SELECT * FROM company;

id| name  | age| address    |salary | entry_date | last_raise_time
-------------------------------------------------------------------
1 | Paul  | 32 | California | 20000 | 1457570000 | 1457570300
2 | Allen | 25 | Texas      | 30000 | 1457570060 | 1457570300
3 | Teddy | 23 | Norway     | 45000 | 1457570120 | 1457570300

From a SQL query to the YAML configuration

The goal is to capture the age and salary of Paul as metric values with his name and address as tags.

SQL query:

SELECT age,salary,name,address FROM company WHERE name = 'Paul'

Corresponding custom_queries YAML configuration:

custom_queries:
  - metric_prefix: postgresql
    query: SELECT age,salary,name,address FROM company WHERE name = 'Paul'
    columns:
      - name: employee_age
        type: gauge
      - name: employee_salary
        type: gauge
      - name: name
        type: tag
      - name: localisation
        type: tag
    tags:
      - query:custom

After updating the Postgres YAML file, restart the Datadog Agent.

Validation

To verify the result, search for the metrics using the Metrics Explorer:

sql_metric_explorer

Debugging

Run the Agent’s status subcommand and look for postgres under the Checks section:

postgres
--------
  - instance #0 [ERROR]: 'Missing metric_prefix parameter in custom_queries'
  - Collected 0 metrics, 0 events & 0 service checks

Additionally, the Agent’s logs may provide useful information.

Further Reading

Additional helpful documentation, links, and articles: