PostgreSQL教程FG306-PostgreSQL备份与恢复

本文档风哥主要介绍PostgreSQL备份与恢复，包括备份类型、恢复类型、备份策略规划以及具体的备份和恢复操作。风哥教程参考PostgreSQL官方文档Server Administration部分，适合DBA人员在生产环境中使用。

Part01-基础概念与理论知识

1.1 PostgreSQL备份概述

PostgreSQL备份是保护数据安全的重要手段，通过备份可以在数据丢失或损坏时恢复数据。备份是数据库运维的核心任务之一，关系到业务的连续性和数据的安全性。

PostgreSQL备份的重要性：

数据保护：防止数据丢失或损坏
业务连续性：确保业务在故障后能够快速恢复
合规要求：满足行业合规性要求
测试环境：为测试和开发提供数据
灾难恢复：在灾难发生时恢复数据

1.2 PostgreSQL备份类型

PostgreSQL备份主要分为以下类型：

物理备份：直接复制数据库文件，包括基础备份和WAL日志
逻辑备份：导出数据库对象和数据，如使用pg_dump和pg_dumpall
连续归档：结合基础备份和WAL日志，实现时间点恢复

1.3 PostgreSQL恢复类型

PostgreSQL恢复主要分为以下类型：

# 恢复类型
1. 完全恢复：恢复到备份时的状态
2. 时间点恢复（PITR）：恢复到指定的时间点
3. 基于WAL的恢复：使用WAL日志进行恢复
4. 逻辑恢复：使用逻辑备份进行恢复

# 恢复级别
– 数据库级恢复：恢复整个数据库
– 表级恢复：恢复特定表
– 行级恢复：恢复特定行

风哥提示：了解PostgreSQL备份和恢复的类型，是制定备份策略的基础。更多视频教程www.fgedu.net.cn

Part02-生产环境规划与建议

2.1 备份策略规划

在生产环境中，合理的备份策略是确保数据安全的关键：

# 备份策略规划原则
1. 数据重要性：根据数据的重要性选择备份类型
2. 恢复时间目标（RTO）：确定可接受的恢复时间
3. 恢复点目标（RPO）：确定可接受的数据丢失量
4. 备份频率：根据数据变化率确定备份频率
5. 备份验证：定期验证备份的可用性
6. 灾难恢复：制定灾难恢复计划

# 备份策略示例
– 每日完整物理备份
– 每小时WAL日志归档
– 每周逻辑备份
– 每月异地备份

2.2 备份计划制定

备份计划应包括以下内容：

备份类型：选择物理备份还是逻辑备份
备份频率：确定备份的时间间隔
备份时间：选择业务低峰期进行备份
备份存储：确定备份的存储位置
备份验证：定期验证备份的完整性
备份清理：定期清理过期备份

2.3 备份存储规划

备份存储规划需要考虑以下因素：

# 备份存储规划
1. 存储介质：使用可靠的存储介质，如磁盘、磁带等
2. 存储位置：本地存储和异地存储相结合
3. 存储容量：确保有足够的存储空间
4. 存储安全：保护备份数据的安全
5. 存储性能：确保备份和恢复的性能

# 备份存储建议
– 使用RAID 10存储备份数据
– 定期将备份复制到异地存储
– 加密备份数据，保护数据安全
– 使用压缩技术，减少存储空间

风哥教程针对风哥教程针对风哥教程针对生产环境建议：根据业务需求和数据重要性，制定合理的备份策略和存储规划，确保数据的安全性和可恢复性。学习交流加群风哥微信: itpux-com

Part03-生产环境项目实施方案

3.1 物理备份与恢复

3.1.1 使用pg_basebackup进行物理备份

# 配置连续归档
$ sudo vi /postgresql/fgdata/postgresql.conf

# 启用归档
archive_mode = on
archive_command = ‘cp %p /postgresql/archive/%f’

# 创建归档目录
$ sudo mkdir -p /postgresql/archive
$ sudo chown postgres:postgres /postgresql/archive
$ sudo chmod 700 /postgresql/archive

# 重启PostgreSQL
$ sudo systemctl restart postgresql

# 使用pg_basebackup进行备份
$ pg_basebackup -h fgedu.localhost -U fgedu -D /postgresql/backup/full_backup -F t -X stream -P

# 输出示例
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/12345678 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot “pg_basebackup_12345”
31964/31964 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/12345678
pg_basebackup: waiting for background process to finish streaming
pg_basebackup: base backup completed

3.1.2 物理备份恢复

# 停止PostgreSQL
$ sudo systemctl stop postgresql

# 清理数据目录
$ sudo rm -rf /postgresql/fgdata/*

# 恢复基础备份
$ tar -xzf /postgresql/backup/full_backup/base.tar.gz -C /postgresql/fgdata

# 创建recovery.conf文件
$ sudo vi /postgresql/fgdata/recovery.conf

restore_command = ‘cp /postgresql/archive/%f %p’
recovery_target_timeline = ‘latest’

# 启动PostgreSQL
$ sudo systemctl start postgresql

# 验证恢复
$ psql -U fgedu -d fgedudb -c “SELECT count(*) FROM fgedu_users;”
count
——-
1000

3.2 逻辑备份与恢复

3.2.1 使用pg_dump进行逻辑备份

# 使用pg_dump备份单个数据库
$ pg_dump -h fgedu.localhost -U fgedu -d fgedudb -F c -f /postgresql/backup/fgedudb.backup

# 输出示例
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrfgappers
pg_dump: reading user-defined foreign servers
pg_dump: reading user-defined foreign tables
pg_dump: reading user-defined materialized views
pg_dump: reading user-defined rules
pg_dump: reading user-defined triggers
pg_dump: reading user-defined table constraints
pg_dump: reading user-defined domain constraints
pg_dump: reading user-defined assertions
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading user-defined sequences
pg_dump: reading user-defined table columns
pg_dump: reading user-defined functions
pg_dump: reading user-defined procedures
pg_dump: reading user-defined aggregates
pg_dump: reading user-defined operators
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrfgappers
pg_dump: reading user-defined foreign servers
pg_dump: reading user-defined foreign tables
pg_dump: reading user-defined materialized views
pg_dump: reading user-defined rules
pg_dump: reading user-defined triggers
pg_dump: reading user-defined table constraints
pg_dump: reading user-defined domain constraints
pg_dump: reading user-defined assertions
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading user-defined sequences
pg_dump: reading user-defined table columns
pg_dump: dumping data for table “fgedu_users”
pg_dump: dumping data for table “fgedu_employees”
pg_dump: dumping data for table “fgedu_logs”

# 使用pg_dumpall备份所有数据库
$ pg_dumpall -h fgedu.localhost -U postgres -f /postgresql/backup/all_databases.sql

3.2.2 逻辑备份恢复

# 恢复单个数据库
$ createdb -h fgedu.localhost -U fgedu fgedudb
$ pg_restore -h fgedu.localhost -U fgedu -d fgedudb /postgresql/backup/fgedudb.backup

# 输出示例
pg_restore: connecting to database for restore
pg_restore: creating SCHEMA “public”
pg_restore: creating COMMENT “SCHEMA public”
pg_restore: creating EXTENSION “plpgsql”
pg_restore: creating COMMENT “EXTENSION plpgsql”
pg_restore: creating TABLE “public.fgedu_users”
pg_restore: creating SEQUENCE “public.fgedu_users_id_seq”
pg_restore: creating SEQUENCE OWNED BY “public.fgedu_users_id_seq”
pg_restore: creating DEFAULT “public.fgedu_users_id_seq”
pg_restore: creating TABLE “public.fgedu_employees”
pg_restore: creating SEQUENCE “public.fgedu_employees_id_seq”
pg_restore: creating SEQUENCE OWNED BY “public.fgedu_employees_id_seq”
pg_restore: creating DEFAULT “public.fgedu_employees_id_seq”
pg_restore: creating TABLE “public.fgedu_logs”
pg_restore: creating SEQUENCE “public.fgedu_logs_id_seq”
pg_restore: creating SEQUENCE OWNED BY “public.fgedu_logs_id_seq”
pg_restore: creating DEFAULT “public.fgedu_logs_id_seq”
pg_restore: creating CONSTRAINT “public.fgedu_users_pkey”
pg_restore: creating CONSTRAINT “public.fgedu_employees_pkey”
pg_restore: creating CONSTRAINT “public.fgedu_logs_pkey”
pg_restore: processing data for table “public.fgedu_users”
pg_restore: processing data for table “public.fgedu_employees”
pg_restore: processing data for table “public.fgedu_logs”
pg_restore: creating INDEX “public.fgedu_users_email_idx”
pg_restore: creating INDEX “public.fgedu_employees_department_idx”
pg_restore: creating INDEX “public.fgedu_logs_log_time_idx”

# 验证恢复
$ psql -U fgedu -d fgedudb -c “SELECT count(*) FROM fgedu_users;”
count
——-
1000

3.3 时间点恢复

3.3.1 配置时间点恢复

# 配置连续归档
$ sudo vi /postgresql/fgdata/postgresql.conf

archive_mode = on
archive_command = ‘cp %p /postgresql/archive/%f’

# 重启PostgreSQL
$ sudo systemctl restart postgresql

# 创建基础备份
$ pg_basebackup -h fgedu.localhost -U fgedu -D /postgresql/backup/base_backup -F t -X stream

# 模拟数据损坏
$ psql -U fgedu -d fgedudb -c “DELETE FROM fgedu_users;”
DELETE 1000

# 停止PostgreSQL
$ sudo systemctl stop postgresql

# 清理数据目录
$ sudo rm -rf /postgresql/fgdata/*

# 恢复基础备份
$ tar -xzf /postgresql/backup/base_backup/base.tar.gz -C /postgresql/fgdata

# 创建recovery.conf文件，指定恢复时间点
$ sudo vi /postgresql/fgdata/recovery.conf

restore_command = ‘cp /postgresql/archive/%f %p’
recovery_target_time = ‘2026-04-06 10:00:00 UTC’
recovery_target_inclusive = true

# 启动PostgreSQL
$ sudo systemctl start postgresql

# 验证恢复
$ psql -U fgedu -d fgedudb -c “SELECT count(*) FROM fgedu_users;”
count
——-
1000

风哥提示：时间点恢复是PostgreSQL的重要功能，可以在数据损坏后恢复到指定的时间点，减少数据丢失。学习交流加群风哥QQ113257174

Part04-生产案例与实战讲解

4.1 物理备份案例

4.1.1 生产环境物理备份

# 生产环境物理备份脚本
$ sudo vi /postgresql/scripts/physical_backup.sh
#!/bin/bash
# physical_backup.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

# 配置参数
BACKUP_DIR=”/postgresql/backup”
ARCHIVE_DIR=”/postgresql/archive”
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_PATH=”$BACKUP_DIR/full_backup_$DATE”

# 创建备份目录
mkdir -p “$BACKUP_PATH”

# 执行物理备份
pg_basebackup -h fgedu.localhost -U fgedu -D “$BACKUP_PATH” -F t -X stream -P

# 压缩备份
cd “$BACKUP_DIR”
tar -czf “full_backup_$DATE.tar.gz” “full_backup_$DATE”
rm -rf “full_backup_$DATE”

# 清理过期备份
find “$BACKUP_DIR” -name “full_backup_*.tar.gz” -mtime +7 -delete

# 输出备份信息
echo “Backup completed: $BACKUP_DIR/full_backup_$DATE.tar.gz”

# 设置脚本权限
$ sudo chmod +x /postgresql/scripts/physical_backup.sh

# 添加到crontab
$ sudo crontab -e
0 2 * * * /postgresql/scripts/physical_backup.sh

# 手动执行脚本测试
$ sudo /postgresql/scripts/physical_backup.sh

4.2 逻辑备份案例

4.2.1 生产环境逻辑备份

# 生产环境逻辑备份脚本
$ sudo vi /postgresql/scripts/logical_backup.sh
#!/bin/bash
# logical_backup.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

# 配置参数
BACKUP_DIR=”/postgresql/backup”
DATE=$(date +%Y%m%d_%H%M%S)

# 创建备份目录
mkdir -p “$BACKUP_DIR”

# 备份单个数据库
pg_dump -h fgedu.localhost -U fgedu -d fgedudb -F c -f “$BACKUP_DIR/fgedudb_$DATE.backup”

# 备份所有数据库
pg_dumpall -h fgedu.localhost -U postgres -f “$BACKUP_DIR/all_databases_$DATE.sql”

# 压缩备份
gzip “$BACKUP_DIR/fgedudb_$DATE.backup”
gzip “$BACKUP_DIR/all_databases_$DATE.sql”

# 清理过期备份
find “$BACKUP_DIR” -name “fgedudb_*.backup.gz” -mtime +7 -delete
find “$BACKUP_DIR” -name “all_databases_*.sql.gz” -mtime +30 -delete

# 输出备份信息
echo “Logical backup completed:”
echo “- $BACKUP_DIR/fgedudb_$DATE.backup.gz”
echo “- $BACKUP_DIR/all_databases_$DATE.sql.gz”

# 设置脚本权限
$ sudo chmod +x /postgresql/scripts/logical_backup.sh

# 添加到crontab
$ sudo crontab -e
0 3 * * * /postgresql/scripts/logical_backup.sh

# 手动执行脚本测试
$ sudo /postgresql/scripts/logical_backup.sh

# 输出示例
Logical backup completed:
– /postgresql/backup/fgedudb_20260406_100000.backup.gz
– /postgresql/backup/all_databases_20260406_100000.sql.gz

4.3 时间点恢复案例

4.3.1 时间点恢复实战

# 时间点恢复实战

# 1. 配置连续归档
$ sudo vi /postgresql/fgdata/postgresql.conf
archive_mode = on
archive_command = ‘cp %p /postgresql/archive/%f’

# 2. 重启PostgreSQL
$ sudo systemctl restart postgresql

# 3. 创建基础备份
$ pg_basebackup -h fgedu.localhost -U fgedu -D /postgresql/backup/base_backup -F t -X stream

# 4. 记录当前时间
$ date
2026-04-06 10:00:00 UTC

# 5. 执行一些数据库操作
$ psql -U fgedu -d fgedudb -c “INSERT INTO fgedu_users (name, email) VALUES (‘Test User 1’, ‘test1@fgedu.net.cn’);”
$ psql -U fgedu -d fgedudb -c “INSERT INTO fgedu_users (name, email) VALUES (‘Test User 2’, ‘test2@fgedu.net.cn’);”
$ psql -U fgedu -d fgedudb -c “DELETE FROM fgedu_users WHERE name = ‘Test User 1’;”

# 6. 记录恢复时间点（删除操作前）
RECOVERY_TIME=”2026-04-06 10:01:00 UTC”

# 7. 停止PostgreSQL
$ sudo systemctl stop postgresql

# 8. 清理数据目录
$ sudo rm -rf /postgresql/fgdata/*

# 9. 恢复基础备份
$ tar -xzf /postgresql/backup/base_backup/base.tar.gz -C /postgresql/fgdata

# 10. 创建recovery.conf文件
$ sudo vi /postgresql/fgdata/recovery.conf
restore_command = ‘cp /postgresql/archive/%f %p’
recovery_target_time = ‘$RECOVERY_TIME’
recovery_target_inclusive = true

# 11. 启动PostgreSQL
$ sudo systemctl start postgresql

# 验证删除的用户已恢复

风哥教程针对风哥教程针对风哥教程针对生产环境建议：定期测试备份和恢复流程，确保在数据丢失时能够快速恢复。更多学习教程公众号风哥教程itpux_com

Part05-风哥经验总结与分享

5.1 备份与恢复最佳实践

PostgreSQL备份与恢复的最佳实践：

制定备份策略：根据业务需求和数据重要性制定合理的备份策略
多种备份方式：结合物理备份和逻辑备份，提高数据安全性
定期备份：根据数据变化率定期执行备份
备份验证：定期验证备份的完整性和可恢复性
异地存储：将备份存储在异地，防止本地灾难
备份加密：加密备份数据，保护数据安全
恢复测试：定期测试恢复流程，确保能够快速恢复
文档化：记录备份和恢复流程，便于团队成员参考

风哥提示：在多年的PostgreSQL管理经验中，我发现备份与恢复是数据库运维的核心任务之一。以下是我的几点经验：

1. 备份策略要全面：结合物理备份和逻辑备份，确保数据的安全性
2. 备份频率要合理：根据数据变化率确定备份频率，避免备份过于频繁或不足
3. 备份存储要安全：使用可靠的存储介质，异地存储备份数据
4. 备份验证要定期：定期验证备份的完整性和可恢复性，确保备份有效
5. 恢复测试要坚持：定期测试恢复流程，确保在数据丢失时能够快速恢复
6. 灾难恢复要规划：制定详细的灾难恢复计划，包括恢复步骤和时间估计

通过合理的备份与恢复策略，可以显著提高数据库的可靠性和可恢复性，为业务提供更好的支持。from PostgreSQL视频:www.itpux.com

5.3 常见问题与解决方案

PostgreSQL备份与恢复常见问题与解决方案：

# 常见问题1：备份失败
症状：备份过程中出现错误，备份失败
解决方案：
– 检查备份目录权限
– 确保PostgreSQL用户有足够的权限
– 检查磁盘空间是否充足
– 检查网络连接是否正常

# 常见问题2：恢复失败
症状：恢复过程中出现错误，恢复失败
解决方案：
– 检查备份文件是否完整
– 确保恢复目录权限正确
– 检查WAL日志是否完整
– 验证recovery.conf配置是否正确

# 常见问题3：备份文件过大
症状：备份文件过大，占用大量存储空间
解决方案：
– 使用压缩技术减少备份文件大小
– 采用增量备份策略
– 定期清理过期备份
– 考虑使用备份工具进行 deduplication

# 常见问题4：恢复时间过长
症状：恢复过程耗时过长，影响业务恢复
解决方案：
– 使用物理备份进行恢复
– 优化恢复配置参数
– 提高存储性能
– 考虑使用并行恢复

# 常见问题5：备份验证失败
症状：备份验证时发现备份文件损坏或不完整
解决方案：
– 检查备份过程是否正常完成
– 验证备份文件的完整性
– 检查存储介质是否可靠
– 考虑使用校验和验证备份文件

持续改进：备份与恢复策略是一个持续优化的过程，需要根据业务需求和系统状态不断调整。建议建立备份与恢复的标准流程，定期评估和优化备份策略，以适应业务发展的需要。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

PostgreSQL教程FG306-PostgreSQL备份与恢复

Part01-基础概念与理论知识

1.1 PostgreSQL备份概述

1.2 PostgreSQL备份类型

1.3 PostgreSQL恢复类型

Part02-生产环境规划与建议

2.1 备份策略规划

2.2 备份计划制定

2.3 备份存储规划

Part03-生产环境项目实施方案

3.1 物理备份与恢复

3.1.1 使用pg_basebackup进行物理备份

3.1.2 物理备份恢复

3.2 逻辑备份与恢复

3.2.1 使用pg_dump进行逻辑备份

3.2.2 逻辑备份恢复

3.3 时间点恢复

3.3.1 配置时间点恢复

Part04-生产案例与实战讲解

4.1 物理备份案例

4.1.1 生产环境物理备份

4.2 逻辑备份案例

4.2.1 生产环境逻辑备份

4.3 时间点恢复案例

4.3.1 时间点恢复实战

Part05-风哥经验总结与分享

5.1 备份与恢复最佳实践

5.2 风哥经验分享

5.3 常见问题与解决方案

相关推荐

联系我们