PG: Utility queries

Long running queries

select datname,usename,current_timestamp - query_start as runtime,client_addr,application_name,query
from pg_stat_activity 
where state = 'active'
and backend_type = 'client backend'
order by runtime desc;

View Backup progress

SELECT pid, phase,
100.0*((backup_streamed*1.0)/backup_total) AS "progress%"
FROM pg_stat_progress_basebackup;

View COPY FROM % progress:

SELECT (SELECT relname FROM pg_class WHERE oid = relid),
100.0*((bytes_processed*1.0)/bytes_total) AS "progress%"
FROM pg_stat_progress_copy;

View COPY TO % progress

SELECT relname,
100.0*((tuples_processed*1.0)/(case reltuples WHEN 0 THEN 10 WHEN -1 THEN 10 ELSE reltuples END)) AS "progress%"
FROM pg_stat_progress_copy JOIN pg_class on oid = relid;

 check if a query is waiting for another query

\x
SELECT pid,usename,wait_event_type,wait_event,state,backend_type,query
FROM pg_stat_activity
where backend_type = 'client backend';

view lock waits

SELECT bl.locktype,
     bl.mode    AS lock_type,
     bl.pid     AS blocked_pid,
     a.usename  AS blocked_user,
     a.query    AS blocked_query,
     now() - a.query_start AS waiting_duration,
     kl.pid     AS blocking_pid,
     ka.usename AS blocking_user,
     ka.query   AS blocking_query,
     now() - ka.query_start AS locking_duration
FROM  pg_catalog.pg_locks         bl
 JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid
 JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
 JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
WHERE NOT bl.granted;

view locks on tables

SELECT 
  pg_namespace.nspname as schemaname,
  pg_class.relname as tablename,
  pg_locks.mode as lock_type,
  age(now(),pg_stat_activity.query_start) AS time_running 
FROM pg_class
JOIN pg_locks on pg_locks.relation = pg_class.oid
JOIN pg_database on pg_database.oid = pg_locks.database
JOIN pg_namespace on pg_namespace.oid = pg_class.relnamespace
JOIN pg_stat_activity on pg_stat_activity.pid = pg_locks.pid
WHERE pg_class.relkind = 'r'
  AND pg_database.datname = current_database();

view object/object type/tablespace

SELECT 
  spcname, 
  relname, 
  CASE WHEN relpersistence = 't' THEN 'temp ' WHEN relpersistence = 'u' THEN 'unlogged ' ELSE '' END || (
    CASE WHEN relkind = 'r' THEN 'table' WHEN relkind = 'p' THEN 'partitioned table' WHEN relkind = 'f' THEN 'foreign table' WHEN relkind = 't' THEN 'TOAST table' WHEN relkind = 'v' THEN 'view' WHEN relkind = 'm' THEN 'materialized view' WHEN relkind = 'S' THEN 'sequence' WHEN relkind = 'c' THEN 'type' ELSE 'index' END
  ) as objtype 
FROM 
  pg_class c 
  join pg_tablespace ts ON (
    CASE WHEN c.reltablespace = 0 THEN (
      SELECT 
        dattablespace 
      FROM 
        pg_database WHERE datname = current_database()
    ) ELSE c.reltablespace END
  ) = ts.oid 
WHERE 
  relname NOT LIKE 'pg_toast%' 
  AND relnamespace NOT IN (
    SELECT 
      oid 
    FROM 
      pg_namespace WHERE nspname IN (
        'pg_catalog', 'information_schema'
      )
  );

You can use the following query to kill all backends that have an open transaction but have been doing nothing for the last 10 minutes:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
AND current_timestamp - state_change > '10 min';

While using a two-phase commit (2PC), you may end up in a situation where you have something locked but cannot find the backend that holds the locks.
look up the pg_locks table for those entries with an empty pid value.

SELECT t.schemaname || '.' || t.relname AS tablename,
       l.pid, l.granted
       FROM pg_locks l JOIN pg_stat_user_tables t
       ON l.relation = t.relid;

To see whether a table is currently in active use

CREATE TEMPORARY TABLE tmp_stat_user_tables AS
SELECT * FROM pg_stat_user_tables;
SELECT * FROM pg_stat_user_tables n
JOIN tmp_stat_user_tables t
ON n.relid=t.relid
AND (n.seq_scan,n.idx_scan,n.n_tup_ins,n.n_tup_upd,n.n_tup_del)
<> (t.seq_scan,t.idx_scan,t.n_tup_ins,t.n_tup_upd,t.n_tup_del);

check the space that's being used by temporary tablespaces:

WITH temporary_tablespaces AS (SELECT
unnest(string_to_array(
  current_setting('temp_tablespaces'), ',')
) AS temp_tablespace
)
SELECT tt.temp_tablespace,
pg_tablespace_location(t.oid) AS location,
pg_tablespace_size(t.oid) AS size
FROM temporary_tablespaces tt
JOIN pg_tablespace t ON t.spcname = tt.temp_tablespace
ORDER BY 1;