davidfetter / pgbouncer_wrapper Goto Github PK

View Code? Open in Web Editor NEW

21.0 5.0 6.0 47 KB

SQL wrapper around pgbouncer's console

License: MIT License

Makefile 2.06% PLpgSQL 87.74% Perl 10.20%

pgbouncer sql reporting observability

pgbouncer_wrapper's Introduction

pgbouncer_wrapper

This project was originally inspired by Peter Eisentraut's excellent post

If you've ever wanted to run SQL queries against pgbouncer's SHOW output, this is a handy way to do just that.

To build it, just do this:

make
make install

Be sure that you have pg_config installed and in your path. If you used a package management system such as RPM to install PostgreSQL, be sure that the -devel package is also installed. If necessary tell the build process where to find it:

env PG_CONFIG=/path/to/pg_config make && make install

Once pgbouncer_wrapper is installed, you can add it to a database like this:

CREATE EXTENSION pgbouncer_wrapper;

You can find how many clients are coming from each address like this:

SELECT
    addr, count(*)
FROM
    clients
GROUP BY addr
ORDER BY count(*) DESC;

You can change pgbouncer settings like this:

SELECT set('default_pool_size', '300');

To see whether the current configuration is out of step with pgbouncer.ini, you can do:

SELECT
    count(*) > 0 AS "out of step"
FROM
    regexp_split_to_table(
        pg_read_file('/etc/pgbouncer/pgbouncer.ini'),
        E'\n'
    ) WITH ORDINALITY AS t(l, o)
JOIN
    config c
    ON (
        c.key =
        (string_to_array(t.l, ' = '))[1]
    )
WHERE
    format('%s = %s', key, value) <> l;

Dependencies

pgbouncer_wrapper depends on dblink.

Copyright and License

pgbouncer_wrapper's People

Contributors

Stargazers

Watchers

Forkers

gplv2 blunney1 valehdba dattatraya-nirmal fabriziomello minh123viptanvan

pgbouncer_wrapper's Issues

Error issuing set command

I get an error trying to execute the sample set command in the README:
SELECT set('default_pool_size', '300');

ERROR:  function dblink_exec(unknown, text) does not exist
LINE 3:     dblink_exec('pgbouncer', format('SET %s=%L', key, value)...
            ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:
SELECT
    dblink_exec('pgbouncer', format('SET %s=%L', key, value));

CONTEXT:  SQL function "set" during inlining

Add config table

Per conversation with and thanks to @fabriziomello, modify pgbouncer_wrapper as folllows:

Add a config table with (bouncer_name TEXT PRIMARY KEY, bouncer_connect_string TEXT, active BOOLEAN NOT NULL DEFAULT true);
Instead of creating a server, do a LATERAL JOIN to the config table with the connect string instead of the server that's been dropped. The view needs to include the bouncer_name column at a minimum to disambiguate info from multiple bouncers.
This way, it's as easy to monitor N pgbouncers as it is to monitor one.

slight change needed to SHOW command for fds

In pgbouncer 1.14.0 I noticed a slight change to the SQL mappings for one of the SHOW commands:

Show fds has 2 additional text columns:
scram_client_key
scram_server_key

I have verified all the other SHOW commands and everything else is still in sync.

New columns for version 1.18

active_sockets --> application_name text (right after the tls column and before the recv_pos column)
comment --> A string containing the application_name set on the linked client connection, or empty if this is not set, or if there is no linked connection.

clients --> application_name text (last column, right after tls column)

-- see my view below where --v18 comment is:
CREATE VIEW pgbouncer.pools AS
SELECT * FROM dblink('pgbouncer', 'show pools') AS _(
database text,
"user" text,
cl_active integer,
cl_waiting integer,
-- cl_cancel_req integer, --v18 removed
cl_active_cancel_req integer, --v18
cl_waiting_cancel_req integer, --v18
sv_active integer,
sv_active_cancel integer, --v18
sv_being_canceled integer, --v18
sv_idle integer,
sv_used integer,
sv_tested integer,
sv_login integer,
maxwait integer,
maxwait_us integer,
pool_mode text
);


remove comment on removed column above!!!!!

COMMENT ON COLUMN pgbouncer.pools."cl_active_cancel_req" IS $$Client connections that have forwarded query cancellations to the server and are waiting for the server response.$$;
COMMENT ON COLUMN pgbouncer.pools."cl_waiting_cancel_req" IS $$Client connections that have not forwarded query cancellations to the server yet.$$;
COMMENT ON COLUMN pgbouncer.pools."sv_active_cancel IS $$Server connections that are currently forwarding a cancel request.$$;
COMMENT ON COLUMN pgbouncer.pools."sv_being_canceled IS $$Servers that normally could become idle but are waiting to do so until all in-flight cancel requests have completed that were sent to cancel a query on this server.$$;

servers --> application_name text (last column, right after tls column)

sockets --> application_name text (after tls column)

new columns for version 1.16

Here are the columns missing for pgbouncer version 1.16:

show databases --> missing min_pool_size (after pool_size and before reserve_pool)
show pools --> missing cl_cancel_req (after cl_waiting and before sv_active)

make install fails

Hi David,

Thanks for making pgbounder_wrapper.

I'm getting a strange error upon make install.

Below is the full log:

$ lsb_release -a
No LSB modules are available.
Distributor ID:	Ubuntu
Description:	Ubuntu 20.04.2 LTS
Release:	20.04
Codename:	focal

$ psql --version
psql (PostgreSQL) 13.2 (Ubuntu 13.2-1.pgdg20.04+1)

$ make
cp sql/pgbouncer_wrapper.sql sql/pgbouncer_wrapper--1.2.0.sql

$ sudo make install
/bin/mkdir -p '/usr/share/postgresql/13/extension'
/bin/mkdir -p '/usr/share/postgresql/13/extension'
/bin/mkdir -p '/usr/share/doc/postgresql-doc-13/extension'
/usr/bin/install -c -m 644 .//pgbouncer_wrapper.control '/usr/share/postgresql/13/extension/'
/usr/bin/install -c -m 644 .//sql/pgbouncer_wrapper--1.2.0.sql .//sql/pgbouncer_wrapper--1.2.0.sql  '/usr/share/postgresql/13/extension/'
/usr/bin/install: will not overwrite just-created '/usr/share/postgresql/13/extension/pgbouncer_wrapper--1.2.0.sql' with './/sql/pgbouncer_wrapper--1.2.0.sql'
make: *** [/usr/lib/postgresql/13/lib/pgxs/src/makefiles/pgxs.mk:233: install] Error 1

new sql for pgbouncer 1.9.0

Hi David,

Attached is a new sql file that works for pgbouncer 1.9.0. I had to change the extension from .sql to .txt since it prevented me from uploading it.
pgbouncer_wrapper--1.9.0.txt

Ha, I can't even type correctly! title should be 1.9.0 not 9.1.0