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

msdnchina

于 2019-06-27 23:53:26 发布

阅读量200

点赞数

PostgreSQL 专栏收录该内容

211 篇文章

订阅专栏

本文介绍如何使用PostgreSQL内置的pg_stat_activity视图来查看当前正在执行的所有SQL查询，包括查询用户、进程ID、客户端地址及等待状态。此外，还提供了按状态分组查询的方法，以及如何设置日志记录所有超过2秒的查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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