印刻数据库

外观

PostgreSQL 备份实施与验证 ​

备份实施是将备份策略转化为实际操作的关键步骤，而备份验证则确保备份的可靠性和可用性。本文将详细介绍PostgreSQL备份实施的具体步骤、验证方法和自动化脚本，帮助DBA建立可靠的备份系统。

备份实施前的准备工作 ​

在实施备份前，需要完成以下准备工作：

  • 环境检查：验证PostgreSQL服务状态、版本和配置
  • 权限配置：创建专门的备份用户，授予适当权限
  • 存储准备：确保备份存储有足够空间和适当的访问权限
  • 网络配置：如果使用远程备份，确保网络连接稳定
  • 工具准备：安装和配置必要的备份工具

创建备份用户 ​

sql
-- 创建备份用户
CREATE ROLE backupuser WITH REPLICATION LOGIN PASSWORD 'strongpassword';

-- 配置pg_hba.conf
host    replication     backupuser     192.168.1.0/24        md5
host    all             backupuser     192.168.1.0/24        md5

物理备份实施 ​

物理备份是直接复制数据库的物理文件，适用于大规模数据库和快速恢复。

使用pg_basebackup实施全量备份 ​

pg_basebackup是PostgreSQL内置的物理备份工具，支持热备份。

基本用法 ​

bash
# 基本全量备份
pg_basebackup -h localhost -p 5432 -U backupuser -D /pgbackup/full_backup -F p -X stream -z -v

# 详细参数说明
# -h：数据库主机
# -p：数据库端口
# -U：备份用户
# -D：备份目录
# -F p：使用plain格式，直接复制文件
# -X stream：流式复制WAL文件
# -z：启用压缩
# -v： verbose模式，显示详细信息

生产环境配置示例 ​

bash
#!/bin/bash

# 备份配置
BACKUP_DIR="/pgbackup"
DATE=$(date +%Y%m%d_%H%M%S)
DB_HOST="localhost"
DB_PORT="5432"
BACKUP_USER="backupuser"
BACKUP_NAME="full_backup_${DATE}"

# 创建备份目录
mkdir -p ${BACKUP_DIR}/${BACKUP_NAME}

# 执行全量备份
pg_basebackup -h ${DB_HOST} -p ${DB_PORT} -U ${BACKUP_USER} \
  -D ${BACKUP_DIR}/${BACKUP_NAME} \
  -F p \
  -X stream \
  -z \
  --checkpoint=fast \
  --no-sync \
  -v

# 同步数据到磁盘
sync

# 创建软链接指向最新备份
ln -sf ${BACKUP_DIR}/${BACKUP_NAME} ${BACKUP_DIR}/latest_backup

# 保留最近14天的备份
find ${BACKUP_DIR} -name "full_backup_*" -type d -mtime +14 -exec rm -rf {} \;

使用pg_probackup实施增量备份 ​

pg_probackup是PostgreSQL的高级备份工具，支持增量和差异备份。

安装pg_probackup ​

bash
# 在CentOS/RHEL上安装
yum install -y pg_probackup15

# 在Debian/Ubuntu上安装
apt-get install -y pg-probackup-15

初始化备份目录 ​

bash
pg_probackup-15 init -D /pgbackup/pg_probackup

注册数据库实例 ​

bash
pg_probackup-15 add-instance -D /pgbackup/pg_probackup -i main -P "host=localhost user=backupuser port=5432"

执行全量备份 ​

bash
pg_probackup-15 backup -D /pgbackup/pg_probackup -i main -b full -j 4 -Z lz4

执行增量备份 ​

bash
# 基于上次备份执行增量备份
pg_probackup-15 backup -D /pgbackup/pg_probackup -i main -b incremental -j 4 -Z lz4

使用Barman实施远程备份 ​

Barman是一个集中式备份管理工具，支持远程备份和恢复。

安装和配置Barman ​

bash
# 安装Barman
apt-get install -y barman

# 配置Barman用户免密码登录
su - barman
ssh-keygen -t rsa
ssh-copy-id postgres@pg-server

配置Barman ​

ini
# /etc/barman.conf
[barman]
barman_home = /var/lib/barman
barman_user = barman
log_file = /var/log/barman/barman.log
compression = gzip
retention_policy = RECOVERY WINDOW OF 7 DAYS

# 数据库实例配置
[pg-server]
description = "PostgreSQL Server"
conninfo = host=pg-server user=postgres dbname=postgres
backup_method = postgres
streaming_conninfo = host=pg-server user=streaming_barman
streaming_archiver = on

执行备份 ​

bash
# 执行全量备份
barman backup pg-server

# 查看备份状态
barman list-backups pg-server

逻辑备份实施 ​

逻辑备份是导出数据库的逻辑结构和数据，适用于小规模数据库和选择性恢复。

使用pg_dump实施单库备份 ​

pg_dump用于备份单个数据库，支持多种格式。

基本用法 ​

bash
# 备份为SQL文本格式
pg_dump -h localhost -U backupuser -d mydb -f /pgbackup/mydb.sql

# 备份为自定义格式，支持压缩和并行
pg_dump -h localhost -U backupuser -d mydb -F c -j 4 -Z 6 -f /pgbackup/mydb.dump

# 详细参数说明
# -d：数据库名称
# -f：输出文件
# -F c：自定义格式
# -j 4：使用4个并行进程
# -Z 6：压缩级别6

选择性备份 ​

bash
# 备份特定表
pg_dump -h localhost -U backupuser -d mydb -t table1 -t table2 -f /pgbackup/mydb_tables.sql

# 备份特定模式
pg_dump -h localhost -U backupuser -d mydb -n schema1 -f /pgbackup/mydb_schema.sql

# 仅备份数据，不备份结构
pg_dump -h localhost -U backupuser -d mydb -a -f /pgbackup/mydb_data.sql

# 仅备份结构，不备份数据
pg_dump -h localhost -U backupuser -d mydb -s -f /pgbackup/mydb_schema_only.sql

使用pg_dumpall实施全实例备份 ​

pg_dumpall用于备份整个PostgreSQL实例，包括所有数据库、角色和表空间。

基本用法 ​

bash
# 备份全实例到SQL文件
pg_dumpall -h localhost -U backupuser -f /pgbackup/full_instance.sql

# 备份全实例，启用压缩
pg_dumpall -h localhost -U backupuser | gzip > /pgbackup/full_instance.sql.gz

WAL归档配置与实施 ​

WAL归档是实现PITR（时间点恢复）的基础，应持续启用。

配置WAL归档 ​

sql
-- 在postgresql.conf中配置
wal_level = replica          -- 或logical，取决于需要
archive_mode = on
archive_command = 'rsync -a %p backupuser@backup-server:/walarchive/%f 2>> /var/log/postgresql/archive.log'
archive_timeout = 60         -- 确保WAL文件定期归档，单位秒
max_wal_senders = 10         -- 允许的最大WAL发送进程数
wal_keep_size = 1GB          -- 保留的WAL大小

使用pg_receivewal实施WAL流复制 ​

pg_receivewal用于流式接收WAL文件，适合高可靠性要求的场景。

基本用法 ​

bash
# 基本用法
pg_receivewal -h localhost -U backupuser -D /walarchive -v -n

# 详细参数说明
# -D：WAL归档目录
# -v： verbose模式
# -n：非阻塞模式

生产环境配置 ​

bash
#!/bin/bash

# WAL归档配置
WAL_DIR="/walarchive"
DB_HOST="localhost"
DB_PORT="5432"
BACKUP_USER="backupuser"

# 确保WAL目录存在
mkdir -p ${WAL_DIR}

# 启动WAL流复制
pg_receivewal -h ${DB_HOST} -p ${DB_PORT} -U ${BACKUP_USER} \
  -D ${WAL_DIR} \
  --status-interval=30 \
  --verbose \
  --no-loop

备份验证 ​

备份验证是确保备份可用性的关键步骤，应定期执行。

物理备份验证 ​

使用pg_checksums验证数据完整性 ​

bash
# 检查数据目录的校验和
pg_checksums -c -D /pgbackup/full_backup

使用pg_probackup验证备份 ​

bash
# 验证备份完整性
pg_probackup-15 validate -D /pgbackup/pg_probackup -i main -b <backup_id>

使用Barman验证备份 ​

bash
# 检查备份状态
barman check pg-server

# 验证特定备份
barman validate pg-server <backup_id>

逻辑备份验证 ​

验证SQL格式备份 ​

bash
# 检查SQL语法
psql -h localhost -U postgres -f /pgbackup/mydb.sql --single-transaction --exit-on-error --dry-run

验证自定义格式备份 ​

bash
# 列出备份内容
pg_restore --list /pgbackup/mydb.dump

# 检查备份完整性
pg_restore --validate /pgbackup/mydb.dump

自动化备份脚本 ​

自动化备份可以提高备份的可靠性和及时性，减少人为错误。

完整的自动化备份脚本 ​

bash
#!/bin/bash

# 配置项
BACKUP_DIR="/pgbackup"
LOG_DIR="/var/log/pgbackup"
DB_HOST="localhost"
DB_PORT="5432"
BACKUP_USER="backupuser"
RETENTION_DAYS=7
PARALLEL_JOBS=4

# 创建目录
mkdir -p ${BACKUP_DIR} ${LOG_DIR}

# 日志文件
LOG_FILE="${LOG_DIR}/pg_backup_$(date +%Y%m%d).log"

# 日志函数
log() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" >> ${LOG_FILE}
}

# 开始备份
log "=== PostgreSQL Backup Start ==="

# 执行全量备份
BACKUP_NAME="full_backup_$(date +%Y%m%d_%H%M%S)"
BACKUP_PATH="${BACKUP_DIR}/${BACKUP_NAME}"

log "Creating backup directory: ${BACKUP_PATH}"
mkdir -p ${BACKUP_PATH}

log "Starting full backup using pg_basebackup"
pg_basebackup -h ${DB_HOST} -p ${DB_PORT} -U ${BACKUP_USER} \
  -D ${BACKUP_PATH} \
  -F p \
  -X stream \
  -z \
  -v \
  >> ${LOG_FILE} 2>&1

if [ $? -eq 0 ]; then
    log "Full backup completed successfully"
    
    # 创建软链接指向最新备份
    ln -sf ${BACKUP_PATH} ${BACKUP_DIR}/latest_backup
    
    # 清理旧备份
    log "Cleaning up backups older than ${RETENTION_DAYS} days"
    find ${BACKUP_DIR} -name "full_backup_*" -type d -mtime +${RETENTION_DAYS} -exec rm -rf {} \;
    
    log "Backup cleanup completed"
else
    log "ERROR: Full backup failed"
    exit 1
fi

# 验证备份
log "Validating backup"
pgsql -h ${DB_HOST} -p ${DB_PORT} -U ${BACKUP_USER} -c "SELECT version();" >> ${LOG_FILE} 2>&1

if [ $? -eq 0 ]; then
    log "Backup validation passed"
else
    log "WARNING: Backup validation failed"
fi

log "=== PostgreSQL Backup End ==="

常见问题与解决方案 ​

备份过程中数据库性能下降 ​

问题：备份过程中导致数据库响应变慢

解决方案：

  • 使用--checkpoint=fast参数加速检查点创建
  • 降低备份优先级，使用ionice和nice命令
  • 调整备份时间到业务低峰期
  • 使用更低的压缩级别
  • 增加备份窗口

备份文件过大 ​

问题：备份文件占用过多存储空间

解决方案：

  • 使用增量或差异备份
  • 优化压缩参数
  • 调整备份频率
  • 实现分层存储策略
  • 清理过期备份

备份失败 ​

问题：备份任务执行失败

解决方案：

  • 检查归档模式配置
  • 验证备份用户权限
  • 查看数据库日志获取详细错误信息
  • 检查备份目录权限和空间
  • 验证网络连接（远程备份）

WAL归档不连续 ​

问题：WAL归档出现中断，导致PITR无法执行

解决方案：

  • 检查archive_command配置
  • 确保归档目录有足够空间
  • 检查网络连接（远程归档）
  • 查看PostgreSQL日志中的归档错误
  • 考虑使用pg_receivewal替代archive_command

备份恢复时间过长 ​

问题：全量恢复时间超过RTO要求

解决方案：

  • 增加全量备份频率，减少WAL应用时间
  • 使用增量备份，减少恢复数据量
  • 优化存储性能，提高恢复速度
  • 增加恢复并行度
  • 考虑使用物理备份而非逻辑备份

生产环境最佳实践 ​

  • 备份策略组合：结合物理备份、逻辑备份和WAL归档
  • 3-2-1备份原则：3份备份，2种存储介质，1份离线存储
  • 定期验证：每周至少验证一次备份完整性
  • 恢复演练：每季度至少进行一次完整恢复演练
  • 监控告警：配置备份失败、延迟和存储不足告警
  • 权限控制：限制备份数据的访问权限
  • 加密存储：对敏感数据的备份进行加密
  • 版本控制：使用版本控制系统管理备份脚本
  • 文档化：详细记录备份策略、流程和恢复步骤
  • 持续改进：根据业务变化和技术发展调整备份策略

版本差异处理 ​

不同PostgreSQL版本在备份功能上存在差异，需要注意：

PostgreSQL版本备份功能差异
12+支持并行pg_dump，增强了pg_basebackup功能
10+支持逻辑复制，改进了WAL管理
9.6+支持pg_rewind，提高了主从切换效率
9.5+支持pg_checksums，数据完整性验证

在跨版本备份恢复时，需要注意：

  • 低版本备份可以恢复到高版本
  • 高版本备份不能直接恢复到低版本
  • 需要使用逻辑备份进行跨版本迁移
  • 不同版本的pg_dump和pg_restore工具不能混用