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

​

环境

• 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