PostgreSQL Monitoring Queries

1.Get Running Queries And Lock statuses in PostgreSQL:

SELECT
S.pid,
age(clock_timestamp(), query_start),
usename,
query,
L.mode,
L.locktype,
L.granted
FROM pg_stat_activity S
inner join pg_locks L on S.pid = L.pid
order by L.granted, L.pid DESC;

2.Cancel Running Queries:

To kill a particular query, simply get its pid (using the above query) and run:

SELECT pg_cancel_backend(pid);
(or)
SELECT pg_terminate_backend(pid);

3.Show Biggest PostgreSQL Tables/Indexes And Their Size:

SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 20;

4.Show All PostgreSQL Databases And Their Size:

select
datname as db,
pg_size_pretty(pg_database_size(datname)) as size
from pg_database
order by pg_database_size(datname) desc;

5.Show dead tubles:

SELECT relname as "relation", EXTRACT (EPOCH FROM current_timestamp-last_autovacuum) as since_last_av,
autovacuum_count as av_count, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup FROM pg_stat_all_tables
WHERE schemaname = 'public' ORDER BY relname;

6.replication status:

postgres9 version:

SELECT application_name,client_addr,state,sent_location,write_location,replay_location,
sent_offset - (replay_offset - (sent_xlog - replay_xlog) * 255 * 16 ^ 6 ))::text AS byte_lag
FROM (SELECT
application_name,client_addr,state,sync_state,sent_location,write_location,replay_location,
('x' || lpad(split_part(sent_location::text, '/', 1), 8, '0'))::bit(32)::bigint AS sent_xlog,
('x' || lpad(split_part(replay_location::text, '/', 1), 8, '0'))::bit(32)::bigint AS replay_xlog,
('x' || lpad(split_part(sent_location::text, '/', 2), 8, '0'))::bit(32)::bigint AS sent_offset,
('x' || lpad(split_part(replay_location::text, '/', 2), 8, '0'))::bit(32)::bigint AS replay_offset
FROM pg_stat_replication)
AS s;

postgres 10+ versions:

SELECT application_name,client_addr,state,
(sent_offset - (replay_offset - (sent_xlog - replay_xlog) * 255 * 16 ^ 6 ))::text AS byte_lag
FROM (SELECT application_name,client_addr,state,sync_state,sent_lsn,write_lsn,replay_lsn,
('x' || lpad(split_part(sent_lsn::text, '/', 1), 8, '0'))::bit(32)::bigint AS sent_xlog,
('x' || lpad(split_part(replay_lsn::text, '/', 1), 8, '0'))::bit(32)::bigint AS replay_xlog,
('x' || lpad(split_part(sent_lsn::text, '/', 2), 8, '0'))::bit(32)::bigint AS sent_offset,
('x' || lpad(split_part(replay_lsn::text, '/', 2), 8, '0'))::bit(32)::bigint AS replay_offset
FROM pg_stat_replication) AS s;