telegraf: postgresql_extensible.query cannot connect to pgbouncer

Bug report

With last version 1.4, many of us thought that telegraf was compatible with pgbouncer through postgresql_extensible.query input plugin.

It is not the case.

Relevant telegraf.conf:

[[inputs.postgresql_extensible]]
  address = "host=localhost user=telegraf dbname=pgbouncer"

[[inputs.postgresql_extensible.query]]
  sqlquery="SHOW STATS"
  measurement="pgbouncer"
  withdbname=false
  version=0

System info:

Telegraf v1.4.0 (git: release-1.4 34b7a4c3611d1ede908ef275401544c34a4a3ba3) Ubuntu 14.04 pgbouncer 1.5.4

Steps to reproduce:

Add telegraf user in pgbouncer config file as admin_users. Then run telegraf.

Expected behavior:

$ telegraf --config /etc/telegraf/telegraf.conf --debug --test
pgbouncer,name=pgbouncer,host=hostname,...

Actual behavior:

$ telegraf --config /etc/telegraf/telegraf.conf --debug --test
* Plugin: inputs.postgresql_extensible, Collection 1
2017-09-20T10:29:17Z E! Error in plugin [inputs.postgresql_extensible]: ERROR: not allowed (SQLSTATE 08P01)

About this issue

Original URL
State: closed
Created 7 years ago
Comments: 19 (13 by maintainers)

Most upvoted comments

@leehambley there isn’t currently a work around. its just straight up work that needs to be done in the driver we’re using.

james-lawrence on Oct 4, 2017

@leehambley Well, I ended up making a python script that does the trick. I am using it with the “exec” plugin from Telegraf to send metrics to InfluxDB and it has been working great so far. In case you can’t wait for the fix (like I couldn’t), feel free to try it out meanwhile:

https://github.com/CrossEngage/telebouncer

PS: In case it’s not allowed to crosspost to another project like I did, please accept my appologies and feel free to remove this comment!

rsaffi on Oct 4, 2017

@rsaffi thanks for the information. I got buyin from the pgx maintainer to make the implicit prepared queries optional. so fixing the issue will be pretty straight forward just need to find the time to do it.

james-lawrence on Sep 26, 2017

@mced, alright did some investigation, SQLSTATE 08P01 is pretty much a catchall error. while exploring I received it multiple times:

ERROR: No such user: james (SQLSTATE 08P01) - missing user from pgbouncer userlist.
ERROR: not allowed (SQLSTATE 08P01) - missing user from admin_users or stats_users in pgbouncer.ini. this looks like your issue, I am using a more recent version of pgbouncer than you are though.
ERROR: unsupported pkt type: 80 (SQLSTATE 08P01) - this looks like an actual problem that would stop the postgresql plugin from working. I’m making an assumption here that pkt type = packet type, and 80 corresponds to the ascii, therefor character ‘P’. documentation on these can be found here. all that being said, I’ll have to look into what pgx is doing, one of my gripes with the driver is that it creates prepared statements implicitly and is likely the cause of this problem. I also believe that there might be a switch to turn it off.

james-lawrence on Sep 24, 2017