PostgreSQL命令行利器psql：从连接到元命令的完整实战指南

不少朋友接触 PostgreSQL第一反应都是装个 pgAdmin 或者 DataGrip然后鼠标点点点。但真到了服务器上排查问题、写一次性脚本、或者处理几百万行数据导入导出的时候你会发现所有图形界面都使不上劲——身边只剩一个黑乎乎的终端窗口。这时候psql 就是你绕不开的看家工具。作为 PostgreSQL 官方自带的命令行客户端psql 不是那种“能跑就行”的附属品。它内置了一套完整的元命令体系、脚本变量机制和基于 Readline 的快捷键操作用熟了之后大部分日常开发、运维、数据核对工作都可以在终端里完成效率反而比切窗口点鼠标高得多。这篇文章不打算讲那些晦涩的数据库原理就纯粹聊聊 psql 的常用操作、元命令、快捷键以及我在真实项目里用出来的习惯和踩过的坑。1. 拿到终端第一步连接数据库的正确姿势与常见翻车点1.1 最基础的连接命令与参数拆解psql 的连接语法说简单也简单说复杂也复杂核心就一行psql -h 192.168.1.100 -p 5432 -U postgres -d mydb这里每个参数对应一个意思-h数据库主机地址本地连可以省略或者填localhost。平时开发连远程库这里填服务器 IP 或域名。-p端口PostgreSQL 默认是 5432。如果你的实例改了端口这里必须显式指定。-U用户名默认跟当前系统用户名一致。很多人第一次连不上就是卡在这——本机用户名是lisi数据库里只有postgres用户不指定-U必然报错。-d数据库名默认也会跟你用户名走。不指定的话连的是和用户同名的库如果那个库不存在你会看到psql: error: connection to server on socket /var/run/postgresql/.s.PGSQL.5432 failed: FATAL: database lisi does not exist。我建议把完整参数写成习惯不要依赖默认值。尤其是-h不写时psql 会优先走 Unix 套接字而不是 TCP有些环境里套接字的认证方式跟 TCP 完全不同最常见的就是 peer 认证同样一套密码走 TCP 能进走套接字直接拒绝。连接成功后终端会变成这样psql (16.3) Type help for help. mydb#提示符最末尾的符号有讲究表示当前不在事务块里#表示当前是超级用户。如果你看到的是mydb-说明你已经在一个未提交的事务里了这时候执行任何语句都不会真正生效直到 COMMIT 或 ROLLBACK。1.2 连接串——用一个字符串搞定所有参数除了散装参数psql 还支持直接传连接串URI 格式这在写脚本或者配置 CI/CD 的时候特别好用psql postgresql://postgres:yourpassword192.168.1.100:5432/mydb?sslmoderequire连接串的好处是把主机、端口、用户、密码、SSL 模式全部打包在一个字符串里环境变量DATABASE_URL可以直接喂给它。很多应用框架比如 Django、Rails、Spring Boot里的配置就是这个格式你复制出来就能直接连不用再手工拆字段。1.3 密码处理别再往命令行里裸写密码了新手最喜欢干的事是psql -h xx -U postgres -p 密码实际上psql压根没有-p密码参数——-p是端口。密码要么交互式输入要么用环境变量PGPASSWORD要么写到.pgpass文件里。交互式输入最安全但麻烦没法用在脚本里环境变量是脚本里最常见的做法export PGPASSWORDyourpassword psql -h localhost -U postgres -d mydb但环境变量有两个坑一是ps aux能看到当前进程的环境变量吗看不到但是/proc/pid/environ里能读出来所以多用户服务器上还是要谨慎二是如果密码里有特殊字符必须用单引号包好否则 shell 会做变量展开或分词。我个人推荐用.pgpass文件这是 PostgreSQL 官方的密码文件机制。在 Linux/macOS 下放在用户主目录Windows 下放在%APPDATA%\postgresql\内容格式是hostname:port:database:username:password比如192.168.1.100:5432:postgres:postgres:My!Passw0rd文件权限必须改成 600chmod 600 ~/.pgpass设置好之后连接时会自动读取不再提示输密码。注意.pgpass里database也可以写*表示匹配所有库字段也可以用*通配但密码里的冒号没法转义所以密码里最好别用冒号。1.4 连接不上的排查思路连不上数据库时错误信息本身就是最好的线索。我列几个高频报错和常见原因报错关键字真实原因Connection refused端口没监听、防火墙挡了、PostgreSQL 没启动Password authentication failed密码错误或者 pg_hba.conf 里认证方式不是 md5/scramPeer authentication failed本地套接字连接用了系统用户身份验证你必须有同名系统用户No pg_hba.conf entry for host...这个 IP 网段没被 pg_hba.conf 放行database xx does not exist库名写错了或者没指定 -d 用了默认值远程连接遇到问题八成是 pg_hba.conf 和监听地址的问题。确认一下listen_addresses是不是*再确认pg_hba.conf里有没有对应网段的host记录。本地套接字连不上大部分是用户身份不匹配。2. psql 里跑查询格式化输出、执行计划与提示符配置连接进去了下面就是日常干活。psql 不只是执行 SQL它自带了一整套展示层优化用好了比客户端工具还顺手。2.1 默认输出的那些坑对齐、截断和 NULL直接执行SELECT * FROM users;如果列很宽psql 默认输出会以终端宽度为界截断中间显示...之类的省略内容。数据一长看起来就像被狗啃了。有两种办法解决一是切到扩展显示模式用\x开关再执行查询就会变成一行一个字段的纵向输出。字段多、行宽超屏的场景下这个模式非常舒服。mydb# \x Expanded display is on. mydb# SELECT * FROM users WHERE id 1; -[ RECORD 1 ]--- id | 1 username | alice email | aliceexample.com created_at | 2025-06-01 10:23:45二是用\pset调整显示参数\pset format wrapped -- 自动换行而不是截断 \pset null (NULL) -- 让 NULL 值显示得更明显 \pset border 2 -- 给表格加完整边框线\pset null这个我特别推荐默认 NULL 在 psql 里显示为空字符串跟空字符串混在一起你根本分不清生产环境核对数据时极容易误判。2.2 行数、耗时和影响行数的实时反馈想知道查询跑了多久最直接的办法是打开计时器mydb# \timing on Timing is on. mydb# SELECT count(*) FROM orders; count ------- 152340 (1 row) Time: 412.345 ms注意Time包含的是从 psql 发送 SQL 到拿回结果的整个链路耗时不只是数据库执行时间。做性能对比时建议多跑几次取稳定值或者直接在 SQL 层面用EXPLAIN ANALYZE看真实执行耗时。如果是从脚本里跑批量 DML想知道影响了多少行psql 会在语句结束后打一行INSERT 0 100、UPDATE 5之类的结果标签这就是客户端行数反馈没法关掉但对核对批量操作非常有用。2.3 执行计划、watch 和反斜杠 g 的连招EXPLAIN ANALYZE是优化慢查询的核心武器在 psql 里配合\g命令可以反复执行查看EXPLAIN ANALYZE SELECT * FROM orders WHERE status pending ORDER BY created_at DESC LIMIT 100;写完这句之后直接按\g回车就可以重复执行上一次的查询。对排查“为什么相同的 SQL 一会快一会慢”这类问题时这个组合极其顺手。还有一种实时监控场景开店大促时盯着某个计数。psql 提供了一个\watch元命令可以每隔几秒自动重跑上一条 SQLmydb# SELECT count(*) FROM orders WHERE status pending; count ------- 152340 (1 row) mydb# \watch 5每 5 秒自动刷新一次结果直到按CtrlC打断。这个用途在监控导入进度、核对同步延迟时非常实用不用自己写循环脚本。2.4 自定义提示符让每个连接都知道自己在哪多环境切换开发、测试、生产时最怕的就是在错误的库上跑了 DELETE。除了肉眼盯着提示符看你可以把环境名称直接烙在 psql 提示符里。用\set设置PROMPT1mydb# \set PROMPT1 %n%M:%%x%# alice192.168.1.100:5432这里%n是用户名%M是主机名带端口%是数据库名%x在事务内显示*%#显示#表示超级用户。我实际使用时还会把生产环境的 PROMPT1 专门改成带红色或特殊前缀的样式把误操作风险压到最低。这个\set配置可以写进~/.psqlrc文件每次启动 psql 自动加载。文件位置在 Linux 是~/.psqlrcWindows 是%APPDATA%\postgresql\psqlrc.conf。3. 元命令反斜杠后面那半个世界psql 里所有以反斜杠开头的命令都叫元命令meta-command。它们不是 SQL而是客户端提供的便捷操作。这部分是 psql 比大多数命令行工具强大的核心原因。3.1 最常用的一批元命令速查我按使用频率排个序把日常最高频的列出来元命令作用\l列出所有数据库\c dbname切换到另一个数据库相当于断开重连\dt列出当前 schema 下的所有表\dt列出表并显示大小、描述等额外信息\d tablename查看表结构字段、类型、约束、索引\d tablename查看表结构并显示存储参数、表注释\du列出所有角色/用户及其权限\dn列出所有 schema\df列出函数\dv列出视图\di列出索引\db列出表空间\conninfo查看当前连接信息\q退出 psql\?查看元命令帮助\h查看 SQL 命令帮助比如\h SELECT\timing开关执行计时\x开关扩展显示\o 文件名把查询结果输出到文件\i 文件名执行 SQL 脚本文件\d家族是整个元命令体系里信息量最大的一个。\d tablename出来的结构信息非常全比如mydb# \d users Table public.users Column | Type | Collation | Nullable | Default -------------------------------------------------------------------------------------------------- id | integer | | not null | nextval(users_id_seq::regclass) username | character varying(64) | | not null | email | character varying(255) | | | created_at | timestamp without time zone | | not null | now() Indexes: users_pkey PRIMARY KEY, btree (id) users_username_key UNIQUE CONSTRAINT, btree (username)字段、默认值、约束、索引一次全看清楚不用再去翻 information_schema 写查询。3.2 信息查询与权限诊断排查权限问题是我工作中最常遇到的一个应用场景。比如用户反馈“连上了但查不了某张表”用\dp tablename可以看这张表的权限授予情况mydb# \dp orders Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies ---------------------------------------------------------------------------------- public | orders | table | postgresarwdDxt/postgres | | | | | app_userarwd/postgres | |看到app_userarwd表示app_user有 INSERT、SELECT、UPDATE、DELETE 权限但d之后缺了DTRUNCATE和xREFERENCES等。权限符号含义不熟时直接看\h GRANT帮助或者用SELECT * FROM information_schema.table_privileges WHERE table_nameorders查明细。3.3 用 \g 和 \o 组合出报表\o的输出重定向很好用。比如你想把查询结果导出成 CSV 用于 Excel 分析mydb# \o /tmp/orders_report.csv mydb# \pset format csv Output format is csv. mydb# SELECT id, status, amount FROM orders WHERE created_at 2025-01-01; mydb# \o最后那个不带参数的\o是把输出重定向回终端。这个过程中终端可能看不到任何查询结果但orders_report.csv文件里已经落好了数据。这里有个小坑\pset format csv输出的 CSV 没有表头如果你需要表头得在 SQL 里用 UNION 手工拼一行字段名或者用COPY ... TO STDOUT WITH CSV HEADER前提是有对应权限。数据量不大时我更喜欢后者COPY (SELECT id, status, amount FROM orders WHERE created_at 2025-01-01) TO STDOUT WITH CSV HEADER;这条命令直接在终端输出带表头的 CSV再用 shell 重定向写到文件即可。3.4 \i 执行脚本与变量传递把一段复杂 SQL 写成文件然后在 psql 里用\i执行是实操中管理长语句的常规做法-- fix_orders.sql BEGIN; UPDATE orders SET status cancelled WHERE created_at 2020-01-01 AND status pending; DELETE FROM order_logs WHERE created_at 2020-01-01; COMMIT;执行mydb# \i /path/to/fix_orders.sql脚本里也可以用 psql 变量机制传参mydb# \set since 2020-01-01 mydb# SELECT count(*) FROM orders WHERE created_at :since;注意冒号加引号的写法:sincepsql 会把它替换成带引号的字符串字面量安全且规范。如果你直接写:since且变量值里有特殊字符很可能会造成 SQL 注入或语法错误。脚本里传参这个能力在对多环境执行同一套变更脚本时非常有用。4. 命令行编辑与快捷键手不离键盘的操作流psql 底层用的是 GNU Readline 库这也意味着它在终端里支持一整套类 Emacs 的快捷键。这部分用好了你的操作节奏能明显上一个台阶不用频繁地在鼠标和键盘之间切换。4.1 Readline 快捷键全表快捷键作用CtrlA/CtrlE光标移到行首 / 行尾CtrlU删除光标到行首的所有字符CtrlK删除光标到行尾的所有字符CtrlW删除光标前的一个单词CtrlY粘贴被删除的内容yankCtrlL清屏CtrlZ挂起 psql回到 shell用fg恢复Tab自动补全表名、列名、函数名、元命令名上/下方向键浏览命令历史CtrlR反向搜索历史命令CtrlG退出搜索模式AltB/AltF光标按单词前移/后移CtrlR这个反向搜索是我最依赖的快捷键。当你需要找出十分钟前跑过的一条长 SQL 时按下CtrlR输入几个关键字母psql 会实时匹配历史命令按CtrlR继续往更早的历史翻找到后直接回车执行。这个操作比用方向键一条条翻高效得多。还有个容易被忽略的技巧方向键上下浏览历史时如果当前行已经输入了部分内容Readline 会做前缀匹配——只浏览以当前输入开头的历史命令。比如你输入了SELECT按上键只会翻到之前以 SELECT 开头的命令。4.2 多行输入与 \e 编辑器模式写复杂 SQL 时在终端一行行续写很痛苦。psql 支持直接在提示符下多行输入SQL 没写完时会变成次级提示符默认是mydb-直到分号结束才执行。如果这段 SQL 实在太长我一般直接用\e打开外部编辑器默认是 vi可以\setenv EDITOR vim改成自己的编辑器编辑完保存退出后psql 自动把内容发送给数据库执行。mydb# \e这会拉起$EDITOR在里面写好 SQL不要加分号也行psql 会自动处理保存退出后立刻执行。对于超过十行的复杂查询这比在终端里一点点敲要舒服得多。4.3 命令历史在哪存、怎么复用psql 的历史记录在 Linux/macOS 下存在~/.psql_historyWindows 下在%APPDATA%\postgresql\psql_history。这个文件是纯文本可以grep也可以直接编辑。跨环境切换时我习惯保留自己的历史文件而不同步到生产服务器。生产环境默认的历史文件可能会把线上库名、表名留痕如果有安全要求可以在~/.psqlrc里设置\set HISTFILE /dev/null或者干脆\set HISTSIZE 0关闭历史记录。4.4 一个提升效率的小习惯把同一条 SQL 变体保存在 shell 历史里这个习惯可能有点反直觉在 psql 里按CtrlC打断一条已经编辑但不想立即执行的 SQLpsql 会把当前输入留在历史里但不会执行。如果你的 SQL 还没写完却要去查另一条数据先别急着删掉自己敲了一半的内容按CtrlC回到干净提示符查完数据后按上方向键刚才敲了一半的语句会重新出现继续补完即可。5. 事务控制与脚本化从交互到自动化的一步之遥5.1 交互模式下的自动提交与手动事务psql 默认对每条独立 SQL 是自动提交的——只要语句执行完没有报错事务立刻生效。这意味着你执行一条DELETE FROM orders WHERE ...如果没有先BEGIN删了就是删了不能反悔。要养成事务习惯有两种方式一是显式BEGINmydb# BEGIN; mydb# DELETE FROM orders WHERE id 12345; mydb# SELECT count(*) FROM orders WHERE id 12345; mydb# ROLLBACK;在事务块里提示符会从变成比如mydb*注意那个*提醒你在事务中。执行完一句 DELETE 后检查一下影响行数确认无误再COMMIT不对就ROLLBACK。这是生产环境操作任何数据的保命操作。二是在启动 psql 时用-1参数强制把整个会话包成一个大事务psql -1 -f fix_orders.sql这样脚本里任何一步失败整个事务全部回滚不会出现执行了一半留下一堆脏数据的尴尬。5.2 脚本化执行的几个关键开关写自动化脚本用 psql 时有几个选项非常重要平时交互模式用不到但脚本里少一个都可能出大事-v ON_ERROR_STOP1遇到第一条 SQL 错误就停止执行并返回非零退出码。不带这个参数脚本里的 SQL 即使报错也会继续往下跑最后你拿到一个“看似成功”的退出码实际上中间漏掉了若干步骤。--echo-all或者-a把每一条执行的 SQL 一并打印出来方便审计。--no-psqlrc启动时不加载~/.psqlrc避免个人配置干扰脚本行为。--single-transaction/-1如上所述整个脚本包成一个事务。一个典型的脚本执行命令长这样psql postgresql://postgres:passlocalhost:5432/mydb -v ON_ERROR_STOP1 -f migrate.sql在 CI/CD 流水线里这种写法能保证迁移脚本的确定性。5.3 用 \if 元命令做条件判断psql 从 9.6 开始支持在脚本里用\if、\elif、\else做条件分支这在处理“某列不存在才添加”这类幂等迁移时非常实用\if :{?column_exists} SELECT 1; \else ALTER TABLE users ADD COLUMN phone varchar(20); \endif需要先在前面定义变量\set column_exists (SELECT 1 FROM information_schema.columns WHERE table_nameusers AND column_namephone)要用 psql 变量装一个子查询的结果语法是\set var (SELECT ...)。这个能力让脚本既能重复执行又不会因对象已存在而报错配合迁移工具非常好用。5.4 管道配合psql 不只是查数据psql 标准输出是普通文本所以可以直接接到 shell 工具链里。比如把最大的一张表捞出来psql -h localhost -U postgres -d mydb -Atc SELECT relname FROM pg_class WHERE relkindr ORDER BY reltuples DESC LIMIT 1-A去掉对齐空格-t只要裸数据-c执行单条 SQL 后退出。这几个参数组合在生产脚本里出场率非常高。数据再经过管道交给排序、去重、统计等命令psql 就变成了一个灵活的数据接口。6. 日常实战经验与高频翻车现场总结6.1 乱码问题显示中文变问号psql 里中文显示不正常先分清楚两种情况存储没问题只是终端显示乱码——检查客户端字符集\encoding看看当前编码改成 UTF8\encoding UTF8。查询结果里直接就是乱码——说明数据入库时编码就错了或者连接参数里没有指定客户端编码。环境变量层面还有个PGCLIENTENCODING必要时可以export PGCLIENTENCODINGUTF8强制客户端编码。6.2 管道 SIGPIPE 导致的“神秘退出”用 psql 配合管道命令时如果管道对端提前退出比如psql ... | head -5psql 会收到 SIGPIPE 直接终止报错可能是broken pipe或者直接无输出。这在非交互脚本里会造成困扰。解决办法是避免 psql 直接接 head 这类提前关闭管道的命令改用 SQL 层面的LIMIT控制返回行数或者把结果先落到临时文件再分段处理。6.3 长查询跑到一半想取消交互模式下执行了一条跑很久的查询想取消在 psql 里直接按CtrlCpsql 会向服务端发送取消当前查询的信号。注意这个操作不一定会让服务端立即停止如果已经在做 CPU 密集的聚合或排序可能得等当前计算结束才能安全取消。取消后事务不会自动回滚还在当前事务块里需要自己判断是 COMMIT 还是 ROLLBACK。6.4 历史命令里误存了密码早年我在~/.psql_history里敲过CREATE USER xx PASSWORD abc123这类语句或者不小心在命令行里把密码写进 SQL然后整段都进了历史文件。如果你的终端环境不是私有的一定注意检查grep -i password ~/.psql_history发现敏感信息直接编辑这个文件删除对应行。更保险的做法是给.psql_history也设个 600 权限。6.5 本地与远程库的 \d 输出差异同一个\d orders在本地和远程看到的输出可能不一样最常见的原因是搜索路径search_path不同。psql 默认展示当前搜索路径里第一个 schema 下的对象如果你的表在configschema 下而搜索路径里没包含它\dt根本看不到。排查思路是先跑SHOW search_path;确认当前的 schema 搜索顺序。6.6 元命令与 SQL 语句的混写边界元命令不是 SQL因此不能和 SQL 混在同一行通过\g传递名字空间做嵌套的除外。一个高频错误是写SELECT * FROM users \x;这不会生效反而会报语法错误。正确做法是先把\x打开再单独执行 SQL。记住一个原则要么先元命令后 SQL要么每条语句独立成行。7. 把 psql 调到最顺手一份个人配置文件参考最后分享一个我自己用了很久的~/.psqlrc配置可以直接抄走根据自己的习惯改\set PROMPT1 %n%M:%%x%# \set PROMPT2 %M %p \timing on \x auto \pset null (NULL) \pset border 2 \pset pager always \encoding UTF8 \set HISTSIZE 5000 \set HISTCONTROL ignoredups逐个说明一下\x auto当查询结果列数太多导致超宽时自动切扩展显示不会打断宽表浏览。\pset pager always结果超过一屏自动用 less 分页避免刷屏。\set HISTCONTROL ignoredups连续的重复命令只记一次历史文件不会膨胀成垃圾场。\set HISTSIZE 5000历史条数给足免得想翻一个半月前的命令时找不到。如果你经常在 psql 里写复杂 SQL可以把默认编辑器改成 vim 或 nano\setenv EDITOR vim然后\e就直接进 vim 了写长查询的体验会好很多。我在实际使用中还有一个不太起眼但很重要的心得凡是涉及生产数据的操作不管多熟练都先把\timing on打开先跑一条SELECT count(*)确认连接和数据状态再进入事务块做变更。psql 是个非常强大的工具但它的强大建立在“你知道自己在干什么”的前提下。把上面这些命令和快捷键内化成肌肉记忆之后你会发现命令行操作数据库不仅不 low反而是效率最高、最接近数据库本质的工作方式。