0

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

0

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, privacy policy and cookie policy

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