通过pgbouncer查找查询源 [英] Find source of query through pgbouncer

问题描述

我正在尽我所能弄清楚特定类型的更新查询来自哪个主机.

I am trying to figure out as much as I can about what host a particular type of update query is coming from.

我的数据库是在本地运行pgbouncer的PostgreSQL 9.0.23，用于连接池.池类型是事务池.

My database is PostgreSQL 9.0.23 locally running pgbouncer for connection pooling. The pooling type is transaction pooling.

数据库中的触发器保存了pg_stat_activity中的信息，告诉了我有关pgbouncer连接的信息，但并没有帮助我弄清楚背后的原因.

A trigger in the database saving information from pg_stat_activity tells me about the pgbouncer connection, but doesn't help me figure out who is behind that.

所讨论的数据库是负载下的生产数据库.因此，删除pgbouncer不是一个选择.让pgbouncer编写冗长的日志可能也是一个坏主意.

The database in question is a production database under load. Therefore removing pgbouncer is not an option. Having pgbouncer write a verbose log is probably also a bad idea.

是否有任何合理的方法来找出查询最终来自哪个主机?

Is there any reasonable way to find out which host the query ultimately comes from?

推荐答案

我用来从pgbouncer跟踪客户端的方法是将pg_stat_statements与pgbouncer show clients命令结合在一起.这是一个示例:

The way I use to keep tracking clients from pgbouncer is joining pg_stat_statements with pgbouncer show clients command. Here is an example:

t=# create extension "postgres_fdw";
CREATE EXTENSION
t=# CREATE SERVER pgbouncer FOREIGN DATA WRAPPER postgres_fdw OPTIONS ( host '10.10.10.10', port '6432', dbname 'pgbouncer');
CREATE SERVER

(总的来说，您只能使用dblink或postgres_fdw-我使用mix支持产品上的过时用法...如果您没有任何注释，请忽略它...)

(By all means you can use just dblink or just postgres_fdw - I use mix to support obsolete usage on prod... If you did not have any notes, just ignore that...)

t=# create extension "dblink";
CREATE EXTENSION
t=#  create view bnc_client AS SELECT _.type,
        _."user",
        _.database,
        _.state,
        _.addr,
        _.port,
        _.local_addr,
        _.local_port,
        _.connect_time,
        _.request_time,
        _.ptr,
        _.link,
        _.remote_pid,
        _.tls
       FROM dblink('pgbouncer'::text, 'show clients'::text) _(type text, "user" text, database text, state text, addr text, port integer, local_addr text, local_port integer, connect_time timestamp with time zone, request_time timestamp with time zone, ptr text, link text, remote_pid smallint, tls text);
CREATE VIEW
t=# create user mapping FOR vao server pgbouncer options (user 'pgbouncer_known_user', password 'password_here');
CREATE USER MAPPING

现在我们可以使用pg_stat_statements加入pgbouncer视图:

Now we can join pgbouncer view with pg_stat_statements:

t=# select
        datname
      , usename
      , p.state
      , case when b.user is not null then 'pgBouncer' else application_name end app
      , case when b.user is null then client_addr else addr::inet end ip
      , b.user
      , b.state "bState"
      , case when waiting then true else null end w
      , b.connect_time
      , query_start
      , md5(query)::uuid
      , pid
    from pg_stat_activity p
    left outer join bnc_client b
      on addr||':'||b.port = regexp_replace(p.application_name,'^.{0,}(- )','')
    where pid <> pg_backend_pid()
  ;
 datname | usename | state |          app           |      ip      | user | bState | w | connect_time |          query_start
|                 md5                  |  pid
---------+---------+-------+------------------------+--------------+------+--------+---+--------------+-------------------------------
+--------------------------------------+-------
 dbn  | usr   | idle  |                        | 192.168.0.1 |      |        |   |              | 2017-03-09 17:19:46.206643+00
| d1730c52-dffd-3650-a399-23f4dd4aa456 | 12614
 dbn  | usr   | idle  | app - 10.10.10.10:24514 | 10.10.10.10  |      |        |   |              | 2017-03-10 11:24:34.999174+00
| 92a0340c-9ecc-9375-37c0-e70e8b225db4 | 22563
(2 rows)

此处app - 10.10.10.10:24514表示此pid来自pgbouncer，并从0.10.10.10连接到pgbouncer

Here app - 10.10.10.10:24514 means this pid is from pgbouncer, and to pgbouncer it connected from 0.10.10.10

这篇关于通过pgbouncer查找查询源的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！