How can I check all the current SQL queries being executed by the PostgreSQL?

https://access.redhat.com/solutions/1248183

 SOLUTION IN PROGRESS - 已更新 2014年十一月5日00:35 - 

English 

环境

• Red Hat Enterprise Linux 5
• Red Hat Enterprise Linux 6
• PostgreSQL 8.4 or higher

问题

• How can I check all the current SQL queries being executed by the PostgreSQL?

决议

• To show all current queries:

Raw

  SELECT datname,procpid,current_query FROM pg_stat_activity;

  SELECT datname,usename,procpid,client_addr,waiting,query_start,current_query FROM pg_stat_activity ;

• To group the queries by status:

Raw

SELECT count(*) as counter, usename, current_query FROM pg_stat_activity GROUP BY usename,current_query ORDER BY counter DESC;

• To save to a log all the queries that takes more the 2 seconds, you can use:

Raw

 # vim /var/lib/pgsql/data/postgresql.conf
    log_min_duration_statement = 2000
 # service postgresql restart