.psqlrc

\set QUIET ON

\pset null 'NULL'

\x auto

\set COMP_KEYWORD_CASE upper

\timing

\set hostname 'SELECT pg_read_file(\'/etc/hostname\') as hostname;'
\set dbsize 'SELECT datname, pg_size_pretty(pg_database_size(datname)) db_size FROM pg_database ORDER BY db_size;'
\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 long_running_queries 'SELECT pid, user, pg_stat_activity.query_start, now() - pg_stat_activity.query_start AS query_time, query, state, wait_event_type, wait_event FROM pg_stat_activity WHERE (now() - pg_stat_activity.query_start) > interval \'5 minutes\' AND state != \'idle\';'

\echo '-- Helpful queries'
\echo '\t :hostname             -- Get server hostname'
\echo '\t :dbsize               -- Show size of all dbs'
\echo '\t :locks                -- Find locks'
\echo '\t :long_running_queries -- Find queries running > 5min'