Mapping pgbouncer with pg_stat_activity

When we do use pgbounecr for connection pooling we do faces difficulties to trace IP of bad/poor performance query .

Here ,In this blog i am trying to get this archive by using PostgreSQL DB link to get more visibility of query from where it is coming 

Let's ses how can we do it .

JFI  -  I have installed pg-bouncer 1.9  

Step 1 - 

Create DB link extension .

 

Step 2 -

Create server 

Details need to be mentioned of pg-bouncer with pg-bouncer database 

Step 3 - 

Create user mapping 

Step 4 -

Create View which will fetch data by using server which is created in step 2 .

create view pg_bou_stat_activity AS SELECT _.type        , _."user"      , _.database    , _.state       , _.addr        , _.port        , _.local_addr  , _.local_port  , _.connect_time, _.request_time, _.wait        , _.wait_us     , _.close_needed, _.ptr         , _.link        , _.remote_pid  , _.tls         , _.recv_pos    , _.pkt_pos     , _.pkt_remain  , _.send_pos    , _.send_remain , _.pkt_avail   , _.send_avail   from dblink('pgbouncer'::text, 'show SOCKETS'::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 , wait           integer          , wait_us        integer          , close_needed   integer          , ptr            bytea          , 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          );

Step 5 - 

Execute the below query to check connection details . 

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 "state"
      , b.connect_time "pgbouncer_connect"
      , query_start 
      , pid
    from pg_stat_activity p
    left outer join pg_bou_stat_activity b
      on addr||':'||b.port = regexp_replace(p.application_name,'^.{0,}(- )','')
    where pid <> pg_backend_pid()

  ;

You will get below kind of result set .