PostgreSQL / 系统课时
使用 psql 恢复 SQL 备份
使用 psql 恢复 SQL 备份
纯文本备份是一组 SQL 语句,应由 psql 执行。本课将创建目标数据库、启用遇错即停、记录恢复日志,并在导入后检查对象和数据是否完整。
学习目标
- 使用
psql恢复纯文本备份 - 让恢复过程在首个 SQL 错误处停止
- 通过日志和查询验证恢复结果
准备干净的目标数据库
恢复前确认目标数据库是否允许覆盖。对演练环境,最安全的方式通常是新建空库;生产环境不得在未确认影响范围时直接执行备份脚本。
bash
createdb -h <HOST> -p <PORT> -U postgres --owner=app_owner --encoding=UTF8 appdb_restore
psql -h <HOST> -p <PORT> -U postgres -d appdb_restore -c "select current_database(), pg_encoding_to_char(encoding) from pg_database where datname = current_database();"预期输出
text
current_database | pg_encoding_to_char
------------------+---------------------
appdb_restore | UTF8
(1 row)若备份文件包含 CREATE DATABASE,应连接到 postgres 等维护数据库执行;普通 pg_dump -Fp -d appdb 生成的脚本通常恢复到预先创建的目标库。
执行恢复并遇错即停
使用 -v ON_ERROR_STOP=1 可让 psql 在第一个 SQL 错误后返回非零退出码,避免后续语句继续执行而形成部分成功状态。
bash
psql -h <HOST> -p <PORT> -U postgres -d appdb_restore -v ON_ERROR_STOP=1 -f appdb.sql > restore.log 2>&1
echo $?
tail -n 5 restore.log预期输出
text
0
<RESTORE_MESSAGES>退出码 0 表示脚本执行完成,但仍需做业务校验。<RESTORE_MESSAGES> 可能包含 CREATE TABLE、COPY、ALTER TABLE 等实际输出。
定位恢复错误
若退出码非零,先查看日志中首个 ERROR,不要只处理最后一条连锁错误。
bash
grep -n -m 1 "ERROR:" restore.log预期输出
text
<LINE>:psql:appdb.sql:<SOURCE_LINE>: ERROR: <ERROR_MESSAGE>常见原因包括目标库已有同名对象、缺少角色、扩展未安装、编码不兼容或对象属主不存在。应根据首个错误修复环境后重建空库并重新恢复,避免在状态不明的半成品数据库上反复续跑。
验证对象和数据
恢复完成后至少检查 schema、关键表、行数和约束。下面的查询以 public.orders 为例,实际表名应替换为业务关键对象。
bash
psql -h <HOST> -p <PORT> -U postgres -d appdb_restore -v ON_ERROR_STOP=1 -c "select to_regclass('public.orders') as orders_table;" -c "select count(*) as order_count from public.orders;"预期输出
text
orders_table
--------------
orders
(1 row)
order_count
-------------
<COUNT>
(1 row)<COUNT> 应与备份时记录的基线一致。仅看到表存在并不能证明数据、索引、约束和权限都已正确恢复。
控制事务边界
psql --single-transaction 可尝试把整个脚本放入一个事务中,失败时整体回滚,但脚本中不能包含某些不允许在事务块中执行的语句。使用前要在演练环境验证。
bash
psql -h <HOST> -p <PORT> -U postgres -d appdb_restore -v ON_ERROR_STOP=1 --single-transaction -f appdb.sql预期结果
text
全部语句成功时统一提交;任一可回滚语句失败时事务回滚并返回非零退出码。按自己的节奏,确认真正理解。