How to configure pgbouncer source?

[klonuo]

Hi,

I run one of the databases under pgbouncer, and set in pgwatch sources postgresql://klo:[email protected]:6432/isd with type pgbouncer and selected corresponded preset.

If I run:

pgwatch --sources=postgresql://pgwatch:pgwatchadmin@localhost:5432/pgwatch --sink=postgresql://pgwatch:pgwatchadmin@localhost:5432/pgwatch_metrics

I get:

2025-09-04 05:29:45.903 [ERROR] [database:isd] [err:failed to connect to `user=klo database=isd`:
        192.168.100.2:6432 (192.168.100.2): dial error: dial tcp 192.168.100.2:6432: connect: connection refused
        192.168.100.2:6432 (192.168.100.2): dial error: dial tcp 192.168.100.2:6432: connect: connection refused] [host:192.168.100.2] [port:6432] [time:967.199µs] Connect
2025-09-04 05:29:45.903 [ERROR] [err:failed to connect to `user=klo database=isd`:
        192.168.100.2:6432 (192.168.100.2): dial error: dial tcp 192.168.100.2:6432: connect: connection refused
        192.168.100.2:6432 (192.168.100.2): dial error: dial tcp 192.168.100.2:6432: connect: connection refused] [time:1.196999ms] Acquire

User "klo" is set as admin and stats user in pgbouncer. I can access pgbouncer database with psql -U klo pgbouncer and SHOW STATS prints the results as expected.

Anyone have a suggestion how to set pgwatch with pgbouncer?

[pashagolub]

Why you set isd database then in the connection string? It must be pgbouncer:

postgresql://klo:[email protected]:6432/pgbouncer

[klonuo]

Ah so to reference the db itself...
I just did that and I get the same result:

[ERROR] [database:pgbouncer] [err:failed to connect to `user=klo database=pgbouncer`

I can connect to pgbouncer db with psql -p 6432 pgbouncer afterwhich I'm asked for password and then headed:

klo@Asus:~ $ psql -p 6432 pgbouncer
Password for user klo:
pgbouncer=# show help;
NOTICE:  Console usage
DETAIL:
        SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|USERS|VERSION
        SHOW PEERS|PEER_POOLS
        SHOW FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM|STATE
        SHOW DNS_HOSTS|DNS_ZONES
        SHOW STATS|STATS_TOTALS|STATS_AVERAGES|TOTALS
        SET key = arg
        RELOAD
        PAUSE [<db>]
        RESUME [<db>]
        DISABLE <db>
        ENABLE <db>
        RECONNECT [<db>]
        KILL <db>
        KILL_CLIENT <client_id>
        SUSPEND
        SHUTDOWN
        SHUTDOWN WAIT_FOR_SERVERS|WAIT_FOR_CLIENTS
        WAIT_CLOSE [<db>]
Time: 0.366 ms
pgbouncer=# 

while in sources I've set

postgresql://klo:[email protected]:6432/pgbouncer

[klonuo]

Ok great thanks @pashagolub for pointing in the right direction.

Besides wrong database, I used lan network address as for other database sources in pgwatch, but after setting localhost instead lan address everything works now :)

[pashagolub]

no problem! you may delete the comment if you wish :)

[damianzawadzki]

Hi, I have another question regarding pgbouncer as a source.

In the doc I found this:
"To enable it choose the according DB Type, provide connection info to the pooler port and make sure the pgbouncer_stats metric or "pgbouncer" preset config is selected for the host. Note that for the "DB Name" field you should insert not "pgbouncer" (although this special DB provides all the statistics) but the real name of the pool you wish to monitor or leave it empty to track all pools. In latter case individual pools will be identified / separated via the "database" tag."

I see the following in the Prometheus:
pgwatch_pgbouncer_clients_wait{addr="10.211.55.48", application_name="psql - 10.211.55.48:53062", connect_time="2025-09-23 19:11:33 CEST", database="app", dbname="pgbouncer", instance="pgwatch:9187", job="pgwatch_agent", local_addr="10.211.55.45", ptr="0x56196c6a61c0", replication="none", request_time="2025-09-23 19:11:33 CEST", state="active", tls="TLSv1.3/TLS_AES_256_GCM_SHA384", type="C", user="pgbench"}

However does it work for PostgreSQL sink?

measurements=# select distinct dbname from pgbouncer_stats;
  dbname
-----------
 pgbouncer
(1 row)

measurements=# select distinct dbname from pgbouncer_clients;
  dbname
-----------
 pgbouncer
(1 row)

I also don’t see an option to select a database (pool) in the Grafana dashboards designed for PgBouncer (with PostgreSQL as a sink).

[pashagolub]

hey. This is a term collision here. dbname in measurements db always means monitored source. For example in my output I have:

measurements=# select distinct dbname from pgbouncer_stats;
dbname        |
--------------+
demo_pgbouncer|

The bad thing is you use pgbouncer as a source name and now you're confused.

Said that you want to look inside the measurement itself:

# select dbname as monitored_source, data->>'database' as db from pgbouncer_stats;
monitored_source|db       |
----------------+---------+
demo_pgbouncer  |pgbouncer|
demo_pgbouncer  |pgbouncer|
demo_pgbouncer  |pgbouncer|
demo_pgbouncer  |pgbouncer|

# select dbname as monitored_source, data->>'database' as db, data->>'application_name' as app  from pgbouncer_clients;
monitored_source|db       |app    |
----------------+---------+-------+
demo_pgbouncer  |pgbouncer|pgwatch|
demo_pgbouncer  |pgbouncer|pgwatch|
demo_pgbouncer  |pgbouncer|pgwatch|
demo_pgbouncer  |pgbouncer|pgwatch|
demo_pgbouncer  |pgbouncer|pgwatch|
demo_pgbouncer  |pgbouncer|pgwatch|

[damianzawadzki]

Ok, now I see how it works. Thanks.
But is the Grafana dashboard for pgbouncer ready for the database tag?
I assume the answer is "no", since the current query for TPS is:

select
  floor(extract(epoch from time)/120)*120 AS "time",
  avg((c - c_lag) / extract(epoch from time - time_lag)) as "TPS"
from (
        select 
          (data->>'total_xact_count')::int8 as c, lag((data->>'total_xact_count')::int8) over w as c_lag,
          time, lag(time) over w as time_lag
        from pgbouncer_stats
        where dbname = 'pgbouncer' and time BETWEEN '2025-09-24T03:58:38.219Z' AND '2025-09-24T09:58:38.219Z'
        window w as (order by time)        
) x
where c >= c_lag and time > time_lag
group by 1
order by 1

[pashagolub]

All Grafana dashboards are just templates for you to start anyway. You may change it as you like. And one should change at least thresholds based on the workloads to be monitored.

Please, consider sending feedback in a form of pull request to the main repo or to #cybertec-postgresql/pgwatch-contrib, if your dashboards are too customized for a general use.

[pashagolub]

btw you might be inerested in #963

[damianzawadzki]

yes, I already mentioned my interest in it there