prometheus-community / pgbouncer_exporter Goto Github PK

I install the exporter using helm and it creates a 8Gb PVC. nowhere in the values have I configured it to persist to disk.

Terence

We are using pgbouncer version 1.9. And we are seeing following error in the exporter logs,
level=info ts=2021-01-28T08:33:21.015Z caller=collector.go:406 msg="Starting scrape" level=error ts=2021-01-28T08:33:21.015Z caller=collector.go:412 msg="error getting version" err="show version didn't return version column"

I can see from the change log of pgbouncer that SHOW VERSION output is changed to normal result from notice message. Is there a way we can disable this version metric or can you make it work with the older versions of pgbouncer as well?

Would be nice to set up the exporter flags via environment variables similar to on how https://github.com/prometheus-community/stackdriver_exporter does.

Will help in cases to protect the pgbouncer stats account credentials it uses to connect.

During exporter initialization, the message from stackdriver_exporter is displayed, when it should be pgbouncer_exporter.

Example:
level=info ts=2021-02-10T15:15:44.792Z caller=pgbouncer_exporter.go:80 msg="Starting stackdriver_exporter" version="(version=0.4.0, branch=HEAD, revision=f0ec9fcb91d6cd2ba02e7f6b1d83f658f65dea82)"

I ran into an issue setting up the exporter in the same VM where pgbouncer is running.
I have a pgbouncer_exporter user setup with a password to connect to the pgbouncer DB locally through pgbouncer's unix socket.

I can connect to the pgbouncer DB with all these 3 commands:

pgbouncer_exporter@db-host:~$ psql postgres://pgbouncer_exporter:****@%2Fvar%2Frun%2Fpostgresql:6432/pgbouncer
pgbouncer_exporter@db-host:~$ psql postgres://pgbouncer_exporter:****@[/var/run/postgresql]:6432/pgbouncer
pgbouncer_exporter@db-host:~$ psql postgres://pgbouncer_exporter:****@:6432/pgbouncer

But none of the 3 connection strings work in the pgbouncer_exporter:

pgbouncer_exporter@db-host:~$ /usr/local/bin/pgbouncer_exporter --pgBouncer.connectionString="postgres://pgbouncer_exporter:****@/var/run/postgresql:6432/pgbouncer"ts=2023-12-01T12:19:52.371Z caller=collector.go:138 level=error msg="error setting up DB connection" err="error pinging pgbouncer: pq: password authentication failed for user \"pgbouncer_exporter\""
pgbouncer_exporter@db-host:~$ /usr/local/bin/pgbouncer_exporter --pgBouncer.connectionString="postgres://pgbouncer_exporter:****@%2Fvar%2Frun%2Fpostgresql:6432/pgbouncer"
ts=2023-12-01T12:20:16.842Z caller=collector.go:138 level=error msg="error setting up DB connection" err="error pinging pgbouncer: parse \"postgres://pgbouncer_exporter:****@%2Fvar%2Frun%2Fpostgresql:6432/pgbouncer\": invalid URL escape \"%2F\""
pgbouncer_exporter@db-host:~$ /usr/local/bin/pgbouncer_exporter --pgBouncer.connectionString="postgres://pgbouncer_exporter:****@[/var/run/postgresql]:6432/pgbouncer"
ts=2023-12-01T12:20:19.659Z caller=collector.go:138 level=error msg="error setting up DB connection" err="error pinging pgbouncer: parse \"postgres://pgbouncer_exporter:****@[/var/run/postgresql]:6432/pgbouncer\": missing ']' in host"

I can see by default if the host is not specified, pgbouncer_exporter will use localhost ::1 instead of using the unix socket. PGbouncer logs from the login failure:

Dec 01 10:55:38 db-host pgbouncer[472115]: C-0x563c122c7050: (nodb)/(nouser)@[::1]:45336 closing because: SSL required (age=0s)
Dec 01 10:55:38 db-host pgbouncer[472115]: C-0x563c122c7050: (nodb)/(nouser)@[::1]:45336 pooler error: SSL required

So then I have to specify the unix socket directory. How can I do this with the exporter?

please contributor here create docker images for pgbouncer_exporter.

It would be helpful to be able to use environment variables to set configuration on the exporter. This is a fairly common pattern and absolutely necessary if running this as a sidecar in the Zalando pgsql operator, which currently only allows environment variables to be defined and prevents using command line arguments. I would be glad to put up a PR to add this functionality.

How do I set up this exporter? What is the connection string supposed to be? I tried the example, but all I get is level=error ts=2021-10-14T10:29:24.248Z caller=collector.go:129 msg="error setting up DB connection" err="error pinging pgbouncer: \"pq: not allowed\"".

Hi,

When connecting, I get this:

ts=2023-01-09T14:32:54.310Z caller=collector.go:129 level=error msg="error setting up DB connection" err="error pinging pgbouncer: "pq: unsupported startup parameter: extra_float_digits""

Any clue on how to solve it? I know I can set pgbouncer to ignore extra_float_digits, but I'm afraid to break something, since the documentation says that newer JBDC drivers use it.

Prometheus reports an error " "INVALID" is not a valid start token " for pgbouncer exporter endpoint.

We use such products versions:
pgbouncer - v1.16.2
pgbouncer exporter - v0.4.1
prometheus - v2.31.1

pgbouncer, pgbouncer exporter and prometheus are deployed on Kubernetes
pgbouncer and pgbouncer exporter are deployed as two containers within a single pod in its own namespace. Prometheus is deployed as a separate pod in its own namespace. There is network connectivity between all components.
When we configure port-forward for pgbouncer exporter we're able to browse exposed data:

However, it seems like Prometheus is not able to parse this data as valid.

Please advise me on possible directions for investigation of this issue.

It would be great if someone could kindly cut a new release including the new pgbouncer-v1.21.0 metrics related to prepared statements.

I would be happy to submit a simple PR but in order to do so I would first have to go through some painful bureaucratic hoops. Hope you understand and I don't come across as entitled brat... :-)

Thank you!

Other exporters seems to be using the https package in exporter-toolkit to support running ove SSL. Can we implement something similar for pgbouncer_exporter also?

Hello.
Thank you for the pgbouncer_exporter. I was surprised that there is a new one maintained by prometheus-community.

As I see, kingpin supports environment variables in addition to already existing command-line flags.
https://github.com/alecthomas/kingpin/blob/master/_examples/chat1/main.go#L11
So I think it would be nice to add this. I could try to do PR if needed.

P.S. Also there is no information about command-line flags in Readme.md, so I have to download binary and check -h output. It might be good to add it there I suppose.

Is there any Grafana dashboards for this exporter?
i found a dashboard for similiar exporter but queries not match!
if we have no dashboards i want to create one

The security team blocked us from implementing this exporter due of the following vulnerabilities:

❯ docker scout cves quay.io/prometheuscommunity/pgbouncer-exporter:v0.8.0
    i New version 1.11.0 available (installed version is 1.6.3) at https://github.com/docker/scout-cli
    ✓ Pulled
    ✓ Image stored for indexing
    ✓ Indexed 27 packages
    ✗ Detected 2 vulnerable packages with a total of 5 vulnerabilities


## Overview

                    │                     Analyzed Image                       
────────────────────┼──────────────────────────────────────────────────────────
  Target            │  quay.io/prometheuscommunity/pgbouncer-exporter:v0.8.0   
    digest          │  ca78408f49b6                                            
    platform        │ linux/arm64/v8                                           
    vulnerabilities │    1C     1H     2M     0L     2?                        
    size            │ 9.2 MB                                                   
    packages        │ 27                                                       


## Packages and Vulnerabilities

   1C     1H     1M     0L     2?  stdlib 1.22.1
pkg:golang/[email protected]

    ✗ CRITICAL CVE-2024-24790
      https://scout.docker.com/v/CVE-2024-24790
      Affected range : >=1.22.0-0  
                     : <1.22.4     
      Fixed version  : 1.22.4      
    
    ✗ HIGH CVE-2024-24791
      https://scout.docker.com/v/CVE-2024-24791
      Affected range : >=1.22.0-0  
                     : <1.22.5     
      Fixed version  : 1.22.5      
    
    ✗ MEDIUM CVE-2024-24789
      https://scout.docker.com/v/CVE-2024-24789
      Affected range : >=1.22.0-0  
                     : <1.22.4     
      Fixed version  : 1.22.4      
    
    ✗ UNSPECIFIED CVE-2024-24788
      https://scout.docker.com/v/CVE-2024-24788
      Affected range : >=1.22.0-0  
                     : <1.22.3     
      Fixed version  : 1.22.3      
    
    ✗ UNSPECIFIED CVE-2023-45288
      https://scout.docker.com/v/CVE-2023-45288
      Affected range : >=1.22.0-0  
                     : <1.22.2     
      Fixed version  : 1.22.2      
    

   0C     0H     1M     0L  golang.org/x/net 0.22.0
pkg:golang/golang.org/x/[email protected]

    ✗ MEDIUM CVE-2023-45288 [Uncontrolled Resource Consumption]
      https://scout.docker.com/v/CVE-2023-45288
      Affected range : <0.23.0                                       
      Fixed version  : 0.23.0                                        
      CVSS Score     : 5.3                                           
      CVSS Vector    : CVSS:3.1/AV:N/AC:L/PR:N/UI:N/S:U/C:N/I:N/A:L  
    
6 vulnerabilities found in 2 packages
  UNSPECIFIED  2  
  LOW          0  
  MEDIUM       2  
  HIGH         1  
  CRITICAL     1

Hi Team:

Thank you for your great contibution.

I am trying to use the prebuild binary (pgbouncer_exporter version 0.6.0 linux amd64) in my micro-service. I am automating it via supervisord. As you can see that the connection string here has ?sslmode=disable,
but the error shows up saying that SSL is disabled and the program quits.

I have tried different users as well (postgres user and root user). I also tried changing the database name
in connection-string to 'pgbouncer' , 'postgres' etc. Here is the entries on my setup:

-- supervisord config --
[program:bouncer_metrics]
autorestart=true command=pgbouncer_exporter --log.level=debug --log.format=json --web.listen-address=":9007" --pgBouncer.connectionString="postgresql://sankalra:[email protected]:6432/bouncer_postgres?sslmode=disable" --pgBouncer.pid-file=/
dev/shm/pg_bouncer.pid
stderr_logfile=/dev/log/bouncer_exporter.err
stdout_logfile=/dev/log/bouncer_exporter.log

-- logs : bouncer_exporter.err --
{"caller":"collector.go:138","err":"error pinging pgbouncer: pq: SSL is not enabled on the server","level":"error","msg":"error setting up DB connection","ts":"2023-03-24T19:42:23.608Z"}
{"caller":"collector.go:138","err":"error pinging pgbouncer: pq: SSL is not enabled on the server","level":"error","msg":"error setting up DB connection","ts":"2023-03-24T19:42:23.710Z"}
{"caller":"collector.go:138","err":"error pinging pgbouncer: pq: SSL is not enabled on the server","level":"error","msg":"error setting up DB connection","ts":"2023-03-24T19:42:23.829Z"}

I have looked at code. The system try to connect to DB at collector.go:L138 -> Calls Other function on collector.go:L333
and I see this code:
db, err := sql.Open("postgres", conn)

-1:
My question is "why is database name 'postgres' hard-coded"? and not pulled from dsn or connectionString ?
I do not have postgres db in my system. I do not have pgbouncer db as well? I do have bouncer_postgres as alias db.
I did tried the two db names also but it still failed.

-2:
The error issue is it telling that SSL is not enabled. However, I do have ?sslmode=disable. Is this some side effect of new TLS work done? I haven't digged into it yet
-3:
I did tried to build the system but one of the go library for logger and flags is not
available in github for v2 version that it tries to build with, and i am not sure if
that is something that is being maintained by one of the developers in this community.
The failure to build out of box means that a lot of developers that would like to contribute cannot.

I hope someone from core team will kindly respond here!
Thanks,

Full error text is following:
ts=2024-02-14T13:27:09.475Z caller=collector.go:138 level=error msg="error setting up DB connection" err="error pinging pgbouncer: dial tcp 127.0.0.1:5432: connect: connection refused"
PG bouncer Exporter container is configured like that:

pgbouncer-exporter:
    image: prometheuscommunity/pgbouncer-exporter
    env_file: .env
    depends_on:
      - db
    expose:
      - "9127"
    command: --pgBouncer.connectionString=${PGBOUNCER_EXPORTER_CONNECTION_STRING}
    deploy:
      placement:
        max_replicas_per_node: 1
        constraints:
          - "node.role == manager"
    networks:
      - backend

Where PGBOUNCER_EXPORTER_CONNECTION_STRING is like that:
postgres://user:password@db:5432/pgbouncer?sslmode=disable

As I understand pgbouncer for some reason is trying to connect on 127.0.0.1 even though it should connect to db through docker network. What should I do to fix that?

Getting the process metrics from pgbouncer would be useful, similar to how the haproxy_exporter gathers process metrics via a PID file argument. This is useful for pgbouncer as it's single-threaded. This would eliminate the need for a separate process exporter.

Unfortunately I couldn't find any documentation for this project, official docker image also don't provide any starter guide, so I think some documentation or starte guide\instalation instructions may be in README would be helpful.

Hi,

Would you be so kind to advise how the connection string in pgbouncer_exporter works?

      --pgBouncer.connectionString="postgres://postgres:@localhost:6543/pgbouncer?sslmode=disable"  
                               Connection string for accessing pgBouncer.

For example, using the psql client to connect to the local pgbouncer instance

> psql --host=127.0.0.1 --port=6432 --dbname=pgbouncer --username=xxx
psql:/home/app/.psqlrc:1: NOTICE:  SET ignored
Timing is on.
Pager is always used.
Null display is "NULL".
psql (12.5, server 1.15.0/bouncer)
Type "help" for help.

[email protected]:6432 pgbouncer# \q

it succeeds.

Trying to do the same using pgbouncer_exporter results to an error

> /usr/local/sbin/pgbouncer_exporter --pgBouncer.connectionString="postgres://xxx:[email protected]:6432/pgbouncer?sslmode=disable"
level=error ts=2021-04-05T13:02:12.688Z caller=collector.go:129 msg="error setting up DB connection" err="error pinging pgbouncer: \"pq: unsupported startup parameter: extra_float_digits\""

Can you please advise?

I am trying to set up monitoring for Yandex Odyssey and getting these errors:

An error has occurred while serving metrics:

5 error(s) occurred:
* collected metric "pgbouncer_databases_pool_size" { label:<name:"database" value:"fastcup" > label:<name:"force_user" value:"" > label:<name:"host" value:"127.0.0.1" > label:<name:"name" value:"fastcup" > label:<name:"pool_mode" value:"transaction" > label:<name:"port" value:"5432" > gauge:<value:100 > } was collected before with the same name and label values
* collected metric "pgbouncer_databases_max_connections" { label:<name:"database" value:"fastcup" > label:<name:"force_user" value:"" > label:<name:"host" value:"127.0.0.1" > label:<name:"name" value:"fastcup" > label:<name:"pool_mode" value:"transaction" > label:<name:"port" value:"5432" > gauge:<value:0 > } was collected before with the same name and label values
* collected metric "pgbouncer_databases_current_connections" { label:<name:"database" value:"fastcup" > label:<name:"force_user" value:"" > label:<name:"host" value:"127.0.0.1" > label:<name:"name" value:"fastcup" > label:<name:"pool_mode" value:"transaction" > label:<name:"port" value:"5432" > gauge:<value:3 > } was collected before with the same name and label values
* collected metric "pgbouncer_databases_paused" { label:<name:"database" value:"fastcup" > label:<name:"force_user" value:"" > label:<name:"host" value:"127.0.0.1" > label:<name:"name" value:"fastcup" > label:<name:"pool_mode" value:"transaction" > label:<name:"port" value:"5432" > gauge:<value:0 > } was collected before with the same name and label values
* collected metric "pgbouncer_databases_disabled" { label:<name:"database" value:"fastcup" > label:<name:"force_user" value:"" > label:<name:"host" value:"127.0.0.1" > label:<name:"name" value:"fastcup" > label:<name:"pool_mode" value:"transaction" > label:<name:"port" value:"5432" > gauge:<value:0 > } was collected before with the same name and label values

Would it be possible to support environment variables for the exporter's configuration?

I intend to use this exporter with Kubernetes, right now I need to do something like this:

        - name: metrics-exporter
          image: prometheuscommunity/pgbouncer-exporter:v0.7.0
          args:
            - --pgBouncer.connectionString=postgres://pgbouncer:pgbouncer@localhost:5432/pgbouncer
            - --web.listen-address=:9127

It is more ideal to use environment variables as they can be derived from pre configured secrets.

        - name: metrics-exporter
          image: prometheuscommunity/pgbouncer-exporter:v0.7.0
          args:
            - --web.listen-address=:9127
          env:
            - name: PGBOUNCER_CONNECTION_STRING
              valueFrom:
                secretKeyRef:
                  name: pgbouncer-connection-string

This way, the configuration can be safely committed to a Git repo, and the secret can be managed by existing secret management tools within the K8s ecosystem.

There is an incorrect colon added on the reserve_pool metric.

https://github.com/prometheus-community/pgbouncer_exporter/blob/master/collector.go#L41

I don't know whether the connectionstringpointer should connect to pgbouncer or PostgreSQL in the code. I see. Default in the code（“ postgres://postgres: @localhost: 6543 / pgbouncer? Sslmode = disable "), which looks like connecting to Postgres, so should I use pgbouncer's account or Postgres's account

Could you tell me how to correctly specify the connection string if there is a dash in the name of my database. Connection string example:

- --pgBouncer.connectionString=postgres://$(DB_USER):$(DB_PASSWORD)@127.0.0.1:5432/tmp-test-db?sslmode=disable&connect_timeout=10

The error I am getting:

{"caller":"collector.go:129","err":"error pinging pgbouncer: \"pq: unrecognized configuration parameter \\\"stats\\\"\"","level":"error","msg":"error setting up DB connection","ts":"2022-05-10T19:56:15.348Z"}

Or I can't use the current database and I need to create a new one? Could you please helm me? Thanks

Update to remove deprecated prometheus/common/log to use prometheus/common/promlog.

PR: #93

Original comment: #93 (comment)

Dear @SuperQ and @kenneth-duffel I have been using pgbouncer_exporter for ages. I have just rebuilt it from master branch and I see in the logs:

{"caller":"collector.go:474","err":"error retrieving columns list from SHOW CONFIG:  <nil>\n","level":"error","msg":"error getting SHOW CONFIG","ts":"2023-01-26T18:51:11.176Z"}

I have PgBouncer 1.17.0 where I have more than 3 colums:

pgbouncer=# SHOW CONFIG;
            key            |                         value                         |                        default                         | changeable
---------------------------+-------------------------------------------------------+--------------------------------------------------------+------------

It should be at least 3, or do I miss something?

UPDATE: I found it, so the code expects these 3 columns in this order but I have default column as well before the changeabl value

Based on the documentation (https://www.pgbouncer.org/usage.html) there are 4 columns:

SHOW CONFIG
Show the current configuration settings, one per row, with the following columns:

key
  Configuration variable name
value
  Configuration value
default
  Configuration default value
changeable
  Either yes or no, shows if the variable can be changed while running. If no, the variable can be changed only at boot time. 
Use SET to change a variable at run time.

I cannot find a Grafana Dashboard for this PgBouncer exporter that somehow it became the default option due to the mention in the Prometheus integration exporter list at:
https://prometheus.io/docs/instrumenting/exporters/

Does somebody have done any work on this topic that could be shared in this issue or uploaded into:
https://grafana.com/grafana/dashboards
Also it will be good to make references from this project to the Grafana dashboard and the same in the opposite way

build github.com/prometheus-community/pgbouncer_exporter: cannot load io/fs: malformed module path "io/fs": missing dot in first path element
!! command failed: build -o /home/anmol_pandey/pgbouncer_exporter/pgbouncer_exporter -ldflags -X github.com/prometheus/common/version.Version=0.6.0 -X github.com/prometheus/common/version.Revision=8f44003fd8b0f2f13d49f11e256acd0596317c1d -X github.com/prometheus/common/version.Branch=master -X github.com/prometheus/common/version.BuildUser=anmol_pandey@dukaan-staging-db -X github.com/prometheus/common/version.BuildDate=20230502-08:00:00 -extldflags '-static' -a -tags 'netgo static_build' github.com/prometheus-community/pgbouncer_exporter: exit status 1
make: *** [Makefile.common:198: common-build] Error 1

OS: ubuntu 20.04