PgBouncer 1.9 wrapper using dblink_fdw and views.

pgbouncer-1-9-wrapper

CREATE SERVER pgbouncer FOREIGN DATA WRAPPER dblink_fdw OPTIONS(host 'localhost', port '6432', dbname 'pgbouncer');

CREATE USER MAPPING FOR PUBLIC SERVER pgbouncer OPTIONS(user 'postgres');

CREATE SCHEMA IF NOT EXISTS pgbouncer;

CREATE VIEW pgbouncer.active_sockets AS
 SELECT a.type,
    a."user",
    a.database,
    a.state,
    a.addr,
    a.port,
    a.local_addr,
    a.local_port,
    a.connect_time,
    a.request_time,
    a.wait,
    a.wait_us,
    a.close_needed,
    a.ptr,
    a.link,
    a.remote_pid,
    a.tls,
    a.recv_pos,
    a.pkt_pos,
    a.pkt_remain,
    a.send_pos,
    a.send_remain,
    a.pkt_avail,
    a.send_avail
   FROM public.dblink('pgbouncer', 'show active_sockets') a(type text, "user" text, database text, state text, addr text, port integer, local_addr text, local_port integer, connect_time timestamp without time zone, request_time timestamp without time zone, wait integer, wait_us integer, close_needed integer, ptr text, link text, remote_pid integer, tls text, recv_pos integer, pkt_pos integer, pkt_remain integer, send_pos integer, send_remain integer, pkt_avail integer, send_avail integer);

CREATE VIEW pgbouncer.clients AS
 SELECT a.type,
    a."user",
    a.database,
    a.state,
    a.addr,
    a.port,
    a.local_addr,
    a.local_port,
    a.connect_time,
    a.request_time,
    a.wait,
    a.wait_us,
    a.close_needed,
    a.ptr,
    a.link,
    a.remote_pid,
    a.tls
   FROM public.dblink('pgbouncer', 'show clients') a(type text, "user" text, database text, state text, addr text, port integer, local_addr text, local_port integer, connect_time timestamp without time zone, request_time timestamp without time zone, wait integer, wait_us integer, close_needed integer, ptr text, link text, remote_pid integer, tls text);

CREATE VIEW pgbouncer.servers AS
 SELECT a.type,
    a."user",
    a.database,
    a.state,
    a.addr,
    a.port,
    a.local_addr,
    a.local_port,
    a.connect_time,
    a.request_time,
    a.wait,
    a.wait_us,
    a.close_needed,
    a.ptr,
    a.link,
    a.remote_pid,
    a.tls
   FROM public.dblink('pgbouncer', 'show servers') a(type text, "user" text, database text, state text, addr text, port integer, local_addr text, local_port integer, connect_time timestamp without time zone, request_time timestamp without time zone, wait integer, wait_us integer, close_needed integer, ptr text, link text, remote_pid integer, tls text);

CREATE VIEW pgbouncer.connections AS
 SELECT c."user",
    c.database,
    c.state,
    c.addr AS cli_addr,
    c.local_port AS cli_port,
    c.local_addr AS srv_addr,
    c.connect_time AS cli_connect,
    c.request_time AS cli_request,
    s.remote_pid AS srv_pid,
    s.connect_time AS srv_connect,
    s.request_time AS srv_request
   FROM (pgbouncer.servers s
     JOIN pgbouncer.clients c ON ((s.link = c.ptr)))
  ORDER BY s.request_time;

CREATE VIEW pgbouncer.config AS
 SELECT a.key,
    a.value,
    a.changeable
   FROM public.dblink('pgbouncer', 'show config') a(key text, value text, changeable boolean);

CREATE VIEW pgbouncer.databases AS
 SELECT a.name,
    a.host,
    a.port,
    a.database,
    a.force_user,
    a.pool_size,
    a.reserve_pool,
    a.pool_mode,
    a.max_connections,
    a.current_connections,
    a.paused,
    a.disabled
   FROM public.dblink('pgbouncer', 'show databases') a(name text, host text, port integer, database text, force_user text, pool_size integer, reserve_pool integer, pool_mode text, max_connections integer, current_connections integer, paused integer, disabled integer);

CREATE VIEW pgbouncer.dns_hosts AS
 SELECT a.hostname,
    a.ttl,
    a.addrs
   FROM public.dblink('pgbouncer', 'show dns_hosts') a(hostname text, ttl bigint, addrs text);

CREATE VIEW pgbouncer.dns_zones AS
 SELECT a.zonename,
    a.serial,
    a.count
   FROM public.dblink('pgbouncer', 'show dns_zones') a(zonename text, serial bigint, count integer);

CREATE VIEW pgbouncer.fds AS
 SELECT a.fd,
    a.task,
    a."user",
    a.database,
    a.addr,
    a.port,
    a.cancel,
    a.link,
    a.client_encoding,
    a.std_strings,
    a.datestyle,
    a.timezone,
    a.password
   FROM public.dblink('pgbouncer', 'show fds') a(fd integer, task text, "user" text, database text, addr text, port integer, cancel bigint, link integer, client_encoding text, std_strings text, datestyle text, timezone text, password text);

CREATE VIEW pgbouncer.lists AS
 SELECT a.list,
    a.items
   FROM public.dblink('pgbouncer', 'show lists') a(list text, items integer);

CREATE VIEW pgbouncer.memory AS
 SELECT a.name,
    a.size,
    a.used,
    a.free,
    a.memtotal
   FROM public.dblink('pgbouncer', 'show mem') a(name text, size integer, used integer, free integer, memtotal integer);

CREATE VIEW pgbouncer.pools AS
 SELECT a.database,
    a."user",
    a.cl_active,
    a.cl_waiting,
    a.sv_active,
    a.sv_idle,
    a.sv_used,
    a.sv_tested,
    a.sv_login,
    a.maxwait,
    a.maxwait_us,
    a.pool_mode
   FROM public.dblink('pgbouncer', 'show pools') a(database text, "user" text, cl_active integer, cl_waiting integer, sv_active integer, sv_idle integer, sv_used integer, sv_tested integer, sv_login integer, maxwait integer, maxwait_us integer, pool_mode text);

CREATE VIEW pgbouncer.sockets AS
 SELECT a.type,
    a."user",
    a.database,
    a.state,
    a.addr,
    a.port,
    a.local_addr,
    a.local_port,
    a.connect_time,
    a.request_time,
    a.wait,
    a.wait_us,
    a.close_needed,
    a.ptr,
    a.link,
    a.remote_pid,
    a.tls,
    a.recv_pos,
    a.pkt_pos,
    a.pkt_remain,
    a.send_pos,
    a.send_remain,
    a.pkt_avail,
    a.send_avail
   FROM public.dblink('pgbouncer', 'show sockets') a(type text, "user" text, database text, state text, addr text, port integer, local_addr text, local_port integer, connect_time timestamp without time zone, request_time timestamp without time zone, wait integer, wait_us integer, close_needed integer, ptr text, link text, remote_pid integer, tls text, recv_pos integer, pkt_pos integer, pkt_remain integer, send_pos integer, send_remain integer, pkt_avail integer, send_avail integer);

CREATE VIEW pgbouncer.stats AS
 SELECT a.database,
    a.total_xact_count,
    a.total_query_count,
    a.total_received,
    a.total_sent,
    a.total_xact_time,
    a.total_query_time,
    a.total_wait_time,
    a.avg_xact_count,
    a.avg_query_count,
    a.avg_recv,
    a.avg_sent,
    a.avg_xact_time,
    a.avg_query_time,
    a.avg_wait_time
   FROM public.dblink('pgbouncer', 'show stats') a(database text, total_xact_count bigint, total_query_count bigint, total_received bigint, total_sent bigint, total_xact_time bigint, total_query_time bigint, total_wait_time bigint, avg_xact_count bigint, avg_query_count bigint, avg_recv bigint, avg_sent bigint, avg_xact_time bigint, avg_query_time bigint, avg_wait_time bigint);

CREATE VIEW pgbouncer.users AS
 SELECT a.name,
    a.pool_mode
   FROM public.dblink('pgbouncer', 'show users') a(name text, pool_mode text);