Telegraf: postgresql_extensible is not working with PostgreSQL version 10

[chaintng]

I tried to use inputs.postgresql_extensible on postgres v10
But it doesn't work with no error in telegraf logs

I don't know how to debug since it has no useful logs.

Below is the telegraf config.

## Replication lag on postgres prod
[[inputs.postgresql_extensible]]
  address = "host=POSTGRES_V10_HOST user=USER password=PASS sslmode=disable"
  [[inputs.postgresql_extensible.query]]
    sqlquery="SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::INT AS delay"
    measurement = "replication_lag"
    version=901
    withdbname=false
    tagvalue=""

[danielnelson]

Can you try running telegraf --input-filter postgresql_extensible --test with this query and add the output:

  [[inputs.postgresql_extensible.query]]
    sqlquery="SELECT * FROM pg_stat_database where datname"
    version=901
    withdbname=false
    tagvalue=""

[makeittotop]

I'm facing the exact same problem with postgresql V10.3.

@danielnelson I don't think telegraf runs with postgresql_extensible in --test mode. It just quits with a line and I quote WARNING: skipping plugin [[input.postgresql_extensible]]: service inputs not supported in --test mode

However when I try to run your query including/excluding the other ones I usually run with any ouput plugin, I don't get any data back even with the --debug switch on.

[chaintng]

@danielnelson here is the out put

/ # telegraf --input-filter postgresql_extensible --test
2018/03/08 08:11:00 I! Using config file: /etc/telegraf/telegraf.conf
* Plugin: inputs.postgresql_extensible, Collection 1

[phemmer]

Simple workaround is to remove the version=901 from your config. This is the part of the code that's broken. What that config param does is unfortunately completely undocumented, but it's supposed to make the query run on only versions of postgres greater than specified. However the method that the plugin is using results in a value of 100 for postgres 10, which is obviously less than 901, and so the query doesn't run.

[chaintng]

workaround is working! thank you.

[lgerlandsen]

postgresql_extensible uses this query to determine version
"select substring(setting from 1 for 3) as version from pg_settings where name='server_version_num"
It is possible to just trim off the two least significant digits with something like this:
left(setting, -2)
And do a numeric comparison afterwards.

[lgerlandsen]

left() is only from version 9.1
this will work for older version also:
substring(setting from 1 for length(setting)-2)

[robinsmidsrod]

Just cast the version number to an integer and divide by 100 and it should work in all versions.

Here is an example:
SELECT setting::integer / 100 AS version FROM pg_settings WHERE name = 'server_version_num'

[james-lawrence]

I'm a fan of just removing that check, imo its just been a source of problems lol

[xelite]

Please add some output in log when query isn't execute.