blocks|key|1090316|text|您可以使用log_statement配置设置来获取服务器的所有查询列表|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1090317|https://www.postgresql.org/docs/current/static/runtime-config-logging.html#guc-log-statement|offset|length|1090318|只需设置它和日志文件路径，您就会得到该列表。您还可以将其配置为仅记录长时间运行的查询。|1090319|然后，您可以获取这些查询，并对它们运行EXPLAIN，以找出它们发生了什么。|1090320|https://www.postgresql.org/docs/9.2/static/using-explain.html|1090321|entityMap|0|LINK|mutability|MUTABLE|url|1^0|0|0|2K|0|0|0|0|0|1P|1|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|U|8|@]|9|@$D|V|E|W|1|X]]|A|$]]|$1|F|3|G|5|6|7|Y|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|Z|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|10|8|@]|9|@$D|11|E|12|1|13]]|A|$]]|$1|L|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|M|$N|$5|O|P|Q|A|$R|C]]|S|$5|O|P|Q|A|$R|K]]]]

You can use the log_statement config setting to get the list of all the queries to a server

<a href="https://www.postgresql.org/docs/current/static/runtime-config-logging.html#guc-log-statement" rel="noreferrer">https://www.postgresql.org/docs/current/static/runtime-config-logging.html#guc-log-statement</a>

Just set that, and the logging file path and you'll have the list. You can also configure it to only log long running queries. 

You can then take those queries and run EXPLAIN on them to find out what's going on with them.

<a href="https://www.postgresql.org/docs/9.2/static/using-explain.html" rel="noreferrer">https://www.postgresql.org/docs/9.2/static/using-explain.html</a>

blocks|key|1090394|text|除了Joshua的回答，要查看which+queries+are+currently+running，只需在任何时候(例如，在PGAdminIII的查询窗口中)发出以下语句：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1090395|SELECT+datname,procpid,current_query+FROM+pg_stat_activity;|code-block|syntax|javascript|1090396|示例输出：|1090397|+++++datname++++%7C+procpid+%7C+current_query
+---------------%2B---------%2B---------------
++mydatabaseabc+%7C++++2587+%7C+<IDLE>
++anotherdb+++++%7C+++15726+%7C+SELECT+*+FROM+users+WHERE+id=123+;
++mydatabaseabc+%7C+++15851+%7C+<IDLE>
+(3+rows)|1090398|entityMap|0|LINK|mutability|MUTABLE|url|https://web.archive.org/web/20200110143058/http://chrismiles.info/systemsadmin/databases/articles/viewing-current-postgresql-queries/^0|F|Z|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@$A|V|B|W|1|X]]|C|$]]|$1|D|3|E|5|F|7|Y|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|C|$]]|$1|K|3|L|5|F|7|10|8|@]|9|@]|C|$G|H]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|C|$]]]|N|$O|$5|P|Q|R|C|$S|T]]]]

Adding to Joshua's answer, to see <a href="https://web.archive.org/web/20200110143058/http://chrismiles.info/systemsadmin/databases/articles/viewing-current-postgresql-queries/" rel="nofollow noreferrer">which queries are currently running</a> simply issue the following statement at any time (e.g. in PGAdminIII's query window):
<pre><code>SELECT datname,procpid,current_query FROM pg_stat_activity;
</code></pre>
Sample output:
<pre><code> datname | procpid | current_query
 ---------------+---------+---------------
 mydatabaseabc | 2587 | &lt;IDLE&gt;
 anotherdb | 15726 | SELECT * FROM users WHERE id=123 ;
 mydatabaseabc | 15851 | &lt;IDLE&gt;
 (3 rows)
</code></pre>

blocks|key|1090456|text|我发现了pgBadger+(http://dalibo.github.io/pgbadger/)，它是一个神奇的工具，多次救了我的命。这里有一个报告的例子：http://dalibo.github.io/pgbadger/samplev4.html。如果你打开它并进入“顶部”菜单，你可以看到最慢的查询和耗时的查询。然后，您可以询问详细信息并看到按小时显示查询的漂亮图形，如果您使用详细信息按钮，则可以看到漂亮形式的SQL文本。所以我可以看到这个工具是免费和完美的。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1090457|entityMap|0|LINK|mutability|MUTABLE|url|http://dalibo.github.io/pgbadger/|1|http://dalibo.github.io/pgbadger/samplev4.html^0|E|X|0|26|1A|1|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]|$A|R|B|S|1|T]]|C|$]]|$1|D|3|-4|5|6|7|U|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]|L|$5|G|H|I|C|$J|M]]]]

I discovered pgBadger (<a href="http://dalibo.github.io/pgbadger/" rel="noreferrer">http://dalibo.github.io/pgbadger/</a>) and it is a fantastic tool that saved my life many times. Here is an example of report: <a href="http://dalibo.github.io/pgbadger/samplev4.html" rel="noreferrer">http://dalibo.github.io/pgbadger/samplev4.html</a>.
If you open it and go to 'top' menu you can see the slowest queries and the time consuming queries. 
Then you can ask details and see nice graphs that show you the queries by hour and if you use detail button you can see the SQL text in a pretty form. So I can see that this tool is free and perfect.

blocks|key|1090504|text|您可以使用pg_stat_statements扩展。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1090505|如果在docker中运行数据库，只需在docker-compose.yml中添加此命令，否则只需查看安装说明即可：|style|CODE|1090506|command:+postgres+-c+shared_preload_libraries=pg_stat_statements+-c+pg_stat_statements.track=all+-c+max_connections=200|code-block|syntax|javascript|1090507|然后在数据库中运行以下查询：|1090508|CREATE+EXTENSION+pg_stat_statements;|1090509|现在来看看花了更多时间运行的操作：|1090510|SELECT+*+FROM+pg_stat_statements+ORDER+BY+total_time/calls+DESC+LIMIT+10;|1090511|或者使用该视图上的其他查询来查找您正在查找的内容。|1090512|entityMap|0|LINK|mutability|MUTABLE|url|https://www.postgresql.org/docs/8.4/pgstatstatements.html^0|5|I|0|0|J|I|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@$A|15|B|16|1|17]]|C|$]]|$1|D|3|E|5|6|7|18|8|@$A|19|B|1A|F|G]]|9|@]|C|$]]|$1|H|3|I|5|J|7|1B|8|@]|9|@]|C|$K|L]]|$1|M|3|N|5|6|7|1C|8|@]|9|@]|C|$]]|$1|O|3|P|5|J|7|1D|8|@]|9|@]|C|$K|L]]|$1|Q|3|R|5|6|7|1E|8|@]|9|@]|C|$]]|$1|S|3|T|5|J|7|1F|8|@]|9|@]|C|$K|L]]|$1|U|3|V|5|6|7|1G|8|@]|9|@]|C|$]]|$1|W|3|-4|5|6|7|1H|8|@]|9|@]|C|$]]]|X|$Y|$5|Z|10|11|C|$12|13]]]]

You can use the <a href="https://www.postgresql.org/docs/8.4/pgstatstatements.html" rel="nofollow noreferrer">pg_stat_statements</a> extension.
If running the db in docker just add this command in <code>docker-compose.yml</code>, otherwise just look at the installation instructions for your setup:
<pre><code>command: postgres -c shared_preload_libraries=pg_stat_statements -c pg_stat_statements.track=all -c max_connections=200
</code></pre>
And then in the db run this query:
<pre><code>CREATE EXTENSION pg_stat_statements;
</code></pre>
Now to see the operations that took more time run:
<pre><code>SELECT * FROM pg_stat_statements ORDER BY total_time/calls DESC LIMIT 10;
</code></pre>
Or play with other queries over that view to find what you are looking for.

I need to see the queries submitted to a PostgreSQL server. Normally I would use SQL Server profiler to perform this action in SQL Server land, but I'm yet to find how to do this in PostgreSQL. There appears to be quite a few pay-for tools, I am hoping there is an open source variant.

Is there a PostgreSQL equivalent of SQL Server profiler?

日志文件

SQL Server

PostgreSQL

数据库

服务器

Docker

我需要查看提交到PostgreSQL服务器的查询。通常我会使用SQL Server profiler在SQL Server平台上执行此操作，但我还不知道如何在PostgreSQL中执行此操作。似乎有相当多的付费工具，我希望有一个开源的变体。

问是否有相当于SQL Server事件探查器的PostgreSQL？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问是否有相当于SQL Server事件探查器的PostgreSQL？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问是否有相当于SQL Server事件探查器的PostgreSQL？
EN