Created
October 26, 2016 14:14
-
-
Save whalesalad/5b25f2086c7a8c8e4728f3b948e0cd95 to your computer and use it in GitHub Desktop.
~/.psqlrc
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
\timing | |
\set PROMPT1 '%[%033[33;1m%]%x%[%033[0m%]%[%033[1m%]%/%[%033[0m%] %m %R%# ' | |
\pset null 'NULL' | |
\pset border 2 | |
\x auto | |
\set HISTCONTROL ignoredups | |
\set COMP_KEYWORD_CASE upper | |
\set QUIET OFF | |
\echo '\nCurrent Host Server Date Time : '`date` '\n' | |
\echo 'Administrative queries:\n' | |
\echo '\t:settings\t-- Server Settings' | |
\echo '\t:ps\t\t-- Show running queries' | |
\echo '\t:locks\t\t-- Show locks and how long they have been locked' | |
\echo '\t:conninfo\t-- Server connections' | |
\echo '\t:activity\t-- Server activity' | |
\echo '\t:dbsize\t\t-- Database Size' | |
\echo '\t:tablesize\t-- Tables Size' | |
\echo '\t:uptime\t\t-- Server uptime' | |
\echo '\t:cachehit\t-- Cache hit ratio' | |
\echo '\t:indexusage\t-- Index usage' | |
\echo '\t:indexsize\t-- Index sizes' | |
\echo '\t:indextotal\t-- Total size of all indexes' | |
\echo '\t:outliers\t-- Slowest queries from pg_stat_statements' | |
\echo '\t:menu\t\t-- Help Menu' | |
\echo '\t\\h\t\t-- Help with SQL commands' | |
\echo '\t\\?\t\t-- Help with psql commands\n' | |
\echo 'Development queries:\n' | |
\echo '\t:sp\t\t-- Current Search Path' | |
\echo '\t:clear\t\t-- Clear screen' | |
\echo '\t:ll\t\t-- List\n' | |
\echo 'Cheat sheet:\n' | |
\echo '\tKill a process:\t\tSELECT pg_cancel_backend(pid)' | |
\echo '\tForce kill a process:\tSELECT pg_terminate_backend(pid)\n' | |
-- Administration queries | |
\set menu '\\i ~/.psqlrc' | |
\set settings 'select name, setting,unit,context from pg_settings;' | |
\set ps 'SELECT pid, state, application_name AS source, age(now(),xact_start) AS running_for, waiting, query FROM pg_stat_activity WHERE query <> \'<insufficient privilege>\' AND state <> \'idle\' AND pid <> pg_backend_pid() ORDER BY query_start ASC;' | |
\set locks 'SELECT a.datname, c.relname, l.transactionid, l.mode, l.granted, a.usename, a.query, a.query_start, age(now(), a.query_start) AS "age", a.pid FROM pg_stat_activity a JOIN pg_locks l ON l.pid = a.pid JOIN pg_class c ON c.oid = l.relation ORDER BY a.query_start;' | |
\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 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 uptime 'select now() - pg_postmaster_start_time() AS uptime;' | |
\set cachehit '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 \'table hit rate\' AS name, sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS ratio FROM pg_statio_user_tables;' | |
\set indexusage '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 indexsize 'SELECT c.relname AS name, pg_size_pretty(sum(c.relpages::bigint*8192)::bigint) AS size FROM pg_class c LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace) WHERE n.nspname NOT IN (\'pg_catalog\', \'information_schema\') AND n.nspname !~ \'^pg_toast\' AND c.relkind=\'i\' GROUP BY c.relname ORDER BY sum(c.relpages) DESC;' | |
\set totalindexsize 'with sizes as ( SELECT sum(c.relpages::bigint*8192)::bigint AS size FROM pg_class c LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace) WHERE n.nspname NOT IN (\'pg_catalog\', \'information_schema\') AND n.nspname !~ \'^pg_toast\' AND c.relkind=\'i\' GROUP BY c.relname ORDER BY sum(c.relpages) DESC) select pg_size_pretty(sum(size)) as total_index_size from sizes;' | |
\set lastvacuum 'select relname, last_vacuum, last_analyze from pg_stat_all_tables where schemaname = \'public\';' | |
\set outliers 'SELECT query AS qry, concat((total_time / calls), \' ms\') as avg_time, interval \'1 millisecond\' * total_time AS exec_time, to_char((total_time/sum(total_time) OVER()) * 100, \'FM90D0\') || \'%\' AS prop_exec_time, to_char(calls, \'FM999G999G990\') AS ncalls, interval \'1 millisecond\' * (blk_read_time + blk_write_time) AS sync_io_time FROM pg_stat_statements WHERE userid = (SELECT usesysid FROM pg_user WHERE usename = current_user LIMIT 1) ORDER BY total_time DESC LIMIT 10;' |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment