postgresql .psqlrc 优化版本

-- .psqlrc设置

-- ==========================================
-- 基础设置
-- ==========================================
\set QUIET on
\pset pager off
\pset border 2
\pset format aligned
\set HISTSIZE 2000
\set SHOW_LINENUMBER on
\set COMP_KEYWORD_CASE preserve-upper

-- ==========================================
-- 行为控制
-- ==========================================
\timing on
\set ON_ERROR_STOP on
\pset null 'NULL'
\x auto
\pset linestyle unicode
\pset unicode_border_linestyle single
\pset unicode_column_linestyle single
\pset unicode_header_linestyle single


-- 分页器设置
\pset pager always
\setenv PAGER 'less -iMnXSx4R'


-- ==========================================
-- 提示符设置 (生产环境红色高亮)
-- %R = 时间, %m = 主机, %n = 用户, %/ = 库名
-- ==========================================

\set PROMPT1 '[%033[1;31m生产环境%033[0m] %`date +%H:%M:%S` [host: %m] [username: %n] [db: %/]\n# '
\set PROMPT2 '> '
\set PROMPT3 '= '


-- ==========================================
-- 性能与安全
-- ==========================================
\set VERBOSITY verbose
\set HISTFILE ~/.psql_history-:DBNAME
\set STATEMENT_TIMEOUT 30000
\setenv PGOPTIONS '-c statement_timeout=30s -c lock_timeout=10s'




-- ==========================================
-- 显示优化
-- ==========================================
-- 设置数字千位分隔符
\pset numericlocale on

-- 设置 INTERVAL 显示格式为详细模式
SET intervalstyle = 'postgres_verbose';

-- 设置时区（可选）
SET timezone = 'Asia/Shanghai';


-- 大结果集处理优化,一次获取1000行，避免内存溢出
\set FETCH_COUNT 1000



-- ==========================================
-- 自定义函数（可选） ，在psql中的使用方法 :dboverview
-- ==========================================


-- 【巡检】数据库概览（连接数、状态、版本）
\set dboverview 'SELECT version(); SHOW max_connections; SELECT count(*), state FROM pg_stat_activity GROUP BY state;'

-- 【锁排查】增强版锁等待（含阻塞详情）
\set locks 'SELECT blocked.pid AS blocked_pid, blocked.query AS blocked_query, blocking.pid AS blocking_pid, blocking.query AS blocking_query, age(now(), blocked.query_start) AS blocked_age FROM pg_stat_activity blocked JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid)) ORDER BY blocked_age DESC;'

-- 【空间】表大小 Top 20（快速定位空间异常）
\set bigtab 'SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||''.''||tablename)) AS size, pg_size_pretty(pg_relation_size(schemaname||''.''||tablename)) AS table_size FROM pg_tables WHERE schemaname NOT IN (''pg_catalog'', ''information_schema'') ORDER BY pg_total_relation_size(schemaname||''.''||tablename) DESC LIMIT 20;'

-- 【性能】长事务与慢查询（默认 > 5 分钟）
\set slowtx 'SELECT pid, usename, datname, state, age(clock_timestamp(), query_start) AS query_age, query FROM pg_stat_activity WHERE (state != ''idle'') AND (clock_timestamp() - query_start) > interval ''5 minutes'' ORDER BY query_start;'

-- 索引使用率（找出无用索引）
\set idxusage 'SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes ORDER BY idx_scan ASC LIMIT 20;'

-- 表膨胀预估（autovacuum 监控）
\set bloat 'SELECT schemaname, tablename, ROUND(100 * (n_dead_tup::numeric / (n_live_tup + n_dead_tup)),2) AS dead_pct, n_dead_tup, last_autovacuum FROM pg_stat_user_tables WHERE (n_live_tup + n_dead_tup) > 10000 ORDER BY dead_pct DESC LIMIT 15;'

-- 查看当前活跃的配置变更
\set setting 'SELECT name, setting, unit, context FROM pg_settings WHERE source != ''default'' ORDER BY context, name;'

-- 按 IP 统计连接数（防连接风暴）
\set connip 'SELECT client_addr, count(*) FROM pg_stat_activity WHERE client_addr IS NOT NULL GROUP BY client_addr ORDER BY count DESC;'



-- ==========================================
-- 启动欢迎信息
-- ==========================================
\echo '========================================'
\echo '  PSQL 已就绪 (ON_ERROR_STOP enabled)'
\echo '========================================'
\conninfo