pgbouncer-show-dblink-fdw

pgbouncer-show-dblink-fdw.sql

CREATE EXTENSION dblink;

-- customize start
CREATE SERVER pgbouncer FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host '172.17.10.95',
                                                                 port '6432',
                                                                 dbname 'pgbouncer');

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


CREATE SCHEMA pgbouncer;


BEGIN;

DROP VIEW IF EXISTS pgbouncer.clients;
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,
      	wait bigint,
      	wait_us bigint,
      	close_needed int,
      	ptr text,
      	link text,
      	remote_pid int,
      	tls text
	);

DROP VIEW IF EXISTS pgbouncer.config;
CREATE VIEW pgbouncer.config AS
    SELECT * FROM dblink('pgbouncer', 'show config') AS _(
    	key text,
    	value text,
    	changeable boolean
    );

DROP VIEW IF EXISTS pgbouncer.databases;
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,
      	pool_mode text,
      	max_connections int,
      	current_connections int,
      	paused int,
      	disabled int
	);

DROP VIEW IF EXISTS pgbouncer.lists;
CREATE VIEW pgbouncer.lists AS
    SELECT * FROM dblink('pgbouncer', 'show lists') AS _(
    	list text,
    	items int
    );

DROP VIEW IF EXISTS pgbouncer.pools;
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 bigint,
      	maxwait_us bigint,
      	pool_mode text
	);

DROP VIEW IF EXISTS pgbouncer.servers;
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,
     	wait bigint,
     	wait_us bigint,
     	close_needed int,
     	ptr text, 
     	link text,
     	remote_pid int,
     	tls text
	);

DROP VIEW IF EXISTS pgbouncer.sockets;
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,
      	wait bigint,
      	wait_us bigint,
      	close_needed int,
      	ptr text,
      	link text,
      	remote_pid int,
      	tls text,
      	recv_pos bigint,
      	pkt_pos bigint,
      	pkt_remain bigint,
      	send_pos bigint,
      	send_remain bigint,
      	pkt_avail bigint,
      	send_avail bigint
	);

DROP VIEW IF EXISTS pgbouncer.stats;
CREATE VIEW pgbouncer.stats AS
    SELECT * FROM dblink('pgbouncer', 'show stats') AS _(
    	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
	);

DROP VIEW IF EXISTS pgbouncer.stats_averages;
CREATE VIEW pgbouncer.stats_averages AS
    SELECT * FROM dblink('pgbouncer', 'show stats_averages') AS _(
        database text,
        xact_count bigint,
        query_count bigint,
        bytes_received bigint,
        bytes_sent bigint,
        xact_time bigint,
        query_time bigint,
        wait_time bigint
    );

DROP VIEW IF EXISTS pgbouncer.stats_totals;
CREATE VIEW pgbouncer.stats_totals AS
    SELECT * FROM dblink('pgbouncer', 'show stats_totals') AS _(
        database text,
        xact_count bigint,
        query_count bigint,
        bytes_received bigint,
        bytes_sent bigint,
        xact_time bigint,
        query_time bigint,
        wait_time bigint
    );

DROP VIEW IF EXISTS pgbouncer.totals;
CREATE VIEW pgbouncer.totals AS
    SELECT * FROM dblink('pgbouncer', 'show totals') AS _(
        name text,
        value bigint
    );

DROP VIEW IF EXISTS pgbouncer.users;
CREATE VIEW pgbouncer.users AS
    SELECT * FROM dblink('pgbouncer', 'show users') AS _(
        name text,
        pool_mode text
    );

DROP VIEW IF EXISTS pgbouncer.fds;
CREATE VIEW pgbouncer.fds AS
    SELECT * FROM dblink('pgbouncer', 'show fds') AS _(
        fd bigint,
        task text,
        "user" text,    
        database text,
        addr text,
        port int,
        cancel numeric,
        link text,
        client_encoding text,
        std_strings text,
        datestyle text,
        timezone text,
        password text
    );

COMMIT;

This is now bundled in the pgbouncer_fdw extension available at https://github.com/CrunchyData/pgbouncer_fdw and used in pgMonitor (https://github.com/CrunchyData/pgmonitor)

@davidfetter also wrote something similar: https://github.com/davidfetter/pgbouncer_wrapper