postgresql_extensible.query cannot connect to pgbouncer

[mced]

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 34b7a4c)
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)

[danielnelson]

Is this from the Open call here? https://github.com/influxdata/telegraf/blob/master/plugins/inputs/postgresql_extensible/postgresql_extensible.go#L134

[james-lawrence]

did we actually confirm it worked? the pgbouncer PR had mixed statements from others on it working? I don't remember the failures being understood and addressed.

the error mentioned here is a protocol violation.

Also looking at your configuration: its going to default to port 5432.... is pgbouncer running on that port? Also what is your pgbouncer configuration?

[mced]

@danielnelson I can't say

@james-lawrence nope nobody confirmed, and you're right according to #2573 (comment), it was not working.

pgbouncer is listening on 5432 port and pooling connections from localhost application to a remote database

pgbouncer is listening on 5432 port

$ psql -h localhost -d pgbouncer -U telegraf -p 5432
Password for user telegraf: 
psql (9.3.16, server 1.5.4/bouncer)
Type "help" for help.

pgbouncer=# 

Here is my pgbouncer config

[databases]
remote_db= host=<remote.tld> port=5432 dbname=<dbname> user=<user> password=<password>

[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid

listen_addr = 127.0.0.1
listen_port = 5432

unix_socket_dir = /var/run/postgresql

auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = session

server_reset_query = DISCARD ALL

server_check_query = select 1

max_client_conn = 200
default_pool_size = 15
ignore_startup_parameters = extra_float_digits

admin_users = telegraf

[james-lawrence]

k, I might have a chance to look into this. but will depend on my mood. =)

[james-lawrence]

@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.

[mced]

Tank you for your feedback.

[rsaffi]

99-pgbouncer.conf.zip
pgbouncer.ini.zip

@james-lawrence On my scenario it's the last one: ERROR: unsupported pkt type: 80 (SQLSTATE 08P01).

root@pg01 ~ # pgbouncer --version
pgbouncer version 1.7.2

root@pg01 ~ # telegraf --version
Telegraf v1.4.0 (git: release-1.4 34b7a4c)

Both telegraf input plugin and pgbouncer config files are attached. IPs and passwords have obviously been redacted.

root@pg01 /etc/telegraf/telegraf.d # telegraf --config /etc/telegraf/telegraf.d/99-pgbouncer.conf --debug --test

• Plugin: inputs.postgresql_extensible, Collection 1
• Internal: 1m0s
  2017-09-26T08:16:18Z E! Error in plugin [inputs.postgresql_extensible]: ERROR: unsupported pkt type: 80 (SQLSTATE 08P01)

[james-lawrence]

@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.

[leehambley]

I just ran into this issue today, is there a workaround for collecting SHOW POOLS metrics with Telegraf pending a fix? I have:

[[inputs.postgresql_extensible]]
  address = "host=/var/run/postgresql user=••••• password=••••• sslmode=disable dbname=pgbouncer port=6432"
  databases = ["pgbouncer"]

[[inputs.postgresql_extensible.query]]
    sqlquery="show pools"
    withdbname = false
    measurement="postgresql_pgbouncer_6432_pools"

Thanks, and sorry for highlighting the issue without a constructive contribution.

[rsaffi]

@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!

[james-lawrence]

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

[leehambley]

Thanks, actually took the chance to remove PGbouncer from the stack, a 🎉 🎈 followed :) 👍

[huyujie]

@james-lawrence if use pgpool not pgbouncer,your pr seems can't resolve the "show ..." query return err. #

[james-lawrence]

@huyujie shame, docs say your error is an undefined object. so not sure exactly what is going on without digging further.

[james-lawrence]

driver can now support SHOW STATS, havent tried the other commands. but anything additional should only require invoking RegisterDataType with the correct information.

will need a separate plugin that overrides the DB creation and use the below for the driver:

	d := &stdlib.DriverConfig{
		ConnConfig: pgx.ConnConfig{
			PreferSimpleProtocol: true,
			RuntimeParams: map[string]string{
				"client_encoding": "UTF8",
			},
			CustomConnInfo: func(c *pgx.Conn) (*pgtype.ConnInfo, error) {
				info := c.ConnInfo.DeepCopy()
				info.RegisterDataType(pgtype.DataType{
					Value: &pgtype.OIDValue{},
					Name:  "int8OID",
					OID:   pgtype.Int8OID,
				})

				return info, nil
			},
		},
	}
	stdlib.RegisterDriverConfig(d)

	db, err := sql.Open("pgx", d.ConnectionString("postgres://localhost:6432/pgbouncer"))

so if someone wants to pick up the work its doable now.

[kramarz]

. E czw., 2 sie 2018, 00:44 użytkownik Greg <[email protected]> napisał:

…

Closed #3253 <#3253> via #3918 <#3918>. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#3253 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AChkHrK-9xzJlvYV3g5srZrcBiNWbTjrks5uMi9FgaJpZM4PdrCI> .

[urusha]

I've got this with pgbouncer input after upgrading to Telegraf 1.9.4

E! [inputs.pgbouncer]: Error in plugin: ERROR: unsupported pkt type: 80 (SQLSTATE 08P01)
E! [inputs.pgbouncer]: Error in plugin: EOF

On 1.9.0 there were no errors. Config is the same.

[danielnelson]

@urusha I don't believe anything has changed in this plugin, can you try to isolate the first Telegraf version where this error occurs and then open a new issue?

[urusha]

@danielnelson The version is 1.9.1. Please, see #5455