|
# Common Admin tasks |
|
|
|
https://pgtune.leopard.in.ua/#/ |
|
https://postgresqlco.nf/ |
|
|
|
## Multiple clusters |
|
https://www.percona.com/blog/2019/06/24/managing-multiple-postgresql-instances-on-ubuntu-debian/ |
|
|
|
## Dump role only |
|
pg_dumpall -p 5433 -v --roles-only -f /tmp/roles.sql |
|
|
|
## Get all connections |
|
SELECT * |
|
FROM pg_stat_activity |
|
WHERE datname = 'lyons_001_clone'; |
|
|
|
## Kill all the connections on one db |
|
SELECT pg_terminate_backend(pid) |
|
FROM pg_stat_activity |
|
WHERE datname='$PG_CLON_DATABASE'; |
|
|
|
## Specific table size |
|
select pg_size_pretty(pg_total_relation_size('public.signabledocuments')); |
|
|
|
## Column Information |
|
SELECT * |
|
FROM information_schema.columns |
|
WHERE table_schema = 'public' |
|
AND table_name = 'addtime' and column_name ='pub_psd_id' |
|
|
|
## Kill connection |
|
SELECT pg_terminate_backend (24628) |
|
FROM pg_stat_activity |
|
WHERE datname = 'lyons_001_clone'; |
|
|
|
SELECT pg_terminate_backend(pg_stat_activity.pid) |
|
FROM pg_stat_activity |
|
WHERE pg_stat_activity.datname = 'db_xxxx' AND pid <> pg_backend_pid(); |
|
|
|
## Slow Running Queries on DB from Last 5 Min |
|
SELECT now()-query_start as Running_Since,pid, datname, usename, application_name, client_addr, left(query,60) from pg_stat_activity where state in ('active','idle in transaction') and (now() - pg_stat_activity.query_start) > interval '5 minutes'; |
|
|
|
SELECT |
|
pid, |
|
now() - pg_stat_activity.query_start AS duration, |
|
query, |
|
state |
|
FROM pg_stat_activity |
|
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'; |
|
|
|
## Get database size |
|
SELECT pg_size_pretty( pg_database_size('dbname') ); |
|
|
|
## Get table size |
|
SELECT pg_size_pretty( pg_total_relation_size('tablename') ); |
|
|
|
## Get list of table sizes |
|
select table_schema, table_name, pg_relation_size('"'||table_schema||'"."'||table_name||'"'), pg_size_pretty(pg_relation_size('"'||table_schema||'"."'||table_name||'"')) |
|
from information_schema.tables |
|
order by 3 DESC |
|
|
|
## Block all connections |
|
update pg_database set datallowconn = false where datname = 'lyons_001_clone'; |
|
|
|
## Vacuum on tables |
|
SELECT schemaname, |
|
relname, |
|
now() - last_autovacuum AS "noautovac", |
|
now() - last_vacuum AS "novac", |
|
n_tup_upd, |
|
n_tup_del, |
|
pg_size_pretty(pg_total_relation_size(schemaname||'.'||relname)), |
|
autovacuum_count, |
|
last_autovacuum, |
|
vacuum_count, |
|
last_vacuum |
|
FROM pg_stat_user_tables |
|
WHERE (now() - last_autovacuum > '7 days'::interval |
|
OR now() - last_vacuum >'7 days'::interval ) |
|
OR (last_autovacuum IS NULL AND last_vacuum IS NULL ) |
|
ORDER BY novac DESC; |
|
|
|
## Unused index |
|
SELECT relname AS table, indexrelname AS index, pg_size_pretty(pg_relation_size(indexrelid)) AS size |
|
FROM pg_stat_user_indexes |
|
WHERE idx_scan = 0 |
|
ORDER BY pg_relation_size(indexrelid) DESC; |
|
|
|
## Duplicated index |
|
SELECT indrelid::regclass AS table, indkey AS column_numbers, array_agg(indexrelid::regclass) AS indexes, pg_catalog.pg_get_expr(indpred, indrelid, true) AS expression |
|
FROM pg_index |
|
GROUP BY indrelid, indkey, pg_catalog.pg_get_expr(indpred, indrelid, true) |
|
HAVING count(*) > 1; |
|
|
|
## Top 10 WRITE Tables |
|
select schemaname as "Schema Name", relname as "Table Name", |
|
n_tup_ins+n_tup_upd+n_tup_del as "no.of writes" from |
|
pg_stat_all_tables where schemaname not in ('snapshots','pg_catalog') |
|
order by n_tup_ins+n_tup_upd+n_tup_del desc limit 10; |
|
|
|
## Top 10 READ Tables |
|
SELECT schemaname as "Schema Name", relname as "Table |
|
Name",seq_tup_read+idx_tup_fetch as "no. of reads" FROM |
|
pg_stat_all_tables WHERE (seq_tup_read + idx_tup_fetch) > 0 and |
|
schemaname NOT IN ('snapshots','pg_catalog') ORDER BY |
|
seq_tup_read+idx_tup_fetch desc limit 10; |
|
|
|
## Largest Tables in DB |
|
SELECT QUOTE_IDENT(TABLE_SCHEMA)||'.'||QUOTE_IDENT(table_name) as |
|
table_name,pg_relation_size(QUOTE_IDENT(TABLE_SCHEMA)||'.'||QUOTE_IDENT(table_name)) as size, |
|
pg_total_relation_size(QUOTE_IDENT(TABLE_SCHEMA)||'.'||QUOTE_IDENT(table_name)) as total_size, |
|
pg_size_pretty(pg_relation_size(QUOTE_IDENT(TABLE_SCHEMA)||'.'||QUOTE_IDENT(table_name))) as pretty_relation_size,pg_size_pretty(pg_total_relation_size(QUOTE_IDENT(TABLE_SCHEMA)||'.'||QUOTE_IDENT(table_name))) as pretty_total_relation_size FROM information_schema.tables WHERE QUOTE_IDENT(TABLE_SCHEMA) NOT IN ('snapshots') ORDER BY size DESC LIMIT 10; |
|
|
|
## DB Size |
|
SELECT datname, pg_database_size(datname), |
|
pg_size_pretty(pg_database_size(datname)) |
|
FROM pg_database |
|
ORDER BY 2 DESC; |
|
|
|
## Table Size |
|
SELECT schemaname, relname, pg_total_relation_size(schemaname |
|
|| '.' || relname ) , |
|
pg_size_pretty(pg_total_relation_size(schemaname || '.' || |
|
relname )) |
|
FROM pg_stat_user_tables |
|
ORDER BY 3 DESC; |
|
|
|
## Index Size |
|
SELECT schemaname, relname, indexrelname, |
|
pg_total_relation_size(schemaname || '.' || indexrelname ) , |
|
pg_size_pretty(pg_total_relation_size(schemaname || '.' || |
|
indexrelname )) |
|
FROM pg_stat_user_indexes |
|
ORDER BY 1,2,3,4 DESC; |
|
|
|
## Index Utilization |
|
SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_fetch, |
|
idx_tup_read |
|
FROM pg_stat_user_indexes |
|
ORDER BY 4 DESC,1,2,3; |
|
|
|
## Tables That Are Being Updated the Most and Looking for VACUUM |
|
select relname, /* pg_size_pretty( pg_relation_size( relid ) ) as table_size, |
|
pg_size_pretty( pg_total_relation_size( relid ) ) as table_total_size, */ |
|
n_tup_upd, n_tup_hot_upd, n_live_tup, n_dead_tup, last_vacuum::date, last_autovacuum::date, last_analyze::date, last_autoanalyze::date |
|
from pg_stat_all_tables |
|
where relid in (select oid from pg_class |
|
where relnamespace not in (select oid from pg_namespace |
|
where nspname in ('information_schema', 'pg_catalog','pg_toast', 'edbhc' ) ) ) |
|
order by n_tup_upd desc, schemaname, relname; |
|
SELECT schemaname, |
|
relname, |
|
now() - last_autovacuum AS "noautovac", |
|
now() - last_vacuum AS "novac", |
|
n_tup_upd, |
|
n_tup_del, |
|
autovacuum_count, |
|
last_autovacuum, |
|
vacuum_count, |
|
last_vacuum |
|
FROM pg_stat_user_tables |
|
WHERE (now() - last_autovacuum > '7 days'::interval |
|
AND now() - last_vacuum >'7 days'::interval) |
|
OR (last_autovacuum IS NULL AND last_vacuum IS NULL ) AND n_dead_tup > 0 |
|
ORDER BY novac DESC; |
|
SELECT relname, n_live_tup, n_dead_tup, trunc(100*n_dead_tup/(n_live_tup+1))::float "ratio%", |
|
to_char(last_autovacuum, 'YYYY-MM-DD HH24:MI:SS') as autovacuum_date, |
|
to_char(last_autoanalyze, 'YYYY-MM-DD HH24:MI:SS') as autoanalyze_date |
|
FROM pg_stat_all_tables where schemaname not in ('pg_toast','pg_catalog','information_schema') |
|
ORDER BY last_autovacuum ; |
|
|
|
## Triggers under table |
|
SELECT event_object_table |
|
,trigger_name |
|
,event_manipulation |
|
,action_statement |
|
,action_timing |
|
FROM information_schema.triggers |
|
WHERE event_object_table = 'tableName' |
|
ORDER BY event_object_table |
|
,event_manipulation |
|
|
|
## Bloated Index to Run Reindexing (Locking Operation)\pgrepack (Online Rebuilding) |
|
SELECT current_database(), nspname AS schemaname, tblname, idxname, bs*(relpages)::bigint AS real_size, |
|
bs*(relpages-est_pages)::bigint AS extra_size, |
|
100 * (relpages-est_pages)::float / relpages AS extra_ratio, |
|
fillfactor, bs*(relpages-est_pages_ff) AS bloat_size, |
|
100 * (relpages-est_pages_ff)::float / relpages AS bloat_ratio, |
|
is_na |
|
-- , 100-(sub.pst).avg_leaf_density, est_pages, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, sub.reltuples, sub.relpages -- (DEBUG INFO) |
|
FROM ( |
|
SELECT coalesce(1 + |
|
ceil(reltuples/floor((bs-pageopqdata-pagehdr)/(4+nulldatahdrwidth)::float)), 0 -- ItemIdData size + computed avg size of a tuple (nulldatahdrwidth) |
|
) AS est_pages, |
|
coalesce(1 + |
|
ceil(reltuples/floor((bs-pageopqdata-pagehdr)*fillfactor/(100*(4+nulldatahdrwidth)::float))), 0 |
|
) AS est_pages_ff, |
|
bs, nspname, table_oid, tblname, idxname, relpages, fillfactor, is_na |
|
-- , stattuple.pgstatindex(quote_ident(nspname)||'.'||quote_ident(idxname)) AS pst, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, reltuples -- (DEBUG INFO) |
|
FROM ( |
|
SELECT maxalign, bs, nspname, tblname, idxname, reltuples, relpages, relam, table_oid, fillfactor, |
|
( index_tuple_hdr_bm + |
|
maxalign - CASE -- Add padding to the index tuple header to align on MAXALIGN |
|
WHEN index_tuple_hdr_bm%maxalign = 0 THEN maxalign |
|
ELSE index_tuple_hdr_bm%maxalign |
|
END |
|
+ nulldatawidth + maxalign - CASE -- Add padding to the data to align on MAXALIGN |
|
WHEN nulldatawidth = 0 THEN 0 |
|
WHEN nulldatawidth::integer%maxalign = 0 THEN maxalign |
|
ELSE nulldatawidth::integer%maxalign |
|
END |
|
)::numeric AS nulldatahdrwidth, pagehdr, pageopqdata, is_na |
|
-- , index_tuple_hdr_bm, nulldatawidth -- (DEBUG INFO) |
|
FROM ( |
|
SELECT |
|
i.nspname, i.tblname, i.idxname, i.reltuples, i.relpages, i.relam, a.attrelid AS table_oid, |
|
current_setting('block_size')::numeric AS bs, fillfactor, |
|
CASE -- MAXALIGN: 4 on 32bits, 8 on 64bits (and mingw32 ?) |
|
WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64' THEN 8 |
|
ELSE 4 |
|
END AS maxalign, |
|
/* per page header, fixed size: 20 for 7.X, 24 for others */ |
|
24 AS pagehdr, |
|
/* per page btree opaque data */ |
|
16 AS pageopqdata, |
|
/* per tuple header: add IndexAttributeBitMapData if some cols are null-able */ |
|
CASE WHEN max(coalesce(s.null_frac,0)) = 0 |
|
THEN 2 -- IndexTupleData size |
|
ELSE 2 + (( 32 + 8 - 1 ) / 8) -- IndexTupleData size + IndexAttributeBitMapData size ( max num filed per index + 8 - 1 /8) |
|
END AS index_tuple_hdr_bm, |
|
/* data len: we remove null values save space using it fractionnal part from stats */ |
|
sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024)) AS nulldatawidth, |
|
max( CASE WHEN a.atttypid = 'pg_catalog.name'::regtype THEN 1 ELSE 0 END ) > 0 AS is_na |
|
FROM pg_attribute AS a |
|
JOIN ( |
|
SELECT nspname, tbl.relname AS tblname, idx.relname AS idxname, idx.reltuples, idx.relpages, idx.relam, |
|
indrelid, indexrelid, indkey::smallint[] AS attnum, |
|
coalesce(substring( |
|
array_to_string(idx.reloptions, ' ') |
|
from 'fillfactor=([0-9]+)')::smallint, 90) AS fillfactor |
|
FROM pg_index |
|
JOIN pg_class idx ON idx.oid=pg_index.indexrelid |
|
JOIN pg_class tbl ON tbl.oid=pg_index.indrelid |
|
JOIN pg_namespace ON pg_namespace.oid = idx.relnamespace |
|
WHERE pg_index.indisvalid AND tbl.relkind = 'r' AND idx.relpages > 0 |
|
) AS i ON a.attrelid = i.indexrelid |
|
JOIN pg_stats AS s ON s.schemaname = i.nspname |
|
AND ((s.tablename = i.tblname AND s.attname = pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE)) -- stats from tbl |
|
OR (s.tablename = i.idxname AND s.attname = a.attname))-- stats from functionnal cols |
|
JOIN pg_type AS t ON a.atttypid = t.oid |
|
WHERE a.attnum > 0 |
|
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9 |
|
) AS s1 |
|
) AS s2 |
|
JOIN pg_am am ON s2.relam = am.oid WHERE am.amname = 'btree' |
|
) AS sub |
|
-- WHERE NOT is_na |
|
ORDER BY 2,3,4; |
|
|
|
## Bloated Tables to Do Vacuumfull (Locking Operation)\pgrepack (Online Rebuilding) |
|
SELECT current_database(), schemaname, tblname, bs*tblpages AS real_size, |
|
(tblpages-est_tblpages)*bs AS extra_size, |
|
CASE WHEN tblpages - est_tblpages > 0 |
|
THEN 100 * (tblpages - est_tblpages)/tblpages::float |
|
ELSE 0 |
|
END AS extra_ratio, fillfactor, (tblpages-est_tblpages_ff)*bs AS bloat_size, |
|
CASE WHEN tblpages - est_tblpages_ff > 0 |
|
THEN 100 * (tblpages - est_tblpages_ff)/tblpages::float |
|
ELSE 0 |
|
END AS bloat_ratio, is_na |
|
-- , (pst).free_percent + (pst).dead_tuple_percent AS real_frag |
|
FROM ( |
|
SELECT ceil( reltuples / ( (bs-page_hdr)/tpl_size ) ) + ceil( toasttuples / 4 ) AS est_tblpages, |
|
ceil( reltuples / ( (bs-page_hdr)*fillfactor/(tpl_size*100) ) ) + ceil( toasttuples / 4 ) AS est_tblpages_ff, |
|
tblpages, fillfactor, bs, tblid, schemaname, tblname, heappages, toastpages, is_na |
|
-- , stattuple.pgstattuple(tblid) AS pst |
|
FROM ( |
|
SELECT |
|
( 4 + tpl_hdr_size + tpl_data_size + (2*ma) |
|
- CASE WHEN tpl_hdr_size%ma = 0 THEN ma ELSE tpl_hdr_size%ma END |
|
- CASE WHEN ceil(tpl_data_size)::int%ma = 0 THEN ma ELSE ceil(tpl_data_size)::int%ma END |
|
) AS tpl_size, bs - page_hdr AS size_per_block, (heappages + toastpages) AS tblpages, heappages, |
|
toastpages, reltuples, toasttuples, bs, page_hdr, tblid, schemaname, tblname, fillfactor, is_na |
|
FROM ( |
|
SELECT |
|
tbl.oid AS tblid, ns.nspname AS schemaname, tbl.relname AS tblname, tbl.reltuples, |
|
tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages, |
|
coalesce(toast.reltuples, 0) AS toasttuples, |
|
coalesce(substring( |
|
array_to_string(tbl.reloptions, ' ') |
|
FROM '%fillfactor=#"__#"%' FOR '#')::smallint, 100) AS fillfactor, |
|
current_setting('block_size')::numeric AS bs, |
|
CASE WHEN version()~'mingw32' OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma, |
|
24 AS page_hdr, |
|
23 + CASE WHEN MAX(coalesce(null_frac,0)) > 0 THEN ( 7 + count(*) ) / 8 ELSE 0::int END |
|
+ CASE WHEN tbl.relhasoids THEN 4 ELSE 0 END AS tpl_hdr_size, |
|
sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024) ) AS tpl_data_size, |
|
bool_or(att.atttypid = 'pg_catalog.name'::regtype) AS is_na |
|
FROM pg_attribute AS att |
|
JOIN pg_class AS tbl ON att.attrelid = tbl.oid |
|
JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace |
|
JOIN pg_stats AS s ON s.schemaname=ns.nspname |
|
AND s.tablename = tbl.relname AND s.inherited=false AND s.attname=att.attname |
|
LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid |
|
WHERE att.attnum > 0 AND NOT att.attisdropped |
|
AND tbl.relkind = 'r' |
|
GROUP BY 1,2,3,4,5,6,7,8,9,10, tbl.relhasoids |
|
ORDER BY 2,3 |
|
) AS s |
|
) AS s2 |
|
) AS s3; |
|
|
|
## Never-Used Indexes |
|
WITH table_scans as ( |
|
SELECT relid, |
|
tables.idx_scan + tables.seq_scan as all_scans, |
|
( tables.n_tup_ins + tables.n_tup_upd + tables.n_tup_del ) as writes, |
|
pg_relation_size(relid) as table_size |
|
FROM pg_stat_user_tables as tables |
|
), |
|
all_writes as ( |
|
SELECT sum(writes) as total_writes |
|
FROM table_scans |
|
), |
|
indexes as ( |
|
SELECT idx_stat.relid, idx_stat.indexrelid, |
|
idx_stat.schemaname, idx_stat.relname as tablename, |
|
idx_stat.indexrelname as indexname, |
|
idx_stat.idx_scan, |
|
pg_relation_size(idx_stat.indexrelid) as index_bytes, |
|
indexdef ~* 'USING btree' AS idx_is_btree |
|
FROM pg_stat_user_indexes as idx_stat |
|
JOIN pg_index |
|
USING (indexrelid) |
|
JOIN pg_indexes as indexes |
|
ON idx_stat.schemaname = indexes.schemaname |
|
AND idx_stat.relname = indexes.tablename |
|
AND idx_stat.indexrelname = indexes.indexname |
|
WHERE pg_index.indisunique = FALSE |
|
), |
|
index_ratios AS ( |
|
SELECT schemaname, tablename, indexname, |
|
idx_scan, all_scans, |
|
round(( CASE WHEN all_scans = 0 THEN 0.0::NUMERIC |
|
ELSE idx_scan::NUMERIC/all_scans * 100 END),2) as index_scan_pct, |
|
writes, |
|
round((CASE WHEN writes = 0 THEN idx_scan::NUMERIC ELSE idx_scan::NUMERIC/writes END),2) |
|
as scans_per_write, |
|
pg_size_pretty(index_bytes) as index_size, |
|
pg_size_pretty(table_size) as table_size, |
|
idx_is_btree, index_bytes |
|
FROM indexes |
|
JOIN table_scans |
|
USING (relid) |
|
), |
|
index_groups AS ( |
|
SELECT 'Never Used Indexes' as reason, *, 1 as grp |
|
FROM index_ratios |
|
WHERE |
|
idx_scan = 0 |
|
and idx_is_btree |
|
UNION ALL |
|
SELECT 'Low Scans, High Writes' as reason, *, 2 as grp |
|
FROM index_ratios |
|
WHERE |
|
scans_per_write <= 1 |
|
and index_scan_pct < 10 |
|
and idx_scan > 0 |
|
and writes > 100 |
|
and idx_is_btree |
|
UNION ALL |
|
SELECT 'Seldom Used Large Indexes' as reason, *, 3 as grp |
|
FROM index_ratios |
|
WHERE |
|
index_scan_pct < 5 |
|
and scans_per_write > 1 |
|
and idx_scan > 0 |
|
and idx_is_btree |
|
and index_bytes > 100000000 |
|
UNION ALL |
|
SELECT 'High-Write Large Non-Btree' as reason, index_ratios.*, 4 as grp |
|
FROM index_ratios, all_writes |
|
WHERE |
|
( writes::NUMERIC / ( total_writes + 1 ) ) > 0.02 |
|
AND NOT idx_is_btree |
|
AND index_bytes > 100000000 |
|
ORDER BY grp, index_bytes DESC ) |
|
SELECT reason, schemaname, tablename, indexname, |
|
index_scan_pct, scans_per_write, index_size, table_size |
|
FROM index_groups; |
|
|
|
## Duplicated Indexes |
|
SELECT |
|
indrelid::regclass AS TableName |
|
,array_agg(indexrelid::regclass) AS Indexes |
|
FROM pg_index |
|
GROUP BY |
|
indrelid |
|
,indkey |
|
HAVING COUNT(*) > 1; |
|
|
|
## Blocked Queries |
|
SELECT blocked_locks.pid AS blocked_pid, |
|
blocked_activity.usename AS blocked_user, |
|
blocking_locks.pid AS blocking_pid, |
|
blocking_activity.usename AS blocking_user, |
|
blocked_activity.query AS blocked_statement, |
|
blocking_activity.query AS current_statement_in_blocking_process |
|
FROM pg_catalog.pg_locks blocked_locks |
|
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid |
|
JOIN pg_catalog.pg_locks blocking_locks |
|
ON blocking_locks.locktype = blocked_locks.locktype |
|
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE |
|
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation |
|
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page |
|
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple |
|
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid |
|
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid |
|
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid |
|
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid |
|
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid |
|
AND blocking_locks.pid != blocked_locks.pid |
|
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid |
|
WHERE NOT blocked_locks.GRANTED; |
|
|
|
## Locking session : |
|
SELECT bl.pid AS blocked_pid, |
|
a.query AS blocking_statement, |
|
now ( ) - ka.query_start AS blocking_duration, |
|
kl.pid AS blocking_pid, |
|
a.query AS blocked_statement, |
|
now ( ) - a.query_start AS blocked_duration |
|
FROM pg_catalog.pg_locks bl |
|
JOIN pg_catalog.pg_stat_activity a ON bl.pid = a.pid |
|
JOIN pg_catalog.pg_locks kl |
|
JOIN pg_catalog.pg_stat_activity ka |
|
ON kl.pid = ka.pid |
|
ON bl.transactionid = kl.transactionid |
|
AND bl.pid != kl.pid |
|
WHERE NOT bl.granted; |
|
|
|
## Blocking query |
|
SELECT blocked_locks.pid AS blocked_pid, |
|
blocked_activity.usename AS blocked_user, |
|
blocking_locks.pid AS blocking_pid, |
|
blocking_activity.usename AS blocking_user, |
|
blocked_activity.query AS blocked_statement, |
|
blocking_activity.query AS current_statement_in_blocking_process |
|
FROM pg_catalog.pg_locks blocked_locks |
|
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid |
|
JOIN pg_catalog.pg_locks blocking_locks |
|
ON blocking_locks.locktype = blocked_locks.locktype |
|
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE |
|
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation |
|
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page |
|
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple |
|
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid |
|
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid |
|
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid |
|
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid |
|
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid |
|
AND blocking_locks.pid != blocked_locks.pid |
|
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.GRANTED; |
|
|
|
## table size corrected |
|
SELECT nspname || '.' || relname AS "relation", |
|
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size" |
|
FROM pg_class C |
|
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) |
|
WHERE nspname NOT IN ('pg_catalog', 'information_schema') |
|
AND C.relkind <> 'i' |
|
AND nspname !~ '^pg_toast' |
|
ORDER BY pg_total_relation_size(C.oid) DESC |
|
LIMIT 5; |