PostgreSQL教程FG100-PG逻辑备份：pg_dump/pg_dumpall全流程与参数
本文档风哥主要介绍PostgreSQL的逻辑备份工具pg_dump和pg_dumpall的使用方法和参数配置。风哥教程参考PostgreSQL官方文档Backup and Restore内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。
Part01-基础概念与理论知识
1.1 逻辑备份的概念
逻辑备份是指通过SQL语句或其他逻辑方式将数据库的结构和数据导出到一个文件中。逻辑备份不依赖于底层的存储结构，而是以逻辑形式存储数据，因此可以在不同版本的PostgreSQL之间进行恢复。更多视频教程www.fgedu.net.cn
学习交流加群风哥QQ113257174
- 独立于存储结构：不依赖于底层的存储格式
- 跨版本兼容：可以在不同版本的PostgreSQL之间恢复
- 选择性备份：可以备份特定的表或数据库
- 备份文件可读性强：备份文件是SQL语句或其他可读格式
- 恢复灵活：可以选择性恢复数据
1.2 pg_dump工具介绍
pg_dump是PostgreSQL的逻辑备份工具，用于备份单个数据库。它可以将数据库的结构和数据导出为SQL语句或其他格式，支持多种输出格式和选项。
– 支持多种输出格式：SQL、自定义、目录、tar
– 支持压缩输出：可以直接压缩备份文件
– 支持并行备份：可以使用多个线程进行备份
– 支持选择性备份：可以备份特定的表或模式
– 支持增量备份：可以基于之前的备份进行增量备份
– 支持备份一致性：使用事务确保备份的一致性
# pg_dump的适用场景
– 备份单个数据库
– 跨版本迁移
– 数据导出和导入
– 选择性备份特定表或模式
– 生成SQL脚本用于数据库重建
1.3 pg_dumpall工具介绍
pg_dumpall是PostgreSQL的全库备份工具，用于备份所有数据库。它可以备份所有数据库的结构和数据，包括系统数据库。
– 备份所有数据库：包括系统数据库和用户数据库
– 备份全局对象：包括角色、表空间等全局对象
– 支持多种输出格式：SQL格式
– 支持压缩输出：可以通过管道压缩输出
– 支持并行备份：可以使用多个线程进行备份
# pg_dumpall的适用场景
– 备份整个PostgreSQL实例
– 迁移整个PostgreSQL实例
– 备份全局对象
– 灾难恢复
1.4 逻辑备份的优势
逻辑备份的优势包括：
from oracle:www.itpux.com
- 跨版本兼容：可以在不同版本的PostgreSQL之间恢复
- 选择性备份：可以备份特定的表或数据库
- 备份文件可读性强：备份文件是SQL语句或其他可读格式
- 恢复灵活：可以选择性恢复数据
- 空间效率：可以只备份需要的数据，节省存储空间
- 安全性：可以通过压缩和加密保护备份文件
Part02-生产环境规划与建议
2.1 逻辑备份策略
逻辑备份策略规划要点：
1. 分析业务需求：了解业务对数据可用性和恢复时间的要求
2. 确定备份范围：选择备份单个数据库或所有数据库
3. 选择备份工具：根据备份范围选择pg_dump或pg_dumpall
4. 制定备份频率：根据数据变更频率确定备份频率
5. 选择备份格式：根据恢复需求选择合适的备份格式
6. 规划备份存储：确定备份文件的存储位置和方式
7. 制定恢复计划：制定详细的恢复步骤和测试计划
# 备份频率建议
– 全量备份：每周或每月执行一次
– 增量备份：每天执行一次
– 差异备份：每两天或每三天执行一次
# 备份格式选择
– SQL格式：适合跨版本恢复，可读性强
– 自定义格式：适合增量备份，恢复速度快
– 目录格式：适合并行备份，恢复灵活
– tar格式：适合归档存储，便于传输
2.2 备份参数选择
pg_dump和pg_dumpall的常用参数：
– -d, –fgedudb：指定数据库名称
– -f, –file：指定输出文件
– -F, –format：指定输出格式（p=plain, c=custom, d=directory, t=tar）
– -j, –jobs：指定并行备份的线程数
– -Z, –compress：指定压缩级别（0-9）
– -v, –verbose：显示详细信息
– -a, –data-only：只备份数据，不备份结构
– -s, –schema-only：只备份结构，不备份数据
– -t, –table：指定要备份的表
– -n, –schema：指定要备份的模式
– -N, –exclude-schema：排除指定的模式
– -T, –exclude-table：排除指定的表
# pg_dumpall常用参数
– -f, –file：指定输出文件
– -v, –verbose：显示详细信息
– -g, –globals-only：只备份全局对象
– -r, –roles-only：只备份角色
– -t, –fgedutbss-only：只备份表空间
– -x, –no-privileges：不备份权限
2.3 备份计划制定
备份计划制定要点：
– pg_dump备份：每天执行一次，备份关键业务数据库
– pg_dumpall备份：每周执行一次，备份所有数据库和全局对象
– 备份验证：每周执行一次，验证备份文件的有效性
– 备份清理：每月执行一次，清理过期的备份文件
# 备份存储规划
– 本地存储：用于短期备份，如7天内的备份
– 远程存储：用于长期备份，如月度和年度备份
– 云存储：用于灾备备份，确保数据安全性
# 备份保留策略
– pg_dump备份：保留7天
– pg_dumpall备份：保留4周
– 月度备份：保留12个月
– 年度备份：保留3年
Part03-生产环境项目实施方案
3.1 pg_dump使用方法
3.1.1 备份单个数据库
$ sudo -u pgsql pg_dump -U pgsql -d fgedu_production -f /backup/fgedu_production_$(date +%Y%m%d).sql
# 2. 备份为自定义格式
$ sudo -u pgsql pg_dump -U pgsql -d fgedu_production -F c -f /backup/fgedu_production_$(date +%Y%m%d).backup
# 3. 备份为目录格式
$ sudo -u pgsql pg_dump -U pgsql -d fgedu_production -F d -j 4 -f /backup/fgedu_production_$(date +%Y%m%d)
# 4. 备份为tar格式
$ sudo -u pgsql pg_dump -U pgsql -d fgedu_production -F t -f /backup/fgedu_production_$(date +%Y%m%d).tar
# 5. 压缩备份
$ sudo -u pgsql pg_dump -U pgsql -d fgedu_production -F c -Z 9 -f /backup/fgedu_production_$(date +%Y%m%d).backup.gz
# 6. 只备份数据
$ sudo -u pgsql pg_dump -U pgsql -d fgedu_production -a -f /backup/fgedu_production_data_$(date +%Y%m%d).sql
# 7. 只备份结构
$ sudo -u pgsql pg_dump -U pgsql -d fgedu_production -s -f /backup/fgedu_production_schema_$(date +%Y%m%d).sql
# 8. 备份特定表
$ sudo -u pgsql pg_dump -U pgsql -d fgedu_production -t fgedu_employees -t fgedu_fgfgfgfgsales -f /backup/fgedu_production_tables_$(date +%Y%m%d).sql
# 9. 备份特定模式
$ sudo -u pgsql pg_dump -U pgsql -d fgedu_production -n public -f /backup/fgedu_production_public_$(date +%Y%m%d).sql
# 10. 排除特定表
$ sudo -u pgsql pg_dump -U pgsql -d fgedu_production -T fgedu_logs -f /backup/fgedu_production_exclude_$(date +%Y%m%d).sql
3.1.2 从备份文件恢复
$ sudo -u pgsql psql -U pgsql -d fgedu_production -f /backup/fgedu_production_$(date +%Y%m%d).sql
# 2. 从自定义格式备份恢复
$ sudo -u pgsql pg_restore -U pgsql -d fgedu_production /backup/fgedu_production_$(date +%Y%m%d).backup
# 3. 从目录格式备份恢复
$ sudo -u pgsql pg_restore -U pgsql -d fgedu_production -j 4 /backup/fgedu_production_$(date +%Y%m%d)
# 4. 从tar格式备份恢复
$ sudo -u pgsql pg_restore -U pgsql -d fgedu_production /backup/fgedu_production_$(date +%Y%m%d).tar
# 5. 只恢复特定表
$ sudo -u pgsql pg_restore -U pgsql -d fgedu_production -t fgedu_employees /backup/fgedu_production_$(date +%Y%m%d).backup
# 6. 只恢复结构
$ sudo -u pgsql pg_restore -U pgsql -d fgedu_production –schema-only /backup/fgedu_production_$(date +%Y%m%d).backup
# 7. 只恢复数据
$ sudo -u pgsql pg_restore -U pgsql -d fgedu_production –data-only /backup/fgedu_production_$(date +%Y%m%d).backup
3.2 pg_dumpall使用方法
3.2.1 备份所有数据库
$ sudo -u pgsql pg_dumpall -U pgsql -f /backup/postgresql_all_$(date +%Y%m%d).sql
# 2. 备份所有数据库并压缩
$ sudo -u pgsql pg_dumpall -U pgsql | gzip > /backup/postgresql_all_$(date +%Y%m%d).sql.gz
# 3. 只备份全局对象
$ sudo -u pgsql pg_dumpall -U pgsql -g -f /backup/postgresql_globals_$(date +%Y%m%d).sql
# 4. 只备份角色
$ sudo -u pgsql pg_dumpall -U pgsql -r -f /backup/postgresql_roles_$(date +%Y%m%d).sql
# 5. 只备份表空间
$ sudo -u pgsql pg_dumpall -U pgsql -t -f /backup/postgresql_fgedutbss_$(date +%Y%m%d).sql
# 6. 不备份权限
$ sudo -u pgsql pg_dumpall -U pgsql -x -f /backup/postgresql_no_privileges_$(date +%Y%m%d).sql
3.2.2 从pg_dumpall备份恢复
$ sudo -u pgsql psql -U pgsql -f /backup/postgresql_all_$(date +%Y%m%d).sql
# 2. 从压缩的pg_dumpall备份恢复
$ gunzip -c /backup/postgresql_all_$(date +%Y%m%d).sql.gz | sudo -u pgsql psql -U pgsql
# 3. 只恢复全局对象
$ sudo -u pgsql psql -U pgsql -f /backup/postgresql_globals_$(date +%Y%m%d).sql
# 4. 只恢复角色
$ sudo -u pgsql psql -U pgsql -f /backup/postgresql_roles_$(date +%Y%m%d).sql
# 5. 只恢复表空间
$ sudo -u pgsql psql -U pgsql -f /backup/postgresql_fgedutbss_$(date +%Y%m%d).sql
3.3 备份恢复方法
3.3.1 完整恢复流程
# 创建目标数据库
$ sudo -u pgsql psql -U pgsql -c “CREATE DATABASE fgedu_production;”
# 2. 从pg_dump备份恢复
# 恢复结构
$ sudo -u pgsql pg_restore -U pgsql -d fgedu_production –schema-only /backup/fgedu_production_$(date +%Y%m%d).backup
# 恢复数据
$ sudo -u pgsql pg_restore -U pgsql -d fgedu_production –data-only /backup/fgedu_production_$(date +%Y%m%d).backup
# 3. 从pg_dumpall备份恢复
# 恢复所有数据库
$ sudo -u pgsql psql -U pgsql -f /backup/postgresql_all_$(date +%Y%m%d).sql
# 4. 验证恢复结果
# 检查数据库是否存在
$ sudo -u pgsql psql -U pgsql -c “\l”
# 检查表是否存在
$ sudo -u pgsql psql -U pgsql -d fgedu_production -c “\dt”
# 检查数据是否完整
$ sudo -u pgsql psql -U pgsql -d fgedu_production -c “SELECT count(*) FROM fgedu_employees;”
Part04-生产案例与实战讲解
4.1 pg_dump备份案例
4.1.1 案例描述
场景：一个生产环境的PostgreSQL数据库，需要每天执行逻辑备份，备份关键业务数据。
4.1.2 实施方案
$ sudo vi /postgresql/scripts/pg_dump_backup.sh
#!/bin/bash
# pg_dump备份脚本
# from:www.itpux.com.qq113257174.wx:itpux-com
# from:www.itpux.com.qq113257174.wx:itpux-com
# from:www.itpux.com.qq113257174.wx:itpux-com
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`
# web: `http://www.fgedu.net.cn`
# web: `http://www.fgedu.net.cn`
# web: http://www.fgedu.net.cn
PG_HOME=”/postgresql”
BACKUP_DIR=”/backup”
LOG_DIR=”/postgresql/logs”
DATABASES=(“fgedu_production” “fgedu_analytics” “fgedu_staging”)
log_message() {
local timestamp=$(date +”%Y-%m-%d %H:%M:%S”)
echo “[$timestamp] $1”
echo “[$timestamp] $1” >> “$LOG_DIR/backup.log”
}
log_message “开始执行pg_dump备份…”
for db in “${DATABASES[@]}”; do
log_message “备份数据库：$db”
# 备份为自定义格式并压缩
sudo -u pgsql pg_dump -U pgsql -d “$db” -F c -Z 9 -f “$BACKUP_DIR/${db}_$(date +%Y%m%d).backup.gz”
if [ $? -ne 0 ]; then
log_message “备份数据库 $db 失败”
else
log_message “备份数据库 $db 成功，备份文件：$BACKUP_DIR/${db}_$(date +%Y%m%d).backup.gz”
fi
done
# 清理旧备份文件
log_message “清理旧备份文件…”
sudo find “$BACKUP_DIR” -name “*.backup.gz” -mtime +7 -delete
log_message “pg_dump备份执行完成”
# 2. 设置定时执行
$ sudo crontab -e
# 添加以下内容
0 1 * * * /postgresql/scripts/pg_dump_backup.sh
# 3. 验证备份脚本执行
$ sudo tail -f /postgresql/logs/backup.log
$ ls -la /backup/fgedu_production_$(date +%Y%m%d).backup.gz
4.2 pg_dumpall备份案例
4.2.1 案例描述
场景：一个PostgreSQL实例，包含多个数据库，需要每周执行全库备份，包括所有数据库和全局对象。
4.2.2 实施方案
$ sudo vi /postgresql/scripts/pg_dumpall_backup.sh
#!/bin/bash
# pg_dumpall备份脚本
# from:www.itpux.com.qq113257174.wx:itpux-com
# from:www.itpux.com.qq113257174.wx:itpux-com
# from:www.itpux.com.qq113257174.wx:itpux-com
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`
# web: `http://www.fgedu.net.cn`
# web: `http://www.fgedu.net.cn`
# web: http://www.fgedu.net.cn
PG_HOME=”/postgresql”
BACKUP_DIR=”/backup”
LOG_DIR=”/postgresql/logs”
log_message() {
local timestamp=$(date +”%Y-%m-%d %H:%M:%S”)
echo “[$timestamp] $1”
echo “[$timestamp] $1” >> “$LOG_DIR/backup.log”
}
log_message “开始执行pg_dumpall备份…”
# 备份所有数据库并压缩
sudo -u pgsql pg_dumpall -U pgsql | gzip > “$BACKUP_DIR/postgresql_all_$(date +%Y%m%d).sql.gz”
if [ $? -ne 0 ]; then
log_message “pg_dumpall备份失败”
else
log_message “pg_dumpall备份成功，备份文件：$BACKUP_DIR/postgresql_all_$(date +%Y%m%d).sql.gz”
fi
# 备份全局对象
sudo -u pgsql pg_dumpall -U pgsql -g | gzip > “$BACKUP_DIR/postgresql_globals_$(date +%Y%m%d).sql.gz”
if [ $? -ne 0 ]; then
log_message “备份全局对象失败”
else
log_message “备份全局对象成功，备份文件：$BACKUP_DIR/postgresql_globals_$(date +%Y%m%d).sql.gz”
fi
# 清理旧备份文件
log_message “清理旧备份文件…”
sudo find “$BACKUP_DIR” -name “postgresql_all_*.sql.gz” -mtime +30 -delete
sudo find “$BACKUP_DIR” -name “postgresql_globals_*.sql.gz” -mtime +30 -delete
log_message “pg_dumpall备份执行完成”
# 2. 设置定时执行
$ sudo crontab -e
# 添加以下内容
0 2 * * 0 /postgresql/scripts/pg_dumpall_backup.sh
# 3. 验证备份脚本执行
$ sudo tail -f /postgresql/logs/backup.log
$ ls -la /backup/postgresql_all_$(date +%Y%m%d).sql.gz
4.3 逻辑备份恢复案例
4.3.1 案例描述
场景：一个PostgreSQL数据库因误操作导致数据丢失，需要使用逻辑备份恢复数据。
4.3.2 实施方案
# 停止应用服务
$ sudo systemctl stop fgapplication.service
# 创建临时数据库
$ sudo -u pgsql psql -U pgsql -c “CREATE DATABASE fgedu_production_temp;”
# 从备份恢复到临时数据库
$ sudo -u pgsql pg_restore -U pgsql -d fgedu_production_temp /backup/fgedu_production_20260401.backup
# 验证临时数据库数据
$ sudo -u pgsql psql -U pgsql -d fgedu_production_temp -c “SELECT count(*) FROM fgedu_employees;”
# 重命名数据库
$ sudo -u pgsql psql -U pgsql -c “ALTER DATABASE fgedu_production RENAME TO fgedu_production_old;”
$ sudo -u pgsql psql -U pgsql -c “ALTER DATABASE fgedu_production_temp RENAME TO fgedu_production;”
# 启动应用服务
$ sudo systemctl start fgapplication.service
# 验证应用服务
$ sudo systemctl status fgapplication.service
# 2. 从pg_dumpall备份恢复
# 停止PostgreSQL服务
$ sudo systemctl stop postgresql
# 清理数据目录
$ sudo rm -rf /postgresql/data/*
# 初始化数据库
$ sudo -u pgsql initdb -D /postgresql/data
# 启动PostgreSQL服务
$ sudo systemctl start postgresql
# 从pg_dumpall备份恢复
$ gunzip -c /backup/postgresql_all_20260401.sql.gz | sudo -u pgsql psql -U pgsql
# 验证恢复结果
$ sudo -u pgsql psql -U pgsql -c “\l”
$ sudo -u pgsql psql -U pgsql -d fgedu_production -c “SELECT count(*) FROM fgedu_employees;”
Part05-风哥经验总结与分享
5.1 逻辑备份最佳实践
逻辑备份最佳实践：
- 制定合理的备份策略：根据业务需求和数据库环境，制定合理的备份策略
- 定期执行备份：按照备份计划定期执行备份操作
- 验证备份文件：定期验证备份文件的有效性，确保备份文件可用于恢复
- 存储备份文件：将备份文件存储在安全的位置，包括本地存储和远程存储
- 加密备份文件：对敏感数据的备份文件进行加密，确保数据安全性
- 测试恢复流程：定期测试恢复流程，确保能够快速恢复数据库
- 监控备份执行：监控备份的执行情况，及时发现和解决备份失败问题
- 文档记录：记录备份策略、执行情况和恢复流程，便于后续参考
5.2 常见问题与解决方案
逻辑备份常见问题及解决方案：
- 备份失败：解决方案：检查数据库连接、权限和磁盘空间
- 备份文件损坏：解决方案：定期验证备份文件，使用校验和确保文件完整性
- 磁盘空间不足：解决方案：监控磁盘空间，清理旧备份文件，使用压缩存储
- 备份时间过长：解决方案：使用并行备份，调整备份时间，使用增量备份
- 恢复失败：解决方案：检查恢复环境，确保备份文件完整，使用正确的恢复命令
- 权限问题：解决方案：确保备份用户有足够的权限，检查文件权限
- 网络传输失败：解决方案：确保网络连接稳定，使用断点续传
- 版本兼容性问题：解决方案：使用SQL格式备份，确保版本兼容性
5.3 备份技巧
逻辑备份技巧：
- 选择合适的备份格式：根据恢复需求选择合适的备份格式
- 使用压缩存储：压缩备份文件，减少存储空间
- 自动化备份：使用脚本和定时任务，自动化备份流程
- 多副本存储：将备份文件存储在多个位置，提高数据安全性
- 增量备份：使用增量备份，减少备份时间和存储空间
- 监控备份状态：使用监控工具，实时监控备份状态
- 定期清理：定期清理旧备份文件，避免磁盘空间不足
- 持续改进：根据备份执行情况，持续改进备份策略
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
