psqlrc

-- # http://serverfault.com/questions/34741/postgres-equivalent-to-mysqls-g
\set G '\\set QUIET 1\\x on\\g\\x off\\set QUIET 0'
-- \set G '\\set QUIET 1\\x on\\g\\x off\\set QUIET 0'

-- # now you can use :G like this
-- select * from customers limit 5 :G

-- # prevent noisy loading of psqlrc file
\set QUIET yes

-- # http://www.postgresql.org/docs/9.3/static/app-psql.html#APP-PSQL-PROMPTING
\set PROMPT1 '%[%033[1m%]%M %n@%/%R%[%033[0m%]%# '
-- # PROMPT2 is printed when the prompt expects more input, like when you type
-- # SELECT * FROM<enter>. %R shows what type of input it expects.
\set PROMPT2 '[more] %R >'

-- # taken from:
-- # http://robots.thoughtbot.com/improving-the-command-line-postgres-experience
-- # https://github.com/Nitrodist/dotfiles/blob/master/.psqlrc
-- # By default, NULL displays as an empty space. Is it actually an empty
-- # string, or is it null? This makes that distinction visible.
-- # show null as '<null>' instead of empty string
\pset null '<null>'

-- # Use table format (with headers across the top) by default, but switch to
-- # expanded table format when there's a lot of data, which makes it much
-- # easier to read.
\x auto

-- # Verbose error reports.
\set VERBOSITY verbose

-- # Use a separate history file per-database.
\set HISTFILE ~/.psql_history- :DBNAME

-- # If a command is run more than once in a row, only store it once in the history.
\set HISTCONTROL ignoredups

-- # Autocomplete keywords (like SELECT) in upper-case, even if you started
-- # typing them in lower case.
\set COMP_KEYWORD_CASE upper

-- # show timing info for queries
\timing

-- # no less/more pager
\pset pager off

-- # set noisyness back to normal
\unset QUIET

-- # list the queries
\set LS_QUERIES 'SELECT query, state, query_start, * FROM pg_stat_activity WHERE pg_stat_activity.query != \'%IDLE%\'::text \:G'
-- SELECT query, state, query_start, * FROM pg_stat_activity WHERE pg_stat_activity.query != '%IDLE%'::text :G

-- # Show queries started in the last 5 min
\set LS_5MIN_QUERIES 'SELECT * FROM pg_stat_activity WHERE pg_stat_activity.query_start < now() - interval \'5 minute\' AND pg_stat_activity.query != \'%IDLE%\'::text \:G'
-- SELECT * FROM pg_stat_activity WHERE pg_stat_activity.query_start < now() - interval '5 minute' AND pg_stat_activity.query != '%IDLE%'::text :G

-- # Show queries started in the last 10 seconds
-- SELECT * FROM pg_stat_activity WHERE pg_stat_activity.query_start < now() - interval '10 second' AND pg_stat_activity.query != '%IDLE%'::text :G

-- # kill long running queries
\set KILL_5MIN_QUERIES 'SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.query_start < now() - interval \'5 minute\'::text AND pg_stat_activity.query != \'%IDLE%\'::text;'
-- # type :KILL_5MIN_QUERIES to use
-- # kill all the queries on my dev machine
\set KILL_ALL_QUERIES 'SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.query != \'%IDLE%\'::text;'
-- # type :KILL_ALL_QUERIES to use
-- SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.query != '<IDLE>';
-- SELECT * FROM pg_stat_activity WHERE pg_stat_activity.query != '<IDLE>' :G

-- https://www.postgresql.org/docs/9.6/static/monitoring-stats.html
-- https://www.postgresql.org/docs/9.6/static/dynamic-trace.html
-- https://www.postgresql.org/docs/9.6/static/disk-usage.html
-- https://www.postgresql.org/docs/9.6/static/view-pg-locks.html
-- https://www.postgresql.org/docs/9.6/static/storage-toast.html

-- # To list queries
-- SELECT * into queries FROM pg_stat_activity;
-- SELECT * FROM queries;

-- # To kill the query
-- SELECT pg_cancel_backend(pid);

-- # To kill the connection
-- SELECT pg_terminate_backend(pid);

-- # To show db-wide stats
-- SELECT * FROM pg_stat_database;

-- # To show background writer's db activity
-- select * from pg_stat_bgwriter;

-- # To show table stats for current db
-- select * from pg_stat_all_tables;

-- # user functions: pg_stat_user_functions
-- select * from pg_stat_user_functions;

-- # To show index stats for current db
-- select * from pg_stat_all_indexes;

-- # locks: pg_locks
-- select * from pg_locks;