PostgreSQL数据库备份与还原方法总结

摘要：本文详细介绍了PostgreSQL数据库的多种备份和还原方法，包括逻辑备份、物理备份以及图形工具的使用。涵盖了常用命令、格式说明及注意事项，适用于不同规模和场景下的数据管理需求。

一、逻辑备份（推荐用于小数据量或结构迁移）

1. 使用 pg_dump 备份单个数据库

pg_dump -U 用户名 数据库名 > 备份文件.sql

示例：

pg_dump -U postgres mydb > mydb_backup.sql

2. 使用 pg_dumpall 备份所有数据库（包括角色等全局信息）

pg_dumpall -U 用户名 > 全部备份.sql

示例：

pg_dumpall -U postgres > full_backup.sql

3. 还原逻辑备份

psql -U 用户名 -d 目标数据库名 < 备份文件.sql

示例：

psql -U postgres -d mydb_restore < mydb_backup.sql

二、物理备份（适用于大数据量、生产环境）

1. 使用 pg_basebackup 做基础备份

pg_basebackup -U 用户名 -D /备份路径/ -Ft -z -P --wal-method=stream

示例：

pg_basebackup -U replicator -D /backup/mydb_base -Ft -z -P --wal-method=stream

2. 搭配归档日志（WAL Archiving）实现 PITR（时间点恢复）

wal_level = replica
archive_mode = on
archive_command = 'cp %p /path/to/archive/%f'

3. 物理还原步骤简要：

1. 停止 PostgreSQL 服务。
2. 清空数据目录。
3. 解压 basebackup 到数据目录。
4. 创建 recovery.conf（PostgreSQL 12 及以前）或使用 standby.signal + 修改 postgresql.conf 来指定恢复目标。
5. 启动 PostgreSQL 服务，自动进入恢复状态。

三、使用图形工具备份（如 pgAdmin）

1. 备份：

• 右键点击数据库 → Backup → 选择格式（plain, custom, directory, tar）→ 导出文件。

2. 还原：

• 新建数据库 → 右键 Restore → 选择备份文件 → 执行恢复。

四、常用备份格式说明

格式	说明
plain	纯 SQL 脚本，默认格式
custom (-Fc)	二进制格式，支持压缩、选择性还原
directory (-Fd)	最适合大数据库，可并行恢复
tar (-Ft)	tar 包格式，便于传输

五、一些常用命令组合

1. 带压缩的 custom 格式备份

pg_dump -U postgres -Fc dbname > dbname.dump

2. 使用 dump 文件还原

pg_restore -U postgres -d dbname dbname.dump

3. 查看 dump 文件内容（不还原）

pg_restore -l dbname.dump

六、注意事项

• 确保你使用的用户有足够权限访问数据库。
• 生产环境中建议定期做逻辑+物理备份，并测试还原流程。
• 使用版本兼容的 PostgreSQL 工具进行还原（例如用 PG13 的 pg_restore 还原 PG12 的备份可能有问题）。