psql 命令行详解

psql 是 PostgreSQL 官方提供的最核心的命令行客户端（Command-Line Client），它随数据库服务端一起安装，无需额外配置即可使用。本节将系统讲解 psql 的连接参数、元命令（Meta-Command）、SQL 执行方式、变量替换、输出格式化与退出码，让你能够纯粹依靠终端完成日常开发与运维工作。

前置知识

阅读本节前，建议先了解：日志文件管理

基础概念

psql 是一个基于终端的前端工具（Frontend），它通过 libpq 库与 PostgreSQL 服务端建立连接，把用户输入通过简单查询协议（Simple Query Protocol）或扩展查询协议（Extended Query Protocol）发送给后端进程执行，再把结果格式化输出到终端。

psql 有「两种输入」：

• SQL 语句：以分号 ; 结尾，发送给服务端执行
• 元命令（Meta-Command）：以反斜杠 \ 开头，由 psql 自己解析执行，不发送给服务端（\i、\copy 等少数例外会读文件）

理解「元命令不经过服务端」这一点很重要：\d 实际上会翻译成对 pg_catalog 的查询语句再发送，但语法解析发生在客户端。

安装与配置

获取 psql

psql 会随服务端一起安装。若只需客户端，可单独安装：

# Debian / Ubuntu：只装客户端，不装服务端
sudo apt install -y postgresql-client-18

# RHEL / Rocky / AlmaLinux
sudo dnf install -y postgresql18

# macOS（Homebrew）
brew install libpq
brew link --force libpq          # 把 psql 链接到 /usr/local/bin 或 /opt/homebrew/bin

# Docker 中临时使用客户端
docker run -it --rm postgres:18 psql --version

# 确认版本（客户端版本应与服务端主版本一致或更高）
psql --version

psql (PostgreSQL) 18.0 (Ubuntu 18.0-1.pgdg24.04+1)

连接参数

psql 的连接参数分为「短选项」与「长选项」两套，二者等价：

短选项	长选项	说明	默认值
-h	--host	主机名或 socket 目录	Unix socket 目录 / localhost
-p	--port	服务端端口	5432
-U	--username	连接用户名	当前系统用户名
-d	--dbname	数据库名或连接串	与用户名相同
-W	--password	强制提示输入密码	无
-w	--no-password	永不提示密码（脚本用）	无
-c	--command	执行单条命令后退出	无
-f	--file	执行文件后退出	无
-o	--output	结果输出到文件	stdout
-L	--log-file	会话日志写入文件	无
-v	--set	设置变量	无
-A	--no-align	非对齐输出（便于脚本解析）	对齐
-t	--tuples-only	只输出数据行，不含表头	含表头
-q	--quiet	静默模式	无
-X	--no-psqlrc	不读取 ~/.psqlrc	读取
-e	--echo-queries	回显执行的 SQL	无

# 最简连接：使用当前系统用户与同名数据库
psql

# 显式指定四要素
psql -h 127.0.0.1 -p 5432 -U app_user -d demo

# 强制提示输入密码（生产环境推荐，避免密码进入 history）
psql -h db.internal -U app_user -d demo -W

# 单条命令模式：执行后立即退出，适合写进脚本
psql -h 127.0.0.1 -U app_user -d demo -c "SELECT version();"

# 免密模式 + 非交互（依赖 ~/.pgpass 或 PGPASSWORD 提供凭据）
psql -h 127.0.0.1 -U app_user -d demo -w -c "SELECT 1;"

DSN 连接串

除了短选项，psql 还支持以统一资源标识符（URI）形式给出连接信息，也支持 libpq 的键值对形式：

# URI 形式：postgresql://用户:密码@主机:端口/数据库?参数
psql "postgresql://app_user:Secret%40123@127.0.0.1:5432/demo?sslmode=require"

# 键值对形式（libpq conninfo）
psql "host=127.0.0.1 port=5432 dbname=demo user=app_user connect_timeout=5"

# 只给 DSN，服务端密码靠 .pgpass
psql "postgresql://app_user@db.internal/demo"

DSN 中常用查询参数：

参数	作用	示例
sslmode	SSL 模式	disable / require / verify-full
connect_timeout	连接超时（秒）	5
application_name	应用标识，出现在 pg_stat_activity	myapp-worker
options	服务端会话参数	-c%20statement_timeout%3D5000
target_session_attrs	只接受可写/只读节点	read-write

环境变量

短期临时连接可用环境变量，避免把参数写进命令行（ps 可见命令行参数）：

环境变量	等价项	说明
PGHOST	-h	主机
PGPORT	-p	端口
PGUSER	-U	用户名
PGDATABASE	-d	数据库
PGPASSWORD	密码	不推荐，进程环境中可被读取
PGPASSFILE	密码文件	默认 ~/.pgpass
PGSSLMODE	sslmode	SSL 模式
PGAPPNAME	application_name	应用名
PGCONNECT_TIMEOUT	连接超时	秒
PSQLRC	启动脚本	自定义 psqlrc 路径

# 推荐：用 .pgpass 保存密码，权限必须 0600
cat > ~/.pgpass <<'EOF'
# hostname:port:database:username:password
127.0.0.1:5432:demo:app_user:Secret%40123
db.internal:5432:*:app_user:AnotherSecret
EOF
chmod 600 ~/.pgpass

# 注意：.pgpass 中的密码不做 URL 解码，冒号与反斜杠需用 \ 转义

详细说明

元命令速查表

元命令是 psql 的效率核心。下表按用途分组列出最常用的命令：

元命令	用途	示例
\l	列出所有数据库	\l+ 显示大小
\c	切换连接（库/用户/主机）	\c demo app_user
\d	描述对象	\d、\d users、\d+ users
\dt	列出表	\dt public.*、\dt+
\dn	列出模式	\dn+
\du	列出角色与权限	\du+
\df	列出函数	\df+、\df public.*
\dv	列出视图	\dv+
\di	列出索引	\di+
\ds	列出序列	\ds+
\dx	列出已安装扩展	\dx
\dp	列出访问权限（同 \z）	\dp users
\x	切换扩展显示（行转列）	\x auto
\timing	开关 SQL 执行计时	\timing on
\e	用编辑器编辑当前缓冲区	\e
\i	执行 SQL 文件	\i /tmp/init.sql
\o	结果重定向到文件	\o /tmp/out.txt
\copy	客户端侧导入导出	\copy users TO 'u.csv' CSV
\set	设置变量或开关	\set v 42
\pset	输出格式设置	\pset format csv
\conninfo	显示当前连接信息	\conninfo
\encoding	查看/设置客户端编码	\encoding UTF8
\h	SQL 语法帮助	\h INSERT
\?	元命令帮助	\?
\q	退出	\q

最常用的对象描述命令（\d 家族）：

-- psql 交互会话中以 \ 开头的是元命令，不是 SQL，不需要分号
-- 列出当前库中所有可见对象
\d

-- 查看表结构（列、类型、约束、索引）
\d users

-- 加 + 显示更多信息：存储参数、注释、大小、描述
\d+ users

-- 通配符匹配：只看 public 模式下以 ord 开头的表
\dt public.ord*

-- 查看某个索引的定义
\di+ idx_users_email

查看表结构的典型输出（\d users）：

                                        Table "public.users"
   Column   |           Type           | Collation | Nullable |           Default
------------+--------------------------+-----------+----------+------------------------------
 id         | bigint                   |           | not null | generated always as identity
 email      | text                     |           | not null |
 nickname   | text                     |           |          |
 status     | smallint                 |           | not null | 1
 created_at | timestamp with time zone |           | not null | now()
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_key" UNIQUE CONSTRAINT, btree (email)
Referenced by:
    TABLE "orders" CONSTRAINT "orders_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)

SQL 执行的三种方式

方式一：交互式输入。 进入 psql 后逐条输入，psql 会缓冲多行直到遇到分号：

-- 交互式：提示符 demo=> 表示普通用户，demo=# 表示超级用户
demo=> SELECT id, email FROM users WHERE status = 1 ORDER BY id LIMIT 3;
 id |        email
----+---------------------
  1 | alice@example.com
  2 | bob@example.com
  3 | carol@example.com
(3 rows)

-- 多行语句：分号之前不会执行，提示符变为 demo->
demo=> SELECT *
demo-> FROM orders
demo-> WHERE amount > 100;

方式二：-c 单命令模式。 一次性执行字符串，不进入交互，适合脚本与 CI：

# 单条命令
psql -h 127.0.0.1 -U app_user -d demo -c "SELECT count(*) FROM orders;"

# 单个事务中包含多条语句（-c 会把整个字符串作为一个多语句查询发送）
psql -h 127.0.0.1 -U app_user -d demo \
  -c "BEGIN; UPDATE users SET status = 0 WHERE id = 42; COMMIT;"

# 配合 -A -t 获得纯净输出，便于 shell 变量赋值
COUNT=$(psql -h 127.0.0.1 -U app_user -d demo -At -c "SELECT count(*) FROM orders;")
echo "订单总数：${COUNT}"

方式三：-f 文件模式。 执行整个 SQL 文件，等价于进入后执行 \i：

# 执行初始化脚本，遇到错误是否继续由 ON_ERROR_STOP 控制
psql -h 127.0.0.1 -U app_user -d demo \
  -v ON_ERROR_STOP=1 \
  -f ./migrations/001_init.sql

# 加 -e 回显每条实际执行的语句，便于审计脚本内容
psql -h 127.0.0.1 -U app_user -d demo -e -f ./migrations/001_init.sql

-- migrations/001_init.sql
-- ON_ERROR_STOP=1 时，任何语句报错都会立刻终止并返回非零退出码
\set ON_ERROR_STOP on

BEGIN;

CREATE TABLE IF NOT EXISTS users (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      text NOT NULL UNIQUE,
  nickname   text,
  status     smallint NOT NULL DEFAULT 1,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_users_status ON users (status);

COMMIT;

变量与 \set

psql 变量在客户端替换，替换结果再作为 SQL 文本发送。变量语法为 :name，字符串插值为 :'name'（自动加引号并转义），标识符插值为 :"name"：

-- 设置变量
\set tenant_id 42
\set table_name users

-- 整数替换（可直接参与比较）
SELECT count(*) FROM orders WHERE user_id = :tenant_id;

-- 字符串插值：自动加单引号，安全防注入
\set email 'alice@example.com'
SELECT id, email FROM users WHERE email = :'email';

-- 标识符插值：自动加双引号，适合动态表名
SELECT count(*) FROM :"table_name";

-- 查看已定义的变量
\set

-- 取消变量
\unset tenant_id

常用内建变量：

变量	默认值	作用
AUTOCOMMIT	on	是否自动提交每条语句
ON_ERROR_STOP	off	出错是否终止（脚本必开）
ECHO	none	回显模式：none/queries/all/errors
QUIET	off	是否抑制欢迎信息与提示
HISTFILE	~/.psql_history	历史文件路径
VERBOSITY	default	错误详细度：terse/default/verbose
FETCH_COUNT	0	分批获取行数，超大结果集必设
PROMPT1	%/%R%#	主提示符格式

-- 脚本开头推荐的防御性设置
\set ON_ERROR_STOP on        -- 出错立即退出，避免半途而废的脏数据
\set VERBOSITY verbose       -- 错误信息包含文件与行号，便于定位
\set FETCH_COUNT 1000        -- 每 1000 行输出一次，避免大结果集吃满内存

-- 交互式调试时打开回显与计时
\timing on
\set ECHO queries

输出格式化

\pset 控制输出外观，-A -t 是脚本模式的快捷写法：

格式	\pset format 取值	特点
对齐表格	aligned（默认）	人类可读，列宽自适应
非对齐	unaligned	字段以分隔符连接，脚本友好
CSV	csv	标准 CSV，含引号转义
JSON	json	每行一个 JSON 对象
HTML	html	生成 <table> 标签
LaTeX	latex	生成 tabular 环境

-- 切换为 CSV 输出并写入文件
\pset format csv
\pset csv_fieldsep ','
\o /tmp/users.csv
SELECT id, email, status FROM users ORDER BY id;
\o

-- 键值对输出（等价于每行两列的 json 风格）
\pset format json
SELECT id, email FROM users LIMIT 2;

-- \pset format json 输出示例
{"id":1,"email":"alice@example.com"}
{"id":2,"email":"bob@example.com"}

-- 行转列显示：字段很多时强烈推荐
\x auto        -- auto：宽度够就正常显示，不够自动转列
SELECT * FROM orders WHERE id = 1001;

-- 显示/隐藏表头
\pset tuples_only on      -- 只显示数据
\pset border 2            -- 边框样式 0~2

-- ASCII 边框（自动化脚本对比输出时更稳定）
\pset linestyle ascii
\pset border 3            -- 设置码页边框，输出为 +---+ 形式

-- \x auto 触发行转列后的输出
-[ RECORD 1 ]----+---------------------------
id               | 1001
user_id          | 42
amount           | 199.00
status           | paid
created_at       | 2026-09-21 10:12:33.481+08

退出码

psql 的退出码直接决定脚本 && 链与 CI 流水线的成败，务必理解：

退出码	含义
0	正常结束
1	致命错误（无法连接、认证失败、参数错误）
2	连接正常但会话异常结束（如 \q 之外的中断）
3	脚本中的 SQL 出错且 ON_ERROR_STOP 为 on

# 关键点：默认 ON_ERROR_STOP=off，SQL 报错时退出码仍是 0，脚本无法感知失败
psql -d demo -c "SELECT * FROM no_such_table;" ; echo "exit=$?"
# 不推荐：未开 ON_ERROR_STOP，失败被静默吞掉

# 正确做法：显式开启
psql -v ON_ERROR_STOP=1 -d demo -c "SELECT * FROM no_such_table;" ; echo "exit=$?"
# 输出 ERROR: relation "no_such_table" does not exist
# exit=3

# 生产脚本模板：任何一步失败立刻退出
set -euo pipefail

PSQL="psql -h 127.0.0.1 -p 5432 -U app_user -d demo -v ON_ERROR_STOP=1 -q"

${PSQL} -c "SELECT 1;" >/dev/null || { echo "数据库不可达"; exit 1; }
${PSQL} -f ./migrations/001_init.sql
echo "迁移完成"

实战示例

目标：在无图形界面的生产服务器上，完成一次「健康巡检 + 数据抽样 + 结果归档」的完整操作。

#!/usr/bin/env bash
# inspect.sh —— PostgreSQL 日常巡检脚本
set -euo pipefail

# 使用 .pgpass 提供凭据，命令行不出现密码
export PGHOST=127.0.0.1
export PGPORT=5432
export PGDATABASE=demo
export PGUSER=app_user
export PGAPPNAME=daily-inspect        # 便于在 pg_stat_activity 中识别

OUTDIR=/var/log/pg-inspect/$(date +%F)
mkdir -p "${OUTDIR}"

PSQL=(psql -w -v ON_ERROR_STOP=1 -q)

# 1) 连接信息确认
"${PSQL[@]}" -c "\conninfo"

预期输出：

You are connected to database "demo" as user "app_user" via socket in "/var/run/postgresql" at port "5432".

# 2) 数据库与表规模概览，导出为 CSV
"${PSQL[@]}" <<'SQL' > "${OUTDIR}/databases.csv"
\pset format csv
SELECT datname,
       pg_size_pretty(pg_database_size(datname)) AS size,
       numbackends
FROM pg_database d
JOIN pg_stat_database s ON s.datname = d.datname
WHERE NOT datistemplate
ORDER BY pg_database_size(datname) DESC;
SQL

# 3) 业务数据抽样：最近 7 天每个渠道的订单量与金额
"${PSQL[@]}" <<'SQL'
\timing on
\x auto
SELECT channel,
       count(*)                            AS order_cnt,
       to_char(sum(amount), 'FM999,999.00') AS total_amount,
       max(created_at)                      AS latest_at
FROM orders
WHERE created_at >= now() - interval '7 days'
GROUP BY channel
ORDER BY order_cnt DESC;
SQL

预期输出：

-[ RECORD 1 ]+-----------------------
channel      | web
order_cnt    | 18432
total_amount | 2,983,412.50
latest_at    | 2026-09-21 10:58:07.213+08
-[ RECORD 2 ]+-----------------------
channel      | app
order_cnt    | 12078
total_amount | 1,742,006.00
latest_at    | 2026-09-21 10:59:41.882+08
Time: 142.318 ms

# 4) 慢查询与锁等待快照
"${PSQL[@]}" -At -F '|' -c "
SELECT pid, now() - query_start AS duration, state, left(query, 60)
FROM pg_stat_activity
WHERE state <> 'idle' AND query_start < now() - interval '5 seconds'
ORDER BY duration DESC;" > "${OUTDIR}/long-running.txt"

# 5) 归档打包，便于留痕
tar -czf "${OUTDIR}.tar.gz" -C "$(dirname "${OUTDIR}")" "$(basename "${OUTDIR}")"
echo "巡检完成：${OUTDIR}.tar.gz"

注意事项

• \c 重连会丢弃会话级设置：SET 的会话参数（search_path、statement_timeout、临时表）在 \c 后全部丢失。
• -c 与 -f 的退出码行为不同：-c 中若存在多条语句，报错后的语句不再执行；-f 默认继续执行，务必开 ON_ERROR_STOP。
• PGPASSWORD 会泄漏：它出现在进程环境变量中，同机其他进程可读。生产脚本请用 ~/.pgpass（chmod 600）或服务端证书认证。

# 错误！密码进入命令行，会留在 shell history 与 ps 输出中
psql "postgresql://app_user:RealPassword@127.0.0.1/demo"

# 不推荐：密码进入环境变量
PGPASSWORD=RealPassword psql -h 127.0.0.1 -U app_user -d demo

• 大结果集要用 FETCH_COUNT：psql 默认一次性把全部结果读进内存，百万行查询可能打爆客户端内存。设 \set FETCH_COUNT 1000（内部改用游标分批取）后仅输出有效。
• \copy 与 SQL COPY 的权限要求不同：\copy 在客户端读写文件，只需业务用户权限；服务端 COPY ... TO '/path' 需要超级用户或 pg_read_server_files 角色。
• \e 依赖 EDITOR 变量：未设置时会退化为 vi，在容器或 CI 中易卡死，脚本里不要用交互式元命令。
• 元命令不写分号：写成 \dt; 时，psql 会把 ; 也当作参数的一部分，导致奇怪的报错。
• \timing on 显示的是服务端执行时间 + 网络往返，不能等同于服务端 EXPLAIN ANALYZE 的结果。

最佳实践

• ~/.psqlrc 统一交互体验，让本地与跳板机行为一致：

# ~/.psqlrc
\set ON_ERROR_STOP on
\set VERBOSITY verbose
\set FETCH_COUNT 1000
\set HISTSIZE 10000
\timing on
\pset null '␀'                 -- NULL 显式标记，避免误读为空字符串
\x auto
\set PROMPT1 '%[%033[1;32m%]%n@%/%R%[%033[0m%]%# '

• 脚本一律使用长选项：--host、--dbname 比 -h、-d 可读性更好，且未来不易与含义变化的短选项冲突。
• 把 application_name 写进 DSN：?application_name=order-worker 能让 DBA 在 pg_stat_activity 中一眼定位问题来源。
• 版本对齐：客户端 psql 版本低于服务端时可能无法识别新元命令（例如 18 引入的 \dconfig 增强），建议客户端版本 ≥ 服务端主版本。
• 危险操作前先 BEGIN：在交互模式下执行 UPDATE/DELETE 前先 BEGIN，确认影响行数后再 COMMIT，出错可 ROLLBACK。
• 用 \o 而不是 shell 重定向保存分析结果：\o 只重定向查询结果，不会把提示符与警告混入文件。

下一节

继续学习：pgAdmin（官方 GUI）

参考链接

• psql — PostgreSQL 交互式终端
• libpq 连接参数
• libpq 环境变量
• pgpass 密码文件