PgBouncer has a virtual
database called pgbouncer. If you connect to that you can run
special SQL-like commands, for example
$ psql -p 6432 pgbouncer
=# SHOW pools;
┌─[ RECORD 1 ]───────────┐
│ database │ pgbouncer │
│ user │ pgbouncer │
│ cl_active │ 1 │
│ cl_waiting │ 0 │
│ sv_active │ 0 │
│ sv_idle │ 0 │
│ sv_used │ 0 │
│ sv_tested │ 0 │
│ sv_login │ 0 │
│ maxwait │ 0 │
└────────────┴───────────┘
This is quite nice, but unfortunately, you cannot run full SQL queries
against that data. So you couldn’t do something like
SELECT * FROM pgbouncer . pools WHERE maxwait > 0 ;
Well, here is a way: From a regular PostgreSQL database, connect to
PgBouncer using dblink. For each SHOW command provided by
PgBouncer, create a view. Then that SQL query actually works.
But before you start doing that, I have already done that here:
CREATE EXTENSION dblink;
-- customize start
CREATE SERVER pgbouncer FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'localhost',
port '6432',
dbname 'pgbouncer');
CREATE USER MAPPING FOR PUBLIC SERVER pgbouncer OPTIONS (user 'pgbouncer');
-- customize stop
CREATE SCHEMA pgbouncer;
CREATE VIEW pgbouncer.clients AS
SELECT * FROM dblink('pgbouncer', 'show clients') AS _(type text, "user" text, database text, state text, addr text, port int, local_addr text, local_port int, connect_time timestamp with time zone, request_time timestamp with time zone, ptr text, link text);
CREATE VIEW pgbouncer.config AS
SELECT * FROM dblink('pgbouncer', 'show config') AS _(key text, value text, changeable boolean);
CREATE VIEW pgbouncer.databases AS
SELECT * FROM dblink('pgbouncer', 'show databases') AS _(name text, host text, port int, database text, force_user text, pool_size int, reserve_pool int);
CREATE VIEW pgbouncer.lists AS
SELECT * FROM dblink('pgbouncer', 'show lists') AS _(list text, items int);
CREATE VIEW pgbouncer.pools AS
SELECT * FROM dblink('pgbouncer', 'show pools') AS _(database text, "user" text, cl_active int, cl_waiting int, sv_active int, sv_idle int, sv_used int, sv_tested int, sv_login int, maxwait int);
CREATE VIEW pgbouncer.servers AS
SELECT * FROM dblink('pgbouncer', 'show servers') AS _(type text, "user" text, database text, state text, addr text, port int, local_addr text, local_port int, connect_time timestamp with time zone, request_time timestamp with time zone, ptr text, link text);
CREATE VIEW pgbouncer.sockets AS
SELECT * FROM dblink('pgbouncer', 'show sockets') AS _(type text, "user" text, database text, state text, addr text, port int, local_addr text, local_port int, connect_time timestamp with time zone, request_time timestamp with time zone, ptr text, link text,
recv_pos int, pkt_pos int, pkt_remain int, send_pos int, send_remain int, pkt_avail int, send_avail int);
Here is another useful example. If you’re tracing back connections
from the database server through PgBouncer to the client, try this:
SELECT * FROM pgbouncer . servers LEFT JOIN pgbouncer . clients ON servers . link = clients . ptr ;
Unfortunately, different versions of PgBouncer return a different
number of columns for some commands. Then you will need different
view definitions. I haven’t determined a way to handle that
elegantly.