Coleccion de cositas para el .psqlrc
-------------------------------------------------- -- 20200115 DAF agrego funcionalidades -- 20200108 DAF .psqlrc Version inicial -------------------------------------------------- -- Para que no muestre el output de los siquientes comandos \set QUIET ON -- Prompts -- Prompt1 / Prompt2 / Prompt3 -- %M : server -- %m : host name -- %> : port -- %n : user -- %/ : current database -- %~ : like %/ but ~ for default database -- %# : '#' if superuser, '>' otherwise -- %R -- %x -- %[...%] : terminal control characters -- Examples \set PROMPT1 'u: %n db: %/ %# ' \set PROMPT2 '%# ' \set PROMPT3 '' \timing on -- Pone en mayusculas las palabras autocompletadas \set COMP_KEYWORD_CASE upper --tipo de lineas, unicode, ascii etc --\pset linestype ascii -- Borde de la tabla de resultados \pset border 0 -- unicode_border_linestyle: border drawing style for unicode style. -- Values: single, double. \pset unicode_border_linestyle single -- unicode_column_linestyle: column drawing style for unicode style. -- Values: single, double. \pset unicode_column_linestyle single -- unicode_header_linestyle: header drawing style for unicode style. -- Values: single, double. \pset unicode_header_linestyle single -- columns: target width for wrapped format -- Values: number \pset columns 0 -- expanded: extended display -- Values: auto, on, off \x off -- fieldsep_zero: set field separator to a zero byte. --Values: no value. --\pset fieldsep_zero -- fieldsep_zero: set field separator to a zero byte. --Values: no value. --\pset fieldsep_zero -- recordsep_zero: set the line separator in unaligned output format to -- zero byte. -- Values: no values. --\pset recordsep_zero -- tableattr: attributes in the table tag for HTML format. -- Values: string. Without value, will unset attributes. --\pset tableattr TODO -- title: table title for printed tables. -- Values: string. Without value, title is unset. -- tuples_only: can also be abreviated with \t -- Values: on, off. Without value, will toggle. \pset tuples_only off -- Para que no pagine \pset pager off -- Como va a imprimir los NULOS \pset null '[null]' -- Nombre del archivo history \set HISTFILE ~/scripts/psqlhists/psql_history- :HOST - :PORT - :DBNAME - :USER -- Cantidad de comandos en el history \set HISTSIZE 5000 -- Sin duplicados \set HISTCONTROL ignoredups -- Nivel de debug \set VERBOSITY verbose \errverbose -- como el @echo off para imprimir algun mensaje \set ECHO_HIDDEN ON \set QUIET OFF \echo '\nCurrent Host Server Date Time : '`date` '\n' \echo '\t:activity :cache_hit :nspsize :tablesize1' \echo '\t:activity1 :conninfo :nspsize_pgs :total_index_size' \echo '\t:activity2 :dbsize :ps :unused_indexes' \echo '\t:activity96 :idletxn :queries :uptime' \echo '\t:activitytho :index_size :seq_scans :uselesscol' \echo '\t: :index_usage :settings :vacuuminfo' \echo '\t:autovacs :index_usage_adv :show_slow_queries :waits' \echo '\t:backends :kill_old_transactions :spcsize :wrapstats' \echo '\t:bloat :locks :statrelfilepath :wrapstats_raw' \echo '\t:bloat2 :locks1 :statrelfilepath_agg :wrapstats_rels' \echo '\t:blocking :long_running_queries :statrelspcfilepath_agg :wrapstats_spc' \echo '\t:buffer_cache_hit_ratio :missing_indexes :tablesize' \echo '\n\t:menu -- Help Menu' \echo '\t\h-- Help with SQL commands' \echo '\t\?-- Help with psql commands' \echo '\nDevelopment queries:\n' \echo '\t:sp-- Current Search Path' \echo '\t:clear -- Clear screen' \echo '\t:ll -- List\n' \set sp 'SHOW search_path;' \set clear '\\! clear;' \set ll '\\! ls -lrt' \set uptime 'select now() - pg_postmaster_start_time() AS uptime;' \set menu '\\i ~/.psqlrc' -- buffer cache hit ratio \set buffer_cache_hit_ratio 'select datname, blks_hit::float/(blks_hit+blks_read) as hit_ratio from pg_stat_database where blks_read+blks_hit <> 0;' \set cache_hit 'SELECT ''index hit rate'' AS name, (sum(idx_blks_hit)) / sum(idx_blks_hit + idx_blks_read) AS ratio FROM pg_statio_user_indexes UNION ALL SELECT ''cache hit rate'' AS name, sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS ratio FROM pg_statio_user_tables;' -- kill transactions that have been "idle in transaction" for more than 10 minutes \set kill_old_transactions 'select count(pg_terminate_backend(pid)) as nb_killed_proc from pg_stat_activity where query = \'in transaction\' and current_timestamp - query_start > \'10 min\';' -- running queries \set queries 'select current_timestamp - query_start as runtime,pid,datname,usename,query from pg_stat_activity where query != \' \' order by 1 desc; ' \set autovacs 'WITH q0_0 AS (SELECT relid, CASE WHEN coalesce(last_vacuum, last_analyze, last_autovacuum, last_autoanalyze) IS NULL THEN NULL WHEN last_vacuum = (SELECT MAX(UNNEST) FROM (SELECT UNNEST(ARRAY[last_vacuum, last_analyze, last_autovacuum, last_autoanalyze]))s0_0) THEN '' v '' WHEN last_analyze = (SELECT MAX(UNNEST) FROM (SELECT UNNEST(ARRAY[last_vacuum, last_analyze, last_autovacuum, last_autoanalyze]))s1_0) THEN '' z '' WHEN last_autoanalyze = (SELECT MAX(UNNEST) FROM (SELECT UNNEST(ARRAY[last_vacuum, last_analyze, last_autovacuum, last_autoanalyze]))s2_0) THEN ''az '' WHEN last_autovacuum = (SELECT MAX(UNNEST) FROM (SELECT UNNEST(ARRAY[last_vacuum, last_analyze, last_autovacuum, last_autoanalyze]))s3_0) THEN ''av '' END AS last_stats_str, CASE WHEN coalesce(last_vacuum, last_analyze, last_autovacuum, last_autoanalyze) IS NULL THEN NULL WHEN last_vacuum = (SELECT MAX(UNNEST) FROM (SELECT UNNEST(ARRAY[last_vacuum, last_analyze, last_autovacuum, last_autoanalyze]))s0_0) THEN last_vacuum::TIMESTAMP WHEN last_analyze = (SELECT MAX(UNNEST) FROM (SELECT UNNEST(ARRAY[last_vacuum, last_analyze, last_autovacuum, last_autoanalyze]))s1_0) THEN last_analyze::TIMESTAMP WHEN last_autoanalyze = (SELECT MAX(UNNEST) FROM (SELECT UNNEST(ARRAY[last_vacuum, last_analyze, last_autovacuum, last_autoanalyze]))s2_0) THEN last_autoanalyze::TIMESTAMP WHEN last_autovacuum = (SELECT MAX(UNNEST) FROM (SELECT UNNEST(ARRAY[last_vacuum, last_analyze, last_autovacuum, last_autoanalyze]))s3_0) THEN last_autovacuum::TIMESTAMP END AS last_stats_stamp FROM pg_stat_user_tables), q0 AS (SELECT relid::regclass rel, n_tup_ins ti, n_tup_upd + n_tup_del tm, n_live_tup tliv, n_dead_tup nded, n_mod_since_analyze tmod, last_stats_str||last_stats_stamp AS last_stats, autoanalyze_count naz, autovacuum_count nav, analyze_count nz, vacuum_count nv FROM pg_stat_user_tables JOIN q0_0 USING (relid) ORDER BY last_stats_stamp DESC nulls LAST, schemaname, relname)SELECT * FROM q0 ;' \set bloat2 'SELECT tablename AS TABLE_NAME, ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS table_bloat, CASE WHEN relpages < otta THEN ''0'' ELSE pg_size_pretty((bs*(sml.relpages-otta)::bigint)::bigint) END AS table_waste, iname AS index_name, ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS index_bloat, CASE WHEN ipages < iotta THEN ''0'' ELSE pg_size_pretty((bs*(ipages-iotta))::bigint) END AS index_waste FROM (SELECT schemaname, tablename, cc.reltuples, cc.relpages, bs, CEIL((cc.reltuples*((datahdr+ma- (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta, COALESCE(c2.relname,''?'') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages, COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta FROM (SELECT ma, bs, schemaname, tablename, (datawidth+(hdr+ma-(CASE WHEN hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr, (maxfracsum*(nullhdr+ma-(CASE WHEN nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2 FROM (SELECT schemaname, tablename, hdr, ma, bs, SUM((1-null_frac)*avg_width) AS datawidth, MAX(null_frac) AS maxfracsum, hdr+ (SELECT 1+count(*)/8 FROM pg_stats s2 WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename) AS nullhdr FROM pg_stats s, (SELECT (SELECT current_setting(''block_size'')::numeric) AS bs, CASE WHEN substring(v,12,3) IN (''8.0'', ''8.1'', ''8.2'') THEN 27 ELSE 23 END AS hdr, CASE WHEN v ~ ''mingw32'' THEN 8 ELSE 4 END AS ma FROM (SELECT version() AS v) AS foo) AS constants GROUP BY 1, 2, 3, 4, 5) AS foo) AS rs JOIN pg_class cc ON cc.relname = rs.tablename JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> ''information_schema'' LEFT JOIN pg_index i ON indrelid = cc.oid LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid) AS sml ORDER BY CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END DESC;' \set bloat 'SELECT tablename AS TABLE_NAME, ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS table_bloat, CASE WHEN relpages < otta THEN ''0'' ELSE pg_size_pretty((bs*(sml.relpages-otta)::bigint)::bigint) END AS table_waste, iname AS index_name, ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS index_bloat, CASE WHEN ipages < iotta THEN ''0'' ELSE pg_size_pretty((bs*(ipages-iotta))::bigint) END AS index_waste FROM (SELECT schemaname, tablename, cc.reltuples, cc.relpages, bs, CEIL((cc.reltuples*((datahdr+ma- (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta, COALESCE(c2.relname,''?'') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages, COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta FROM (SELECT ma, bs, schemaname, tablename, (datawidth+(hdr+ma-(CASE WHEN hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr, (maxfracsum*(nullhdr+ma-(CASE WHEN nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2 FROM (SELECT schemaname, tablename, hdr, ma, bs, SUM((1-null_frac)*avg_width) AS datawidth, MAX(null_frac) AS maxfracsum, hdr+ (SELECT 1+count(*)/8 FROM pg_stats s2 WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename) AS nullhdr FROM pg_stats s, (SELECT (SELECT current_setting(''block_size'')::numeric) AS bs, CASE WHEN substring(v,12,3) IN (''8.0'', ''8.1'', ''8.2'') THEN 27 ELSE 23 END AS hdr, CASE WHEN v ~ ''mingw32'' THEN 8 ELSE 4 END AS ma FROM (SELECT version() AS v) AS foo) AS constants GROUP BY 1, 2, 3, 4, 5) AS foo) AS rs JOIN pg_class cc ON cc.relname = rs.tablename JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> ''information_schema'' LEFT JOIN pg_index i ON indrelid = cc.oid LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid) AS sml ORDER BY CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END DESC;' \set blocking 'SELECT bl.pid AS blocked_pid, ka.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;' \set idletxn 'SELECT datname, pid, (clock_timestamp() - backend_start) AS bk_age, (clock_timestamp() - state_change) state_age, (clock_timestamp() - xact_start) xact_age, usename, application_name app, client_addr, (STATE || CASE WHEN waiting THEN ''(w)'' ELSE '''' END) AS STATE, queryFROM pg_stat_activityWHERE xact_start IS NOT NULL AND pid <> pg_backend_pid()ORDER BY xact_start;' \set index_size 'SELECT relname AS name, pg_size_pretty(sum(relpages*1024)) AS SIZEFROM pg_classWHERE reltype=0GROUP BY relnameORDER BY sum(relpages) DESC;' \set index_usage_adv 'SELECT *FROM (SELECT stat.relname AS TABLE, stai.indexrelname AS INDEX, CASE stai.idx_scan WHEN 0 THEN ''Insufficient data'' ELSE (100 * stai.idx_scan / (stat.seq_scan + stai.idx_scan))::text || ''%'' END hit_rate, CASE stat.idx_scan WHEN 0 THEN ''Insufficient data'' ELSE (100 * stat.idx_scan / (stat.seq_scan + stat.idx_scan))::text || ''%'' END all_index_hit_rate, ARRAY (SELECT pg_get_indexdef(idx.indexrelid, k + 1, TRUE) FROM generate_subscripts(idx.indkey, 1) AS k ORDER BY k) AS cols, stat.n_live_tup rows_in_table FROM pg_stat_user_indexes AS stai JOIN pg_stat_user_tables AS stat ON stai.relid = stat.relid JOIN pg_index AS idx ON (idx.indexrelid = stai.indexrelid)) AS sub_innerORDER BY rows_in_table DESC, hit_rate ASC;' \set index_usage 'SELECT relname, CASE idx_scan WHEN 0 THEN ''Insufficient data'' ELSE (100 * idx_scan / (seq_scan + idx_scan))::text END percent_of_times_index_used, n_live_tup rows_in_tableFROM pg_stat_user_tables ORDER BY n_live_tup DESC;' \set locks 'SELECT pg_stat_activity.pid, pg_class.relname, pg_locks.transactionid, pg_locks.granted, substring(pg_stat_activity.query FROM ''([^]*?){1,3}'') AS query_snippet, age(now(),pg_stat_activity.query_start) AS "age"FROM pg_stat_activity, pg_locksLEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)WHERE pg_stat_activity.query <> '' '' AND pg_locks.pid=pg_stat_activity.pid AND pg_locks.mode = ''ExclusiveLock''ORDER BY query_start;' \set locks1 'SELECT bl.pid AS blocked_pid, a.usename AS blocked_user, kl.pid AS blocking_pid, ka.usename AS blocking_user, a.query AS blocked_statement 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;' \set long_running_queries 'SELECT pid, now() - pg_stat_activity.query_start AS duration, query AS queryFROM pg_stat_activityWHERE pg_stat_activity.query <> ''''::text AND now() - pg_stat_activity.query_start > interval ''5 minutes''ORDER BY now() - pg_stat_activity.query_start DESC;' \set missing_indexes 'SELECT relname, seq_scan-idx_scan AS too_much_seq, CASE WHEN seq_scan-idx_scan > 0 THEN ''Missing Index?'' ELSE ''OK'' END, pg_relation_size(relname::regclass) AS rel_size, seq_scan, idx_scanFROM pg_stat_all_tablesWHERE schemaname=''public'' AND pg_relation_size(relname::regclass) > 80000ORDER BY too_much_seq DESC;' \set nspsize_pgs 'SELECT * from (SELECT nspname, pg_size_pretty(sum(8192::bigint*nullif(tbl.relpages, 0))) "tables", pg_size_pretty(sum(8192::bigint*nullif(idx.relpages, 0))) "indexes", pg_size_pretty(sum(8192::bigint*nullif(tst.relpages, 0))) "toast", pg_size_pretty(sum(8192::bigint*( coalesce(tbl.relpages, 0) + coalesce(idx.relpages, 0) + coalesce(tst.relpages, 0)))) AS "total", concat( count(distinct tbl.oid) FILTER (where tbl.relkind=''r'')::text, ''/'', count(distinct tst.oid)::text, ''/'', count(distinct idx.oid)::text, ''/'', count(distinct tbl.oid) FILTER (where tbl.relkind=''m'')::text ) AS "#r/t/i/m"FROM pg_class AS tblJOIN pg_namespace AS nsp ON (tbl.relnamespace=nsp.oid)LEFT JOIN pg_class tst ON (tbl.reltoastrelid=tst.oid)LEFT JOIN pg_index ON (pg_index.indrelid=tbl.oid)LEFT JOIN pg_class idx ON pg_index.indexrelid=idx.oidWHERE (tbl.relkind = ''r'' OR tbl.relkind = ''m'')GROUP BY nspnameORDER BY sum(coalesce(tbl.relpages, 0)+coalesce(idx.relpages, 0)+coalesce(tst.relpages, 0))DESC) _' \set nspsize 'SELECT * from (SELECT nspname, pg_size_pretty(sum(pg_table_size(pg_class.oid))) "Schema Size", pg_size_pretty(sum(pg_indexes_size(pg_class.oid))) "Indexes", count(pg_class.oid) "Tables"FROM pg_classJOIN pg_namespace ON (pg_class.relnamespace=pg_namespace.oid)WHERE relkind = ''r'' or relkind = ''m''GROUP BY nspnameORDER BY sum(pg_total_relation_size(pg_class.oid)) DESC) _' \set ps 'SELECT pid, application_name AS SOURCE, age(now(),query_start) AS running_for, waiting, query AS queryFROM pg_stat_activityWHERE query <> '' '' AND STATE <> ''idle'' AND pid <> pg_backend_pid()ORDER BY 3 DESC;' \set seq_scans 'SELECT relname AS name, seq_scan AS COUNTFROM pg_stat_user_tables ORDER BY seq_scan DESC;' -- number of connected backends \set settings 'select name, setting,unit,context from pg_settings;' \set show_slow_queries 'SELECT (total_time / 1000 / 60) AS total_minutes, (total_time/calls) AS average_time, queryFROM pg_stat_statementsORDER BY 1 DESC LIMIT 100;' \set spcsize 'SELECT coalesce(spcname, ''[Default]'') "Tablespace", pg_size_pretty(sum(pg_relation_size(c.oid)) FILTER (WHERE relkind = ''r'' or relkind = ''m'')) "Data Size", pg_size_pretty(sum(pg_relation_size(c.oid)) FILTER (WHERE relkind = ''i'' )) "Index Size", count(c.oid) "# Tables"FROM pg_class cLEFT JOIN pg_tablespace spc ON (c.reltablespace=spc.oid)WHERE relkind = ''r'' or relkind = ''m'' or relkind = ''i''GROUP BY 1/*ORDER BY sum(pg_total_relation_size(c.oid)) DESC;*/ORDER BY 1;' \set statrelfilepath_agg 'select * from (select relid, nsp.nspname, c.relname, fpath,numsegs,tot_size,access_bnds,modification_bnds,change_bnds,creation_bnds,isdir from (SELECT c.oid relid, relpages / segsz.seg_size AS numsegs, pg_relation_filepath(c.oid) AS fpath, sum(size) tot_size, tstzrange(min(fstat.access), max(fstat.access), ''[]'') access_bnds, tstzrange(min(fstat.modification), max(fstat.modification), ''[]'') modification_bnds, tstzrange(min(fstat.change), max(fstat.change), ''[]'') change_bnds, tstzrange(min(fstat.creation), max(fstat.creation), ''[]'') creation_bnds, fstat.isdirFROM (SELECT setting::bigint seg_sizeFROM pg_settingsWHERE name = ''segment_size'') AS segsz,pg_class c,generate_series(0::bigint, relpages / segsz.seg_size) segnum,concat(pg_relation_filepath(c.oid), coalesce(''.''||nullif(segnum, 0), '''')) _fpath,pg_stat_file(_fpath) fstatwhere relkind=''r'' group by relid, numsegs, fstat.isdir)_JOIN pg_class c on c.oid=relidJOIN pg_namespace nsp ON c.relnamespace=nsp.oid)_' \set statrelfilepath 'SELECT c.oid relid, nsp.nspname, c.relname, segnum, fpath, fstat.size, fstat.access, fstat.modification, fstat.change, fstat.creation, fstat.isdirFROM (SELECT setting::bigint seg_sizeFROM pg_settingsWHERE name = ''segment_size'') AS segsz,pg_class cJOIN pg_namespace nsp ON c.relnamespace=nsp.oid,generate_series(0::bigint, relpages / segsz.seg_size) segnum,concat(pg_relation_filepath(c.oid), coalesce(''.''||nullif(segnum, 0), '''')) fpath,pg_stat_file(fpath) fstat' \set statrelspcfilepath_agg 'SELECTrelid,nspname,relname,spcname,fpath,numsegs,tot_size,access_bnds,modification_bnds,change_bnds,creation_bnds,isdirFROM (SELECT relid, nsp.nspname, c.relname, spc.spcname, fpath, numsegs, tot_size, access_bnds, modification_bnds, change_bnds, creation_bnds, isdirFROM (SELECT c.oid relid, 1 + relpages/segsz.seg_size AS numsegs, pg_relation_filepath(c.oid) AS fpath, sum(size) tot_size, tstzrange(min(fstat.access), max(fstat.access), ''[]'') access_bnds, tstzrange(min(fstat.modification), max(fstat.modification), ''[]'') modification_bnds, tstzrange(min(fstat.change), max(fstat.change), ''[]'') change_bnds, tstzrange(min(fstat.creation), max(fstat.creation), ''[]'') creation_bnds, fstat.isdirFROM (SELECT setting::bigint seg_sizeFROM pg_settingsWHERE name = ''segment_size'') AS segsz,pg_class c,generate_series(0::bigint, relpages / segsz.seg_size) segnum,concat(pg_relation_filepath(c.oid), coalesce(''.''||nullif(segnum, 0), '''')) _fpath,pg_stat_file(_fpath) fstatwhere relkind=''r'' group by relid, numsegs, fstat.isdir)_JOIN pg_class c ON c.oid=relidJOIN pg_namespace nsp ON c.relnamespace=nsp.oidJOIN pg_tablespace spc ON ((c.reltablespace=spc.oid) OR (spc.spcname=''pg_default'' and c.reltablespace=0)))_' \set total_index_size 'SELECT pg_size_pretty(sum(relpages*1024)) AS SIZEFROM pg_classWHERE reltype=0;' \set unused_indexes 'SELECT schemaname || ''.'' || relname AS TABLE, indexrelname AS INDEX, pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, idx_scan AS index_scansFROM pg_stat_user_indexes uiJOIN pg_index i ON ui.indexrelid = i.indexrelidWHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST, pg_relation_size(i.indexrelid) DESC;' \set vacuuminfo '/*https://gist.githubusercontent.com/skehlet/36aad599171b25826e82/raw/b84501643ec16d05f8b307ad87bac884bc14fd52/gistfile1.sql*/WITH table_opts AS ( SELECT pg_class.oid, relname, nspname, array_to_string(reloptions, '''') AS relopts FROM pg_class INNER JOIN pg_namespace ns ON relnamespace = ns.oid), storage_settings AS ( SELECT oid, relname, nspname, CASE WHEN relopts LIKE ''%autovacuum_vacuum_threshold%'' THEN regexp_replace(relopts, ''.*autovacuum_vacuum_threshold=([0-9.]+).*'', E''\\1'')::integer ELSE current_setting(''autovacuum_vacuum_threshold'')::integer END AS autovacuum_vacuum_threshold, CASE WHEN relopts LIKE ''%autovacuum_vacuum_scale_factor%'' THEN regexp_replace(relopts, ''.*autovacuum_vacuum_scale_factor=([0-9.]+).*'', E''\\1'')::real ELSE current_setting(''autovacuum_vacuum_scale_factor'')::real END AS autovacuum_vacuum_scale_factor, CASE WHEN relopts LIKE ''%autovacuum_freeze_min_age%'' THEN regexp_replace(relopts, ''.*autovacuum_freeze_min_age=([0-9.]+).*'', E''\\1'')::integer ELSE current_setting(''vacuum_freeze_min_age'')::integer END AS autovacuum_freeze_min_age, CASE WHEN relopts LIKE ''%autovacuum_freeze_table_age%'' THEN regexp_replace(relopts, ''.*autovacuum_freeze_table_age=([0-9.]+).*'', E''\\1'')::real ELSE current_setting(''vacuum_freeze_table_age'')::real END AS autovacuum_freeze_table_age, CASE WHEN relopts LIKE ''%autovacuum_freeze_max_age%'' THEN regexp_replace(relopts, ''.*autovacuum_freeze_max_age=([0-9.]+).*'', E''\\1'')::real ELSE current_setting(''autovacuum_freeze_max_age'')::real END AS autovacuum_freeze_max_age FROM table_opts)SELECT storage_settings.nspname AS schema, storage_settings.relname AS table, to_char(pg_class.reltuples, ''9G999G999G999'') AS rowcount, to_char(psut.n_dead_tup, ''9G999G999G999'') AS dead_rowcount, to_char(autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor::numeric * pg_class.reltuples), ''9G999G999G999'') AS autovacuum_threshold, CASE WHEN autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor::numeric * pg_class.reltuples) < psut.n_dead_tup THEN ''yes'' END AS expect_autovacuum, age(relfrozenxid) as relfrozenxid_age, autovacuum_freeze_table_age, CASE WHEN age(relfrozenxid) > autovacuum_freeze_table_age THEN ''yes'' END AS next_autovacuum_will_be_a_freeze, autovacuum_freeze_max_age, ROUND(100.0 * age(relfrozenxid) / autovacuum_freeze_max_age::numeric, 1) || ''%'' AS "% til forced vacuum freeze"FROM pg_stat_user_tables psut INNER JOIN pg_class ON psut.relid = pg_class.oid INNER JOIN storage_settings ON pg_class.oid = storage_settings.oidORDER BY storage_settings.relname;' \set waits 'SELECT pg_stat_activity.pid, pg_stat_activity.query, pg_stat_activity.state, now() - pg_stat_activity.query_start AS \"totaltime\", pg_stat_activity.backend_start FROM pg_stat_activity WHERE pg_stat_activity.query !~ \'%IDLE%\'::text AND pg_stat_activity.waiting = TRUE;' \set waits1 'SELECT pg_stat_activity.pid, pg_stat_activity.query, pg_stat_activity.state, now() - pg_stat_activity.query_start AS \"totaltime\", pg_stat_activity.backend_start FROM pg_stat_activity WHERE pg_stat_activity.query !~ \'%IDLE%\'::text AND pg_stat_activity.waiting = true;' \set wrapstats_raw '/*https://gist.githubusercontent.com/skehlet/36aad599171b25826e82/raw/b84501643ec16d05f8b307ad87bac884bc14fd52/gistfile1.sql*/select relid, spcid, spcname, nspid, nspname, reltuples, relpages, relsize_pgs, frozenage, iceinfrom (SELECT storage_settings.oid AS relid, spc.oid spcid, spc.spcname, nsp.oid nspid, nsp.nspname, c.reltuples, c.relpages, 8192::bigint*c.relpages relsize_pgs, age(relfrozenxid) as frozenage, autovacuum_freeze_max_age - age(relfrozenxid) AS iceinFROM pg_stat_user_tables psut JOIN pg_class c ON psut.relid = c.oid JOIN ( SELECT oid, relname, nspname, CASE WHEN relopts LIKE ''%autovacuum_vacuum_threshold%'' THEN regexp_replace(relopts, ''.*autovacuum_vacuum_threshold=([0-9.]+).*'', E''\\1'')::integer ELSE current_setting(''autovacuum_vacuum_threshold'')::integer END AS autovacuum_vacuum_threshold, CASE WHEN relopts LIKE ''%autovacuum_vacuum_scale_factor%'' THEN regexp_replace(relopts, ''.*autovacuum_vacuum_scale_factor=([0-9.]+).*'', E''\\1'')::real ELSE current_setting(''autovacuum_vacuum_scale_factor'')::real END AS autovacuum_vacuum_scale_factor, CASE WHEN relopts LIKE ''%autovacuum_freeze_min_age%'' THEN regexp_replace(relopts, ''.*autovacuum_freeze_min_age=([0-9.]+).*'', E''\\1'')::integer ELSE current_setting(''vacuum_freeze_min_age'')::integer END AS autovacuum_freeze_min_age, CASE WHEN relopts LIKE ''%autovacuum_freeze_table_age%'' THEN regexp_replace(relopts, ''.*autovacuum_freeze_table_age=([0-9.]+).*'', E''\\1'')::real ELSE current_setting(''vacuum_freeze_table_age'')::real END AS autovacuum_freeze_table_age, CASE WHEN relopts LIKE ''%autovacuum_freeze_max_age%'' THEN regexp_replace(relopts, ''.*autovacuum_freeze_max_age=([0-9.]+).*'', E''\\1'')::real ELSE current_setting(''autovacuum_freeze_max_age'')::real END AS autovacuum_freeze_max_age FROM ( SELECT pg_class.oid, relname, nspname, array_to_string(reloptions, '''') AS relopts FROM pg_class INNER JOIN pg_namespace ns ON relnamespace = ns.oid )table_opts)storage_settings ON c.oid = storage_settings.oid JOIN pg_tablespace spc on c.reltablespace=spc.oid or (c.reltablespace=0 and spc.spcname=''pg_default'') JOIN pg_namespace nsp on c.relnamespace=nsp.oidORDER BY icein)_' \set wrapstats_rels '/*https://gist.githubusercontent.com/skehlet/36aad599171b25826e82/raw/b84501643ec16d05f8b307ad87bac884bc14fd52/gistfile1.sql*/WITH table_opts AS ( SELECT pg_class.oid, relname, nspname, array_to_string(reloptions, '''') AS relopts FROM pg_class INNER JOIN pg_namespace ns ON relnamespace = ns.oid), storage_settings AS ( SELECT oid, relname, nspname, CASE WHEN relopts LIKE ''%autovacuum_vacuum_threshold%'' THEN regexp_replace(relopts, ''.*autovacuum_vacuum_threshold=([0-9.]+).*'', E''\\1'')::integer ELSE current_setting(''autovacuum_vacuum_threshold'')::integer END AS autovacuum_vacuum_threshold, CASE WHEN relopts LIKE ''%autovacuum_vacuum_scale_factor%'' THEN regexp_replace(relopts, ''.*autovacuum_vacuum_scale_factor=([0-9.]+).*'', E''\\1'')::real ELSE current_setting(''autovacuum_vacuum_scale_factor'')::real END AS autovacuum_vacuum_scale_factor, CASE WHEN relopts LIKE ''%autovacuum_freeze_min_age%'' THEN regexp_replace(relopts, ''.*autovacuum_freeze_min_age=([0-9.]+).*'', E''\\1'')::integer ELSE current_setting(''vacuum_freeze_min_age'')::integer END AS autovacuum_freeze_min_age, CASE WHEN relopts LIKE ''%autovacuum_freeze_table_age%'' THEN regexp_replace(relopts, ''.*autovacuum_freeze_table_age=([0-9.]+).*'', E''\\1'')::real ELSE current_setting(''vacuum_freeze_table_age'')::real END AS autovacuum_freeze_table_age, CASE WHEN relopts LIKE ''%autovacuum_freeze_max_age%'' THEN regexp_replace(relopts, ''.*autovacuum_freeze_max_age=([0-9.]+).*'', E''\\1'')::real ELSE current_setting(''autovacuum_freeze_max_age'')::real END AS autovacuum_freeze_max_age FROM table_opts),_ as (SELECT storage_settings.oid::regclass AS "rel", to_char(pg_class.reltuples, ''9G999G999G999'') AS "Rows", to_char(pg_class.relpages, ''9G999G999'') AS "Pages", pg_size_pretty(pg_table_size(pg_class.oid)) AS "Size", pg_table_size(pg_class.oid) "size", age(relfrozenxid) as "icedXID", autovacuum_freeze_table_age "AV TblIceAge", CASE WHEN age(relfrozenxid) > autovacuum_freeze_table_age THEN ''AV'' WHEN age(relfrozenxid) > current_setting(''vacuum_freeze_table_age'')::integer THEN ''V'' END AS "Icing", autovacuum_freeze_max_age "IceMaxAV", autovacuum_freeze_max_age - age(relfrozenxid) AS "ice_in", ROUND(100.0 * age(relfrozenxid) / autovacuum_freeze_max_age::numeric, 1) || ''%'' AS "IceAV%"FROM pg_stat_user_tables psut INNER JOIN pg_class ON psut.relid = pg_class.oid INNER JOIN storage_settings ON pg_class.oid = storage_settings.oidORDER BY ice_in)select "rel", "Pages", "Size", "icedXID", ice_in, "IceAV%" from _' \set wrapstats_spc '/*https://gist.githubusercontent.com/skehlet/36aad599171b25826e82/raw/b84501643ec16d05f8b307ad87bac884bc14fd52/gistfile1.sql*/WITH table_opts AS ( SELECT pg_class.oid, relname, nspname, array_to_string(reloptions, '''') AS relopts FROM pg_class INNER JOIN pg_namespace ns ON relnamespace = ns.oid), storage_settings AS ( SELECT oid, relname, nspname, CASE WHEN relopts LIKE ''%autovacuum_vacuum_threshold%'' THEN regexp_replace(relopts, ''.*autovacuum_vacuum_threshold=([0-9.]+).*'', E''\\1'')::integer ELSE current_setting(''autovacuum_vacuum_threshold'')::integer END AS autovacuum_vacuum_threshold, CASE WHEN relopts LIKE ''%autovacuum_vacuum_scale_factor%'' THEN regexp_replace(relopts, ''.*autovacuum_vacuum_scale_factor=([0-9.]+).*'', E''\\1'')::real ELSE current_setting(''autovacuum_vacuum_scale_factor'')::real END AS autovacuum_vacuum_scale_factor, CASE WHEN relopts LIKE ''%autovacuum_freeze_min_age%'' THEN regexp_replace(relopts, ''.*autovacuum_freeze_min_age=([0-9.]+).*'', E''\\1'')::integer ELSE current_setting(''vacuum_freeze_min_age'')::integer END AS autovacuum_freeze_min_age, CASE WHEN relopts LIKE ''%autovacuum_freeze_table_age%'' THEN regexp_replace(relopts, ''.*autovacuum_freeze_table_age=([0-9.]+).*'', E''\\1'')::real ELSE current_setting(''vacuum_freeze_table_age'')::real END AS autovacuum_freeze_table_age, CASE WHEN relopts LIKE ''%autovacuum_freeze_max_age%'' THEN regexp_replace(relopts, ''.*autovacuum_freeze_max_age=([0-9.]+).*'', E''\\1'')::real ELSE current_setting(''autovacuum_freeze_max_age'')::real END AS autovacuum_freeze_max_age FROM table_opts),_ as (SELECT storage_settings.oid::regclass AS "rel", spc.spcname, to_char(c.reltuples, ''9G999G999G999'') AS "Rows", to_char(c.relpages, ''9G999G999'') AS "Pages", pg_size_pretty(8192::bigint*c.relpages) AS "Size", 8192::bigint*c.relpages "size", age(relfrozenxid) as "icedXID", autovacuum_freeze_table_age "AV TblIceAge", CASE WHEN age(relfrozenxid) > autovacuum_freeze_table_age THEN ''AV'' WHEN age(relfrozenxid) > current_setting(''vacuum_freeze_table_age'')::integer THEN ''V'' END AS "Icing", autovacuum_freeze_max_age "IceMaxAV", autovacuum_freeze_max_age - age(relfrozenxid) AS "ice_in", ROUND(100.0 * age(relfrozenxid) / autovacuum_freeze_max_age::numeric, 1) || ''%'' AS "IceAV%"FROM pg_stat_user_tables psut JOIN pg_class c ON psut.relid = c.oid JOIN storage_settings ON c.oid = storage_settings.oid JOIN pg_tablespace spc on c.reltablespace=spc.oid or (c.reltablespace=0 and spc.spcname=''pg_default'') /*LEFT JOIN pg_locks ON c.oid=pg_locks.relation WHERE pg_locks.relation is null*/ORDER BY ice_in)select "rel", spcname, "Pages", "Size", "icedXID", ice_in, "IceAV%" from _' \set wrapstats '/*https://gist.githubusercontent.com/skehlet/36aad599171b25826e82/raw/b84501643ec16d05f8b307ad87bac884bc14fd52/gistfile1.sql*/WITH table_opts AS ( SELECT pg_class.oid, relname, nspname, array_to_string(reloptions, '''') AS relopts FROM pg_class INNER JOIN pg_namespace ns ON relnamespace = ns.oid), storage_settings AS ( SELECT oid, relname, nspname, CASE WHEN relopts LIKE ''%autovacuum_vacuum_threshold%'' THEN regexp_replace(relopts, ''.*autovacuum_vacuum_threshold=([0-9.]+).*'', E''\\1'')::integer ELSE current_setting(''autovacuum_vacuum_threshold'')::integer END AS autovacuum_vacuum_threshold, CASE WHEN relopts LIKE ''%autovacuum_vacuum_scale_factor%'' THEN regexp_replace(relopts, ''.*autovacuum_vacuum_scale_factor=([0-9.]+).*'', E''\\1'')::real ELSE current_setting(''autovacuum_vacuum_scale_factor'')::real END AS autovacuum_vacuum_scale_factor, CASE WHEN relopts LIKE ''%autovacuum_freeze_min_age%'' THEN regexp_replace(relopts, ''.*autovacuum_freeze_min_age=([0-9.]+).*'', E''\\1'')::integer ELSE current_setting(''vacuum_freeze_min_age'')::integer END AS autovacuum_freeze_min_age, CASE WHEN relopts LIKE ''%autovacuum_freeze_table_age%'' THEN regexp_replace(relopts, ''.*autovacuum_freeze_table_age=([0-9.]+).*'', E''\\1'')::real ELSE current_setting(''vacuum_freeze_table_age'')::real END AS autovacuum_freeze_table_age, CASE WHEN relopts LIKE ''%autovacuum_freeze_max_age%'' THEN regexp_replace(relopts, ''.*autovacuum_freeze_max_age=([0-9.]+).*'', E''\\1'')::real ELSE current_setting(''autovacuum_freeze_max_age'')::real END AS autovacuum_freeze_max_age FROM table_opts),_ as (SELECT storage_settings.nspname AS "Schema", storage_settings.relname AS "Table", to_char(pg_class.reltuples, ''9G999G999G999'') AS "Rows", to_char(pg_class.relpages, ''9G999G999'') AS "Pages", pg_size_pretty(pg_table_size(pg_class.oid)) AS "Size", age(relfrozenxid) as "icedXID", autovacuum_freeze_table_age "AV TblIceAge", CASE WHEN age(relfrozenxid) > autovacuum_freeze_table_age THEN ''AV'' WHEN age(relfrozenxid) > current_setting(''vacuum_freeze_table_age'')::integer THEN ''V'' END AS "Icing", autovacuum_freeze_max_age "IceMaxAV", autovacuum_freeze_max_age - age(relfrozenxid) AS "ice_in", ROUND(100.0 * age(relfrozenxid) / autovacuum_freeze_max_age::numeric, 1) || ''%'' AS "IceAV%"FROM pg_stat_user_tables psut INNER JOIN pg_class ON psut.relid = pg_class.oid INNER JOIN storage_settings ON pg_class.oid = storage_settings.oidORDER BY ice_in)select "Schema", "Table", "Pages", "Size", "icedXID", ice_in, "IceAV%" from _' -- number of connected backends \set backends 'SELECT datname, numbackends FROM pg_catalog.pg_stat_database;' \set conninfo 'select usename, count(*) from pg_stat_activity group by usename;' \set activity 'select datname, pid, usename, application_name,client_addr, client_hostname, client_port, state, left(query,20) query1 from pg_stat_activity;' \set activity1 'WITH _0 AS (SELECT datname, pid, (clock_timestamp() - backend_start) AS bk_age, (clock_timestamp() - state_change) state_age, (clock_timestamp() - xact_start) xact_age, (clock_timestamp() - query_start) query_age, usename, application_name app, client_addr, state, left(query,20) query1 FROM pg_stat_activity order by xact_start desc nulls last, query_start desc) SELECT * FROM _0 ;' \set activity96 'WITH _0 AS (SELECT datname, pid, (clock_timestamp() - backend_start) AS bk_age, (clock_timestamp() - state_change) state_age, (clock_timestamp() - xact_start) xact_age, (clock_timestamp() - query_start) query_age, usename, application_name app, client_addr, concat(STATE, CASE WHEN waiting THEN ''[WAIT]'' ELSE '''' END) AS STATE, regexp_replace(query, ''[ ]+'', '' '', ''g'') query1 FROM pg_stat_activity order by xact_start desc nulls last, query_start desc)SELECT * FROM _0; ' \set activity2 'WITH _0 AS (SELECT datname, pid, (clock_timestamp() - backend_start) AS bk_age, (clock_timestamp() - state_change) state_age, (clock_timestamp() - xact_start) xact_age, (clock_timestamp() - query_start) query_age, usename, application_name app, client_addr, state, left(regexp_replace(query, ''[ ]+'', '' '', ''g''),20) query1 FROM pg_stat_activity order by xact_start desc nulls last, query_start desc) SELECT * FROM _0;' --\set activitytho1 'WITH _0 AS (SELECT datname, pid, (clock_timestamp() - backend_start) AS bk_age, (clock_timestamp() - state_change) state_age, (clock_timestamp() - xact_start) xact_age, (clock_timestamp() - query_start) query_age, usename, application_name app, client_addr, state, left(regexp_replace(query, ''[ ]+'', '' '', ''g''),20) query1 FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND state <> ''idle'' ORDER BY xact_start DESC NULLS LAST, query_start DESC)SELECT * FROM _0 ;' \set activitytho 'WITH _0 AS (SELECT datname, pid, (clock_timestamp() - backend_start) AS bk_age, (clock_timestamp() - state_change) state_age, (clock_timestamp() - xact_start) xact_age, (clock_timestamp() - query_start) query_age, usename, application_name app, client_addr, state, left(query,20) query1, FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND state <> ''idle'' ORDER BY xact_start DESC NULLS LAST, query_start DESC)SELECT * FROM _0 ;' \set dbsize 'SELECT datname, pg_size_pretty(pg_database_size(datname)) db_size FROM pg_database ORDER BY db_size;' \set tablesize '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 40;' \set tablesize1 'WITH _0 AS (SELECT (nspname || \'.\' || relname) AS rel, 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)SELECT * from _0 ;' \set uselesscol 'SELECT nspname, relname, attname, typname, (stanullfrac*100)::int AS null_percent, case when stadistinct >= 0 then stadistinct else abs(stadistinct)*reltuples end AS \"distinct\", case 1 when stakind1 then stavalues1 when stakind2 then stavalues2 end AS \"values\" FROM pg_class c JOIN pg_namespace ns ON (ns.oid=relnamespace) JOIN pg_attribute ON (c.oid=attrelid) JOIN pg_type t ON (t.oid=atttypid) JOIN pg_statistic ON (c.oid=starelid AND staattnum=attnum) WHERE nspname NOT LIKE E\'pg\\\\_%\' AND nspname != \'information_schema\' AND relkind=\'r\' AND NOT attisdropped AND attstattarget != 0 AND reltuples >= 100 AND stadistinct BETWEEN 0 AND 1 ORDER BY nspname, relname, attname;'
Mas info en el gi: https://github.com/DiegoDAF/pgScrips