应用程序实时监控PostgreSQL查询吗? [英] App to monitor PostgreSQL queries in real time?

问题描述

我想监视从应用程序发送到数据库的查询.为此，我找到了pg_stat_activity，但更常见的是，返回的行读为"in transaction".我要么做错了什么，不够快以至于无法通过查询，要么感到困惑，要么就是以上所有！！

I'd like to monitor the queries getting sent to my database from an application. To that end, I've found pg_stat_activity, but more often then not, the rows which are returned read " in transaction". I'm either doing something wrong, am not fast enough to see the queries come through, am confused, or all of the above!

有人可以推荐最防白痴的方法来监视针对PostgreSQL运行的查询吗?我希望使用一种易于使用的基于UI的解决方案(例如:SQL Server的"Profiler")，但我不太挑剔.

Can someone recommend the most idiot-proof way to monitor queries running against PostgreSQL? I'd prefer some sort of easy-to-use UI based solution (example: SQL Server's "Profiler"), but I'm not too choosy.

推荐答案

对于PostgreSQL 8.4或更高版本，您可以使用 contrib模块

With PostgreSQL 8.4 or higher you can use the contrib module pg_stat_statements to gather query execution statistics of the database server.

在数据库中运行此贡献模块pg_stat_statements.sql的SQL脚本(在ubuntu上可以在/usr/share/postgresql/<version>/contrib中找到)，并将此示例配置添加到您的postgresql.conf中(需要重新启动):

Run the SQL script of this contrib module pg_stat_statements.sql (on ubuntu it can be found in /usr/share/postgresql/<version>/contrib) in your database and add this sample configuration to your postgresql.conf (requires re-start):

custom_variable_classes = 'pg_stat_statements'
pg_stat_statements.max = 1000
pg_stat_statements.track = top # top,all,none
pg_stat_statements.save = off

要查看实时执行哪些查询，您可能只想配置服务器日志以显示所有查询或执行时间最短的查询.为此，请设置日志记录配置参数 log_statement和log_min_duration_statement相应地放在您的postgresql.conf中.

To see what queries are executed in real time you might want to just configure the server log to show all queries or queries with a minimum execution time. To do so set the logging configuration parameters log_statement and log_min_duration_statement in your postgresql.conf accordingly.

这篇关于应用程序实时监控PostgreSQL查询吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！