目录第一部分PostgreSQL备份的误区误区一只依赖主从复制就是备份误区二认为备份就是用pg_dump导出SQL误区三备份策略一成不变第二部分PostgreSQL备份方案对比方案一pg_dump逻辑备份方案二pg_basebackup物理备份方案三WAL归档 pg_basebackup推荐第三部分实战备份方案方案A小型企业50GB数据方案B中型企业50GB500GB数据方案C大型企业500GB数据第四部分恢复流程详解场景一恢复整个数据库pg_dump格式场景二恢复到特定时间点PITR场景三恢复单个表场景四恢复到另一个服务器第五部分备份最佳实践1. 定期测试恢复2. 监控备份过程3. 自动告警4. 权限和安全5. 异地存储6. 定期验证总结与检查清单周五晚上10点一条警报突然袭来生产环境的PostgreSQL数据库出现异常。应急团队连夜排查发现一个出错的SQL语句把用户表的核心字段更新成了NULL。半小时内超过10万条用户记录被损坏。DBA冲回办公室打开备份系统才松了一口气——幸好有每天凌晨的备份。经过40分钟的恢复流程用户数据恢复到前一天的状态。虽然丢失了一天的新增数据但总算保住了公司的核心资产。这个故事在互联网公司几乎每年都在重演。但故事的结局取决于一个因素你有没有做好PostgreSQL备份。第一部分PostgreSQL备份的误区如果你正在使用PostgreSQL你可能踩过这些坑误区一只依赖主从复制就是备份很多人觉得有了PostgreSQL的主从复制Replication就有了备份。但这大错特错。主从复制的目的是提高可用性而不是备份- 主库被恶意删除从库上的数据也会被删除- 主库被勒索软件加密从库上的数据也被加密- 主库发生逻辑错误错误的UPDATE语句从库会复制同样的错误真正的备份需要与原数据隔离在时间上也要有历史版本。主从复制只是一个热备不是真正的备份。误区二认为备份就是用pg_dump导出SQL很多小公司的做法是每天晚上跑一次pg_dump把数据库导出成SQL文件然后上传到某个网盘或服务器。这个方案的问题- 恢复很慢几十GB的SQL文件导入需要好几小时- 不支持增量每次都要备份全量数据浪费存储- 容易损坏中断备份进程文件就可能不完整- 不支持时间点恢复只能恢复到备份时刻无法恢复到故障前的某个具体时间误区三备份策略一成不变有些DBA会说我们的数据库不大一个月备份一次就够了。但这取决于你的数据变化频率和容忍度- 电商订单数据每小时或更频繁- 用户信息数据一天一次- 配置表等非关键数据一周一次而且不同类型的数据应该用不同的备份策略。第二部分PostgreSQL备份方案对比PostgreSQL提供了多种备份方式。让我们对比一下方案一pg_dump逻辑备份原理转储为SQL文本或二进制格式# 导出为SQL文本 pg_dump -U postgres -d mydb mydb.sql # 导出为压缩格式推荐 pg_dump -U postgres -d mydb -F c -f mydb.dump优点- 简单易用不需要额外工具- 可以跨版本恢复- 可以只导出特定表缺点- 恢复速度慢- 不支持时间点恢复- 不支持增量适用场景小型数据库、版本升级、迁移方案二pg_basebackup物理备份原理备份数据库文件系统级别的内容# 基础备份 pg_basebackup -U replication_user -h localhost \ -D /backup/base -Pv -Xstream -R # 包含WAL日志的备份 pg_basebackup -U replication_user -h localhost \ -D /backup/base -Pv -Xfetch -R优点- 恢复速度快秒级- 支持时间点恢复配合WAL- 支持增量缺点- 复杂度高需要理解WAL- 备份文件较大- 恢复需要特定步骤适用场景大型数据库、RTO要求严格、需要时间点恢复方案三WAL归档 pg_basebackup推荐原理结合基础备份和持续的WAL预写日志这是PostgreSQL推荐的企业级方案1. 定期做pg_basebackup2. 持续归档WAL日志到独立存储3. 需要恢复时先恢复基础备份再通过WAL重放到指定时间优点- 最灵活支持任意时间点恢复- 可以恢复到秒级精度- 支持增量和压缩- 数据更安全缺点- 需要持续的磁盘空间- 需要自动化脚本维护适用场景生产环境、对数据一致性要求高第三部分实战备份方案根据不同规模的企业我们给出三个递进式的方案。方案A小型企业50GB数据策略每天一次pg_dump#!/bin/bash # 每天凌晨2点执行 BACKUP_DIR/data/backups DB_NAMEmydb DATE$(date %Y%m%d_%H%M%S) pg_dump -U postgres -d $DB_NAME -F c -f $BACKUP_DIR/backup_${DATE}.dump # 只保留最近30天的备份 find $BACKUP_DIR -name backup_*.dump -mtime 30 -delete # 上传到阿里云OSS或腾讯云COS ossutil cp $BACKUP_DIR/backup_${DATE}.dump oss://my-bucket/backups/成本- 本地备份存储100GB左右- 云存储50-200元/月- 运维基本无恢复时间30分钟2小时配置cron任务crontab -e # 每天凌晨2点执行备份 0 2 * * * /scripts/backup.sh优点- 简单、成本低- 不需要特殊配置缺点- 恢复较慢- 只能恢复到备份时刻方案B中型企业50GB500GB数据策略周一全量备份 每日增量备份#!/bin/bash # pg_basebackup WAL归档 BACKUP_DIR/data/backups WAL_ARCHIVE/data/wal_archive DATE$(date %Y%m%d_%H%M%S) # 周一做全量备份1表示周一 if [ $(date %u) -eq 1 ]; then pg_basebackup -U replication_user \ -h localhost \ -D $BACKUP_DIR/full_backup_${DATE} \ -Pv -Xfetch -R # 压缩备份 tar -czf $BACKUP_DIR/backup_full_${DATE}.tar.gz \ $BACKUP_DIR/full_backup_${DATE} fipostgresql.conf 配置# 启用WAL归档wal_level replicaarchive_mode onarchive_command test ! -f /data/wal_archive/%f cp %p /data/wal_archive/%farchive_timeout 300# 保留足够的WAL段数wal_keep_segments 64成本- 本地存储600GB全量WAL- 云存储200-500元/月- 运维1小时/周恢复时间5分钟30分钟基础备份快速恢复加上WAL重放优点- 支持时间点恢复- 恢复速度较快- 存储成本中等缺点- 需要持续监控WAL空间- 配置复杂度高方案C大型企业500GB数据建议采用专业的企业级备份产品搭建专业的备份方案。第四部分恢复流程详解备份的最终目的是恢复。让我们看看不同场景的恢复方式。场景一恢复整个数据库pg_dump格式# 方式1SQL文本恢复 psql -U postgres -d mydb backup.sql # 方式2二进制格式恢复更快 pg_restore -U postgres -d mydb backup.dump # 方式3恢复到新数据库 pg_restore -U postgres -C -d postgres backup.dump场景二恢复到特定时间点PITR前提已有基础备份和WAL日志# 1. 停止PostgreSQL sudo systemctl stop postgresql # 2. 清空data目录 rm -rf /var/lib/postgresql/data/* # 3. 恢复基础备份 tar -xzf backup_full_20240101.tar.gz \ -C /var/lib/postgresql/data # 4. 创建recovery.signal文件PostgreSQL 12 touch /var/lib/postgresql/data/recovery.signal # 5. 配置recovery参数postgresql.conf cat /var/lib/postgresql/data/postgresql.conf EOF restore_command cp /data/wal_archive/%f %p recovery_target_timeline latest recovery_target_time 2024-01-01 14:30:00 EOF # 6. 启动PostgreSQL sudo systemctl start postgresql场景三恢复单个表# 从备份中提取单个表 pg_restore -U postgres -d mydb \ -t table_name backup.dump # 或者从备份中列出对象 pg_restore --list backup.dump | grep TABLE场景四恢复到另一个服务器# 在源服务器生成备份 pg_dump -U postgres -d mydb | \ ssh usertarget_host psql -U postgres -d mydb # 或者使用pg_basebackup跨网络恢复 pg_basebackup -U replication_user \ -h source_host \ -D /var/lib/postgresql/data \ -Pv -X stream -R第五部分备份最佳实践1. 定期测试恢复最重要的是备份是否真的能恢复#!/bin/bash # 每月第一个周日测试恢复 if [ $(date %w) -eq 0 ] [ $(date %d) -le 7 ]; then # 在测试环境恢复一次 pg_restore -U postgres -d test_restore backup_latest.dump # 验证数据完整性 psql -U postgres -d test_restore \ -c SELECT COUNT(*) FROM users; # 对比行数是否一致2. 监控备份过程# 记录备份日志 pg_dump -U postgres -d mydb -F c \ -f backup.dump 21 | tee backup.log # 验证备份文件完整性 pg_restore -U postgres --list backup.dump /dev/null echo 备份验证结果: $?3. 自动告警# 检查备份文件是否存在且足够新 BACKUP_FILE/data/backups/backup_latest.dump CURRENT_TIME$(date %s) FILE_TIME$(stat -c %Y $BACKUP_FILE) DIFF$((CURRENT_TIME - FILE_TIME)) # 如果备份超过25小时没更新告警 if [ $DIFF -gt 90000 ]; then echo WARNING: 备份已超过25小时未更新 | \ mail -s 备份告警 admincompany.com fi4. 权限和安全# 创建专用备份用户 CREATE USER backup_user WITH ENCRYPTED PASSWORD strong_password; GRANT CONNECT ON DATABASE mydb TO backup_user; GRANT USAGE ON SCHEMA public TO backup_user; GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user; # .pgpass 文件权限设置600 echo localhost:5432:mydb:backup_user:password ~/.pgpass chmod 600 ~/.pgpass5. 异地存储# 每周上传到云存储 aws s3 cp /data/backups/backup.dump \ s3://my-backup-bucket/postgresql/ \ --sse AES256 # 或使用阿里云OSS ossutil cp /data/backups/backup.dump \ oss://backup-bucket/postgresql/6. 定期验证# 每月检查一次备份的可恢复性 backup_list$(ls -t /data/backups/*.dump | head -3) for backup in $backup_list; do echo 验证备份: $backup pg_restore --list $backup /dev/null 21 if [ $? -eq 0 ]; then echo ✓ $backup 可恢复 else echo ✗ $backup 可能损坏 fi done总结与检查清单现在检查一下你的PostgreSQL备份方案立即行动- ✓ 你有备份吗pg_dump、pg_basebackup或其他- ✓ 备份存储位置是否独立于主库- ✓ 最近一次成功备份是什么时候- ✓ 你测试过恢复吗进阶优化- ✓ 是否有自动化备份脚本- ✓ 是否有备份告警机制- ✓ 是否定期测试时间点恢复- ✓ 是否有完整的恢复操作手册企业级- ✓ 是否使用了专业备份工具- ✓ 是否有多地域备份- ✓ 是否定期进行灾备演练一个简单的PostgreSQL备份方案可能只需要每月花费几百块钱但能保护你数年积累的数据。相比数据丢失带来的损失恢复费用、业务中断、客户流失这笔投资值得。现在就配置你的PostgreSQL备份吧。不要等到数据丢失的时刻。