postgresql_extensible.query cannot connect to pgbouncer

mced commented 7 years ago

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)

danielnelson commented 7 years ago

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

james-lawrence commented 7 years ago

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 commented 7 years ago

@danielnelson I can't say

@james-lawrence nope nobody confirmed, and you're right according to https://github.com/influxdata/telegraf/pull/2573#discussion_r134276525, 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 commented 7 years ago

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

james-lawrence commented 7 years ago

@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 commented 7 years ago

Tank you for your feedback.

rsaffi commented 7 years ago

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

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 commented 7 years ago

@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 commented 7 years ago

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 commented 7 years ago

@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 commented 7 years ago

@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 commented 7 years ago

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

huyujie commented 6 years ago

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

james-lawrence commented 6 years ago

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

james-lawrence commented 6 years ago

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 commented 6 years ago

. E

czw., 2 sie 2018, 00:44 użytkownik Greg notifications@github.com napisał:

Closed #3253 https://github.com/influxdata/telegraf/issues/3253 via

3918 https://github.com/influxdata/telegraf/pull/3918.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/influxdata/telegraf/issues/3253#event-1766416043, or mute the thread https://github.com/notifications/unsubscribe-auth/AChkHrK-9xzJlvYV3g5srZrcBiNWbTjrks5uMi9FgaJpZM4PdrCI .

urusha commented 5 years ago

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 commented 5 years ago

@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 commented 5 years ago

@danielnelson The version is 1.9.1. Please, see https://github.com/influxdata/telegraf/issues/5455

influxdata / telegraf