psqlrc

\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;'