PostgreSQL 备份与恢复

数据备份是数据库管理中至关重要的环节。本章将详细介绍 PostgreSQL 的各种备份和恢复方法，确保数据安全。

备份策略概述

备份类型

类型	说明	恢复粒度	优点	缺点
逻辑备份	导出 SQL 语句	表级别	灵活、跨版本	慢、占用空间大
物理备份	复制数据文件	整个集群	快、占用空间小	不够灵活
增量备份	只备份变化	时间点	节省空间	管理复杂

备份选择指南

pg_dump 逻辑备份

基本用法

pg_dump 是 PostgreSQL 最常用的逻辑备份工具，可以将数据库导出为 SQL 脚本或自定义格式文件。

# 基本备份命令
pg_dump -U postgres -d mydb > backup.sql

# 指定主机和端口
pg_dump -h localhost -p 5432 -U postgres -d mydb > backup.sql

# 使用环境变量设置密码
export PGPASSWORD="your_password"
pg_dump -U postgres -d mydb > backup.sql

输出格式

# 1. SQL 脚本格式（纯文本）
pg_dump -U postgres -d mydb -F p > backup.sql
# 恢复方式：psql -U postgres -d mydb < backup.sql

# 2. 自定义格式（推荐，支持并行恢复）
pg_dump -U postgres -d mydb -F c -f backup.dump
# 恢复方式：pg_restore -U postgres -d mydb backup.dump

# 3. 目录格式（支持并行备份和恢复）
pg_dump -U postgres -d mydb -F d -f /backup/mydb
# 恢复方式：pg_restore -U postgres -d mydb /backup/mydb

# 4. tar 格式
pg_dump -U postgres -d mydb -F t -f backup.tar
# 恢复方式：pg_restore -U postgres -d mydb backup.tar

选择性备份

# 只备份指定表
pg_dump -U postgres -d mydb -t users > users_backup.sql
pg_dump -U postgres -d mydb -t table1 -t table2 > tables_backup.sql

# 使用通配符备份多个表
pg_dump -U postgres -d mydb -t 'log_*' > logs_backup.sql

# 排除指定表
pg_dump -U postgres -d mydb -T log_* > backup_without_logs.sql

# 只备份表结构（不包含数据）
pg_dump -U postgres -d mydb --schema-only > schema.sql

# 只备份数据（不包含结构）
pg_dump -U postgres -d mydb --data-only > data.sql

# 备份指定 schema
pg_dump -U postgres -d mydb -n sales > sales_schema.sql

# 排除指定 schema
pg_dump -U postgres -d mydb -N temp > backup_without_temp.sql

并行备份

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

# -j 4：使用 4 个并行进程

压缩备份

# 使用 gzip 压缩
pg_dump -U postgres -d mydb | gzip > backup.sql.gz

# 使用自定义格式的压缩级别（0-9，默认 6）
pg_dump -U postgres -d mydb -F c -Z 9 -f backup.dump

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

pg_restore 恢复工具

基本恢复

# 恢复到现有数据库
pg_restore -U postgres -d mydb backup.dump

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

# 恢复到新数据库
createdb -U postgres mydb_new
pg_restore -U postgres -d mydb_new backup.dump

选择性恢复

# 只恢复指定表
pg_restore -U postgres -d mydb -t users backup.dump

# 只恢复指定 schema
pg_restore -U postgres -d mydb -n sales backup.dump

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

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

并行恢复

# 使用 4 个并行进程恢复
pg_restore -U postgres -d mydb -j 4 backup.dump

# 并行恢复可以显著提高大型数据库的恢复速度

恢复选项

# 清理现有对象后恢复（DROP 后重建）
pg_restore -U postgres -d mydb --clean backup.dump

# 如果对象已存在则跳过
pg_restore -U postgres -d mydb --if-exists backup.dump

# 禁用触发器（加快数据导入速度）
pg_restore -U postgres -d mydb --disable-triggers backup.dump

# 使用单事务恢复（失败时全部回滚）
pg_restore -U postgres -d mydb --single-transaction backup.dump

# 生成 SQL 脚本而不执行
pg_restore backup.dump > restore.sql

pg_dumpall 全库备份

pg_dumpall 用于备份整个 PostgreSQL 集群，包括所有数据库和全局对象（用户、角色、权限）。

# 备份所有数据库
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

恢复全库备份

# 恢复所有数据库（使用 psql）
psql -U postgres -f all_databases.sql

# 恢复全局对象（先恢复角色，再恢复数据库）
psql -U postgres -f globals.sql
psql -U postgres -f database_backup.sql

pg_basebackup 物理备份

pg_basebackup 用于创建数据库的物理备份，适合大型数据库和需要时间点恢复的场景。

基本用法

# 基本物理备份
pg_basebackup -U postgres -D /backup/base

# 指定主机和端口
pg_basebackup -h localhost -p 5432 -U postgres -D /backup/base

# 指定输出格式
pg_basebackup -U postgres -D /backup/base -Ft -z
# -Ft：tar 格式
# -z：压缩

# 创建基础备份并写入 WAL
pg_basebackup -U postgres -D /backup/base -X stream
# -X stream：同时流式传输 WAL 日志

备份选项

# 检查点模式
pg_basebackup -U postgres -D /backup/base --checkpoint=fast
# fast：立即执行检查点（推荐）
# spread：分散执行检查点（默认）

# 进度显示
pg_basebackup -U postgres -D /backup/base --progress

# 备份包含配置文件
pg_basebackup -U postgres -D /backup/base -c fast -X stream -P

# 压缩级别
pg_basebackup -U postgres -D /backup/base -Ft -z --compress=9

从物理备份恢复

# 1. 停止 PostgreSQL 服务
systemctl stop postgresql

# 2. 清空数据目录
rm -rf /var/lib/postgresql/16/main/*

# 3. 解压备份文件到数据目录
tar -xf /backup/base/base.tar -C /var/lib/postgresql/16/main

# 4. 如果有 WAL 日志，也解压
tar -xf /backup/base/pg_wal.tar -C /var/lib/postgresql/16/main/pg_wal

# 5. 修改文件权限
chown -R postgres:postgres /var/lib/postgresql/16/main

# 6. 启动 PostgreSQL
systemctl start postgresql

WAL 归档与 PITR

什么是 WAL？

WAL（Write-Ahead Logging）是 PostgreSQL 的预写式日志机制。所有数据修改在写入数据文件前，先写入 WAL 日志，确保数据的持久性和一致性。

配置 WAL 归档

编辑 postgresql.conf：

# 启用 WAL 归档
archive_mode = on

# 归档命令
archive_command = 'cp %p /archive/%f'

# 或者使用更安全的归档脚本
archive_command = 'rsync -a %p backup_server:/archive/%f'

# WAL 级别（minimal、replica、logical）
wal_level = replica

# WAL 保持数量
wal_keep_size = 1GB

时间点恢复（PITR）

PITR（Point-In-Time Recovery）允许将数据库恢复到任意时间点：

# 1. 创建基础备份
pg_basebackup -U postgres -D /backup/base -Ft -z -X stream -P

# 2. 持续归档 WAL 日志
# 配置 archive_command 后自动进行

# 3. 模拟数据丢失，需要恢复

# 4. 准备恢复环境
systemctl stop postgresql
rm -rf /var/lib/postgresql/16/main/*

# 5. 解压基础备份
tar -xf /backup/base/base.tar -C /var/lib/postgresql/16/main

# 6. 创建恢复配置文件 recovery.signal
touch /var/lib/postgresql/16/main/recovery.signal

# 7. 编辑 postgresql.conf 或 postgresql.auto.conf
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2024-01-15 14:30:00'
# 或恢复到指定事务 ID
# recovery_target_xid = '123456'
# 或恢复到最新状态
# recovery_target = 'immediate'

# 8. 启动数据库（自动进入恢复模式）
systemctl start postgresql

# 9. 恢复完成后，重命名 recovery.signal
# PostgreSQL 会自动将 recovery.signal 重命名为 recovery.done

使用 pgBackRest

pgBackRest 是一个专业的 PostgreSQL 备份工具，支持增量备份和 PITR：

# 安装 pgBackRest
# Ubuntu/Debian
apt-get install pgbackrest

# 配置 /etc/pgbackrest/pgbackrest.conf
[global]
repo1-path=/backup/pgbackrest
repo1-retention-full=2
process-max=4
compress-type=lz4

[main]
pg1-path=/var/lib/postgresql/16/main

# 创建 stanza（备份配置）
pgbackrest --stanza=main stanza-create

# 执行完整备份
pgbackrest --stanza=main --type=full backup

# 执行增量备份
pgbackrest --stanza=main --type=incr backup

# 执行差异备份
pgbackrest --stanza=main --type=diff backup

# 恢复数据库
pgbackrest --stanza=main --delta restore

# 恢复到指定时间点
pgbackrest --stanza=main --delta --target="2024-01-15 14:30:00" restore

自动化备份脚本

简单备份脚本

#!/bin/bash
# PostgreSQL 备份脚本

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

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

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

# 检查备份是否成功
if [ $? -eq 0 ]; then
    echo "备份成功: ${DB_NAME}_${DATE}.dump"
    
    # 删除旧备份
    find $BACKUP_DIR -name "*.dump" -mtime +$RETENTION_DAYS -delete
    echo "已删除 $RETENTION_DAYS 天前的旧备份"
else
    echo "备份失败!"
    exit 1
fi

完整备份脚本

#!/bin/bash
# PostgreSQL 完整备份脚本

set -e

# 配置变量
DB_HOST="localhost"
DB_PORT="5432"
DB_USER="postgres"
BACKUP_DIR="/backup/postgresql"
LOG_FILE="/var/log/postgresql-backup.log"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=30

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

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

# 记录开始时间
log "========== 备份开始 =========="

# 1. 备份全局对象（角色、表空间）
log "备份全局对象..."
pg_dumpall -h $DB_HOST -p $DB_PORT -U $DB_USER --globals-only | gzip > "$BACKUP_DIR/globals_${DATE}.sql.gz"
log "全局对象备份完成"

# 2. 获取所有数据库列表
log "获取数据库列表..."
DATABASES=$(psql -h $DB_HOST -p $DB_PORT -U $DB_USER -t -c "SELECT datname FROM pg_database WHERE datistemplate = false;")

# 3. 逐个备份数据库
for DB in $DATABASES; do
    DB=$(echo $DB | tr -d ' ')
    if [ -n "$DB" ]; then
        log "备份数据库: $DB"
        pg_dump -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB -F c -f "$BACKUP_DIR/${DB}_${DATE}.dump"
        log "数据库 $DB 备份完成"
    fi
done

# 4. 清理旧备份
log "清理 $RETENTION_DAYS 天前的旧备份..."
find $BACKUP_DIR -name "*.dump" -mtime +$RETENTION_DAYS -delete
find $BACKUP_DIR -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

# 5. 备份大小统计
BACKUP_SIZE=$(du -sh $BACKUP_DIR | cut -f1)
log "备份目录大小: $BACKUP_SIZE"

log "========== 备份完成 =========="

设置定时备份

# 编辑 crontab
crontab -e

# 每天凌晨 2 点执行备份
0 2 * * * /scripts/backup-postgresql.sh

# 每周日凌晨 3 点执行完整备份
0 3 * * 0 /scripts/backup-postgresql-full.sh

备份验证

验证备份完整性

# 验证自定义格式备份
pg_restore -l backup.dump > /dev/null

# 验证 SQL 备份（检查语法）
head -n 100 backup.sql

# 测试恢复到临时数据库
createdb -U postgres test_restore
pg_restore -U postgres -d test_restore backup.dump
psql -U postgres -d test_restore -c "SELECT COUNT(*) FROM users;"
dropdb -U postgres test_restore

定期恢复测试

#!/bin/bash
# 备份恢复测试脚本

BACKUP_FILE="/backup/postgresql/mydb_latest.dump"
TEST_DB="test_restore_$(date +%Y%m%d)"

# 创建测试数据库
createdb -U postgres $TEST_DB

# 恢复备份
pg_restore -U postgres -d $TEST_DB $BACKUP_FILE

# 验证数据
TABLE_COUNT=$(psql -U postgres -d $TEST_DB -t -c "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public';")
ROW_COUNT=$(psql -U postgres -d $TEST_DB -t -c "SELECT SUM(n_live_tup) FROM pg_stat_user_tables;")

echo "表数量: $TABLE_COUNT"
echo "总行数: $ROW_COUNT"

# 清理
dropdb -U postgres $TEST_DB

小结

本章我们学习了 PostgreSQL 备份与恢复：

备份类型：逻辑备份、物理备份、增量备份
pg_dump：逻辑备份工具，支持多种格式
pg_restore：恢复工具，支持并行恢复
pg_dumpall：全库备份，包含全局对象
pg_basebackup：物理备份，适合大型数据库
WAL 归档：实现时间点恢复（PITR）
自动化备份：脚本和定时任务
备份验证：确保备份可用性

练习

使用 pg_dump 备份指定数据库
编写自动化备份脚本，包含备份验证
配置 WAL 归档并测试时间点恢复
使用 pgBackRest 实现增量备份

备份策略概述​

备份类型​

备份选择指南​

pg_dump 逻辑备份​

基本用法​

输出格式​

选择性备份​

并行备份​

压缩备份​

pg_restore 恢复工具​

基本恢复​

选择性恢复​

并行恢复​

恢复选项​

pg_dumpall 全库备份​

恢复全库备份​

pg_basebackup 物理备份​

基本用法​

备份选项​

从物理备份恢复​

WAL 归档与 PITR​

什么是 WAL？​

配置 WAL 归档​

时间点恢复（PITR）​

使用 pgBackRest​

自动化备份脚本​

简单备份脚本​

完整备份脚本​

设置定时备份​

备份验证​

验证备份完整性​

定期恢复测试​

小结​

练习​

参考资源​

备份策略概述

备份类型

备份选择指南

pg_dump 逻辑备份

基本用法

输出格式

选择性备份

并行备份

压缩备份

pg_restore 恢复工具

基本恢复

选择性恢复

并行恢复

恢复选项

pg_dumpall 全库备份

恢复全库备份

pg_basebackup 物理备份

基本用法

备份选项

从物理备份恢复

WAL 归档与 PITR

什么是 WAL？

配置 WAL 归档

时间点恢复（PITR）

使用 pgBackRest

自动化备份脚本

简单备份脚本

完整备份脚本

设置定时备份

备份验证

验证备份完整性

定期恢复测试

小结

练习

参考资源