database 是否可以获取在 postgres 中进行的查询历史记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1523446/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to get a history of queries made in postgres
提问by Chiwai Chan
Is it possible to get a history of queries made in postgres? and is it be possible to get the time it took for each query? I'm currently trying to identify slow queries in the application I'm working on.
是否可以获取在 postgres 中进行的查询历史记录？是否有可能获得每个查询所花费的时间？我目前正在尝试识别我正在处理的应用程序中的慢查询。
I'm using Postgres 8.3.5
我正在使用 Postgres 8.3.5
回答by Greg Smith
There's no history in the database itself, if you're using psql you can use "\s" to see your command history there.
数据库本身没有历史记录，如果您使用 psql，您可以使用“\s”在那里查看您的命令历史记录。
You can get future queries or other types of operations into the log files by setting log_statementin the postgresql.conf file. What you probably want instead is log_min_duration_statement, which if you set it to 0 will log all queries and their durations in the logs. That can be helpful once your apps goes live, if you set that to a higher value you'll only see the long running queries which can be helpful for optimization (you can run EXPLAIN ANALYZE on the queries you find there to figure out why they're slow).
您可以通过在 postgresql.conf 文件中设置log_statement将未来的查询或其他类型的操作获取到日志文件中。您可能想要的是log_min_duration_statement，如果您将其设置为 0 将在日志中记录所有查询及其持续时间。一旦您的应用程序上线，这会很有帮助，如果您将其设置为更高的值，您将只会看到有助于优化的长时间运行的查询（您可以对在那里找到的查询运行 EXPLAIN ANALYZE 以找出它们的原因' 很慢）。
Another handy thing to know in this area is that if you run psql and tell it "\timing", it will show how long every statement after that takes. So if you have a sql file that looks like this:
在这方面要知道的另一个方便的事情是，如果您运行 psql 并告诉它“\timing”，它将显示此后的每个语句需要多长时间。因此，如果您有一个如下所示的 sql 文件：
\timing
select 1;
You can run it with the right flags and see each statement interleaved with how long it took. Here's how and what the result looks like:
您可以使用正确的标志运行它，并查看每条语句与花费的时间交错。结果如下：
$ psql -ef test.sql
Timing is on.
select 1;
?column?
----------
1
(1 row)
Time: 1.196 ms
This is handy because you don't need to be database superuser to use it, unlike changing the config file, and it's easier to use if you're developing new code and want to test it out.
这很方便，因为您不需要成为数据库超级用户即可使用它，这与更改配置文件不同，如果您正在开发新代码并想要对其进行测试，它会更易于使用。
回答by Chiwai Chan
If you want to identify slow queries, than themethod is to use log_min_duration_statementsetting (in postgresql.conf or set per-database with ALTER DATABASE SET).
如果要识别慢查询，则方法是使用log_min_duration_statement设置（在 postgresql.conf 中或使用 ALTER DATABASE SET 设置每个数据库）。
When you logged the data, you can then use grep or some specialized tools - like pgFouineor my own analyzer- which lacks proper docs, but despite this - runs quite well.
当你记录数据时，你可以使用 grep 或一些专门的工具——比如pgFouine或我自己的分析器——缺乏适当的文档，但尽管如此——运行得很好。
回答by Vincent De Smet
pgBadger is another option - also listed here: https://github.com/dhamaniasad/awesome-postgres#utilities
pgBadger 是另一种选择 - 也在这里列出：https: //github.com/dhamaniasad/awesome-postgres#utilities
Requires some additional setup in advance to capture the necessary data in the postgres logs though, see the official website.
需要提前进行一些额外的设置以在 postgres 日志中捕获必要的数据，请参阅官方网站。
回答by JayRizzo
FYI for those using the UINavicat:
仅供使用UINavicat的用户参考：
You MUST set your preferences to utilize a file as to whereto store the history.
您必须设置您的首选项以使用文件where来存储历史记录。
If this is blank your Navicat will be blank.
如果这是空白的，您的 Navicat 将是空白的。
PS: I have no affiliation with or in association to Navicat or it's affiliates. Just looking to help.
PS：我与 Navicat 或其附属公司没有任何从属关系或关联。只是想帮忙。
