📚 课程目录第3章 psql与元命令自动化基础

3. psql与元命令自动化基础

psql 不只是交互式客户端，它同时是一个可脚本化的数据库执行引擎。日常巡检、批量授权、跨库统计、定时导出等任务，都可以通过 psql 的非交互模式配合变量、元命令和输出格式控制来完成。理解这三者的协作方式，是编写可靠运维脚本的基础。

非交互模式：让 psql 成为脚本执行器

psql 的非交互模式主要通过 -c、-f 和 -t 等参数实现。-c 用于执行单条命令或分号分隔的多条语句，执行完立即退出；-f 用于执行 SQL 文件，适合较长的巡检脚本；-t 只输出元组数据，去掉表头和行数统计，便于后续用 awk、grep 处理。

psql -h 10.0.0.12 -p 5432 -U monitor -d postgres \
  -v ON_ERROR_STOP=1 \
  -c "SELECT now(), pg_is_in_recovery();"

这里 -v ON_ERROR_STOP=1 非常关键。默认情况下 psql 遇到 SQL 错误会继续执行后续语句，脚本可能“看似成功”却漏掉了失败步骤。开启后，一旦出错立即以非零状态退出，配合 shell 的 set -e 或 || exit 1 就能让巡检脚本在异常时及时中断。

连接参数建议集中管理。除了命令行显式指定，也可以使用 PGHOST、PGPORT、PGUSER、PGDATABASE 环境变量，或通过 ~/.pgpass 保存密码，避免在脚本中硬编码凭据。对于需要连接多个实例的巡检，通常把连接信息写入配置文件，再由 shell 循环调用 psql。

变量：把脚本参数注入 SQL

psql 变量用 -v name=value 定义，在 SQL 中通过 :'name' 或 :"name" 引用。:'name' 会加单引号，适合字符串值；:"name" 会加双引号，适合标识符。这个区别在动态表名、模式名场景中尤其重要。

psql -d appdb \
  -v schema_name=public \
  -v min_size=1048576 \
  -c "SELECT relname, pg_total_relation_size(relid) AS bytes
      FROM pg_stat_user_tables
      WHERE schemaname = :'schema_name'
        AND pg_total_relation_size(relid) > :min_size
      ORDER BY bytes DESC;"

变量还可以在 SQL 文件内部用 \set 定义，或通过 \if 做条件分支。例如巡检脚本中根据是否为主库决定是否检查复制延迟：

\set is_primary `psql -tAc "SELECT NOT pg_is_in_recovery();"`
\if :is_primary
  SELECT client_addr, state, replay_lag FROM pg_stat_replication;
\else
  SELECT now() - pg_last_xact_replay_timestamp() AS replay_delay;
\endif

注意反引号中的命令会在 psql 启动时执行一次，适合获取环境状态。变量值不会自动转义，若来自外部输入，应确保来源可信，避免 SQL 注入风险。

元命令：控制执行流程与输出

元命令以反斜杠开头，是 psql 特有的控制指令。自动化脚本中最常用的包括：

\timing：输出每条语句的执行耗时，用于巡检慢查询。
\pset：设置输出格式，如 \pset format csv、\pset border 0。
\o：将后续查询结果重定向到文件。
\copy：执行 COPY 并支持客户端文件，适合批量导入导出。
\gexec：把查询结果当作 SQL 执行，常用于批量维护。
\watch：周期性重复执行查询，适合实时观察。

其中 \gexec 是批量操作的利器。例如需要为所有超过 10GB 的表执行 VACUUM：

SELECT format('VACUUM (ANALYZE) %I.%I;', schemaname, relname)
FROM pg_stat_user_tables
WHERE pg_total_relation_size(relid) > 10737418240
\gexec

这里先用 format() 生成 SQL 文本，再用 \gexec 逐条执行。相比 shell 循环拼接，这种方式在数据库内部完成对象枚举，减少了往返和转义问题。

输出格式控制：让结果可被程序消费

巡检脚本的输出通常要交给监控系统或日志分析，因此格式必须稳定。psql 提供多种输出模式：

模式	设置方式	适用场景
对齐文本	默认	人工阅读
无对齐	`-A` 或 `\pset format unaligned`	字段分隔解析
CSV	`--csv` 或 `\pset format csv`	导入表格工具
HTML	`-H`	生成报告
仅元组	`-t`	取值赋值给变量

推荐组合是 -A -F ',' -t，即无对齐、逗号分隔、仅数据行。这样输出可以直接被 awk 按逗号切分，或写入 CSV 文件。若字段本身可能包含逗号，应改用 --csv，它会按 CSV 规范处理引号转义。

psql -d appdb -A -F ',' -t -c "
SELECT datname, numbackends, xact_commit, blks_read
FROM pg_stat_database
WHERE datname NOT IN ('template0','template1');" \
> /var/log/pg_inspect/db_stats_$(date +%F).csv

对于需要生成巡检报告的场合，可以用 \pset 切换格式，把多个查询结果分别写入不同文件：

\pset format csv
\o /tmp/conn.csv
SELECT pid, usename, state, query_start FROM pg_stat_activity WHERE state <> 'idle';
\o /tmp/locks.csv
SELECT locktype, mode, granted, pid FROM pg_locks WHERE NOT granted;
\o

把三者组合成巡检脚本

下面是一个可直接参考的巡检脚本骨架，它把非交互模式、变量和输出控制组合在一起。脚本接收实例地址和数据库名作为参数，输出 CSV 格式的巡检结果，并在任一查询失败时退出。

#!/bin/bash
set -euo pipefail

HOST="${1:?usage: inspect.sh host dbname}"
DB="${2:?usage: inspect.sh host dbname}"
OUT="/var/log/pg_inspect/${HOST}_$(date +%F_%H%M).csv"

psql -h "$HOST" -U monitor -d "$DB" \
  -v ON_ERROR_STOP=1 \
  -A -F ',' -t \
  -c "SELECT 'db_size', pg_database_size(current_database());" \
  -c "SELECT 'conn', count(*) FROM pg_stat_activity;" \
  -c "SELECT 'long_query', count(*) FROM pg_stat_activity
      WHERE state='active' AND now()-query_start > interval '5 min';" \
  -c "SELECT 'bloat_top', relname, n_dead_tup
      FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;" \
  > "$OUT"

echo "inspection written to $OUT"

执行后，输出文件每行形如 db_size,12345678、conn,42，监控系统可以按第一列指标名解析。若某条查询报错，脚本会因 ON_ERROR_STOP 立即失败，不会产生半截结果。

在 CI 或定时任务中运行 psql 脚本时，建议同时设置 PGCONNECT_TIMEOUT，避免网络异常导致任务长时间挂起。对于需要密码的场景，优先使用 .pgpass 或连接服务文件，而不是在命令行传递密码。

常见陷阱与规避

第一，变量引用位置错误。:'var' 只能出现在 SQL 中，不能用于元命令参数；元命令中引用变量需用 :'var' 的展开形式或 shell 变量。第二，-c 多条语句共享同一会话，但事务边界需显式控制；若希望整体原子性，应在 SQL 中写 BEGIN; ... COMMIT;。第三，输出重定向与 \o 混用时，注意 \o 会覆盖 shell 重定向，脚本中应统一选择一种方式。第四，-t 会去掉列名，若后续需要列名，应改用 --csv 并保留表头。

掌握这些基础后，psql 就能从“手动敲命令的工具”变成“可编排、可审计、可复用的运维执行层”。后续章节中的批量操作、巡检报告和任务治理，都会建立在这一层之上。