CODEWITHSUNDEEP
postgresqlmonitoring
Russell Christopher
Russell Christopher

Reputation: 1707

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.

Upvotes: 31

Views: 59298

Answers (5)

Mahdi mehrabi
Mahdi mehrabi

Reputation: 1744

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

Upvotes: 2

user3617786
user3617786

Reputation: 171

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

Upvotes: 11

ertx
ertx

Reputation: 1544

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

(Tools ->ServerStatus)

Upvotes: 27

tscho
tscho

Reputation: 2054

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.

Upvotes: 19

Patrick
Patrick

Reputation: 17973

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/

Upvotes: 0

Related Questions