Mi .psqlrc

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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.