PostgreSQL 命令行利器 psql：从连接到脚本化实战完整指南-育师

做了这么多年跟数据库打交道的工作，我有个习惯一直没改：桌面上不管装了多少图形化工具，一遇到正经问题，第一反应还是打开终端敲psql。不是矫情，是这玩意儿在关键时刻真顶用——服务器上排查问题、容器里看数据、写定时任务导报表，图形界面往往帮不上忙，psql 就是那个永远在线的兜底方案。

PostgreSQL 自带的这个命令行客户端，功能远比很多人以为的强。它不只是"能跑 SQL"，还包括一套相当完整的元命令体系、可控的输出格式、变量替换、脚本化执行、内嵌的 EXPLAIN 支持，每一项都能让人在排查和自动化上节省大量时间。这篇文章就是围绕 psql 做的完整梳理，适合刚接触 PostgreSQL 的新手，也适合用了很久但只拿它执行零星 SQL 的老手。看完之后，你至少能把日常 90% 的数据库操作搬进终端，而且很多场景下比图形界面更顺手。

1. 先把连接这件事彻底搞明白

1.1 psql 从哪来：安装与版本确认

PostgreSQL 的安装方式很多，官方二进制包、系统自带软件源、容器镜像都行。只要 PostgreSQL 装完整，psql 就会作为客户端工具一并出现。这里有个认知要提前摆正：psql 的版本和服务器版本不需要完全一致，但尽量保持大版本接近。比如本地 psql 16 连线上 PostgreSQL 14，绝大多数功能没问题，但部分新版本才引入的元命令或者输出选项，在旧服务器上可能会报错或者行为异常，排查起来容易让人绕弯。

确认自己的客户端版本用psql --version，连上服务器之后用SELECT version();能看到服务器版本和编译信息。如果版本差距过大，最常见的场景是系统自带源里的 PostgreSQL 太老，而线上是较新版本，这时候我一般建议直接用官方源或容器镜像，不要在低版本客户端上硬折腾。

1.2 连接参数逐个拆解

psql 最常见的用法就是一行命令连上库：

psql -h 127.0.0.1 -p 5432 -U app_user -d app_db

-h指定主机，-p指定端口，-U指定用户，-d指定数据库。这里有个特别容易被忽略的点：如果不带-d，psql 会默认尝试连接与当前系统用户名同名的数据库。比如系统用户叫 weiyi，直接敲psql，它找的是名为 weiyi 的数据库，不存在就报错。很多人第一次跑 psql 得到 "database does not exist" 的提示，往往不是参数错了，而是压根没指定库名。

连接字符串（URI）格式也很常用：

psql "postgresql://app_user@127.0.0.1:5432/app_db?sslmode=require&application_name=report_job"

这种写法最大的价值，是能在一条字符串里带上连接超时、SSL 模式、application_name 等参数，适合写进配置文件和脚本。application_name 会出现pg_stat_activity里，对于定位"这条连接是谁发起的"非常有用，生产环境强烈建议设置。

密码怎么处理？交互式输入简单，但脚本场景没法人工敲。最常用的两个方案：.pgpass文件和PGPASSWORD环境变量。

.pgpass的格式是host:port:database:user:password，放在用户主目录下，权限必须为 600，否则 psql 会直接忽略它。这个文件的价值在于让需要密码的脚本摆脱明文传参，也避免密码出现在命令行里被进程列表抓走。

注意：PGPASSWORD虽然方便，但在多用户环境里要格外小心，子进程、shell 历史和日志都可能泄漏它。能用.pgpass就用.pgpass，这是更稳妥的方式。

连接上之后，确认当前位置用\conninfo，一条命令把当前库、用户、端口、连接选项全部打出来，比自己一个个猜快得多。

1.3 交互式会话的基本操作

psql 进入交互模式后，SQL 语句以分号结尾才执行，回车换行只是继续输入。这个设计让长 SQL 可以分段书写，很多人刚接触时不习惯，老想着敲完回车就出结果。另外，psql 内部维护了一个编辑缓冲，输入到一半想改，可以用上下键，也可以随时\e打开系统编辑器，把缓冲内容带进去编辑，保存退出后自动执行。

退出会话用\q。这个命令不用分号，也别写成quit——很多人刚上手会试着敲exit或者quit，psql 不认这两个词，只认\q。

2. 查询输出：把结果变成你真正要的样子

2.1 三种主流输出格式怎么选

psql 默认的输出格式是aligned，就是常见的表格对齐样式，人眼读起来很舒服。但这个格式有个硬伤：字段宽度超过屏幕时，列数据会被截断，而且机器解析起来也不方便。需要把结果交给其他工具时，切换到 unaligned 模式：

\pset format unaligned SELECT id, name FROM users LIMIT 3;

unaligned 模式下字段默认用|分隔，处理起来不够直观，更常用的是配合 fieldsep 改成 CSV：

\pset fieldsep ',' SELECT id, name FROM users LIMIT 3;

还有一种模式是x，也就是扩展模式，这是我在日常排查里离不开的。当一行数据列特别多、或者单列内容特别长的时候，aligned 模式会变得又挤又乱，扩展模式把每一行变成一组"字段名: 值"的垂直排列，长文本不截断，读起来舒服得多：

\x SELECT * FROM orders WHERE id = 123456;

\x不带参数时是开关切换。我个人的工作习惯是：日常查询用默认对齐模式，看单行详情就开\x，看多行统计类结果再回到对齐模式。输出格式直接决定你能不能快速发现数据异常，这个习惯值得刻意培养。

2.2 落盘输出：\o 与 \copy 的区别

把查询结果保存到文件，两个思路要分清。

第一个是用\o把 psql 的输出重定向到文件：

\o /tmp/report.txt SELECT * FROM orders WHERE created_at > now() - interval '7 days'; \o

执行\o后，后续所有查询结果都写入文件，直到再次\o不带参数恢复输出到屏幕。适合写留档、出报表，但注意\o保存的是 psql 格式化后的内容，带表格线，不适合直接给程序解析。

真正给程序用、或者想把数据导到另一个库，用\copy：

\copy (SELECT * FROM orders WHERE created_at > now() - interval '7 days') TO '/tmp/orders.csv' WITH (FORMAT CSV, HEADER)

这里必须说清楚\copy和COPY的差别：COPY是服务器端执行，文件路径是数据库服务器上的路径，普通用户通常没有权限去写服务器文件系统；\copy是 psql 客户端负责读取本地文件，路径是本机的，权限也是本机用户的。日常导入导出几乎都该用\copy，这个区别我见过太多次踩坑——本地执行COPY TO '/path'，以为写到自己电脑上，结果文件出现在远端服务器，半天找不到。

再补充一个细节：\copy FROM方向是导入，如果 CSV 文件带表头，导入时用WITH (FORMAT CSV, HEADER)让服务器跳过首行；不带表头就别写 HEADER，否则第一行数据会被当成字段名。

3. 元命令：记住这些，效率直接翻倍

3.1 \d 家族：不翻文档也能摸清结构

psql 最让我惊艳的设计，是元命令的命名足够统一。\d表示 describe，后面跟不同字母组合表示不同对象类型。

命令	作用	典型输出
`\d 表名`	查看表结构、字段、约束、索引	列名、类型、允许空、默认值
`\dt`	列出当前库所有表	表名、schema、所有者
`\dv`	列出视图	视图名、schema
`\di`	列出索引	索引名、表名、字段
`\ds`	列出序列	序列名及关联
`\df`	列出函数	函数签名、返回类型
`\du`	列出角色/用户	角色名、属性、成员
`\dn`	列出 schema	schema 名、所有者
`\l`	列出所有数据库	库名、所有者、编码

特别提醒：\d+带加号的版本会在基础信息之上多显示存储参数、表空间、注释、触发器、索引定义等更细的信息。排查表膨胀、确认索引是否生效时，\d+比\d有用得多。

还有一类带 pattern 的用法：\dt schema.*只列某个 schema 下的表，\d orders *status*能找出 orders 表里所有名字带 status 的列。psql 里的 pattern 用的是*通配符，不是正则表达式，别拿正则习惯去套。

3.2 摸底一个陌生库的标准流程

刚接一个不熟悉的数据库时，我一般按这个顺序摸底：

\l看有哪些数据库
\c dbname切换目标库
\dn看 schema 分布
\dt看表清单
\d 关键表逐个看结构

这套流程走完，库的整体面貌基本就在脑子里了。元命令都以反斜杠开头，在 psql 里输入后立即执行，不需要加结尾分号。很多人从图形界面转过来，习惯性在元命令后面敲分号，多数情况无害，但养成不加分号的习惯更规范。

3.3 \timing 与 \watch：让测量变成肌肉记忆

做 SQL 优化的人必须养成一个习惯：所有查询开着计时。\timing on会在每次查询后显示执行耗时。我基本是永久开启——不是为了炫耀，而是没有耗时的概念，你根本不知道一条"看起来还行"的 SQL 到底是不是瓶颈。

\timing on SELECT count(*) FROM orders WHERE status = 'pending';

\watch更进一步，它可以每隔 N 秒重复执行上一条查询：

SELECT count(*) FROM active_sessions; \watch 5

每 5 秒刷新一次，非常适合监控线上某项指标的变化，比如队列积压、活跃会话数。要停止就按 Ctrl+C。注意\watch重复的是上一条已执行的 SQL，如果中途跑了别的查询，再按\watch会换成新的那条，容易误判，留意就好。

3.4 psqlrc：你的专属启动配置

psql 启动时会读取~/.psqlrc文件，这里面可以放默认设置。我自己的 psqlrc 长这样：

\timing on \pset null 'NULL' \pset pager off \x auto \set HISTSIZE 20000

\x auto是较新版本才有的选项，意思是只有当行宽超过屏幕时才自动切扩展模式，平时还是对齐样式，体验非常顺。\pset pager off则关闭分页器，让所有结果直接滚出来，配合管道做输出处理时特别有用。如果你管理多台机器，还可以设置\set COMP_KEYWORD_CASE upper这类小细节，让自动补全的关键字统一大写，代码风格瞬间整齐。

不同库需要不同配置时，psql 启动时会先读取~/.psqlrc，再读取当前目录下的.psqlrc作为补充，这个特性可以让你针对项目做差异化配置。

4. 脚本化执行：从单条命令到自动任务

4.1 三种执行方式的取舍

psql 不只是交互式工具，它天生适合被脚本调用。执行 SQL 的方式主要有三种：

psql -c "SQL语句"：执行单条或分号分隔的多条 SQL，执行完退出，适合临时操作。
psql -f 脚本文件：从文件读取执行，文件里可以混着 SQL 和元命令，适合复杂任务。
heredoc 方式：psql <<EOF ... EOF，在 shell 脚本里最常见，方便插入 shell 变量。

三者的核心区别在哪？-c是"单次执行但非交互"，-f是"严格按文件执行"，heredoc 最接近交互会话，支持全部元命令。写自动化任务，我个人的排序是：能用-f就用-f，文件可维护；一次性的动态拼接用-c；需要把 SQL 和 shell 逻辑混在一起的就 heredoc。

4.2 变量替换：告别引号转义地狱

psql 支持变量替换，这让脚本写起来灵活很多。最常用的是命令行传参：

psql -v start_date="2024-01-01" -v end_date="2024-01-31" -f report.sql

在 report.sql 里用:'start_date'来引用变量的值。这里的冒号用法非常讲究，必须分清：

:var直接替换，适合不需要加引号的场景。
:'var'会把变量值自动包上 SQL 字符串的单引号，适合日期、文本这类需要引号的场景。

不用 psql 的变量机制，很多人就只能用 shell 变量拼 SQL，很容易出现多层引号转义地狱。psql 的变量把参数边界画清楚了，能在很大程度上减少转义和注入问题。

更进一步，psql 还能用\if、\elif、\else做条件控制，条件里可以判断变量是否存在、比较数值，甚至基于 SQL 返回结果做决策：

\if :{?start_date} SELECT '有起始日期，执行增量逻辑'; \else SELECT '没有起始日期，退化为全量逻辑'; \endif

这类逻辑在生成动态报表、按环境差异执行不同 SQL 的场景里非常实用，我在做多环境发布脚本时经常靠它区分开发库和生产库的初始化语句。

4.3 ON_ERROR_STOP 与事务：别让半截脚本毒害数据

脚本执行最怕什么？跑了一半报错，前面已经提交。psql 默认每条 SQL 是独立事务，想在脚本里保证整体原子性，要显式包在BEGIN; ... COMMIT;里。但这里有个细节：如果脚本里出现了\copy、\d这类元命令，它们并不真正参与事务，别指望回滚能覆盖到它们。

另一个容易踩的坑是错误处理。psql 从文件执行脚本时，默认遇到错误会继续往下跑，这非常危险。务必在脚本开头加上：

\set ON_ERROR_STOP on

一旦某条语句报错，psql 立即停止执行并返回非零退出码。crontab、CI 流水线里判断任务成败，靠的就是这个退出码。配合\set QUIET on，可以关掉一堆冗余提示信息，让自动化输出干净很多。

4.4 一个完整的定时导出示例

把前面这些串起来，一个典型的每日导出脚本长这样：

#!/bin/bash export PGPASSFILE=/home/app/.pgpass psql -h 127.0.0.1 -U report_user -d app_db \ -v report_date="2024-06-01" \ -f /home/app/scripts/daily_export.sql \ >> /var/log/daily_export.log 2>&1 if [ $? -eq 0 ]; then echo "[OK] export finished at $(date)" else echo "[FAIL] export failed at $(date)" exit 1 fi

daily_export.sql 内部可以这样写：

\set ON_ERROR_STOP on \pset format unaligned \pset fieldsep ',' \pset footer off \o /data/export/orders_:'report_date'.csv SELECT order_id, user_id, amount, created_at FROM orders WHERE created_at::date = DATE :'report_date'; \o

注意最后那个\o不带参数，作用是关闭文件输出，避免影响后续查询。这种组合拳写清楚后，整个数据导出流程就是全自动的，人只需要检查日志和产物。

5. 排查慢查询：EXPLAIN 的正确打开方式

5.1 EXPLAIN 和 EXPLAIN ANALYZE 怎么选

SQL 慢，第一件事就是看执行计划。psql 里直接EXPLAIN SELECT ...就能看到计划树。但有一点必须清楚：EXPLAIN 只输出计划，不真正执行 SQL，它的行数是基于统计信息的估算值，误差可能很大。

要拿到真实执行信息，用EXPLAIN (ANALYZE, BUFFERS) SELECT ...。ANALYZE 会真的跑这条 SQL，输出每个节点的实际耗时和行数；BUFFERS 则显示缓存命中情况。这两个参数组合，是排查性能问题的黄金搭档：

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 123 AND status = 'paid';

输出里重点看这几个信号：

哪个节点的 actual time 最大，说明瓶颈在这里。
有没有Seq Scan全表扫描，以及扫描的行数和过滤后的行数。
估算行数（rows）和实际行数（actual rows）差了多少。如果差 10 倍以上，说明统计信息过期，后手是执行ANALYZE orders;更新统计。

5.2 BUFFERS 输出怎么读

BUFFERS 相关的字段常被忽略，但对定位 IO 问题帮助极大。一条查询的 Buffers 输出可能长这样：

Buffers: shared hit=421 read=56

这里的shared hit是命中了操作系统或 PostgreSQL 共享缓存的块数，read是从磁盘实际读取的块数。如果read占大头，说明数据大概率没有缓存在内存里，可能是查询范围太大、索引缺失，或者机器内存本身不够。反之hit占大头，说明整体 IO 情况良好，问题可能出在 CPU 计算或者锁等待上。

5.3 一套完整的慢查询排查流程

结合\timing和 EXPLAIN，我通常这么处理一条慢 SQL：

确保\timing on，先确认整条 SQL 的耗时基线。
EXPLAIN (ANALYZE, BUFFERS)跑一遍，圈定最大耗时节点。
看是不是 Seq Scan，如果是，检查过滤条件能否走索引、有没有现成索引可用。
对比估算行数和实际行数，判断统计信息是否过期，必要时ANALYZE。
看 BUFFERS 里 read 和 hit 的占比，判断瓶颈在 IO 还是 CPU。

这套流程走完，多数慢查询都能定位到根因。剩下一部分可能是锁等待、索引失效、数据分布极端不均这类深水区，那就需要进一步结合数据库自带的统计视图和慢查询日志来看了。

关于索引有个常见误解需要澄清：不是加了索引就一定快。如果查询条件选择性太低，比如 status 这种只有两三种取值的字段，优化器可能判断全表扫描比走索引更划算。这时候正确做法不是硬删索引，而是看过滤条件组合是否合理，或者考虑部分索引、覆盖索引。这些经验攒多了，你才会真正理解为什么 EXPLAIN 是每个用 PostgreSQL 的人必学的技能。

6. 实务中的坑：连接、认证、乱码与杂项

6.1 连接失败：从报错信息里找线索

psql: error: connection to server at ... failed这类报错看似千篇一律，实际上要从报错的具体文字里找线索。把常见的几种列成表，排查思路一眼就清楚了：

报错关键字	含义	优先排查方向
Connection refused	服务器没监听、防火墙拦截、端口写错	确认端口监听、检查防火墙、用`pg_isready`
Timeout expired	网络不通或连接被卡住	ping、traceroute、检查负载
no pg_hba.conf entry	认证配置没有匹配来源	检查 pg_hba.conf 规则和 reload
password authentication failed	密码不对或认证方式不匹配	确认密码、检查认证方法配置
database does not exist	库名错误或没指定 -d	确认库名、用`\l`查看

排查顺序有个讲究：先确认网络通不通，再看端口通不通，然后看 pg_hba.conf 匹配规则，最后才怀疑密码。很多时候连接问题其实是防火墙或网络层的，别一上来就改密码。

pg_hba.conf 还有一个特点：排在最前面的匹配规则优先生效。很多人改了文件忘了 reload，或者规则顺序放错，导致后面的规则永远不生效。改完配置记得执行SELECT pg_reload_conf();，不需要重启数据库服务。

6.2 认证方式与角色授权的三层权限

PostgreSQL 的认证方式在 pg_hba.conf 里逐条声明，常见的是 scram-sha-256 和 md5。新版本默认 scram，老项目可能还在用 md5。如果连接报认证方法不支持的错，看看两端的版本和配置是否兼容。

角色管理上我踩过不少坑：很多人误以为创建了用户就算完事，其实还要给角色授权。psql 里的完整操作是这样：

CREATE ROLE app_user LOGIN PASSWORD 'secure_password'; GRANT CONNECT ON DATABASE app_db TO app_user; GRANT USAGE ON SCHEMA public TO app_user; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

记住：CONNECT、USAGE、表级权限是三层，漏掉任何一层，应用都会在某个环节报权限错误。这类错误排查时别只看一个点，要从连接权限、schema 权限、对象权限逐层检查。排错时用\du看角色属性，用\dp 表名看某张表的权限授权情况，比盲猜快得多。

6.3 输出乱码：九成是 client_encoding 问题

连接数据库后中文字符变乱码，九成是客户端字符集和数据库编码对不上。psql 连接后，服务端会根据客户端设置的 client_encoding 来做转码。检查方式：

SHOW client_encoding; SHOW server_encoding;

如果不一致，最简单是设置环境变量PGCLIENTENCODING=UTF8，或者在 psql 里\encoding UTF8。批量做数据迁移时，最好在导出和导入两端都把 client_encoding 固定下来，否则很容易出现"本地看正常，换台机器就乱码"的情况。

6.4 几个提升幸福感的小技巧

最后分享几个我日常离不开的 psql 技巧：

\e打开编辑器。psql 里输入\e会用系统默认编辑器打开当前编辑缓冲的 SQL，编辑完保存退出后自动执行。长 SQL 写得费劲时，这个功能是救命的。
\?和\h。\?列出所有元命令，\h查看某条 SQL 命令的语法帮助。忘记用法时直接查，不用切浏览器。
\gexec。把查询结果当作 SQL 逐条执行，做批处理、动态生成 DDL 时特别强势。
历史命令。psql 的历史保存在~/.psql_history，方向键上下可以回看。换机器后把这个文件拷过去，操作习惯无缝延续。
Tab 自动补全。较新版本的 psql 支持表名、列名补全，按键 Tab 即可，老版本没有这个功能。

7. 写在最后的经验之谈

psql 不是那种用一次就丢的玩具，它是一个能陪你很多年的工具。刚开始可能觉得元命令记不住、输出格式不如图形界面直观，但用熟之后你会越来越依赖它，因为它把"思考 SQL"和"操作数据库"这两件事缝合得足够紧密。

我个人最后的建议是：别把 psql 和图形工具对立起来。图形工具看数据分布、画图表有自己的优势，psql 强在快速、可脚本化、能进最小化环境。最佳状态是两者配合，但先把 psql 练熟，你会获得一种"任何环境都能干活"的底气。这也是我认为这个"老工具"在今天依然值得投入时间学习的根本原因。

PostgreSQL 命令行利器 psql：从连接到脚本化实战完整指南