Postgresql – Equivalent of PGAdmin III Server Status Activity Window in PGAdmin 4 v5

pgadmin-4postgresqlpostgresql-13

I'm in the process of upgrading to Postgres 13.2 from a much older version. This entails an upgrade from PGAdmin III to PGAdmin 4.

PGAdmin III has a brilliantly designed feature called Server Status Activity Window which lists all open connections on a server. If you sort the connections by the "Query Start" column (descending) you can see the connections that have the longest-running queries. The rows are color coded: active connections are initially green, then after a query is running for 10-15 seconds it turns brown. Idle queries are blue. The autovacuum queries are usually sitting in brown color because they tend to run for a while.

So with PGAdmin III Server Status Activity Window, a dba can quickly spot queries that have been running for a long time.

PGAdmin 4 v5 has something similar called "Server Activity" but it falls short. Only the connection start timestamp is available as a column that can be sorted by. You have drill into the connection details to see the "Query Start" timestamp and you can't sort by it.

What work-arounds are available in PGAdmin 4 v5 to get this view that was available in PGAdmin III?

A related question appears here: PGAdmin 4 – server status / view log file

Best Answer

I came up with this query that returns the same columns as PGAdmin III Status Activity window but alas there is no color-coding of queries running longer than 5-10 seconds and no button that will terminate the query or the connection.

select pid as process_id, 
       application_name,
       datname as Database,
       usename as username, 
       client_addr as client_address, 
       backend_start as client_start,
       query_start,
       xact_start,
       state,
       state_change,
       pg_blocking_pids(pid) AS blocked_by,
       query
from pg_stat_activity
where state is not null 
order by state,xact_start desc;

This query is based loosely on this post and this post

Related Question