Is it possible to get the history of requests made in postgres

Question

Can I get a history of requests made in postgres? and is it possible to get the time spent on each request? I am currently trying to identify slow queries in the application I'm working on.

I am using Postgres 8.3.5

Answer 1

There is no history in the database itself, if you use psql, you can use "\ s" to see your command history there.

You can receive future requests or other types of operations in the log files by setting log_statement in the postgresql.conf file. Instead, you most likely want a log_min_duration_statement , which, if you set it to 0, will log all requests and their duration in the logs. This can be useful after your applications run live, if you set a higher value, you will see only long queries that can be useful for optimization (you can run EXPLAIN ANALYZE on the queries you find there to find out why are they slow).

Another useful thing to know in this area is that if you run psql and say "\ timing", it will show how long each statement will take after that. Therefore, if you have a sql file that looks like this:

\timing select 1; 

You can run it with the correct flags and see how each statement alternates with how much time it took. Here's what the result looks like:

 $ psql -ef test.sql Timing is on. select 1; ?column? ---------- 1 (1 row) Time: 1.196 ms 

This is convenient because you do not need to be the superuser of the database to use it, unlike modifying the configuration file, and it is easier to use if you are developing new code and want to test it.

Answer 2

If you want to identify slower queries than , the method should use the log_min_duration_statement parameter (in postgresql. Conf or set for each database using ALTER DATABASE SET).

When you log data, you can use grep or some specialized tools - for example, pgFouine or my own parser - which does not have the proper documents, but despite this, it works pretty well.

Answer 3

pgBadger is another option - also listed here: https://github.com/dhamaniasad/awesome-postgres#utilities

Some additional configuration is required to capture the necessary data in postgres logs, but see the official website.

Answer 4

FYI for those using the Navicat UI :

You must set your preferences for using the file as where to store history.

If it is empty, your Navicat will be empty.

PS: I have no connection with Navicat or its branches or not. I just want to help.

Answer 5

You can use the DATAGRIP IDE. It keeps a history of the last 300 queries (as of November 2018).

However, this will not show query performance. Only their story