31

I'd like to monitor the queries getting sent to my database from an application. To that end, I've found pg_stat_activity, but more often then not, the rows which are returned read " in transaction". I'm either doing something wrong, am not fast enough to see the queries come through, am confused, or all of the above!

Can someone recommend the most idiot-proof way to monitor queries running against PostgreSQL? I'd prefer some sort of easy-to-use UI based solution (example: SQL Server's "Profiler"), but I'm not too choosy.

Improve this question

5 Answers 5

Reset to default
27

PgAdmin offers a pretty easy-to-use tool called server monitor

(Tools ->ServerStatus)

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

This tool appears to have disappeared in PgAdmin 4.
@Lasf - Agreed... any idea why this is gone?
I have found PgAdmin 4 to be a worse experience than PgAdmin 3 in general. =(
why was that removed from pgadmin?
19

With PostgreSQL 8.4 or higher you can use the contrib module pg_stat_statements to gather query execution statistics of the database server.

Run the SQL script of this contrib module pg_stat_statements.sql (on ubuntu it can be found in /usr/share/postgresql/<version>/contrib) in your database and add this sample configuration to your postgresql.conf (requires re-start):

custom_variable_classes = 'pg_stat_statements'
pg_stat_statements.max = 1000
pg_stat_statements.track = top # top,all,none
pg_stat_statements.save = off

To see what queries are executed in real time you might want to just configure the server log to show all queries or queries with a minimum execution time. To do so set the logging configuration parameters log_statement and log_min_duration_statement in your postgresql.conf accordingly.

Improve this answer

1 Comment

While it's a good answer, it doesn't cover how one might collect data from pg_stat_statements, because it's ever changing and have only cumulative stats about query executions. For that you might use some of the tools like okmeter.io or PoWA that will show you charts and detailed info about query execution and performance. Disclaimer - I work for okmeter.io
11

pg_activity is what we use. https://github.com/dalibo/pg_activity

It's a great tool with a top-like interface.

You can install and run it on Ubuntu 21.10 with:

sudo apt install pg-activity
pg_activity
Improve this answer

3 Comments

I tried to get this to work, but it's not reporting any queries. Looks great, connects fine, just not sure what's up, why I'm getting an empty screen when I execute a query.
@andyczerwonka I can see queries e.g. done by this test script, but it only shows queries being currently executed, so you won't see any/most short queries. For a full query log rather than a live query monitoring, see: stackoverflow.com/questions/722221/… Ubuntu has it on by default under /var/log: stackoverflow.com/questions/722221/…
pg_activity will definitely throw pg_activity: FATAL: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: role "my_user" does not exist (exit code 1)
2

If you are using Docker Compose, you can add this line to your docker-compose.yaml file:

command: ["postgres", "-c", "log_statement=all"]

now you can see postgres query logs in docker-compose logs with

docker-compose logs -f

or if you want to see only postgres logs

docker-compose logs -f [postgres-service-name]

https://stackoverflow.com/a/58806511/10053470

Improve this answer

Comments

0

I haven't tried it myself unfortunately, but I think that pgFouine can show you some statistics.

Although, it seems it does not show you queries in real time, but rather generates a report of queries afterwards, perhaps it still satisfies your demand?

You can take a look at http://pgfouine.projects.postgresql.org/

Improve this answer

Comments

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.