-- Get current session info:

SELECT 
	datname as database ,usename as user ,client_addr,state, count(*) as total_connections,query
FROM pg_stat_activity
WHERE pid<>pg_backend_pid()
GROUP BY usename,client_addr,datname,state,query;

/*
 database |   user   | client_addr | state  | total_connections |                       query                       
----------+----------+-------------+--------+-------+---------------------------------------------------------------
 test11   | dba      | 192.168.2.1 | idle   |     1             | delete from tabl3 where id in (select id from tabl3 LIMIT 2);
          | postgres |             |        |     1             | 
 postgres | postgres |             | active |     1             | SELECT pg_sleep(20);
          | slave    | 127.0.0.1   | active |     1             | 
(4 rows)
 */

-- Get current session info with Duration

SELECT                                 
        datname as database ,usename as user ,client_addr,state,
        now() - backend_start as duration, count(*) as total_connections,query
FROM pg_stat_activity
WHERE state <> 'idle' 
AND pid<>pg_backend_pid()
GROUP BY usename,client_addr,datname,state, now() - backend_start, query

/*
 database |  user   | client_addr |        state        |        duration        | total_connections |                               query                                
----------+---------+-------------+---------------------+------------------------+-------------------+--------------------------------------------------------------------
 postgres | datadog | 127.0.0.1   | idle in transaction | 00:00:12.81569         |                 1 | select split_part(split_part(version(), 'PostgreSQL ', 2), ' ', 1)
          | slave   | 10.10.48.81 | active              | 3 days 20:13:40.662896 |                 1 | 
(2 rows)
*/

-- Get db connection count by user

SELECT 
	datname as database ,usename as user ,client_addr,state, count(*) as total_connections
FROM pg_stat_activity
WHERE pid<>pg_backend_pid()
GROUP BY usename,client_addr,datname,state;

/*
 database |      user      | client_addr |        state        | total_connections 
----------+----------------+-------------+---------------------+-------------------
          |                |             |                     |                 4
          | slave          | 10.10.48.81 | active              |                 1
 beacon   | beacon_service | 10.10.17.23 | idle                |                 6
 postgres | datadog        | 127.0.0.1   | idle                |                 1
          | postgres       |             |                     |                 1
 postgres | datadog        | 127.0.0.1   | idle in transaction |                 1
 beacon   | beacon_service | 10.10.17.24 | idle                |                36
(7 rows)
*/

-- Active sessions by state

SELECT 
	datname as database ,usename as user ,client_addr,state, count(*) as total_connections,query
FROM pg_stat_activity
WHERE state <> 'idle' 
AND pid<>pg_backend_pid()
GROUP BY usename,client_addr,datname,state,query;

/*
database |   user   | client_addr | state  | total_connections |                                                            query                                                             
----------+----------+-------------+--------+-------+------------------------------------------------------------------------------------------------------------------------------------------
 test11   | dba      | 192.168.2.1 | active |     1            | insert into tabl3 (cool_int,cool_json) values ( 113,'{"name":"testdata","ts": "var_created", "userId":  "1037" , "username":  "dba"}' );
 postgres | postgres |             | active |     1            | SELECT pg_sleep(20);
          | slave    | 127.0.0.1   | active |     1            | 
(3 rows)

*/

-- Queries running for greater than x ms

SELECT
 (now() - query_start)  as query_time_taken,
 datname as database ,usename as user,
 query 
FROM pg_stat_activity 
WHERE xact_start IS NOT NULL 
AND (now() - query_start) > interval '300 ms';

/*
query_time_taken | database |    user    |        query         
------------------+----------+------------+----------------------
 00:00:06.432377  | test11   | app_level3 | SELECT pg_sleep(20);
(1 row)
*/

-- Get blocking queries

SELECT pid, 
       usename, 
       pg_blocking_pids(pid) AS blocked_by, 
       query AS blocked_query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

 pid | usename | blocked_by | blocked_query 
-----+---------+------------+---------------
(0 rows)


/* References:
* https://www.postgresql.org/docs/10/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW
* http://bajis-postgres.blogspot.com/2014/01/handy-queries-of-postgresql-let-us.html
*/