上一篇
pg数据库备份恢复怎么做？关键步骤和注意事项有哪些？
PostgreSQL（简称PG）数据库的备份与恢复是保障数据安全的核心操作，涉及多种策略和工具，需根据业务场景选择合适的方法，以下是关于PG数据库备份与恢复的详细说明，涵盖常用方式、操作步骤及注意事项。
备份类型与工具选择
PostgreSQL的备份可分为物理备份和逻辑备份,两者原理和适用场景不同：
物理备份（文件级备份）
物理备份直接复制数据库文件（数据文件、 WAL日志、配置文件等），恢复时直接替换文件即可，速度快，适合全量备份或大型数据库。
- 工具：
- pg_basebackup：官方提供的流式备份工具，支持在线备份（无需停库），可备份整个集群或指定数据库，常用于搭建流复制从库。
- 文件系统级备份：通过tar、rsync等工具直接复制数据目录（$PGDATA），需确保数据库处于一致状态（如使用pg_start_backup()和pg_stop_backup()）。
逻辑备份（SQL级备份）
逻辑备份将数据库对象（表、索引、函数等）导出为SQL脚本，恢复时通过执行脚本重建数据，灵活性高，适合小型数据库或数据迁移。
- 工具：
- pg_dump：官方逻辑备份工具，支持自定义备份范围（单个表、schema或全库），可输出为自定义格式（Fc）、目录格式（Fd）或纯SQL（Fp）。
- pg_dumpall：备份所有数据库（含全局对象如用户、角色），适合全实例逻辑备份。
备份操作实践
使用pg_basebackup进行物理备份
# 基础全量备份（需在从库或配置流复制后执行） pg_basebackup h 主库IP p 5432 U repluser D /backup/$(date +%Y%m%d) Ft z P # 参数说明：h主机、p端口、U用户、D备份目录、Ft tar格式、z压缩、P显示进度
使用pg_dump进行逻辑备份
# 备份单个表（输出为自定义格式，压缩） pg_dump h localhost U postgres t employees mydb Fc f employees.dump # 备份整个数据库（输出为纯SQL） pg_dump h localhost U postgres mydb Fp > mydb_backup.sql
定时备份（结合cron）
# 每日凌晨2点执行全量逻辑备份 0 2 * * * /usr/bin/pg_dump U postgres mydb Fp | gzip > /backup/mydb_$(date +%Y%m%d).sql.gz
恢复操作实践
物理恢复（基于pg_basebackup）
- 步骤：
- 停止数据库：pg_ctl stop D $PGDATA m fast
- 替换数据文件：mv $PGDATA $PGDATA.bak && cp r /backup/20251010/ $PGDATA
- 修改权限：chown R postgres:postgres $PGDATA
- 启动数据库：pg_ctl start D $PGDATA
逻辑恢复（基于pg_dump）
- 恢复单个表： # 先删除原表（可选） psql U postgres d mydb c "DROP TABLE employees;" # 从自定义格式恢复 pg_restore h localhost U postgres d mydb employees.dump
- 恢复整个数据库： # 创建新数据库（若不存在） createdb U postgres mydb_restore # 从纯SQL恢复 psql U postgres d mydb_restore f mydb_backup.sql
备份策略建议
| 备份类型 | 适用场景 | 优点 | 缺点 |
|---|---|---|---|
| 物理备份 | 大型数据库、全量备份、灾难恢复 | 速度快、恢复简单 | 占用空间大、跨版本兼容性差 |
| 逻辑备份 | 小型数据库、数据迁移、部分表恢复 | 灵活性高、跨版本兼容 | 速度慢、不支持时间点恢复 |
| WAL日志备份 | 结合物理备份实现时间点恢复 | 数据零丢失 | 需定期归档WAL，配置复杂 |
推荐策略：全量物理备份（每日）+ 增量WAL备份（实时）+ 逻辑备份（每周）,兼顾恢复速度与数据安全性。
注意事项
- 备份前检查：确保磁盘空间充足，数据库无长时间运行的事务。
- 权限控制：备份用户需具有SUPERUSER或pg_read_all_files权限。
- 备份验证：定期恢复测试备份文件，确保可用性。
- 加密存储：敏感数据备份需加密（如使用pgcrypto或文件系统加密）。
FAQs
Q1: 如何通过WAL日志实现时间点恢复（PITR）？
A: 需先完成全量物理备份，然后启用归档模式（archive_mode = on），配置archive_command归档WAL日志，恢复时，将全量备份恢复到指定时间点前的位置，再应用对应的WAL日志：
# 1. 停止数据库，恢复全量备份 pg_ctl stop m fast cp r /backup/full_backup /data/pgdata # 2. 恢复到特定时间点（需在recovery.conf中设置） echo "restore_command = 'cp /archive/%f %p'" > /data/pgdata/recovery.conf echo "recovery_target_time = '20251010 14:30:00'" >> /data/pgdata/recovery.conf # 3. 启动数据库，进入恢复模式 pg_ctl start D /data/pgdata
Q2: pg_dump备份的文件损坏，如何处理？
A: 首先检查备份文件完整性：
- 若为纯SQL文件：使用head或tail查看文件头尾是否正常，或尝试执行psql f backup.sql l（仅检查语法，不执行数据）。
- 若为自定义格式（Fc）：使用pg_restore l backup.dump列出备份内容，若报错则文件可能损坏。
预防措施：备份后使用md5sum生成校验码，定期校验文件完整性；启用压缩减少传输损坏概率。
