Is there a PostgreSQL equivalent of SQL Server profiler?

Question

I need to see the queries submitted to a PostgreSQL server. Normally I would use SQL Server profiler to perform this action in SQL Server land, but I'm yet to find how to do this in PostgreSQL. There appears to be quite a few pay-for tools, I am hoping there is an open source variant.

Answer 1

You can use the log_statement config setting to get the list of all the queries to a server

https://www.postgresql.org/docs/current/static/runtime-config-logging.html#guc-log-statement

Just set that, and the logging file path and you'll have the list. You can also configure it to only log long running queries.

You can then take those queries and run EXPLAIN on them to find out what's going on with them.

https://www.postgresql.org/docs/9.2/static/using-explain.html

Answer 2

Adding to Joshua's answer, to see which queries are currently running simply issue the following statement at any time (e.g. in PGAdminIII's query window):

SELECT datname,procpid,current_query FROM pg_stat_activity; 

Sample output:

     datname    | procpid | current_query  ---------------+---------+---------------   mydatabaseabc |    2587 | <IDLE>   anotherdb     |   15726 | SELECT * FROM users WHERE id=123 ;   mydatabaseabc |   15851 | <IDLE>  (3 rows)