How do I configure this PostgreSQL check for DataDog?

Question

I try to setup a postgres check using DD Agent and i'm getting an error thrown by postgres.py script. As you can see in the screenshot, i'm using this simple query to get the number of active connections to a db. I've put it inside the /etc/datadog-agent/conf.d/postgres.d/conf.yaml like this :

- metric_prefix: postgresql
     query: SELECT datname as db_name, count(pid) as active_connections FROM pg_stat_activity where state = 'active' group by db_name;
     columns:
       - name: active_connections
         type: gauge
       - name: db_name
         type: tag

The error i get when i run a config check is the following :

[root@my_box postgres.d]# datadog-agent check postgres | grep -i -A 20 -B 20  active_connections
Error: postgres:953578488181a512 | (postgres.py:398) | non-numeric value `cldtx` for metric column `active_connections` of metric_prefix `postgresql`

If i understood correctly the conf.yaml file is used to call the postgres.py script with certain parameters. The postgres.py script can be found here : https://github.com/DataDog/integrations-core/blob/master/postgres/datadog_checks/postgres/postgres.py

Answer 1

In these kinds of checks, the response order matters, since the columns returned from the DB are going to be mapped back to the names specified in the YAML.

Reading the error message:

Error: postgres:953578488181a512 | (postgres.py:398) | non-numeric value cldtx for metric column active_connections of metric_prefix postgresql

We can see that the value of cldtx is being returned for the active_connections column, which in the YAML is declared as a gauge, and this is a string.

The fix should be straightforward, by reordering the YAML, like so:

...
     columns:
       - name: db_name
         type: tag
       - name: active_connections
         type: gauge

Alternately, if you want to keep the YAML ordered, change the query to:

...
     query: SELECT count(pid) as active_connections, datname as db_name FROM pg_stat_activity where state = 'active' group by db_name;
...