4

I am running PostgreSQL on my Linux terminal and If we do select pg_backend_pid(); it gives pid for specific session.

Below gives us only the last query executed

select pid,
       usename as username,
       datname as database_name,
       query,
       application_name,
       backend_start,
       state,
       state_change
from pg_stat_activity
where pid = 'your-pid';

but looks like this \s gives us all the history of query but not with dates. can we get dates along with them

and if we do save output to a filename where does it gets stored on Linux server?

\s filename

Kindly suggest if there are more specific way, actually I am looking for the queries executed on particular date.

2
  • 1
    The history you see with \s is managed by the psql client not by the server.
    – user1822
    Nov 27, 2020 at 19:22
  • okee.. I quit PostgreSQL and could see that file at my local Linux server :-) but its doesn't have the dates. Nov 27, 2020 at 20:40

1 Answer 1

5

PostgreSQL doesn't remember the times when queries are run.

If you need that information, set log_statement = 'all' and include %m in log_line_prefix. Then the queries and their time are written to the log file.

5
  • Thanks and where we can edit this.. in the configuration files.. Nov 28, 2020 at 2:45
  • Yes; change postgresql.conf and reload. Nov 28, 2020 at 2:45
  • And regardless of how many time we logged in and ran commands.. \s will capture all the previous query's.. isn't ? Nov 28, 2020 at 3:41
  • If possible can you also update info regarding conf file... Will be helpful for others or new comer like me. Thanks Nov 28, 2020 at 3:42
  • 1
    There are several ways to change the configuration, all documented, so I see no worth in that. \s is a psql tool to access the history on the client. Nov 28, 2020 at 7:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.