List of handful PostgreSQL commands to improve productivity
If you are a PostgreSQL DBA working a lot on PostgreSQL. You may want to know some of the useful commands that can be very useful while performing day to day task.
/*Session Monitoring*/
———————————————————————————————————–
SELECT now()::timestamp(0) as time,datname,pid,(now() - query_start)::time(0) AS runtime,EXTRACT(EPOCH FROM (now() - query_start))*1000::INT AS runtime_millisecs,query_start::timestamp(0),usename,client_addr,state, query FROM pg_stat_activity Where pid <> pg_backend_pid() AND now() - query_start > '15 milliseconds'::INTERVAL ORDER BY EXTRACT(EPOCH FROM (now() - query_start))::INT DESC;
SELECT pid,query::varchar(100),datname, now() - pg_stat_activity.query_start AS duration,usename,client_addr,state,wait_event_type,wait_event
FROM pg_stat_activity
WHERE state!='idle' and pid <> pg_backend_pid() -- and now() - query_start > '1 minutes'::interval -- and query not like '%VACUUM%'
order by duration desc;
SELECT pid,query::varchar(100),datname, now() - pg_stat_activity.query_start AS duration,usename,client_addr,state,wait_event_type,wait_event
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
order by duration desc;
/* Query to check Blocking Session */
———————————————————————————————————–
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;
/* Query to check tables list with Primary Key*/
———————————————————————————————————–
select kcu.table_schema,
kcu.table_name,
tco.constraint_name,
kcu.ordinal_position as position,
kcu.column_name as key_column
from information_schema.table_constraints tco
join information_schema.key_column_usage kcu
on kcu.constraint_name = tco.constraint_name
and kcu.constraint_schema = tco.constraint_schema
and kcu.constraint_name = tco.constraint_name
where tco.constraint_type <> 'PRIMARY KEY' and kcu.table_schema<>'sys'
order by kcu.table_schema,
kcu.table_name,
position;
/* Query to check tables list without Primary Key*/
———————————————————————————————————–
select tab.table_schema,
tab.table_name
from information_schema.tables tab
left join information_schema.table_constraints tco
on tab.table_schema = tco.table_schema
and tab.table_name = tco.table_name
and tco.constraint_type = 'PRIMARY KEY'
where tab.table_type = 'BASE TABLE'
and tab.table_schema not in ('pg_catalog','sys','information_schema')
and tco.constraint_name is null
order by table_schema,
table_name;
SELECT t.table_schema || '.' || t.table_name SchemaName_TableName
FROM information_schema.tables t
WHERE
(table_catalog, table_schema, table_name) NOT IN (
SELECT tc.table_catalog, tc.table_schema, tc.table_name
FROM information_schema.table_constraints tc
WHERE constraint_type = 'PRIMARY KEY') AND t.table_type = 'BASE TABLE' AND
t.table_schema NOT IN ('information_schema', 'pg_catalog', 'pgq', 'londiste','sys');
/*Query to check User privileges*/
———————————————————————————————————–
SELECT table_catalog, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE grantee = 'Username';
/*Query to check Table Size*/
———————————————————————————————————–
SELECT relnamespace::regclass, relname,pg_size_pretty(pg_relation_size(pg_class.oid, 'main')) as main, pg_size_pretty(pg_relation_size(pg_class.oid,'fsm')) as fsm, pg_size_pretty(pg_relation_size(pg_class.oid, 'vm')) as vm, pg_size_pretty(pg_relation_size(pg_class.oid, 'init')) as init, pg_size_pretty(pg_table_size(pg_class.oid)) as table, pg_size_pretty(pg_indexes_size(pg_class.oid)) as indexes, pg_size_pretty(pg_total_relation_size(pg_class.oid)) as total FROM pg_class WHERE relkind='r' ORDER BY pg_total_relation_size(pg_class.oid) DESC LIMIT 10;
/*Query to Check the Active and Inactive users in PostgreSQL*/
———————————————————————————————————–
select count(*),usename,state from pg_stat_activity group by usename, state;
select usename,state,state_change,pid,backend_start,client_addr from pg_stat_activity where usename like 'sc_%' order by 5,2,1;
/*PostgreSQL: Script to kill all idle sessions and connections of a Database*/
———————————————————————————————————–
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = ''
AND pid <> pg_backend_pid()
AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled')
AND state_change < current_timestamp - INTERVAL '15' MINUTE;
/*Query to Check Unused Index */
———————————————————————————————————–
SELECT now(), schemaname || '.' || relname AS table, indexrelname AS index, pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, idx_scan as index_scans,pg_get_indexdef(i.indexrelid) FROM pg_stat_user_indexes ui JOIN pg_index i ON ui.indexrelid = i.indexrelid WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192 ORDER BY pg_relation_size(i.indexrelid) DESC LIMIT 20 ;
/*Query to identify the orphan tables*/
———————————————————————————————————–
select pgns.nspname as schema_name
, pgc.relname as object_name
from pg_class pgc
join pg_namespace pgns on pgc.relnamespace = pgns.oid
where pg_is_other_temp_schema(pgc.relnamespace);
/*Generate the drop sql command*/
———————————————————————————————————–
select case when pgc.relname like '%_index'
then 'drop index ' || pgns.nspname || '.' || pgc.relname || ';'
else 'drop table ' || pgns.nspname || '.' || pgc.relname || ';' end as drop_query
from pg_class pgc
join pg_namespace pgns on pgc.relnamespace = pgns.oid
where pg_is_other_temp_schema(pgc.relnamespace)
and pgc.relname not like '%toast%';
/*Query to check Bloated Index*/
———————————————————————————————————–
WITH btree_index_atts AS (
SELECT nspname, relname, reltuples, relpages, indrelid, relam,
regexp_split_to_table(indkey::text, ' ')::smallint AS attnum,
indexrelid as index_oid
FROM pg_index
JOIN pg_class ON pg_class.oid=pg_index.indexrelid
JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
JOIN pg_am ON pg_class.relam = pg_am.oid
WHERE pg_am.amname = 'btree'
-- AND pg_namespace.nspname IN ('')
AND pg_class.relname NOT LIKE 'pk_%'
),
index_item_sizes AS (
SELECT
i.nspname, i.relname, i.reltuples, i.relpages, i.relam,
s.starelid, a.attrelid AS table_oid, index_oid,
current_setting('block_size')::numeric AS bs,
/* MAXALIGN: 4 on 32bits, 8 on 64bits (and mingw32 ?) / CASE WHEN version() ~ 'mingw32' OR version() ~ '64-bit' THEN 8 ELSE 4 END AS maxalign, 24 AS pagehdr, / per tuple header: add index_attribute_bm if some cols are null-able / CASE WHEN max(coalesce(s.stanullfrac,0)) = 0 THEN 2 ELSE 6 END AS index_tuple_hdr, / data len: we remove null values save space using it fractionnal part from stats */
sum( (1-coalesce(s.stanullfrac, 0)) * coalesce(s.stawidth, 2048) ) AS nulldatawidth
FROM pg_attribute AS a
JOIN pg_statistic AS s ON s.starelid=a.attrelid AND s.staattnum = a.attnum
JOIN btree_index_atts AS i ON i.indrelid = a.attrelid AND a.attnum = i.attnum
WHERE a.attnum > 0
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
),
index_aligned AS (
SELECT maxalign, bs, nspname, relname AS index_name, reltuples,
relpages, relam, table_oid, index_oid,
( 2 +
maxalign - CASE /* Add padding to the index tuple header to align on MAXALIGN / WHEN index_tuple_hdr%maxalign = 0 THEN maxalign ELSE index_tuple_hdr%maxalign END + nulldatawidth + maxalign - CASE / Add padding to the data to align on MAXALIGN / WHEN nulldatawidth::integer%maxalign = 0 THEN maxalign ELSE nulldatawidth::integer%maxalign END )::numeric AS nulldatahdrwidth, pagehdr FROM index_item_sizes AS s1 ), otta_calc AS ( SELECT bs, nspname, table_oid, index_oid, index_name, relpages, coalesce( ceil((reltuples(4+nulldatahdrwidth))/(bs-pagehdr::float)) +
CASE WHEN am.amname IN ('hash','btree') THEN 1 ELSE 0 END , 0 -- btree and hash have a metadata reserved block
) AS otta
FROM index_aligned AS s2
LEFT JOIN pg_am am ON s2.relam = am.oid
),
raw_bloat AS (
SELECT current_database() as dbname, nspname, c.relname AS table_name, index_name,
bs(sub.relpages)::bigint AS totalbytes, CASE WHEN sub.relpages <= otta THEN 0 ELSE bs(sub.relpages-otta)::bigint END
AS wastedbytes,
CASE
WHEN sub.relpages <= otta THEN 0 ELSE bs*(sub.relpages-otta)::bigint * 100 / (bs*(sub.relpages)::bigint) END AS realbloat, pg_relation_size(sub.table_oid) as table_bytes, stat.idx_scan as index_scans FROM otta_calc AS sub JOIN pg_class AS c ON c.oid=sub.table_oid JOIN pg_stat_user_indexes AS stat ON sub.index_oid = stat.indexrelid ) SELECT dbname as database_name, nspname as schema_name, table_name, index_name, round(realbloat, 1) as bloat_pct, wastedbytes as bloat_bytes, replace(pg_size_pretty(wastedbytes::bigint),' ','') as bloat_size, totalbytes as index_bytes, replace(pg_size_pretty(totalbytes::bigint),' ','') as index_size, table_bytes, replace(pg_size_pretty(table_bytes),' ','') as table_size, index_scans FROM raw_bloat WHERE realbloat > 40
-- AND wastedbytes > 50000000
ORDER BY wastedbytes DESC;
/*Query to Check Bloated Table */
———————————————————————————————————–
WITH table_bloat AS (
SELECT current_database(),
schemaname,
tblid,
tblname,
bstblpages AS real_size, 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_size100) ) ) + 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 + (2ma) - 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
-- enable below filter for schema specific bloat detection
-- AND ns.nspname = '${schema}'
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
ORDER BY bloat_size DESC
)
SELECT schemaname,
tblid,
tblname,
REPLACE(pg_size_pretty(real_size::numeric), ' ','') as real_size,
fillfactor,
REPLACE(pg_size_pretty(bloat_size::numeric),' ','') as bloat_size,
round(bloat_ratio::numeric,2) as bloat_ratio,
is_na
FROM table_bloat
-- modify the ratio based on your use case
WHERE bloat_ratio > 40
--WHERE tblname=''
ORDER BY bloat_ratio DESC
;
/*Cache Hit*/
———————————————————————————————————–
with
all_tables as
(
SELECT *
FROM (
SELECT 'all'::text as table_name,
sum( (coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) ) as from_disk,
sum( (coalesce(heap_blks_hit,0) + coalesce(idx_blks_hit,0) + coalesce(toast_blks_hit,0) + coalesce(tidx_blks_hit,0)) ) as from_cache
FROM pg_statio_all_tables -- change to pg_statio_USER_tables if you want to check only user tables (excluding postgres's own tables)
) a
WHERE (from_disk + from_cache) > 0 -- discard tables without hits
),
tables as
(
SELECT *
FROM (
SELECT relname as table_name,
( (coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) ) as from_disk,
( (coalesce(heap_blks_hit,0) + coalesce(idx_blks_hit,0) + coalesce(toast_blks_hit,0) + coalesce(tidx_blks_hit,0)) ) as from_cache
FROM pg_statio_all_tables -- change to pg_statio_USER_tables if you want to check only user tables (excluding postgres's own tables)
) a
WHERE (from_disk + from_cache) > 0 -- discard tables without hits
)
SELECT table_name as "table name",
from_disk as "disk hits",
round((from_disk::numeric / (from_disk + from_cache)::numeric)100.0,2) as "% disk hits", round((from_cache::numeric / (from_disk + from_cache)::numeric)100.0,2) as "% cache hits",
(from_disk + from_cache) as "total hits"
FROM (SELECT * FROM all_tables UNION ALL SELECT * FROM tables) a
ORDER BY (case when table_name = 'all' then 0 else 1 end), from_disk desc
/*OS process and DB Process check*/
———————————————————————————————————–
Grep for top 10 postgres memory process:-
ps axu | awk '{print $2, $3, $4, $11, $12 }' | sort -k3 -nr |head -10| grep -i postgres
/*Finding the database replication delay*/
———————————————————————————————————–
a) Running the following query on the slave:
SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::INT;
SELECT extract(epoch from now() - pg_last_xact_replay_timestamp()) AS replica_lag
This query gives you the lag in seconds.
Note: The issue with this query is that while your replica(s) may be 100% caught up, the time interval being returned is always increasing until new write activity occurs on the primary that the replica can replay. This can cause monitoring to give false positives.
b) This can be achieved by comparing pg_last_xlog_receive_location() and pg_last_xlog_replay_location()
on the slave, and if they are the same itreturns 0, otherwise it runs the above query again:
SELECT
CASE
WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0
ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())::INTEGER
END
AS replication_lag;
c) Compare master and slave xlog
Master:
SELECT pg_current_xlog_location();
Slave:
SELECT pg_last_xlog_receive_location()
d) Execute on Source :
SELECT now()::timestamp(0), slot_name, pg_current_wal_lsn(), restart_lsn, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),restart_lsn)) as replicationSlotLag, active from pg_replication_slots;
