Created
February 24, 2016 15:22
-
-
Save gregorg/d079a56022f93c042231 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
----------------------------------------- | |
-- psqlrc file to set psql preferences -- | |
----------------------------------------- | |
\set QUIET ON | |
\set PROMPT1 '%[%033[1m%]%n@%/%[%033[0m%]% # ' | |
\set PAGER OFF | |
-- \set HISTFILE ~/.psql_history- :HOST - : DBNAME -- Remove space between : and DBNAME | |
-- \set HISTFILE ~/.psql_history-:DBNAME | |
\set HISTSIZE 2000 | |
\set ECHO_HIDDEN ON | |
\timing on | |
\encoding unicode | |
\pset linestyle unicode | |
\pset null '¤' | |
\pset border 2 | |
\x auto | |
\set VERBOSITY verbose | |
\set HISTCONTROL ignoredups | |
\set COMP_KEYWORD_CASE upper | |
\unset QUIET | |
\echo '\nCurrent Host Server Date Time : '`date` '\n' | |
\echo 'Administrative queries:\n' | |
\echo '\t\t\t:settings\t-- Server Settings' | |
\echo '\t\t\t:conninfo\t-- Server connections' | |
\echo '\t\t\t:activity\t-- Server activity' | |
\echo '\t\t\t:locks\t\t-- Lock info' | |
\echo '\t\t\t:waits\t\t-- Waiting queires' | |
\echo '\t\t\t:dbsize\t\t-- Database Size' | |
\echo '\t\t\t:tablesize\t-- Tables Size' | |
\echo '\t\t\t:uselesscol\t-- Useless columns' | |
\echo '\t\t\t:uptime\t\t-- Server uptime' | |
\echo '\t\t\t:menu\t\t-- Help Menu' | |
\echo '\t\t\t\\h\t\t-- Help with SQL commands' | |
\echo '\t\t\t\\?\t\t-- Help with psql commands\n' | |
\echo 'Replication queries:\n' | |
\echo '\t\t\t:rep_delay\t-- replication delay (from slave)' | |
\echo '\t\t\t:rep_mon\t-- replication monitoring (from master)' | |
\echo '\t\t\t:rep_is_slave\t-- check if is a slave' | |
\echo '\t\t\t:rep_is_paused\t-- check if replication is paused' | |
\echo '\t\t\t:rep_pause\t-- pause replication' | |
\echo '\t\t\t:rep_resume\t-- resume replication' | |
\echo '\n' | |
\echo 'Development queries:\n' | |
\echo '\t\t\t:sp\t\t-- Current Search Path' | |
\echo '\t\t\t:clear\t\t-- Clear screen' | |
\echo '\t\t\t:ll\t\t-- List\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;' | |
-- Replication queries: | |
\set rep_delay 'SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN \'00:00:00\' ELSE now() - pg_last_xact_replay_timestamp() END AS replication_lag;' | |
\set rep_mon 'SELECT pid, application_name, client_addr, pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(), sent_location)) as pending_xlog, pg_size_pretty(pg_xlog_location_diff(sent_location, write_location)) as write, pg_size_pretty(pg_xlog_location_diff(write_location,flush_location)) as flush, pg_size_pretty(pg_xlog_location_diff(flush_location,replay_location)) as replay, pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(),replay_location)) as total_lag FROM pg_stat_replication;' | |
\set rep_is_slave 'SELECT pg_is_in_recovery();' | |
\set rep_is_paused 'SELECT pg_is_xlog_replay_paused();' | |
\set rep_pause 'SELECT pg_xlog_replay_pause();' | |
\set rep_resume 'SELECT pg_xlog_replay_resume();' | |
-- Development queries: | |
\set sp 'SHOW search_path;' | |
\set clear '\\! clear;' | |
\set ll '\\! ls -lrt;' | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment