App to monitor PostgreSQL queries in real time?

Question

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.

score 27 · Answer 1 · answered Dec 22 '11 at 08:14

27

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

(Tools ->ServerStatus)

answered Dec 22 '11 at 08:14

ertx

1,494
2
15
21

21

This tool appears to have disappeared in PgAdmin 4. – Lasf May 14 '17 at 19:55
@Lasf - Agreed... any idea why this is gone? – Scott Skiles Feb 23 '18 at 19:33
9

I have found PgAdmin 4 to be a worse experience than PgAdmin 3 in general. =( – sivabudh Jul 22 '18 at 16:08
1

why was that removed from pgadmin? – uberrebu Aug 10 '21 at 10:56

score 19 · Accepted Answer · answered Dec 22 '11 at 00:29

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.

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 — Pavel T, Aug 07 '18 at 10:18

score 6 · Answer 3 · edited Nov 29 '21 at 23:39

6

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

edited Nov 29 '21 at 23:39

Ciro Santilli OurBigBook.com

347,512
102
1,199
985

answered Feb 11 '20 at 00:28

user3617786

121
2
5

1

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 Feb 11 '21 at 23:42
@andyczerwonka I can see queries e.g. [done by this test script](https://github.com/cirosantilli/cirosantilli.github.io/blob/fa90fef378804f7a57c3596b0e7210b781da3bc3/sequelize/raw/parallel_create_delete_empty_tag.js), 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: https://stackoverflow.com/questions/722221/how-to-log-postgresql-queries Ubuntu has it on by default under `/var/log`: https://stackoverflow.com/questions/722221/how-to-log-postgresql-queries#comment65038122_722236 – Ciro Santilli OurBigBook.com Nov 29 '21 at 23:42
`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) – Chris Vilches Jan 28 '23 at 12:18

Mahdi mehrabi · Answer 4 · 2022-01-26T03:37:28.423

1

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

edited Jan 26 '22 at 03:37

answered Jan 26 '22 at 03:32

Mahdi mehrabi

1,486
2
18
21

score 0 · Answer 5 · answered Dec 21 '11 at 23:12

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/

App to monitor PostgreSQL queries in real time?

5 Answers5

Linked