Created
August 26, 2017 18:22
-
-
Save begriffs/761c04e44b68b75c8d4886b97a1a34bd to your computer and use it in GitHub Desktop.
Pretty epic psql config
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Be quiet while setting these values | |
\set QUIET 1 | |
-- Show time of all queries | |
-- \timing | |
-- Colors appear entirely broken with command-completion. Leaving commented for now. | |
/*\set PROMPT1 '%[%033[0;31m%]%n%[%033[0;37m%]@%[%033[0;33m%]%m %[%033[0;34m%]%~ %[%033[1;31%# m%]❯%[%033[1;33m%]❯%[%033[1;32m%]❯ %033[0m'*/ | |
\set PROMPT1 '%n@%m %~ ❯❯❯ ' | |
\set PROMPT2 '%R> ' | |
-- Set client encoding | |
\encoding unicode | |
-- Ignore errors from entered commands, not from scripts | |
-- \set ON_ERROR_ROLLBACK interactive | |
-- Be verbose | |
\set VERBOSITY verbose | |
-- "the expanded mode is used whenever the query output is wider than the | |
-- screen, otherwise the regular mode is used" | |
\x auto | |
\pset format wrapped | |
-- History files are cluster/user/db-specific | |
\set PGCLUSTER `echo $PGCLUSTER | tr '/' '-'` | |
\set HISTFILE ~/.psql_history- :PGCLUSTER - :USER -:DBNAME | |
-- Infinite history? | |
\set HISTSIZE | |
\set HISTCONTROL ignoredups | |
-- Show actual SQL executed by backslash commands | |
-- \set ECHO_HIDDEN ON | |
-- Autocomplete keywords (like SELECT) in upper-case, honoring whatever case | |
-- you starting in: http://robots.thoughtbot.com/an-explained-psqlrc | |
\set COMP_KEYWORD_CASE preserve-upper | |
\pset border 2 | |
\pset null ∅ | |
\pset linestyle unicode | |
\unset QUIET | |
-- From https://github.com/AF83/dotfiles/blob/9a9b3dbe64291ecfa0fe3d0a16302cfbf6592a39/.psqlrc | |
-- \echo '\nCurrent Host Server Date Time : '`date` '\n' | |
-- | |
-- \echo 'Administrative queries:\n' | |
-- | |
-- \echo '\t:activity\t-- Server activity' | |
-- \echo '\t:bloat\t\t-- show table and index bloat in your database ordered by most wasteful' | |
-- \echo '\t:blocking\t-- display queries holding locks other queries are waiting to be released' | |
-- \echo '\t:cache_hit\t-- calculates your cache hit rate (effective databases are at 99% and up)' | |
-- \echo '\t:conninfo\t-- Server connections' | |
-- \echo '\t:dbsize\t\t-- Database Size' | |
-- \echo '\t:index_size\t-- show the size of the indexes, descending by size' | |
-- \echo '\t:index_usage\t-- calculates your index hit rate (effective databases are at 99% and up)' | |
-- \echo '\t:locks\t\t-- Lock info' | |
-- \echo '\t:locks\t\t\t-- display queries with active locks' | |
-- \echo '\t:long_running_queries\t-- show all queries taking longer than five minutes ordered by duration' | |
-- \echo '\t:ps\t\t\t-- view active queries with execution time' | |
-- \echo '\t:seq_scans\t\t--show the count of seq_scans by table descending by order' | |
-- \echo '\t:settings\t-- Server Settings' | |
-- \echo '\t:tablesize\t-- Tables Size' | |
-- \echo '\t:total_index_size\t-- show the total size of the indexes in MB' | |
-- \echo '\t:unused_indexes\t\t-- Show unused and almost unused indexes, ordered by their size relative' | |
-- \echo '\t\t\t\t to the number of index scans. Exclude indexes of very small tables (< 5 pages),' | |
-- \echo '\t\t\t\t where the planner will almost invariably select a sequential scan, but may not' | |
-- \echo '\t\t\t\t in the future as the table grows.' | |
-- \echo '\t:uptime\t\t-- Server uptime' | |
-- \echo '\t:uselesscol\t-- Useless columns (must be run as superuser)' | |
-- \echo '\t:waits\t\t-- Waiting queries' | |
-- \echo '\n' | |
-- | |
-- \echo 'Development queries:\n' | |
-- | |
-- \echo '\t:show_slow_queries\t-- Show slow queries (requires pg_stat_statements)' | |
-- \echo '\t:sp\t\t\t-- Current Search Path' | |
-- \echo '\t:clear\t\t\t-- Clear screen' | |
-- \echo '\t:ll\t\t\t-- List\n' | |
-- | |
-- \echo 'Help:\n' | |
-- | |
-- \echo '\t:menu\t\t-- This help menu' | |
-- \echo '\t\\h\t\t-- Help with SQL commands' | |
-- \echo '\t\\?\t\t-- Help with psql commands\n' | |
-- Administration queries | |
\set menu '\\i ~/.psqlrc' | |
\set settings 'select name, setting,unit,context from pg_settings;' | |
\set locks '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 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, query, state from pg_stat_activity;' | |
\set waits 'SELECT pg_stat_activity.pid, pg_stat_activity.query, pg_stat_activity.waiting, 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 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 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;' | |
\set uptime 'select now() - pg_postmaster_start_time() AS uptime;' | |
-- Taken from https://github.com/heroku/heroku-pg-extras | |
-- via https://github.com/dlamotte/dotfiles/blob/master/psqlrc | |
\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 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;' | |
\set index_size 'SELECT relname AS name, pg_size_pretty(sum(relpages*1024)) AS size FROM pg_class WHERE reltype=0 GROUP BY relname ORDER BY sum(relpages) DESC;' | |
\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_table FROM 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, substr(pg_stat_activity.query,1,30) as query_snippet, age(now(),pg_stat_activity.query_start) as "age" from pg_stat_activity,pg_locks left outer join pg_class on (pg_locks.relation = pg_class.oid) where pg_stat_activity.query <> ''<insufficient privilege>'' and pg_locks.pid=pg_stat_activity.pid and pg_locks.mode = ''ExclusiveLock'' order by query_start;' | |
\set long_running_queries 'SELECT pid, now() - pg_stat_activity.query_start AS duration, query AS query FROM pg_stat_activity WHERE 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 ps 'select pid, application_name as source, age(now(),query_start) as running_for, waiting, query as query from pg_stat_activity where query <> ''<insufficient privilege>'' AND state <> ''idle'' and pid <> pg_backend_pid() order by 3 desc;' | |
\set seq_scans 'SELECT relname AS name, seq_scan as count FROM pg_stat_user_tables ORDER BY seq_scan DESC;' | |
\set total_index_size 'SELECT pg_size_pretty(sum(relpages*1024)) AS size FROM pg_class WHERE 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_scans 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) / nullif(idx_scan, 0) DESC NULLS FIRST, pg_relation_size(i.indexrelid) DESC;' | |
-- | |
-- Development queries: | |
\set sp 'SHOW search_path;' | |
\set clear '\\! clear;' | |
\set ll '\\! ls -lrt;' | |
\set show_slow_queries 'SELECT (total_time / 1000 / 60) as total_minutes, (total_time/calls) as average_time, query FROM pg_stat_statements ORDER BY 1 DESC LIMIT 100;' |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment