pgBouncer support

[keithf4]

Is your feature request related to a problem? Please describe.

Working on converting our query set from our existing exporter and one of the things that even the previous one did not support was being able to connect to pgBouncer (https://www.pgbouncer.org/) to collect its statistics. I did run across another PostgreSQL exporter that supported this, but it's lack of TLS is why we couldn't use it: https://github.com/Vonng/pg_exporter. It didn't allow custom queries for bouncer, but maybe could use it as a reference to see how they did it?

Describe the solution you'd like
Be able to target a pgbouncer instance and connect to its special pgbouncer database to be able to run the SHOW commands. Don't need built in query support and happy to just be able to use our own custom collector.

I know the issue in the other exporter we use was that it always tries to run some queries that are not possible in pgbouncer's admin connection. There's a very limited set of commands available there, so would likely need some sort of flag or mode to detect it's making this connection. That special database is always called pgbouncer so could possible do what Vonng did and detect the database name? Here's the debug output when I try to use the example collector below:

Sep 15 15:16:16 cd-dc1-pgbouncer1 sql_exporter[15886]: ts=2023-09-15T19:16:16.598Z caller=klog.go:108 level=warn func=Warningf msg="Starting SQL exporter (version=0.11.1, branch=HEAD, revision=6293d4bc7ffe9531c06779c49d9003b8c92082aa) (go=go1.20.5, platform=linux/amd64, user=jenkins@build-rhel8.packaging.crunchydata.com, date=20230710-16:15:54, tags=netgo)"
Sep 15 15:16:16 cd-dc1-pgbouncer1 sql_exporter[15886]: ts=2023-09-15T19:16:16.598Z caller=klog.go:84 level=debug func=Infof msg="Loading configuration from /etc/sql_exporter/sql_exporter.yml"
Sep 15 15:16:16 cd-dc1-pgbouncer1 sql_exporter[15886]: ts=2023-09-15T19:16:16.598Z caller=klog.go:84 level=debug func=Infof msg="Loaded collector 'crunchy_pgbouncer' from /etc/sql_exporter/crunchy_pgbouncer_collector.yml"
Sep 15 15:16:16 cd-dc1-pgbouncer1 sql_exporter[15886]: ts=2023-09-15T19:16:16.598Z caller=klog.go:96 level=warn func=Warning msg="Listening on 0.0.0.0:9399"
Sep 15 15:16:16 cd-dc1-pgbouncer1 sql_exporter[15886]: ts=2023-09-15T19:16:16.599Z caller=tls_config.go:274 level=info msg="Listening on" address=[::]:9399
Sep 15 15:16:16 cd-dc1-pgbouncer1 sql_exporter[15886]: ts=2023-09-15T19:16:16.599Z caller=tls_config.go:277 level=info msg="TLS is disabled." http2=false address=[::]:9399
Sep 15 15:16:22 cd-dc1-pgbouncer1 sql_exporter[15886]: ts=2023-09-15T19:16:22.092Z caller=klog.go:84 level=debug func=Infof msg="[job=\"pgbouncer_targets\", target=\"pgbouncer\"] Database handle successfully opened with 'postgres' driver"
Sep 15 15:16:22 cd-dc1-pgbouncer1 pgbouncer[15731]: invalid command ';', use SHOW HELP;
Sep 15 15:16:22 cd-dc1-pgbouncer1 pgbouncer[15731]: C-0x5616bfca7260: pgbouncer/ccp_monitoring@127.0.0.1:54596 pooler error: invalid command ';', use SHOW HELP;
Sep 15 15:16:22 cd-dc1-pgbouncer1 pgbouncer[15731]: invalid command ';', use SHOW HELP;
Sep 15 15:16:22 cd-dc1-pgbouncer1 pgbouncer[15731]: C-0x5616bfca74c0: pgbouncer/ccp_monitoring@127.0.0.1:54606 pooler error: invalid command ';', use SHOW HELP;
Sep 15 15:16:22 cd-dc1-pgbouncer1 pgbouncer[15731]: invalid command ';', use SHOW HELP;
Sep 15 15:16:22 cd-dc1-pgbouncer1 pgbouncer[15731]: C-0x5616bfca7260: pgbouncer/ccp_monitoring@127.0.0.1:54612 pooler error: invalid command ';', use SHOW HELP;
Sep 15 15:16:22 cd-dc1-pgbouncer1 pgbouncer[15731]: invalid command ';', use SHOW HELP;
Sep 15 15:16:22 cd-dc1-pgbouncer1 pgbouncer[15731]: C-0x5616bfca74c0: pgbouncer/ccp_monitoring@127.0.0.1:54620 pooler error: invalid command ';', use SHOW HELP;
Sep 15 15:16:22 cd-dc1-pgbouncer1 sql_exporter[15886]: ts=2023-09-15T19:16:22.136Z caller=klog.go:84 level=debug func=Infof msg="Error gathering metrics: [from Gatherer #1] [job=\"pgbouncer_targets\", target=\"pgbouncer\"] driver: bad connection"

Our current work around is this extension that uses the dblink fdw to loop back a connection to pgbouncer from within the database.

https://github.com/CrunchyData/pgbouncer_fdw

It works, but it has limitations and would be much simpler to just be able to run an exporter directly on the pgbouncer instance.

Below is a sample config and collector file to just try and collect the SHOW DATABASE output. pgBouncer supports standard libpq connection strings and the SHOW commands just return a normal table format, so hopefully it works ok otherwise and may not be too much trouble?

Appreciate your time!

# The target(s) to monitor and the list of collectors to execute
#
jobs:
  - job_name: pgbouncer_targets
    collectors: [crunchy_pgbouncer]
    static_configs:
        - targets:
            pgbouncer: 'pg://ccp_monitoring@127.0.0.1:6432/pgbouncer?sslmode=disable'

# Collector definition files.
# Glob patterns are supported (see <https://pkg.go.dev/path/filepath#Match> for syntax).
collector_files:
  - "/etc/sql_exporter/crunchy_pgbouncer_collector.yml"

collector_name: crunchy_pgbouncer

metrics:
  - metric_name: ccp_pgbouncer_databases_pool_size
    type: gauge
    help: "Maximum number of server connections"
    values: [pool_size]
    key_labels:
      - name
      - host
      - port
      - database
      - force_user
      - pool_mode
    query_ref: ccp_pgbouncer_databases

  - metric_name: ccp_pgbouncer_databases_min_pool_size
    type: gauge
    help: "Minimum number of server connections"
    values: [min_pool_size]
    key_labels:
      - name
      - host
      - port
      - database
      - force_user
      - pool_mode
    query_ref: ccp_pgbouncer_databases

  - metric_name: ccp_pgbouncer_databases_reserve_pool
    type: gauge
    help: "Maximum number of additional connections for this database"
    values: [reserve_pool]
    key_labels:
      - name
      - host
      - port
      - database
      - force_user
      - pool_mode
    query_ref: ccp_pgbouncer_databases

  - metric_name: ccp_pgbouncer_databases_max_connections
    type: gauge
    help: "Maximum number of allowed connections for this database, as set by max_db_connections, either globally or per database"
    values: [max_connections]
    key_labels:
      - name
      - host
      - port
      - database
      - force_user
      - pool_mode
    query_ref: ccp_pgbouncer_databases


  - metric_name: ccp_pgbouncer_databases_current_connections
    type: gauge
    help: "Current number of connections for this database"
    values: [current_connections]
    key_labels:
      - name
      - host
      - port
      - database
      - force_user
      - pool_mode
    query_ref: ccp_pgbouncer_databases

  - metric_name: ccp_pgbouncer_databases_paused
    type: gauge
    help: "1 if this database is currently paused, else 0"
    values: [paused]
    key_labels:
      - name
      - host
      - port
      - database
      - force_user
      - pool_mode
    query_ref: ccp_pgbouncer_databases

  - metric_name: ccp_pgbouncer_databases_disabled
    type: gauge
    help: "1 if this database is currently disabled, else 0"
    values: [disabled]
    key_labels:
      - name
      - host
      - port
      - database
      - force_user
      - pool_mode
    query_ref: ccp_pgbouncer_databases


########## QUERIES ##########
queries:
  - query_name: ccp_pgbouncer_databases
    query: |
      SHOW DATABASES;

[burningalchemist]

Hey @keithf4, thank you for your message! Let me have a look, reproduce and debug the connection, technically it should work. Still, thanks for a good request, I have everything to investigate and try things out. 👍

Random idea: by default we use prepared statements, but recently there was a pull request (folks run sql_exporter against ProxySQL) to add no_prepared_statement flag if that's necessary. I can't tell if it helps with pgbouncer or not, but if you could give it a quick try, that'd be great.

Quick question: what doesn't work for you in pgbouncer_exporter (link)? I briefly checked the metrics it exports and it looks similar, and it also has TLS support. I understand, though, if you would like to have a low variety of exporters in your environment. 🙂

[keithf4]

The default pooling mode of pgbouncer (session) supports prepared statements and that's what I tested it with, so I don't think that's the issue here. However, that would still be a very useful feature to have if people put pgbouncer into transaction or statement pooling mode, which does not support prepared statements (transaction mode may actually support it in the future).

We hadn't actually tested pgbouncer_exporter. The FDW tool had already been written and in use by the time we saw it. And the reason is basically what you said, we didn't want to have to have another exporter.

[keithf4]

Actually, i'm not sure if you can do prepared statements in the admin database. I'll test that out as you asked and see.

[burningalchemist]

There's also binary_parameters flag (lib/pq@2997d16), which might be an additional parameter to make it work (from this discussion). I checked the docs and it's mentioned that the admin console only supports the simple query protocol.

And the last thought before I disconnect for today (it's quite late here): you can try pgx driver that's also available out of the box, something like below:

pgx://postgres:PASSWORD@HOST:6543/postgres?default_query_exec_mode=simple_protocol

(mind pgx for the scheme name, it should pick the pgx driver).

Other than that, I'll find some time to also run through all these suggestions myself and see what can be done. 👍

[keithf4]

I tried doing this, but didn't seem to work. Flag seemed like it could be set either in the metrics or queries section, so did it with the query. Assuming it's a boolean?

queries:
  - query_name: ccp_pgbouncer_databases
    no_prepared_statement: true
    query: |
      SHOW DATABASES;

Then tried this

jobs:
  - job_name: pgbouncer_targets
    collectors: [crunchy_pgbouncer]
    static_configs:
        - targets:
            pgbouncer: 'pg://ccp_monitoring@127.0.0.1:6432/pgbouncer?sslmode=disable&binary_parameters'

Both of the above resulted in the original error message reported.

Then tried pgx driver...

jobs:
  - job_name: pgbouncer_targets
    collectors: [crunchy_pgbouncer]
    static_configs:
        - targets:
            pgbouncer: 'pgx://ccp_monitoring@127.0.0.1:6432/pgbouncer?default_query_exec_mode=simple_protocol'

...and got this

Sep 15 22:26:06 cd-dc1-pgbouncer1 sql_exporter[33366]: ts=2023-09-16T02:26:06.313Z caller=klog.go:108 level=warn func=Warningf msg="Starting SQL exporter (version=0.11.1, branch=HEAD, revision=6293d4bc7ffe9531c06779c49d9003b8c92082aa) (go=go1.20.5, platform=linux/amd64, user=jenkins@build-rhel8.packaging.crunchydata.com, date=20230710-16:15:54, tags=netgo)"
Sep 15 22:26:06 cd-dc1-pgbouncer1 sql_exporter[33366]: ts=2023-09-16T02:26:06.313Z caller=klog.go:84 level=debug func=Infof msg="Loading configuration from /etc/sql_exporter/sql_exporter.yml"
Sep 15 22:26:06 cd-dc1-pgbouncer1 sql_exporter[33366]: ts=2023-09-16T02:26:06.313Z caller=klog.go:84 level=debug func=Infof msg="Loaded collector 'crunchy_pgbouncer' from /etc/sql_exporter/crunchy_pgbouncer_collector.yml"
Sep 15 22:26:06 cd-dc1-pgbouncer1 sql_exporter[33366]: ts=2023-09-16T02:26:06.313Z caller=klog.go:96 level=warn func=Warning msg="Listening on 0.0.0.0:9399"
Sep 15 22:26:06 cd-dc1-pgbouncer1 sql_exporter[33366]: ts=2023-09-16T02:26:06.314Z caller=tls_config.go:274 level=info msg="Listening on" address=[::]:9399
Sep 15 22:26:06 cd-dc1-pgbouncer1 sql_exporter[33366]: ts=2023-09-16T02:26:06.314Z caller=tls_config.go:277 level=info msg="TLS is disabled." http2=false address=[::]:9399
Sep 15 22:26:09 cd-dc1-pgbouncer1 sql_exporter[33366]: ts=2023-09-16T02:26:09.246Z caller=klog.go:84 level=debug func=Infof msg="[job=\"pgbouncer_targets\", target=\"pgbouncer\"] Database handle successfully opened with 'pgx' driver"
Sep 15 22:26:09 cd-dc1-pgbouncer1 pgbouncer[31343]: C-0x563b3bc8dda0: (nodb)/(nouser)@127.0.0.1:38834 unsupported startup parameter: default_query_exec_mode=simple_protocol
Sep 15 22:26:09 cd-dc1-pgbouncer1 pgbouncer[31343]: C-0x563b3bc8dda0: (nodb)/(nouser)@127.0.0.1:38834 pooler error: unsupported startup parameter: default_query_exec_mode
Sep 15 22:26:09 cd-dc1-pgbouncer1 sql_exporter[33366]: ts=2023-09-16T02:26:09.250Z caller=klog.go:84 level=debug func=Infof msg="Error gathering metrics: [from Gatherer #1] [job=\"pgbouncer_targets\", target=\"pgbouncer\"] failed to connect to `host=127.0.0.1 user=ccp_monitoring database=pgbouncer`: server error (FATAL: unsupported startup parameter: default_query_exec_mode (SQLSTATE 08P01))"

[burningalchemist]

Ah yes, this parameter has been added in pgx v5, and by default we use v4, it's different there.

Anyways, thanks for trying, I'll take a look and let you know. 🙂👍

[burningalchemist]

Hey @keithf4,

TL;DR:

• add no_prepared_statement: true to the query;
• add --config.enable-ping=false to the sql_exporter argument list.

---

I had a quick look in the afternoon and was able to make it query against PgBouncer with both drivers (libpq/postgres and pgx). Now to the details:

• The reason of invalid command ';', use SHOW HELP; is that before sending a query we call ping() function, which sends ; (empty query) to the backend. While it is fine for SQL databases, pgbouncer doesn't seem to like it. So if we start sql_exporter with --config.enable-ping=false, this shouldn't be a blocker anymore. There might be a catch, though [1].

• Once I added the flag I got a way more familiar error: extended query protocol not supported by admin console. For libpq(postgres) this is solved by adding no_prepared_statement flag in the query config for libpq(postgres). For pgx to work we need to add no_prepared_statement: true, and prefer_simple_protocol=true to the DSN (for v4).

In the end, the result is:

~/Projects/sql_exporter (master ✗) curl localhost:9399/metrics
...
# TYPE test gauge
test{current_connections="0",database="pgbouncer",disabled="0",force_user="pgbouncer",instance="test",min_pool_size="0",name="pgbouncer",paused="0",pool_mode="statement",pool_size="2",port="6432",reserve_pool="0"} 0
...

The connection configuration (libpq or pgx):

target:
   name: 'test'
#   data_source_name: 'postgres://pgbouncer:fake@192.168.64.2:6432/pgbouncer?sslmode=disable'
    data_source_name: 'pgx://pgbouncer:fake@192.168.64.2:6432/pgbouncer?sslmode=disable&prefer_simple_protocol=true'

Query configuration:

    metrics:
      - metric_name: test
        no_prepared_statement: true
        type: gauge
        values: [max_connections]
        key_labels:
          - name
          - database
          - force_user
          - pool_mode
          - disabled
          - paused
          - current_connections
          - reserve_pool
          - min_pool_size
          - pool_size
          - port
        query: |
          SHOW DATABASES;

Overall, it works fine. What I want to figure out next [1]:

• What's wrong with sending ping() to pgbouncer;
• --config.enable-ping works fine with a single target configuration. However, it might be suboptimal for jobs, so we might want to add ping flag per job item.

[burningalchemist]

In the end, lib/pq explicitly sends an empty query (;) as a ping (link), whereas pgx driver sends -- ping (link) (a comment which resolves to the same empty query). From what I've found, pgbouncer doesn't support this behaviour (i.e. requires an explicit command), so the only option for the time being is to disable ping.

That being said, there's a PR to add a configuration parameter enable_ping per job config, so it doesn't need to be global for all the connections (#323).

I'll close this issue since pgBouncer is already supported, but I'll make an additional note here once the above PR is merged.

[keithf4]

Been busy with some other stuff so hadn't gotten back to testing this for a while. Just wanted to confirm that disabling ping in the job and disabling the prepared statement in the query allowed pgbouncer to work for me. Thanks so much for being so responsive on this project!