pgBouncer

NOTE: All of the queries use views from the pgMonitor project. The views execute SHOW commands over a foreign data wrapper to pgBouncer. For details on these views and the setup, see the Crunchy Data gitrepo for pgMonitor.

For a more detailed look at pgBouncer metrics, see the Missing Guide to pgBouncer Metrics blog post.

Databases

-- pgMonitor Exporter (ccp_pgbouncer_databases)
SELECT 
    name AS pool_db, 
    CASE 
      WHEN max_connections != 0 THEN ((current_connections::float / max_connections::float) * 100)::int
      ELSE ((current_connections::float / pool_size::float) * 100)::int
    END AS db_conn_perc_used, 
    paused, disabled            
FROM 
    pgbouncer_databases

Clients

-- pgMonitor Exporter (ccp_pgbouncer_clients)
SELECT 
    database || '.' || user as conn_pool, state AS client_state, count(*) AS client_state_count
FROM 
    pgbouncer_clients
GROUP BY 1,2

Lists

-- pgMonitor Exporter (ccp_pgbouncer_lists)
SELECT 
    list AS item, items AS item_count
FROM 
    pgbouncer_lists
WHERE 
    list IN ('databases', 'pools', 'free_clients', 'free_servers', 'used_servers')

Pools

-- pgMonitor Exporter (ccp_pgbouncer_pools)
SELECT 
    database || '.' || user as conn_pool, sum(cl_active) AS client_active, sum(cl_waiting) AS client_waiting, 
    sum(sv_active) AS server_active, sum(sv_idle) AS server_idle, sum(sv_used) AS server_used
FROM 
    pgbouncer_pools
GROUP BY 1

Servers

-- pgMonitor Exporter (ccp_pgbouncer_servers)
SELECT 
    database || '.' || user as conn_pool, state as server_state, count(*) AS server_state_count
FROM 
    pgbouncer_servers
GROUP BY 1,2