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.
I'm using Postgres 8.3.5
This question is related to
database
performance
postgresql
timing
pgBadger is another option - also listed here: 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.
If you want to identify slow queries, than the method is to use log_min_duration_statement setting (in postgresql.conf or set per-database with ALTER DATABASE SET).
When you logged the data, you can then use grep or some specialized tools - like pgFouine or my own analyzer - which lacks proper docs, but despite this - runs quite well.
Not logging but if you're troubleshooting slow running queries in realtime, you can query the pg_stat_activity
view to see which queries are active, the user/connection they came from, when they started, etc. Eg...
SELECT *
FROM pg_stat_activity
WHERE state = 'active'
See the pg_stat_activity
view docs.
You can use like
\s
it will fetch you all command history of the terminal, to export it to file using
\s filename
FYI for those using the UI Navicat:
You MUST set your preferences to utilize a file as to where
to store the history.
If this is blank your Navicat will be blank.
PS: I have no affiliation with or in association to Navicat or it's affiliates. Just looking to help.
If The question is the see the history of queries executed in the Command line. Answer is
As per Postgresql 9.3
, Try \?
in your command line, you will find all possible commands, in that search for history,
\s [FILE] display history or save it to file
in your command line, try \s
. This will list the history of queries, you have executed in the current session. you can also save to the file, as shown below.
hms=# \s /tmp/save_queries.sql
Wrote history to file ".//tmp/save_queries.sql".
hms=#
Source: Stackoverflow.com