Is there a PostgreSQL equivalent of SQL Server profiler?

Solution 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

Solution 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)

Solution 3:

I discovered pgBadger (http://dalibo.github.io/pgbadger/) and it is a fantastic tool that saved my life many times. Here is an example of report: http://dalibo.github.io/pgbadger/samplev4.html. If you open it and go to 'top' menu you can see the slowest queries and the time consuming queries. Then you can ask details and see nice graphs that show you the queries by hour and if you use detail button you can see the SQL text in a pretty form. So I can see that this tool is free and perfect.

Solution 4:

I need to see the queries submitted to a PostgreSQL server

As an option, if you use pgAdmin (on my picture it's pgAdmin 4 v2.1). You can observe queries via "Dashboard" tab: pgadmin4 query from application, dashboard

Solution 5:

You can use the pg_stat_statements extension.

If running the db in docker just add this command in docker-compose.yml, otherwise just look at the installation instructions for your setup:

command: postgres -c shared_preload_libraries=pg_stat_statements -c pg_stat_statements.track=all -c max_connections=200

And then in the db run this query:

CREATE EXTENSION pg_stat_statements;

Now to see the operations that took more time run:

SELECT * FROM pg_stat_statements ORDER BY total_time/calls DESC LIMIT 10;

Or play with other queries over that view to find what you are looking for.