Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to use pg_stat_activity?

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?

like image 667
Richard Avatar asked Jul 15 '13 12:07

Richard


People also ask

How do you use PG activities?

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.

What is backend start in Pg_stat_activity?

The time when the active query was started. If the state field is set to a value other than active , the query_start field indicates the time when the last query was started. backend_start. datetime. The time when the current backend process was started.

What is Datname in Pg_stat_activity?

In the PostgreSQL terminology this process is called backend or worker. datid, datname – ID and name of the database to which client connects.

What is Datid in PostgreSQL?

datid oid. OID of the database this backend is connected to. datname name. Name of the database this backend is connected to. pid integer.


2 Answers

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 SELECTing 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.

like image 175
Craig Ringer Avatar answered Sep 28 '22 10:09

Craig Ringer


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.

like image 37
Umesh Patil Avatar answered Sep 28 '22 10:09

Umesh Patil