Create view to get information of pgbouncer if you need to process the pools

view_to_pgbouncer.sql

-- password is saved in ~/.pgpass
-- Connect to your pgbouncer and then get "SHOW CLIENTS"
-- SELECT * FROM pgbouncer.connect;
-- SELECT * FROM pgbouncer.clients;

CREATE EXTENSION IF NOT EXISTS dblink;
CREATE SCHEMA IF NOT EXISTS pgbouncer;

DROP VIEW pgbouncer.connect;
CREATE VIEW pgbouncer.connect AS SELECT dblink_connect('pgbouncer','host=localhost port=5432 user=pgbouncer dbname=pgbouncer');

DROP VIEW pgbouncer.clients;
CREATE VIEW pgbouncer.clients AS SELECT * FROM dblink('pgbouncer','show clients') AS 
	f(type text, 
	  "user" text,
	  "database" text,
	  state text,
	  addr text,
	  port int,
	  local_addr text,
	  local_port int,
	  connect_time timestamp,
	  request_time timestamp,
	  wait int,
	  wait_us int,
	  close_needed boolean,
	  ptr text,
	  link text,
	  remote_pid int,
	  tls text);
	  
DROP VIEW pgbouncer.config;	  
CREATE VIEW pgbouncer.config AS SELECT * FROM dblink('pgbouncer','show config') AS 
	f(key text,
	  value text,
	  changeable text);
	  
DROP VIEW pgbouncer.pools;	  
CREATE VIEW pgbouncer.pools AS SELECT * FROM dblink('pgbouncer','show pools') AS 
	f("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,
	  maxwait_us int,
	  pool_mode text);

DROP VIEW pgbouncer.servers;
CREATE VIEW pgbouncer.servers AS SELECT * FROM dblink('pgbouncer','show servers') AS 
	f(type text,
	  "user" text,
	  "database" text,
	  state text,
	  addr text,
	  port int,
	  local_addr text,
	  local_port int,
	  connect_time timestamp,
	  request_time timestamp,
	  wait int,
	  wait_us int,
	  close_needed boolean,
	  ptr text,
	  link text,
	  remote_pid int,
	  tls text);