Postgres: How can I see all SQL statements being executed by the database server?

Solution 1:

The config option you're looking for is log_statement = "all" (if you just want the statements), or log_min_statement_duration = <some number> if you're just after "slow" queries (for some value of "slow"). See http://www.postgresql.org/docs/current/static/runtime-config-logging.html for more details on logging configuration.

Solution 2:

The auto_explain module is very useful for this. It'll not only log the statements, it'll log their execution plans and can even log statements run within PL/PgSQL functions. The performance hit is fairly low unless you enable analyze, in which case you incur a fair bit of timing overhead for all queries.

See auto_explain in the documentation.


Solution 3:

Of course, you can detect slowest queries by yourself, but I advise you to use pgFouine — a PostgreSQL log analyzer. It`s easy to install and really useful.

Sample reports: here and here.