PostgreSQL在CentOS上的备份恢复流程

一、备份流程

PostgreSQL在CentOS上的备份主要分为逻辑备份（基于SQL语句或自定义格式）和物理备份（基于数据目录）两类，以下是具体操作：

1. 逻辑备份（推荐日常使用）

逻辑备份通过pg_dump工具实现，将数据库结构和数据导出为SQL脚本或自定义格式文件，适用于跨版本迁移或部分数据恢复。

常用命令示例：

• 备份单个数据库为SQL格式（可读性强，适合小数据库）：

  su - postgres -c "pg_dump -U postgres -d 数据库名 -f /path/to/backup/backup_file.sql"
  

• 备份单个数据库为自定义格式（支持压缩、并行，适合大数据库）：

  su - postgres -c "pg_dump -U postgres -d 数据库名 -F c -b -v -f /path/to/backup/backup_file.dump"
  

• 备份所有数据库（包括角色、表空间等集群级信息）：

  su - postgres -c "pg_dumpall -U postgres -f /path/to/backup/all_databases.sql"
  

• 压缩备份文件（节省存储空间）：

  su - postgres -c "pg_dump -U postgres -d 数据库名 | gzip > /path/to/backup/backup_file.sql.gz"
  

• 并行备份（利用多核CPU加速，--jobs指定并行任务数）：

  su - postgres -c "pg_dump -U postgres -d 数据库名 --jobs 4 -f /path/to/backup/backup_file.dump"
  

2. 物理备份（推荐时间点恢复）

物理备份通过pg_basebackup工具复制整个数据目录（包括WAL日志），适用于需要精确恢复到某一时间点的场景（如灾难恢复）。

常用命令示例：

• 备份整个数据目录（-F t表示tar格式，-P显示进度，-X stream流式传输WAL日志）：

  su - postgres -c "pg_basebackup -U postgres -D /path/to/backup -F t -P -X stream -f backup.tar"
  

3. 自动化备份（可选）

通过cron定时任务定期执行备份脚本，确保数据定期保存。

脚本示例（/path/to/backup_postgresql.sh）：

#!/bin/bash
USER="postgres"
DB_NAME="数据库名"
BACKUP_DIR="/path/to/backups"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_${DATE}.dump"

# 执行备份
su - postgres -c "pg_dump -U $USER -d $DB_NAME -F c -b -v -f $BACKUP_FILE"

# 删除7天前的备份（可选）
find $BACKUP_DIR -type f -name "${DB_NAME}_*.dump" -mtime +7 -exec rm {} \;

添加定时任务（每天凌晨2点执行）：

crontab -e

添加以下内容：

0 2 * * * /path/to/backup_postgresql.sh

二、恢复流程

恢复流程需根据备份类型选择对应工具，以下是具体操作：

1. 准备工作

• 确认备份文件完整性：检查备份文件是否存在、大小是否合理（可通过ls -lh查看）。
• 停止PostgreSQL服务（逻辑恢复无需停止，物理恢复建议停止）：

  sudo systemctl stop postgresql
  

• 创建目标数据库（逻辑恢复需先创建空数据库，物理恢复无需创建）：

  su - postgres -c "createdb -U postgres 目标数据库名"
  

2. 逻辑恢复

逻辑恢复通过psql（SQL格式）或pg_restore（自定义格式）工具将备份数据导入数据库。

常用命令示例：

• 恢复SQL格式备份：

  su - postgres -c "psql -U postgres -d 目标数据库名 -f /path/to/backup/backup_file.sql"
  

• 恢复自定义格式备份：

  su - postgres -c "pg_restore -U postgres -d 目标数据库名 -v /path/to/backup/backup_file.dump"
  

• 恢复所有数据库（pg_dumpall生成的备份）：

  su - postgres -c "psql -U postgres -f /path/to/backup/all_databases.sql"
  

3. 物理恢复

物理恢复通过pg_basebackup工具覆盖现有数据目录（需停止服务），适用于灾难恢复。

常用命令示例：

• 覆盖现有数据目录（-R自动配置WAL日志回放）：

  su - postgres -c "pg_basebackup -U postgres -D /var/lib/pgsql/data -F t -P -X stream -R -f /path/to/backup/backup.tar"
  

  注：/var/lib/pgsql/data为PostgreSQL默认数据目录，需根据实际路径修改。

4. 恢复WAL日志（可选，时间点恢复）

若备份后生成了WAL日志（如物理备份后有新数据），需通过pg_waldump查看日志并重放，实现时间点恢复（PITR）。

常用命令示例：

• 查看WAL日志内容：

  su - postgres -c "pg_waldump /path/to/wal_file > wal_log.txt"
  

• 重放WAL日志（需结合物理备份，将WAL日志应用到备份数据目录）：

  su - postgres -c "pg_basebackup -U postgres -D /var/lib/pgsql/data -F t -P -X stream -R --replay /path/to/wal_file"
  

5. 启动服务并验证

• 启动PostgreSQL服务：

  sudo systemctl start postgresql
  

• 验证数据完整性：

  su - postgres -c "psql -U postgres -d 目标数据库名 -c '\l'"  # 列出所有表
  su - postgres -c "psql -U postgres -d 目标数据库名 -c 'SELECT COUNT(*) FROM 表名;'"  # 检查数据行数
  

注意事项

1. 权限问题：所有备份/恢复操作需以postgres用户或root用户执行，避免权限不足。
2. 备份存储：备份文件需存放在安全位置（如异地服务器、云存储），避免单点故障。
3. 测试恢复：定期在测试环境中模拟恢复流程，确保备份文件可用。
4. 监控性能：备份时选择低峰时段，避免影响数据库正常运行。
5. 版本兼容性：逻辑备份的数据库版本需与恢复环境的版本兼容（如PostgreSQL 15备份的文件可在15及以上版本恢复）。