.psqlrc使用技巧

mahan

核心作用

.psqlrc 文件（也称为 psql 的启动文件）的主要作用是为用户定制化和自动化 psql 命令行工具的环境和行为。

简单来说，每次你启动 psql 时，它在连接到数据库之前，都会自动在你的家目录（$HOME）下寻找并执行这个文件中的命令。这就像是为你的 psql 会话进行“个性化设置”。

文件位置和名称

文件名: .psqlrc（在 Linux/macOS 系统上，以点开头的文件是隐藏文件）。
位置: 通常位于数据库用户的家目录下（~/.psqlrc）。
特定版本: 你也可以在启动 psql 时使用 PSQLRC 环境变量来指定一个不同的启动文件。
执行时机
当你直接在命令行输入 psql 并回车后，psql 会按顺序做以下事情：
读取并执行家目录下的 .psqlrc 文件中的所有命令。
连接到你在命令行指定的数据库（如果没有指定，则连接与你系统用户名同名的数据库）。
进入交互式界面，等待你输入命令。
.psqlrc 文件可以包含任何有效的 psql 元命令（以反斜杠 \ 开头的命令）或 SQL 命令。

重要注意事项

跳过执行: 如果你不希望某次会话执行 .psqlrc 文件，可以使用 -X 或 --no-psqlrc 命令行选项启动
psql -X -d mydb

安全性: 不要从不信任的来源获取 .psqlrc 文件，因为它可能包含恶意的 SQL 命令。
避免“危险”操作: 通常不建议在 .psqlrc 中执行会修改数据的 SQL 命令（如 INSERT, UPDATE, DELETE），因为它会在每次 psql 启动时都执行。
常见用途和配置示例

a) 优化显示

优化命令行交互体验，设置启动信息输出、欢迎提示、调整数据展示格式以提升可读性，以及扩展历史记录功能以便追溯操作。此外，自动显示连接标识、数据库版本和当前时间提供了清晰的上下文信息，而启用计时功能则有助于监控查询性能，为数据库管理和调试工作提供全面支持。

-- 初始静默模式：抑制启动信息输出

\set QUIET ON

--设置登录欢迎语

\echo 'Welcome to PostgreSQL!!!'

-- 设置表格的边框样式（可选值有 ascii, old-style, unicode）

\pset border 2

-- 设置 NULL 值的显示方式，默认为空字符串，容易混淆

\pset null '[NULL]'

-- 开启扩展模式，让输出更美观（类似于 MySQL 的 \G）

\x auto

-- SQL执行历史

\set HISTFILE ~/.psql_history-:DBNAME

-- 历史命令记录数量提升至2000条

\set HISTSIZE 2000

-- 获取连接进程信息（使用变量）

SELECT pg_backend_pid() AS pid \gset

\echo Your PostgreSQL18 connection id is :pid, connected at `date`

-- SQL关键字自动补全转为大写

\set COMP_KEYWORD_CASE upper

-- 每次连接后自动查看数据库版本和当前时间

SELECT version() AS "Version", now() AS "Current Time";

-- 关闭静默模式，恢复正常输出

\set QUIET OFF

-- 启用计时功能

\timing


b) 自定义提示符，避免误操作

在实际工作中，数据库管理员经常需要在多个环境间切换执行操作。如果没有明确的环境标识，极有可能在错误的环境中执行诸如数据删除、结构变更等高危操作，从而造成不可逆的数据丢失或系统故障，给企业带来严重的业务影响和经济损失。

通过定制化提示符，可以实现以下安全增强措施：

环境明确标识：在提示符中醒目显示当前所属环境（如 PROD、TEST、DEV 等）
色彩警示系统：为生产环境配置红色等警示色，测试和开发环境使用绿色或蓝色
提示符1： psql 请求新命令时发出的正常提示。

提示符2：当命令输入期间需要更多输入时，例如因为命令未以分号终止或引号未关闭，因此会发出提示符2。

提示符3：当您运行SQL命令并且您需要在终端上键入行值时，会发出提示符3。

%date +%H:%M:%S 表示显示时间

%n 表示连接数据库的用户名

%/ 数据库名称

%p当前连接的进程ID

%x事物状态

%# 如果连接用户是超级用户，则提示符为#，否则为>

%> 显示端口号

%R 在PROMPT1中，正常是=，但如果会话位于一个条件块的一个非活动分支中则是@，如果会话处于单行模式中则是^，如果会话从数据库断开连接（\connect失败时会发生这种情况）则是!。在PROMPT 2 中，会根据psql中的输入，%R会被一个相应的字符替换：如果命令还没有被终止是-，如果有一个未完的/* ... */注释则是*，如果有一个未完的被引用字符串则是一个单引号，如果有一个未完的被引用标识符则是一个双引号，如果有一个未完的美元引用字符串则是一个美元符号，如果有一个还没有被配对的左圆括号则是(。



--彩色psql客户端

\set PROMPT1 '%[%033[1;31m%]%[%033[32m%]%`hostname`:%[%033[36m%]//%n%[%033[34m%]@%[%033[36m%]%M:%>%[%033[33m%]/%/%[%033[K%]%[%033[0m%]%[%033[1;33m%](%p)%[%033[0m%]%# '

\set PROMPT2 '%[%033[0;31;31m%]%/%R%#%[%033[0m%] '

\set PROMPT3 '>> '

格式如下：

hostname://user@[ip]:port/db(PID)#

PG://dbadmin@192.168.192.18:5432/mydb(1722)#



c) 定义常用快捷键（别名）

用户可以通过定义别名（快捷键）来简化和优化日常工作流程。这一功能允许将冗长或复杂的查询语句封装为简洁易记的短命令，甚至能够执行 shell 命令，显著提升数据库操作效率。

-- 显示当前搜索路径

\set sp 'SHOW search_path;'

-- 数据库运行时长

\set uptime 'select now() - pg_postmaster_start_time() AS uptime;'

-- 查看活跃会话

\set sessions 'select datname,pid,usesysid,usename,application_name,client_addr,client_port,query_start,state_change,query_id,query from pg_stat_activity where state<>\'idle\';'

-- 查看锁情况

\set locks 'SELECT pid, state, usename, query, query_start from pg_stat_activity where pid in ( select pid from pg_locks l join pg_class t on l.relation = t.oid and t.relkind = \'r\' );'

--只显示当前用户拥有的数据库

\set l 'SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",CASE d.datlocprovider WHEN ''b'' THEN ''builtin'' WHEN ''c'' THEN ''libc'' WHEN ''i'' THEN ''icu'' END AS "Locale Provider",d.datcollate as "Collate", d.datctype as "Ctype", d.datlocale as "Locale", d.daticurules as "ICU Rules", CASE WHEN pg_catalog.array_length(d.datacl, 1) = 0 THEN ''(none)'' ELSE pg_catalog.array_to_string(d.datacl, E''\n'') END AS "Access privileges", CASE WHEN pg_catalog.has_database_privilege(d.datname, ''CONNECT'') THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname)) ELSE ''No Access'' END as "Size", t.spcname as "Tablespace", pg_catalog.shobj_description(d.oid, ''pg_database'') as "Description" FROM pg_catalog.pg_database d JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid WHERE pg_catalog.pg_get_userbyid(d.datdba) = current_user ORDER BY 1;'





-- 用 \! 调用 shell 查看操作系统、内存、cpu、磁盘

\set osinfo '\\! uname -a'

\set cpu '\\! lscpu'

\set mem '\\! free -h'

\set disk '\\! df -hT --total'