PostgreSQL 逻辑备份，以及一次恢复演练

备份这件事，没演练过就等于没有。我们一直有每日 pg_dump， 但从来没人真的恢复过。上个月做了一次，才发现三个问题： 权限没带、序列没重置、还有扩展依赖。

备份命令

用自定义格式（-Fc）而不是纯 SQL，原因是它压缩、可以选择性恢复， 而且 pg_restore -l 能先看清里面有什么。

pg_dump -h 127.0.0.1 -U postgres -Fc -Z6 \
        --no-owner --no-privileges \
        -f /backup/app-$(date +%F).dump app

--no-owner --no-privileges 是为了让备份能恢复到 另一套角色体系里，不然还原时报一堆 role does not exist。

保留 14 天

find /backup -name 'app-*.dump' -mtime +14 -delete

放在 cron 里，顺手把执行结果写日志。-delete 之前建议先用 -print 跑一遍，确认匹配范围，别把当天那份也删了。

演练：恢复到临时库

绝不覆盖生产库。新建一个 app_restore_test，把 dump 灌进去， 然后跑几条业务查询比对行数：

createdb -h 127.0.0.1 -U postgres app_restore_test
pg_restore -h 127.0.0.1 -U postgres \
  --no-owner --no-privileges \
  -d app_restore_test /backup/app-2026-09-07.dump
psql -h 127.0.0.1 -U postgres -d app_restore_test \
  -c "select count(*), max(created_at) from orders;"

踩到的三个问题

1. 恢复前要先看目录

pg_restore -l /backup/app-2026-09-07.dump | head -30

这一步能提前发现 dump 里有没有你不想要的东西（比如某个巨型日志表）， 也能确认扩展对象是否包含在内。

2. 扩展要先装

dump 里带 CREATE EXTENSION，但目标库如果没装那个扩展的 .so，恢复会在中途失败，而且前面已经建好的表不会回滚。 正确顺序是：先 CREATE EXTENSION IF NOT EXISTS，再 restore， 或者用 --exit-on-error 让它停下来别继续往下灌。

3. 序列值不会自动跟上

数据行数对得上，但插入时主键冲突——因为 setval 没执行。 恢复完手动补一遍：

select setval(pg_get_serial_sequence('orders','id'),
              coalesce(max(id),1), max(id) is not null)
from orders;

把演练也自动化

现在每周日凌晨自动跑一次：建临时库 → restore → 比对各表行数 → 删库。 行数差异超过阈值就推告警。这样「备份能不能用」不再是靠信心。

psql -d app_restore_test -Atc \
  "select relname, n_live_tup from pg_stat_user_tables order by 1" > /tmp/restore_counts.txt

备份的价值 = 成功恢复的次数，不是 dump 文件的体积。