为什么要专门学 psql
图形客户端(pgAdmin、DBeaver)上手是快,但真到了服务器上排查问题,能依靠的往往只有 SSH 里一个 psql:它随 PostgreSQL 一起安装、永远可用、速度最快,还能写进脚本自动化。前几篇里我们已经零散用过它——《PostgreSQL 安装与初始配置》里连库验证、《PostgreSQL 用户、角色与权限管理》 里查角色——这一篇把它系统地补齐。
psql 的能力分两半:SQL 语句本身(上一篇 《SQL 基础速成》 已练过)和元命令(以反斜杠 \ 开头的 psql 专属命令,不发给服务器、在客户端本地执行)。元命令才是 psql 的精华所在。
连接:四种常用姿势
# 1. 参数式:主机、端口、用户、库分开写psql -h 127.0.0.1 -p 5432 -U appuser -d appdb
# 2. 连接串式:一条 URI 搞定,写脚本时最方便psql "postgresql://appuser:password@127.0.0.1:5432/appdb"
# 3. Docker 里直接进docker exec -it lab-postgres psql -U appuser -d appdb
# 4. 本机系统包安装版,借 peer 认证以 postgres 身份进sudo -u postgres psql连接相关的实用细节:
-W强制提示输入密码;密码不想出现在命令历史里,用环境变量PGPASSWORD=xxx psql ...,或在~/.pgpass文件里存(权限必须 600),格式为主机:端口:库:用户:密码。- 连接串里密码含特殊字符(
@、/等)要做 URL 编码,否则解析会错位。长期用的连接建议都收进.pgpass,命令里只留主机和用户。 - 进错库不用退出重连,psql 里直接
\c appdb切换数据库,\c appdb alice还能顺带换用户。
元命令速查:先记这十五个
| 元命令 | 作用 | 备注 |
|---|---|---|
\l | 列出全部数据库 | 加 + 显示大小等详情:\l+ |
\c 库名 | 切换数据库 | 相当于重新连接 |
\dt | 列出当前模式的表 | \dt *.* 看全部模式;\dt+ 带大小 |
\d 表名 | 查看表结构 | 字段、类型、索引、外键一屏看全,高频第一名 |
\di | 列出索引 | \di+ 带大小 |
\dv / \df | 列出视图 / 函数 | 排查视图和函数时用 |
\du | 列出角色及属性 | 查权限体系的入口 |
\z 表名 | 查看对象的权限授予情况 | 核对 GRANT 结果 |
\dn | 列出模式 | 多模式项目必备 |
\x | 切换扩展显示 | 宽表竖排展示,开/关切换 |
\timing | 开关语句耗时显示 | 调 SQL 时常开 |
\e | 把当前语句调入编辑器 | 长 SQL 写错了不用重敲 |
\i 文件.sql | 执行脚本文件 | 在 psql 内部跑脚本 |
\q | 退出 psql | 也可以 Ctrl+D |
几乎所有 \d 系列都支持 + 后缀看详情、支持通配符过滤,比如 \dt user* 只看 user 开头的表。
看结构与看数据:日常两板斧
排查问题时最高频的动作是「这张表长什么样、里面有什么」:
\d users -- 表结构:字段、索引、约束SELECT * FROM users ORDER BY id DESC LIMIT 5; -- 最近 5 行SELECT count(*) FROM users; -- 总行数
-- 看表和索引占多大空间SELECT pg_size_pretty(pg_total_relation_size('users')) AS total, pg_size_pretty(pg_relation_size('users')) AS table_only, pg_size_pretty(pg_indexes_size('users')) AS indexes;宽表查询时结果横向溢出看不清,打开扩展显示:
\x onSELECT * FROM users WHERE id = 1;-- 结果会变成一列一行竖排:id │ 1 / username │ zhangsan / ...再加一个救急技巧:查询结果太多刷屏时,用 \pset pager off 关掉分页器,或者干脆在 SQL 里老老实实加 LIMIT。
输出控制:把结果变成想要的样子
\timing on -- 每条语句后显示耗时(毫秒)\pset null '(null)' -- NULL 显示成 (null),和空字符串区分开\a -- 切换对齐/非对齐输出(导出数据时用非对齐)\o /tmp/out.txt -- 之后的结果都写进文件,直到再次 \o 关闭执行脚本与导入导出
批量操作靠脚本,不要一行行手敲:
# 在 shell 里直接执行 SQL 文件,出错即停(ON_ERROR_STOP 很重要)psql -U appuser -d appdb -v ON_ERROR_STOP=1 -f init.sql
# 只跑一条语句拿结果,常用于脚本里取值psql -U appuser -d appdb -t -c "SELECT count(*) FROM users;"数据导出导入用 \copy(客户端侧执行,普通用户权限即可;服务端的 COPY 需要超级用户或相应权限,日常优先 \copy):
-- 导出成 CSV\copy users TO '/tmp/users.csv' WITH (FORMAT csv, HEADER true)
-- 从 CSV 导入(列顺序要与表一致,或显式写列名)\copy users (username, email, age) FROM '/tmp/users.csv' WITH (FORMAT csv, HEADER true)WARNING导入前先想清楚目标表里已有数据怎么办:
\copy是纯追加,遇到唯一约束冲突会整批失败回滚。不要在没确认的情况下对生产表做全量覆盖式导入;稳妥做法是先导入临时表、校验无误后再合并进正式表。大批量整库迁移则用 《数据库备份与恢复全攻略》 里的 pg_dump 路线。
实用进阶:这几个技巧最提效
1. \gset 把查询结果存成变量,后续语句直接引用:
SELECT count(*) AS total FROM users \gsetSELECT :total AS user_total;2. 变量替换:psql -v uid=1 传入变量,SQL 里用 :uid 引用,脚本立刻可复用:
psql -U appuser -d appdb -v uid=1 -c "SELECT * FROM users WHERE id = :uid;"3. \watch 定时重复执行,盯实时状态不用手刷:
-- 每 2 秒刷新一次当前连接数SELECT count(*) FROM pg_stat_activity; \watch 24. 事务里干活防手滑:重要修改先 BEGIN;,查验无误再 COMMIT;,不对就 ROLLBACK;——和《SQL 基础速成》里讲的习惯一脉相承。
记住 psql 的定位:它是你和 PostgreSQL 之间最短的那条路,元命令负责「看」,SQL 负责「做」,两者合起来就是完整的运维现场。后面 《PostgreSQL 索引与查询优化入门》 里的 EXPLAIN 分析,也全是在 psql 里完成的。
常见问题
Q:SQL 写了一半发现错了,怎么取消当前输入?
A:按 Ctrl+C 取消当前语句回到提示符;或者新起一行输入 \r 清空查询缓冲区重新来。
Q:粘贴大段 SQL 进去,格式全乱、还提前执行了?
A:多半是粘贴内容里混了换行触发的自动执行。用 \e 进编辑器粘贴整理,或者把 SQL 存成文件用 -f 执行,最稳。
Q:中文显示乱码?
A:查客户端编码:\encoding 看当前设置,SET client_encoding TO 'UTF8'; 临时修正;根源上确保数据库编码是 UTF8、终端也是 UTF8。
Q:连接时报 FATAL: no pg_hba.conf entry for host?
A:这是服务器门卫拒绝:你的来源 IP / 用户 / 库组合在 pg_hba.conf 里没有放行规则。回《PostgreSQL 安装与初始配置》一篇补规则并 reload,不要为了图省事把认证改成 trust。
Q:psql 里怎么看一条慢 SQL 卡在哪?
A:先开 \timing,再用 EXPLAIN ANALYZE 你的SQL; 看执行计划和实际耗时——这是下一篇索引优化的正题,这里先把工具握在手里。
小结
这一篇把 psql 从「能连上」练到「能干活」:连接四姿势、十五个高频元命令、结构与数据查看、输出控制、脚本与 \copy 导入导出,再加变量与 \watch 等提效技巧。PostgreSQL 部分还剩最后一块拼图——索引与查询优化,下一篇收官。