1

How do we check the queries that were executed by user in PostgreSQL DB? I have a use case where some applications (AWS EMR, Python Scripts) execute queries on a daily basis on PostgreSQL DB. It would be helpful if I can see/log the last executed queries in case of any failures. I tried checking with pg_stat_activity table, but it lists down the queries that are executed by the DB Server internally. Any pointers on this problem would be helpful.

In AWS Redshift we achieve this through tables STV_RECENTS, STL_QUERY. I am looking for a similar feature/solution, also open to any other approach to implement this.

saurabhynwa
  • 51
  • 10

1 Answers1

0

If you want a dashboard and you are using RDS/Aurora you can you performance insight provided by AWS, by default it will list all queries executed by users.

However, pg_stat_activity gives the username who has initiated the query being executed you can use the below query which will give you how much time the query is taking, the username who initiated this query, and query in text

select now()-query_start,usename,query from pg_stat_activity where state='active';

you can setup a cron and append this data into a text file refer: How to use pg_stat_activity? https://www.postgresql.org/docs/9.2/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

Nikhil B
  • 353
  • 2
  • 7