Home

深色模式

PostgreSQL 备份、恢复与导入导出 ​

概述 ​

PostgreSQL 真出问题时，最能救命的通常不是“记住了多少配置项”，而是手里有没有一份能恢复出来的备份。误删数据、迁移机器、升级回滚、导环境做排查，最后都绕不开备份和恢复。

对中小项目来说，最常用的方案通常还是逻辑备份。先把 pg_dump、pg_restore、psql、COPY、\copy 这几样工具用顺，比一开始就上很重的备份体系更实际。

工具区别 ​

  • pg_dump：导出单个数据库的逻辑备份
  • pg_restore：恢复自定义格式或目录格式备份
  • psql：执行纯文本 SQL 备份文件
  • pg_dumpall：导出整个实例的全局对象，例如角色
  • COPY：服务端读写文件，适合表级导入导出
  • \copy：客户端读写文件，适合本机导入导出

多数项目里，高频组合通常是：

  • 日常备份：pg_dump
  • 恢复演练：pg_restore 或 psql
  • 表级导入导出：\copy

整库备份 ​

整库逻辑备份，一个比较常见的命令是：

sh
pg_dump -h 127.0.0.1 -U app_owner -d app -Fc -f app.dump

这里的 -Fc 表示自定义格式。它的好处是：

  • 恢复时更灵活
  • 可以按对象选择性恢复
  • 后续更适合交给 pg_restore

它也顺手带来一个边界：自定义格式通常不是拿 psql -f 直接恢复，而是交给 pg_restore。

如果只是想得到一份可读 SQL，也可以导出纯文本：

sh
pg_dump -h 127.0.0.1 -U app_owner -d app -f app.sql

纯文本看着亲切，但恢复时灵活性不如自定义格式。日常备份通常更推荐 -Fc。

恢复命令 ​

如果备份文件是自定义格式：

sh
createdb -h 127.0.0.1 -U postgres app_restore
pg_restore -h 127.0.0.1 -U postgres -d app_restore app.dump

如果备份文件是普通 SQL：

sh
createdb -h 127.0.0.1 -U postgres app_restore
psql -h 127.0.0.1 -U postgres -d app_restore -f app.sql

恢复前最好先确认目标数据库是不是空的。尤其是做演练时，旧库没清干净，恢复结果很容易让人误判。

本地恢复 ​

如果是把线上导出的备份恢复到本地开发环境，比较常见的需求是：

  • 先删掉目标库里已有对象
  • 不沿用原库的 owner
  • 不恢复原有权限定义

这时候常见命令会写成：

sh
pg_restore \
  --clean \
  --if-exists \
  --no-owner \
  --no-privileges \
  -h 127.0.0.1 \
  -U postgres \
  -d app_restore \
  app.dump

如果对象很多，对自定义格式或目录格式备份，还可以再加 -j 4 这类并行参数加快恢复速度。

拆分导出 ​

迁移或排错时，有时只想要库结构，或者只想要数据。这时候可以拆开：

导结构：

sh
pg_dump -h 127.0.0.1 -U app_owner -d app --schema-only -f app_schema.sql

导数据：

sh
pg_dump -h 127.0.0.1 -U app_owner -d app --data-only -f app_data.sql

这在几种场景里尤其好用：

  • 对比两个环境的表结构
  • 把生产数据脱敏后导进测试环境
  • 只需要恢复少量业务数据，不想重建整套 schema

全局对象 ​

pg_dump 导的是单个数据库，不会把整个实例的角色、表空间和一些全局对象一起打包走。迁移环境时，最好额外导一份：

sh
pg_dumpall --globals-only -U postgres -f globals.sql

很多人第一次迁移时，数据表恢复了，结果应用还是连不上，最后才发现角色没建、权限没补、全局对象没带过去。

表级导出 ​

如果只是导一张表的数据，COPY 很直接：

sql
COPY users TO '/tmp/users.csv' WITH (FORMAT csv, HEADER true);

导入也类似：

sql
COPY users(email, nickname)
FROM '/tmp/users.csv'
WITH (FORMAT csv, HEADER true);

但 COPY 读写的是数据库服务器本机的文件路径。如果没有数据库服务器文件系统权限，或者数据库跑在容器里，不方便直接碰服务端文件，这时更常用的是 \copy：

sh
psql -h 127.0.0.1 -U app_owner -d app -c "\\copy users TO './users.csv' WITH (FORMAT csv, HEADER true)"

\copy 的文件读写发生在客户端本机，日常导入导出通常更方便。

容器备份 ​

如果 PostgreSQL 跑在容器里，又懒得在宿主机额外装客户端工具，也可以直接用容器里的命令：

sh
docker compose exec postgres pg_dump -U postgres -d app -Fc -f /tmp/app.dump

再把文件从容器里拷出来：

sh
docker cp blog-postgres:/tmp/app.dump ./app.dump

这不是唯一做法，但在本地开发环境里很实用。

备份习惯 ​

备份不是生成文件就算结束 ​

真正有价值的是“能不能恢复出来”。至少要定期把备份恢复到测试环境演练一次。

文件名别写成一堆 backup.dump ​

名字里最好带上系统名、环境和时间，例如：

text
app-prod-2026-04-21-020000.dump

朴素一点没关系，关键是别让备份文件看起来像抽盲盒。

客户端工具版本尽量和服务端接近 ​

pg_dump、pg_restore 和服务端主版本越接近，兼容性问题通常越少。备份恢复时，少一点版本惊喜总是好事。

备份脚本 ​

sh
#!/usr/bin/env sh
set -eu

now=$(date +%F-%H%M%S)
out="app-${now}.dump"

pg_dump \
  -h 127.0.0.1 \
  -U app_owner \
  -d app \
  -Fc \
  -f "$out"

脚本不复杂，但比每次临时拼命令可靠得多。真到要恢复的时候，越少临场发挥越稳。

参考 ​