1. 命令行客户端自动化的定位
一句话总结： 数据库 CLI 是「最不会失效的接口」——不依赖驱动版本、不依赖网络库，任何环境都有，适合做运维脚本的最后一层。
应用代码用 ORM 或驱动连数据库，但运维脚本用 CLI 更稳：一台只有 busybox 的容器里也能跑 psql。难点不在「怎么连」，而在错误处理——默认情况下 psql 遇到 SQL 错误会继续执行后面的语句并返回 0，这对脚本是灾难。
# 危险：SQL 报错但退出码是 0
psql -c "INSERT INTO t VALUES (1); SELECT bad_column FROM t;"
echo "退出码: $?" # 可能仍是 0
# 正确：让 psql 遇错即停
psql -v ON_ERROR_STOP=1 -c "INSERT INTO t VALUES (1);"
1.1 三条工程原则
一句话总结： 显式错误处理、幂等设计、连接参数不外泄，是数据库脚本的三条底线。
# 统一入口设置
set -euo pipefail
export PGOPTIONS='-c statement_timeout=30000' # 30 秒语句超时
# 幂等：重复执行不产生副作用
psql -v ON_ERROR_STOP=1 -c "CREATE TABLE IF NOT EXISTS t (id int PRIMARY KEY)"
1.2 客户端能力对比
一句话总结： psql 有元命令与
\copy，mysql 有--batch与LOAD DATA，各自的批量导入路径不同。
# psql：\copy 是客户端侧拷贝，不需要服务端文件权限
psql -c "\copy t FROM 'data.csv' WITH (FORMAT csv, HEADER true)"
# mysql：LOAD DATA 默认走服务端路径，需 local_infile 或 LOAD DATA LOCAL
mysql --local-infile=1 -e "LOAD DATA LOCAL INFILE 'data.csv' INTO TABLE t"
2. 连接参数的安全传递
一句话总结： 密码永远不要写在命令行上——
-p后跟密码、URL 里的明文，都会被ps和日志捕获。
2.1 psql 的 PGPASSFILE
一句话总结： 用 0600 权限的
.pgpass文件存放密码，psql 自动读取，进程列表干净。
# .pgpass 格式: host:port:db:user:password
umask 077
printf '%s:%s:%s:%s:%s\n' "db.internal" 5432 "appdb" "app" "$DB_PASS" > ~/.pgpass
chmod 600 ~/.pgpass
# 连接时不再需要密码参数
psql -h db.internal -U app -d appdb -c "SELECT 1"
# 或指定自定义路径（适合多套凭据）
PGPASSFILE=/run/secrets/pgpass psql -h db -U app -c "SELECT 1"
2.2 服务文件与连接串
一句话总结： PostgreSQL 的连接服务文件（pg_service.conf）把主机、端口、库名、用户名打包成一个别名，脚本只写别名。
# ~/.pg_service.conf
# [appdb]
# host=db.internal
# port=5432
# dbname=appdb
# user=app
psql "service=appdb" -c "SELECT current_database()"
# 通过 PGSERVICEFILE 指定路径
PGSERVICEFILE=/etc/pg_service.conf psql "service=appdb" -c "SELECT 1"
2.3 mysql 的 option file
一句话总结： MySQL 用
--defaults-extra-file指定一个 0600 的 my.cnf 片段，密码从文件读。
# /run/secrets/mysql.cnf
# [client]
# user=app
# password=xxx
# host=db.internal
chmod 600 /run/secrets/mysql.cnf
mysql --defaults-extra-file=/run/secrets/mysql.cnf -e "SHOW DATABASES"
# 用进程替换避免文件落盘
mysql --defaults-extra-file=<(printf '[client]\npassword=%s\n' "$DB_PASS") -e "SELECT 1"
3. 批处理模式与输出格式
一句话总结： 脚本里不要人类可读的表格输出，要机器可解析的分隔符格式。
# psql：无对齐、无表头、字段用竖线分隔
psql -At -F$'\t' -c "SELECT id, name FROM users"
# mysql：--batch 输出 TSV，--skip-column-names 去掉表头
mysql --batch --skip-column-names -e "SELECT id, name FROM users"
# JSON 输出（psql 9.5+）
psql -At -c "SELECT json_agg(row_to_json(u)) FROM users u"
3.1 读取结果到数组
一句话总结： 用
mapfile把逐行结果读进数组，注意-A与-t的组合要配套。
# 逐行读取
mapfile -t ids < <(psql -At -c "SELECT id FROM users WHERE active")
for id in "${ids[@]}"; do
echo "处理用户 $id"
done
# 多字段用 tab 分隔，再切分
while IFS=$'\t' read -r id name; do
printf '用户 %s: %s\n' "$id" "$name"
done < <(psql -At -F$'\t' -c "SELECT id, name FROM users")
3.2 NULL 与空字符串的区别
一句话总结： 批处理模式下 NULL 默认输出为空串，与真正的空字符串无法区分，需要显式处理。
# 把 NULL 输出成特殊标记
psql -At -P null='<NULL>' -c "SELECT name FROM users"
# mysql 用 --raw 与 IFNULL 显式转换
mysql --batch --skip-column-names \
-e "SELECT IFNULL(name, '<NULL>') FROM users"
4. 事务与错误处理
一句话总结： 多条相关写操作必须包在一个事务里，失败整体回滚；psql 用
ON_ERROR_STOP，mysql 用--force的反面。
4.1 显式事务
一句话总结： 把 BEGIN 到 COMMIT 写进一个 SQL 文件，用
-1（single transaction）让 psql 整体包裹。
# psql -1：把整个文件包进一个事务，任何语句失败则全部回滚
psql -v ON_ERROR_STOP=1 -1 -f migrate.sql
# migrate.sql 内容示意
# BEGIN;
# ALTER TABLE users ADD COLUMN status text DEFAULT 'active';
# UPDATE users SET status = 'active' WHERE status IS NULL;
# COMMIT;
4.2 mysql 的事务与错误处理
一句话总结： mysql 默认遇错即停（除非
--force），但 DDL 会隐式提交，迁移脚本要避免混合 DDL 与 DML。
# 关闭自动提交，显式控制
mysql --defaults-extra-file=/run/secrets/mysql.cnf <<'SQL'
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
SQL
# 注意：--force 会忽略错误继续执行，脚本中绝不要加
4.3 失败重试与幂等
一句话总结： 网络抖动导致的连接失败可以重试，SQL 逻辑错误不能重试，两者要区分开。
# 只对连接类错误重试
run_with_retry() {
local max=5 i=1 out
while (( i <= max )); do
out=$(psql -v ON_ERROR_STOP=1 "$@" 2>&1) && return 0
grep -qiE 'could not connect|connection refused|timeout' <<< "$out" || {
printf '%s\n' "$out" >&2; return 1 # 逻辑错误，立即失败
}
echo "连接失败，第 $i 次重试..." >&2
sleep $(( i * 2 )); i=$(( i + 1 ))
done
return 1
}
5. 导出与导入
一句话总结： 逻辑备份用 pg_dump/mysqldump，数据搬运用
\copy/LOAD DATA，两者场景不同不要混用。
# pg_dump 自定义格式，支持并行恢复
pg_dump -Fc -f backup.dump -d appdb
# 恢复：-j 并行，-c 先清空
pg_restore -j 4 -c -d appdb backup.dump
# 纯 SQL 备份（可读、可 grep）
pg_dump --no-owner --no-privileges -f backup.sql -d appdb
5.1 分批导出大表
一句话总结： 大表导出要按主键分片，避免单条 SQL 长时间持锁与内存爆掉。
# 按 id 区间分批导出
min=$(psql -At -c "SELECT min(id) FROM events")
max=$(psql -At -c "SELECT max(id) FROM events")
step=100000
for (( lo=min; lo<=max; lo+=step )); do
hi=$(( lo + step - 1 ))
psql -At -c "\copy (SELECT * FROM events WHERE id BETWEEN $lo AND $hi) \
TO 'events_${lo}_${hi}.csv' WITH (FORMAT csv)"
done
5.2 导入的原子性
一句话总结： 导入前建临时表，校验通过后再原子换表，失败时原表毫发无损。
# 导入到临时表，校验后原子切换
psql -v ON_ERROR_STOP=1 -1 <<'SQL'
CREATE TABLE users_new (LIKE users INCLUDING ALL);
\copy users_new FROM 'users.csv' WITH (FORMAT csv, HEADER true)
-- 行数校验
DO $$
DECLARE n bigint;
BEGIN
SELECT count(*) INTO n FROM users_new;
IF n < 1000 THEN RAISE EXCEPTION '导入行数异常: %', n; END IF;
END $$;
ALTER TABLE users RENAME TO users_old;
ALTER TABLE users_new RENAME TO users;
DROP TABLE users_old;
SQL
5.3 校验与去重
一句话总结： 导入后做行数与校验和对比，是发现截断与编码问题最快的手段。
# 行数对比
src=$(wc -l < users.csv)
dst=$(psql -At -c "SELECT count(*) FROM users")
[[ $(( src - 1 )) -eq "$dst" ]] || echo "行数不匹配: 文件 $src 库 $dst"
# 关键字段校验和
psql -At -c "SELECT md5(string_agg(id::text, ',' ORDER BY id)) FROM users"
6. 慢查询与锁排查
一句话总结： 脚本自动化不只是「跑 SQL」，还包括定期抓取慢查询与阻塞链，把问题在爆发前暴露。
# PostgreSQL：当前正在执行的慢查询
psql -At -F$'\t' -c "
SELECT pid, now() - query_start AS duration, state, left(query, 80)
FROM pg_stat_activity
WHERE state <> 'idle' AND now() - query_start > interval '5 seconds'
ORDER BY duration DESC"
6.1 阻塞链分析
一句话总结：
pg_blocking_pids直接给出阻塞关系，比手工 join 两张视图可靠得多。
# 找出被阻塞的会话及其阻塞者
psql -At -F$'\t' -c "
SELECT blocked.pid AS blocked_pid,
blocker.pid AS blocker_pid,
blocked.query AS blocked_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocker
ON blocker.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0"
6.2 长事务告警
一句话总结： 长事务会阻止 vacuum 回收，超过阈值就告警甚至主动终止。
# 找出超过 5 分钟的事务
psql -At -c "
SELECT pid, now() - xact_start AS age, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
AND now() - xact_start > interval '5 minutes'"
# 谨慎终止（先确认再执行）
# psql -c "SELECT pg_terminate_backend(12345)"
7. 实战：幂等的数据库迁移脚本
一句话总结： 把迁移拆成「版本表 + 顺序执行 + 单事务」三件事，重复执行安全，失败可重跑。
#!/usr/bin/env bash
set -euo pipefail
DB_SERVICE="${DB_SERVICE:-appdb}"
MIGRATIONS_DIR="${MIGRATIONS_DIR:-./migrations}"
# 1. 确保版本表存在
psql "service=$DB_SERVICE" -v ON_ERROR_STOP=1 -q <<'SQL'
CREATE TABLE IF NOT EXISTS schema_migrations (
version text PRIMARY KEY,
applied_at timestamptz NOT NULL DEFAULT now()
);
SQL
# 2. 读取已应用版本
applied=$(psql "service=$DB_SERVICE" -At -c "SELECT version FROM schema_migrations")
# 3. 顺序执行未应用的迁移
for f in $(ls "$MIGRATIONS_DIR"/*.sql | sort); do
ver=$(basename "$f" .sql)
grep -qxF "$ver" <<< "$applied" && { echo "跳过: $ver"; continue; }
echo "应用迁移: $ver"
psql "service=$DB_SERVICE" -v ON_ERROR_STOP=1 -1 -f "$f"
psql "service=$DB_SERVICE" -q \
-c "INSERT INTO schema_migrations (version) VALUES ('$ver')"
done
7.1 迁移的安全检查
一句话总结： 生产环境执行前先
--dry-run打印将要执行的 SQL，人工确认后再跑。
# dry-run：只打印不执行
if [[ "${DRY_RUN:-0}" == "1" ]]; then
echo "将执行: $f"; sed 's/^/ /' "$f"; continue
fi
7.2 回滚设计
一句话总结： 每个迁移配一个 down 脚本，或至少记录「反向操作」注释，出问题时有退路。
# 命名约定：0001_create_users.up.sql / 0001_create_users.down.sql
for f in $(ls "$MIGRATIONS_DIR"/*.up.sql | sort -r); do
ver=$(basename "$f" .up.sql)
if grep -qxF "$ver" <<< "$applied"; then
down="${MIGRATIONS_DIR}/${ver}.down.sql"
[[ -f "$down" ]] && psql "service=$DB_SERVICE" -v ON_ERROR_STOP=1 -1 -f "$down"
psql "service=$DB_SERVICE" -q -c "DELETE FROM schema_migrations WHERE version='$ver'"
break # 一次只回滚一步
fi
done
8. 总结
| 环节 | 要点 |
|---|---|
| 错误处理 | psql 必须 -v ON_ERROR_STOP=1，否则报错也返回 0 |
| 连接参数 | .pgpass、pg_service.conf、--defaults-extra-file 三选一 |
| 输出格式 | -At -F 或 --batch --skip-column-names，不要人类表格 |
| 事务 | psql -1 整体包裹，mysql 注意 DDL 隐式提交 |
| 重试 | 只对连接类错误重试，逻辑错误立即失败 |
| 备份 | pg_dump 自定义格式 + pg_restore 并行恢复 |
| 大表 | 按主键分片导出，临时表 + 原子换表导入 |
| 迁移 | 版本表 + 顺序执行 + 单事务，支持 dry-run 与回滚 |
数据库脚本的可靠性来自**「显式声明失败」**：让每一个客户端都遇错即停，让每一个写操作都在事务里，让每一次导入都能被回滚。把这三点做到，脚本才敢在凌晨无人值守时跑。数据库之后，另一类高频批处理是媒体文件——图片与视频的批量转码，这也是下一篇的主题。
延伸阅读
继续阅读
探索更多技术文章
浏览归档，发现更多关于系统设计、工具链和工程实践的内容。