psqlrc

\set QUIET ON

\timing on
\encoding unicode
\x auto

\pset null ∅
\pset linestyle unicode
\pset border 2
--\pset pager always

\set VERBOSITY verbose
\set COMP_KEYWORD_CASE upper
\set PROMPT1 '\n%[%033[1;31;40m%] ➤ %[%033[32m%]psql:%[%033[36m%]//%n%[%033[34m%]@%[%033[36m%]%`hostname`:%>%[%033[33m%]/%/ %[%033[K%]%[%033[0m%]\n\n%[%033[1;33m%]%#%[%033[0m%] '

\set QUIET OFF

\echo '\nCurrent Host Server Date Time : '`date` '\n'
\echo 'Administrative queries:\n'
\echo '\t:settings\t-- Server Settings'
\echo '\t:conninfo\t-- Server connections'
\echo '\t:activity\t-- Server activity'
\echo '\t:locks\t\t-- Lock info'
\echo '\t:waits\t\t-- Waiting queires'
\echo '\t:dbsize\t\t-- Database Size'
\echo '\t:tablesize\t-- Tables Size'
\echo '\t:toastsize\t-- Toasts Size'
\echo '\t:deletedb\t-- Delete database'
\echo '\t:uselesscol\t-- Useless columns'
\echo '\t:uptime\t\t-- Server uptime'
\echo '\t:queries\t-- Running queries'
\echo '\t:kill_old_trans\t-- Kill transactions that have been "idle in transaction" for 10+ min\n'
\echo '\t:drop_all_tables\t-- Drop all tables in current database'
\echo '\t:refresh_collation\t-- Run REFRESH COLLATION VERSION on all databases'
\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'
\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'

-- Administration queries

\set menu '\\i ~/.psqlrc'
\set settings 'SELECT name, setting, unit, context FROM pg_settings;'
\set locks 'SELECT bl.pid "Blocked PID", a.usename "Blocked user", kl.pid "Blocking PID", ka.usename "Blocking user", a.query "Query" 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 "Username", COUNT(*) "Connections" 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.wait_event, 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.wait_event IS NOT NULL;'
\set dbsize 'SELECT datname, pg_size_pretty(pg_database_size(datname)) db_size FROM pg_database ORDER BY pg_database_size(datname) DESC;'
\set tablesize 'SELECT nspname || \'.\' || relname AS \"relation\", (CASE WHEN C.reltoastrelid <> 0 THEN pg_size_pretty(pg_relation_size(C.oid) + pg_relation_size(C.reltoastrelid)) ELSE pg_size_pretty(pg_relation_size(C.oid)) END) AS \"size\", TO_CHAR(reltuples, \'999 999 999 999 999D\') tuples, (CASE WHEN relkind = \'r\' THEN \'Table\' WHEN relkind = \'i\' THEN \'Index\' WHEN relkind = \'t\' THEN \'Toast\' ELSE relkind::text END) \"relkind\" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE relkind != \'t\' AND nspname NOT IN (\'pg_catalog\', \'information_schema\') ORDER BY (CASE WHEN C.reltoastrelid <> 0 THEN pg_relation_size(C.oid) + pg_relation_size(C.reltoastrelid) ELSE pg_relation_size(C.oid) END) DESC;'
\set toastsize 'SELECT oid::regclass, reltoastrelid::regclass, pg_size_pretty(pg_relation_size(reltoastrelid)) AS \"size\" FROM pg_class WHERE relkind = \'r\' AND reltoastrelid <> 0 ORDER BY pg_relation_size(reltoastrelid) DESC;'
\set deletedb 'DROP SCHEMA public CASCADE; CREATE SCHEMA public; GRANT ALL ON SCHEMA public TO postgres; GRANT ALL ON SCHEMA public TO public'
\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;'
\set queries 'SELECT current_timestamp - query_start AS runtime, pid, datname, usename, query FROM pg_stat_activity WHERE query != \'<IDLE>\' ORDER BY 1 DESC; '
\set kill_old_trans 'SELECT COUNT(pg_terminate_backend(pid)) AS nb_killed_proc FROM pg_stat_activity WHERE query = \'<IDLE> IN transaction\' AND current_timestamp - query_start > \'5 min\'';
\set drop_all_tables 'do $$ declare current_db text; begin SELECT current_database() INTO current_db; if current_db != \'postgres\' then execute \'drop schema public cascade;\'; execute \'create schema public;\'; execute \'grant all on schema public to postgres;\'; execute \'grant all on schema public to public;\'; end if; end; $$;'
\set refresh_collation 'DO $$ DECLARE db_record RECORD; BEGIN FOR db_record IN SELECT datname FROM pg_database WHERE datistemplate = FALSE LOOP EXECUTE format(\'ALTER DATABASE %I REFRESH COLLATION VERSION;\', db_record.datname); END LOOP; END $$;'

\set sp 'SHOW search_path;'
\set clear '\\! clear;'
\set ll '\\! ls -lrt;'