How To List Active Queries On PostgreSQL

dbtut

6 years ago

In this article I will explain you to list active queries in postgresql.

First you must connect to postgresql.

You may want to read the article “How To Connect To Database On PostgreSQL” for connect to postgresql.

After connect to postgresql yo should run the below scripts for readable results.

\pset format wrapped
\pset columns 100
\pset linestyle unicode

\pset format wrapped

\pset columns 100

\pset linestyle unicode

Then you can list active queries with below scripts.

SELECT datname as database_name,usename as user_name,pid,query,query_start,
backend_start FROM pg_stat_activity ORDER BY pid;

1 2	SELECT datname as database_name,usename as user_name,pid,query,query_start, backend_start FROM pg_stat_activity ORDER BY pid;

SELECT  pid, now() -pg_stat_activity.query_start AS duration,
query, state FROM pg_stat_activity
WHERE (now()-pg_stat_activity.query_start) > interval '10 seconds';

SELECT pid, now() -pg_stat_activity.query_start AS duration,

query, state FROM pg_stat_activity

WHERE (now()-pg_stat_activity.query_start) > interval '10 seconds';