postgres common admin task - from stackoverflow

 

Check for locks 

How to show all blocked queries.

select pid, 
       usename, 
       pg_blocking_pids(pid) as blocked_by, 
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

Kill a blocked query by using the below command.

SELECT pg_cancel_backend(a.pid), pg_terminate_backend(a.pid);

Terminate all blocked queries using it.

SELECT pg_cancel_backend(a.pid), pg_terminate_backend(a.pid)
FROM( select pid, 
       usename, 
       pg_blocking_pids(pid) as blocked_by, 
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0) a

check database size

-- Database Size

SELECT pg_size_pretty(pg_database_size('Database Name'));

-- Table Size
SELECT pg_size_pretty(pg_relation_size('table_name'));

-- table size order by size 

select
  table_name,
  pg_size_pretty(pg_total_relation_size(quote_ident(table_name))),
  pg_relation_size(quote_ident(table_name))
from information_schema.tables
where table_schema = 'public'
order by 3 desc;

Working with indexes 

Check on replication status

On master:
select * from pg_stat_replication;
On replica (streaming replication in my case):
select * from pg_stat_wal_receiver;

query user priv

SELECT table_catalog, table_schema, table_name, privilege_type

FROM   information_schema.table_privileges 
WHERE  grantee = 'MY_USER