PostgreSQL 备份与恢复 #

备份概述 #

备份是数据安全的重要保障，PostgreSQL 提供多种备份方式。

text 
┌─────────────────────────────────────────────────────────────┐
│                    备份类型                                  │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  逻辑备份                                                    │
│  ├── pg_dump      - 单数据库备份                            │
│  ├── pg_dumpall   - 所有数据库备份                          │
│  ├── 可跨版本恢复                                          │
│  └── 适合小型数据库                                        │
│                                                             │
│  物理备份                                                    │
│  ├── pg_basebackup - 基础备份                               │
│  ├── 文件系统级别备份                                      │
│  ├── 恢复速度快                                            │
│  └── 适合大型数据库                                        │
│                                                             │
│  时间点恢复（PITR）                                          │
│  ├── 结合基础备份和 WAL 日志                               │
│  ├── 可以恢复到任意时间点                                  │
│  └── 适合关键业务系统                                      │
│                                                             │
└─────────────────────────────────────────────────────────────┘

pg_dump 逻辑备份 #

基本用法 #

bash 
# 备份单个数据库
pg_dump -U postgres -d mydb > mydb_backup.sql

# 备份到压缩文件
pg_dump -U postgres -d mydb | gzip > mydb_backup.sql.gz

# 备份为自定义格式（推荐）
pg_dump -U postgres -d mydb -F c -f mydb_backup.dump

# 备份为目录格式
pg_dump -U postgres -d mydb -F d -f /backup/mydb/

# 备份为 tar 格式
pg_dump -U postgres -d mydb -F t -f mydb_backup.tar

常用选项 #

bash 
# 只备份表结构
pg_dump -U postgres -d mydb --schema-only > schema.sql

# 只备份数据
pg_dump -U postgres -d mydb --data-only > data.sql

# 备份特定表
pg_dump -U postgres -d mydb -t employees > employees.sql

# 备份特定模式
pg_dump -U postgres -d mydb -n sales > sales_schema.sql

# 排除特定表
pg_dump -U postgres -d mydb -T audit_logs > mydb_backup.sql

# 包含创建数据库语句
pg_dump -U postgres -d mydb -C > mydb_backup.sql

# 压缩级别（0-9）
pg_dump -U postgres -d mydb -F c -Z 6 -f mydb_backup.dump

# 并行备份（目录格式）
pg_dump -U postgres -d mydb -F d -j 4 -f /backup/mydb/

# 完整选项示例
pg_dump -U postgres \
    -h localhost \
    -p 5432 \
    -d mydb \
    -F c \
    -Z 6 \
    -v \
    -f mydb_backup.dump

格式对比 #

text 
┌─────────────┬─────────────────────────────────────────────────┐
│ 格式        │ 说明                                            │
├─────────────┼─────────────────────────────────────────────────┤
│ -F p (plain)│ 纯 SQL 文本，可直接执行                         │
│ -F c (custom)│ 自定义格式，支持并行恢复，可选择恢复对象       │
│ -F d (directory)│ 目录格式，支持并行备份和恢复               │
│ -F t (tar)  │ tar 归档格式，不支持并行                       │
└─────────────┴─────────────────────────────────────────────────┘

pg_dumpall 全库备份 #

bash 
# 备份所有数据库
pg_dumpall -U postgres > all_databases.sql

# 只备份角色和表空间定义
pg_dumpall -U postgres --globals-only > globals.sql

# 只备份角色
pg_dumpall -U postgres --roles-only > roles.sql

# 只备份表空间
pg_dumpall -U postgres --tablespaces-only > tablespaces.sql

# 备份所有（包含数据）
pg_dumpall -U postgres -f all_backup.sql

pg_restore 恢复 #

基本用法 #

bash 
# 从自定义格式恢复
pg_restore -U postgres -d mydb mydb_backup.dump

# 从目录格式恢复
pg_restore -U postgres -d mydb /backup/mydb/

# 从 tar 格式恢复
pg_restore -U postgres -d mydb mydb_backup.tar

# 创建数据库后恢复
pg_restore -U postgres -C -d postgres mydb_backup.dump

常用选项 #

bash 
# 只恢复表结构
pg_restore -U postgres -d mydb --schema-only mydb_backup.dump

# 只恢复数据
pg_restore -U postgres -d mydb --data-only mydb_backup.dump

# 恢复特定表
pg_restore -U postgres -d mydb -t employees mydb_backup.dump

# 恢复特定模式
pg_restore -U postgres -d mydb -n sales mydb_backup.dump

# 并行恢复
pg_restore -U postgres -d mydb -j 4 mydb_backup.dump

# 清理后恢复（先删除再创建）
pg_restore -U postgres -d mydb --clean mydb_backup.dump

# 如果存在则跳过
pg_restore -U postgres -d mydb --if-exists mydb_backup.dump

# 禁用触发器
pg_restore -U postgres -d mydb --disable-triggers mydb_backup.dump

# 详细输出
pg_restore -U postgres -d mydb -v mydb_backup.dump

# 列出备份内容
pg_restore -l mydb_backup.dump

# 使用列表文件选择性恢复
pg_restore -U postgres -d mydb -L restore.list mydb_backup.dump

SQL 文本恢复 #

bash 
# 从 SQL 文本恢复
psql -U postgres -d mydb < mydb_backup.sql

# 从压缩文件恢复
gunzip -c mydb_backup.sql.gz | psql -U postgres -d mydb

# 恢复全局对象
psql -U postgres -f globals.sql

pg_basebackup 物理备份 #

基本用法 #

bash 
# 基础备份
pg_basebackup -U postgres -D /backup/base

# 压缩备份
pg_basebackup -U postgres -D /backup/base -z

# 指定压缩格式
pg_basebackup -U postgres -D /backup/base --format=tar --gzip

# 包含 WAL 文件
pg_basebackup -U postgres -D /backup/base --xlog-method=stream

# 检查点模式
pg_basebackup -U postgres -D /backup/base --checkpoint=fast

# 完整选项示例
pg_basebackup -U postgres \
    -h localhost \
    -p 5432 \
    -D /backup/base \
    --format=tar \
    --gzip \
    --xlog-method=stream \
    --checkpoint=fast \
    --progress \
    --verbose

备份选项说明 #

text 
┌─────────────────────┬─────────────────────────────────────────┐
│ 选项                │ 说明                                    │
├─────────────────────┼─────────────────────────────────────────┤
│ -D, --pgdata        │ 备份目录                                │
│ -F, --format        │ 输出格式：plain, tar                    │
│ -z, --gzip          │ 启用 gzip 压缩                          │
│ -Z, --compress      │ 压缩级别（0-9）                         │
│ -X, --xlog-method   │ WAL 方法：none, fetch, stream           │
│ -c, --checkpoint    │ 检查点：fast, spread                    │
│ -P, --progress      │ 显示进度                                │
│ -v, --verbose       │ 详细输出                                │
│ -R, --write-recovery-conf │ 创建 standby.signal 和配置       │
└─────────────────────┴─────────────────────────────────────────┘

PITR 时间点恢复 #

配置 WAL 归档 #

ini 
# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /archive/%f'

# 或使用更可靠的归档命令
archive_command = 'rsync -a %p backup_server:/archive/%f'

执行基础备份 #

bash 
# 创建基础备份
pg_basebackup -U postgres -D /backup/base_$(date +%Y%m%d) --xlog-method=stream

恢复到指定时间点 #

bash 
# 1. 停止 PostgreSQL 服务
pg_ctl stop

# 2. 备份当前数据目录
mv /var/lib/postgresql/16/main /var/lib/postgresql/16/main.old

# 3. 恢复基础备份
tar -xf /backup/base_20260329.tar -C /var/lib/postgresql/16/main

# 4. 创建恢复配置
cat > /var/lib/postgresql/16/main/postgresql.auto.conf << EOF
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-03-29 14:30:00'
recovery_target_action = 'promote'
EOF

# 5. 创建恢复信号文件
touch /var/lib/postgresql/16/main/recovery.signal

# 6. 启动 PostgreSQL
pg_ctl start

恢复目标选项 #

ini 
# 恢复到指定时间
recovery_target_time = '2026-03-29 14:30:00'

# 恢复到指定事务 ID
recovery_target_xid = '12345'

# 恢复到指定 WAL 位置
recovery_target_lsn = '0/5000000'

# 恢复到指定恢复点
recovery_target_name = 'before_upgrade'

# 恢复到最早一致点
recovery_target = 'immediate'

# 恢复后操作
recovery_target_action = 'promote'    # 提升为主库
recovery_target_action = 'pause'      # 暂停
recovery_target_action = 'shutdown'   # 关闭

创建恢复点 #

sql 
-- 创建命名恢复点
SELECT pg_create_restore_point('before_critical_operation');

-- 切换 WAL 文件
SELECT pg_switch_wal();

备份策略 #

备份策略设计 #

text 
┌─────────────────────────────────────────────────────────────┐
│                    备份策略示例                              │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  小型数据库（< 100GB）                                      │
│  ├── 每日全量备份（pg_dump）                               │
│  ├── 保留 7 天                                            │
│  └── 异地存储                                              │
│                                                             │
│  中型数据库（100GB - 1TB）                                  │
│  ├── 每周全量备份（pg_basebackup）                         │
│  ├── 每日增量备份                                          │
│  ├── WAL 归档                                              │
│  └── PITR 支持                                             │
│                                                             │
│  大型数据库（> 1TB）                                        │
│  ├── 基础备份 + WAL 归档                                   │
│  ├── 流复制热备                                            │
│  ├── 定期测试恢复                                          │
│  └── 自动化监控                                            │
│                                                             │
└─────────────────────────────────────────────────────────────┘

自动化备份脚本 #

bash 
#!/bin/bash
# backup.sh

# 配置
DB_NAME="mydb"
BACKUP_DIR="/backup"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7

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

# 执行备份
pg_dump -U postgres -d $DB_NAME -F c -f "$BACKUP_DIR/${DB_NAME}_${DATE}.dump"

# 检查备份是否成功
if [ $? -eq 0 ]; then
    echo "Backup completed: ${DB_NAME}_${DATE}.dump"
    
    # 删除旧备份
    find $BACKUP_DIR -name "${DB_NAME}_*.dump" -mtime +$RETENTION_DAYS -delete
    
    # 发送通知
    echo "Backup successful" | mail -s "PostgreSQL Backup" admin@example.com
else
    echo "Backup failed!"
    exit 1
fi

定时任务 #

bash 
# crontab -e

# 每天凌晨 2 点执行备份
0 2 * * * /scripts/backup.sh >> /var/log/backup.log 2>&1

# 每周日凌晨 3 点执行全库备份
0 3 * * 0 /scripts/full_backup.sh >> /var/log/backup.log 2>&1

验证备份 #

测试恢复 #

bash 
# 创建测试数据库
createdb -U postgres test_restore

# 恢复到测试数据库
pg_restore -U postgres -d test_restore mydb_backup.dump

# 验证数据
psql -U postgres -d test_restore -c "SELECT COUNT(*) FROM employees;"

# 清理测试数据库
dropdb -U postgres test_restore

检查备份完整性 #

bash 
# 检查备份文件
pg_verifybackup /backup/base_20260329

# 列出备份内容
pg_restore -l mydb_backup.dump

# 检查 SQL 备份语法
head -100 mydb_backup.sql

学习路径 #

text 
运维阶段
├── 用户权限管理
├── 备份与恢复（本文）
└── 性能优化

下一步 #

掌握了备份与恢复后，接下来学习 性能优化，了解如何优化数据库性能！

最后更新：2026-03-29