problem getting PgBouncer metrics because of "SET lock_timeout TO '100ms';"

[martinkovacik]

I have some issues collecting PgBouncer metrics.
It seems pgwatch2 is issuing these two statements to get PgBouncer version, but the first one fails:

SET lock_timeout TO '100ms';show version

If I login to pgbouncer at port 6432 as pgbouncer to pgbouncer DB and manually issue SET lock_timeout statement it also fails:

psql -h localhost -p 6432 -U pgbouncer pgbouncer
Password for user pgbouncer:
psql (14.5 (Debian 14.5-1.pgdg110+1), server 1.17.0/bouncer)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

pgbouncer=# SET lock_timeout TO '100ms';
ERROR:  SET failed

I'm using PostgreSQL 14.5, PgBouncer 1.17.0 and pgwatch2 1.9.0.

The command to start pgwatch2 is: /usr/bin/pgwatch2-daemon -c /etc/pgwatch2/config/instances.yaml -m /etc/pgwatch2/metrics --datastore=prometheus

The error log from pgwatch2 when I hit the prometheus endpoint:

2022/09/02 15:33:15 ERRO setInstanceUpDownState: [pool_pg1_postgres:instance_up] could not determine instance version, reporting as 'down': pq: invalid command 'SET lock_timeout TO '100ms';show version', use SHOW HELP;
2022/09/02 15:33:15 ERRO FetchMetrics: failed to fetch pg version for  pool_pg1_postgres pgbouncer_stats pq: invalid command 'SET lock_timeout TO '100ms';show version', use SHOW HELP;
2022/09/02 15:33:15 ERRO Collect: failed to scrape [pool_pg1_postgres:pgbouncer_stats]: pq: invalid command 'SET lock_timeout TO '100ms';show version', use SHOW HELP;

Content of /etc/pgwatch2/config/instances.yaml:

- unique_name: pool_pg1
  dbtype: pgbouncer
  dbname: ''
  host: localhost
  port: 6432
  user: pgbouncer
  password: secret
  sslmode: require
  stmt_timeout: 5
  is_superuser: false 
  preset_metrics: pgbouncer
  custom_metrics:
  preset_metrics_standby:
  custom_metrics_standby:
  dbname_include_pattern:
  dbname_exclude_pattern:
  is_enabled: true
  group: default
  custom_tags:
  sslrootcert: ''
  sslcert: ''
  sslkey: ''

I'm not entirely sure if my config is OK.
Can you please help? Thank you.

[pmpetit]

here is a part of my tf config

    [for db in var.pw2_monitored_dbs : {
      unique_name  = db.unique_name
      dbtype       = "pgbouncer"
      host         = db.host
      port         = db.port
      user         = db.username
      password     = db.password
      dbname       = db.dbname
      sslmode      = "require"
      stmt_timeout = 15
      is_superuser = false
      is_enabled   = true
      group        = "default"
      sslrootcert  = ""
      sslcert      = ""
      sslkey       = ""
      custom_metrics = {
        pgbouncer_stats = 60
      }
    } if db.dbtype == "pgbouncer"]

i think
custom_metrics = {
pgbouncer_stats = 60
}
is missing
v 1.8.3 is ok

[pmpetit]

from aiven pgbouncer, with v1.9.0

pgbouncer=# SET lock_timeout TO '100ms';show version;
ERROR:  admin access needed
Time: 33.686 ms
     version      
------------------
 PgBouncer 1.16.1
(1 row)

[leguaan]

I'm getting similar errors to Martin.

invalid command 'SET lock_timeout TO '100ms';show version', use SHOW HELP;
unknown parameter: pgbouncer/lock_timeout
SET failed

@pmpetit It seems like the user you log in with is not added as an admin in pgbouncer.ini