'App to monitor PostgreSQL queries in real time?

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.

postgresqlmonitoring


Solution 1:[1]

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

(Tools ->ServerStatus)

Solution 2:[2]

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

Solution 3:[3]

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

Solution 4:[4]

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/

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 ertx
Solution 2 Ciro Santilli Путлер Капут 六四事
Solution 3
Solution 4 Patrick

Related Questions