How to use pg_stat_activity?
Solution 1
See this closely related answer for question "Postgres Query execution time".
pg_stat_activity
is a view in the pg_catalog
schema.
You can query it by SELECT
ing from it like any other table, e.g. SELECT * FROM pg_stat_activity
. The manual page you linked to explains its columns.
You'll sometimes find yourself wanting to join on other tables like pg_class
(tables), pg_namespace
(schemas), etc.
Limitations
pg_stat_activity
does not expose information about back-end memory use. You need to use operating-system level facilities for that. However it does tell you the process ID, active user, currently running query, activity status, time the last query started, etc. It's good for identifying long-running idle in transaction
sessions, very long running queries, etc.
Frankly, PostgreSQL's built-in monitoring is rather rudimentary. It's one of the areas that's not that exciting to work on, and commercial clients aren't often willing to fund it. Most people couple tools like check_postgres
with Icinga and Munin, or use Zabbix or other external monitoring agents.
In your case it sounds like you really want pg_stat_statements
, and/or PgBadger
log analysis with suitable logging settings and possibly the auto_explain
module.
Solution 2
Log into the PGAdmin, select your database and right click on your database. Then click on Query tool to run your query and run,
Select * from pg_stat_activity;
It will show you all the stats available and you have permissions to.
Richard
Updated on July 14, 2022Comments
-
Richard almost 2 years
I'd like to see which queries are being executed on a live Django application, and how much memory they are taking up. I have read that
pg_stat_activity
can be useful to monitor a Postgres database.I have looked at the Postgres documentation, but I have a very simple question that doesn't seem to be answered there.
How do I actually get started with
pg_stat_activity
? What do I type to use it, and where do I type it? -
Richard almost 11 yearsThanks for this answer! So just to cover the real basics.... I should start running my script, then in a new console tab, open a postgres connection to my database, and type
select * from pg_stat_activity
and the various statements will appear. Is that correct? -
Craig Ringer almost 11 years@Richard No, only the currently running statement.
pg_stat_activity
is a view of what's happening right now. Sounds like you want thepg_stat_statements
extension and/or PgBadger + auto_explain to me. -
Basil Bourque over 9 yearsEven shorter:
TABLE pg_stat_activity;