1

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

2
  • I just noticed that in PGAdmin4 v5.5 a new color-coding feature has been added that identifies connections with long-running queries in the "Server Activity" window on the "Dashboard" tab by highlighting connection in a different color. There is a setting that configures the "Warning" and "Alert" thresholds. The units for this setting are in MINUTES. I wish that the default units were measured in SECONDS instead.
    – sevzas
    Jul 25, 2021 at 6:46
  • Referring to my comment above, it appears that the "Warning" and "Alert" thresholds will accept decimals, so you can enter ".25" if you want a threshold to be 15 seconds.
    – sevzas
    Jul 26, 2021 at 12:33

1 Answer 1

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.