备份与恢复

备份是数据库管理的生命线。PostgreSQL 提供了多种备份策略，从简单的 SQL 导出到基于 WAL 的持续归档（PITR），能满足从开发到企业级的各种需求。

逻辑备份

使用 pg_dump 导出 SQL 格式备份，是简单场景的首选。

备份单个数据库

# 导出纯 SQL 格式（最通用，可读可编辑）
pg_dump -U postgres -d mydb > mydb_backup.sql

# 自定义格式（压缩，支持并行恢复）
pg_dump -U postgres -d mydb -Fc > mydb_backup.dump

# 目录格式（并行备份，默认每个表一个文件）
pg_dump -U postgres -d mydb -Fd -j 4 > mydb_backup_dir/

# 只备份 schema（不备份数据）
pg_dump -U postgres -d mydb --schema-only > mydb_schema.sql

# 只备份数据（不备份 schema）
pg_dump -U postgres -d mydb --data-only > mydb_data.sql

恢复备份

# 方式 1：SQL 格式直接导入
psql -U postgres -d mydb < mydb_backup.sql

# 方式 2：自定义格式用 pg_restore
pg_restore -U postgres -d mydb -j 4 mydb_backup.dump  # -j 并行恢复

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

备份远程数据库

pg_dump -h remote_host -p 5432 -U remote_user -d remote_db > remote_backup.sql

备份特定表

# 只备份指定的表
pg_dump -U postgres -d mydb -t orders -t customers > tables_backup.sql

# 排除某些表
pg_dump -U postgres -d mydb -T temp_data > no_temp_backup.sql

pg_dumpall 备份所有数据库

# 包括用户、表空间、数据库全局信息
pg_dumpall -U postgres > all_databases.sql

# 恢复
psql -U postgres -f all_databases.sql postgres

物理备份

物理备份直接复制数据库文件，适合大数据量场景。

冷备份

# 1. 停止 PostgreSQL 服务
# 2. 复制数据目录
cp -R /var/lib/postgresql/17/main /backup/pg_cold_backup/
# 3. 重启服务
pg_ctl start

热备份（pg_basebackup）

-- 先确认开启了归档
SHOW archive_mode;
SHOW archive_command;

# pg_basebackup 在数据库运行时创建物理备份
pg_basebackup -U postgres -D /backup/pg_basebackup/ -Ft -z -P

# 参数说明：
# -D：备份目录
# -Ft：tar 格式
# -z：压缩
# -P：显示进度
# -Xf：包含 WAL 文件

时间点恢复（PITR）

时间点恢复是 PostgreSQL 最强大的备份功能——可以恢复到任意时间点。

配置归档

# postgresql.conf
archive_mode = on
archive_command = 'cp %p /backup/archive/%f'
restore_command = 'cp /backup/archive/%f %p'

执行基础备份

# 做一次全量基础备份
pg_basebackup -U postgres -D /backup/base/ -Ft -z

模拟恢复

# 1. 假设误删了数据表，需要恢复到删除前的时间点
# 2. 创建恢复配置文件

# 在数据目录下创建 recovery.signal（PG 12+）
touch /var/lib/postgresql/17/main/recovery.signal

# 在 postgresql.conf 或 recovery.conf 中添加：
restore_command = 'cp /backup/archive/%f %p'
recovery_target_time = '2025-02-20 14:30:00+08'  # 恢复到误删除前

# 3. 启动 PostgreSQL，自动进入恢复模式
pg_ctl start
# 数据库恢复完成后，可以看到恢复到的状态

查看恢复状态

-- 恢复完成后
SELECT pg_is_in_recovery();  -- true = 正在恢复
SELECT pg_last_wal_receive_lsn();
SELECT pg_last_wal_replay_lsn();
SELECT * FROM pg_stat_progress_recovery;

单表恢复

# 从自定义格式备份中恢复单表
pg_restore -U postgres -d mydb -t orders -c mydb_backup.dump

# 从 SQL 格式中提取单表
grep -n "^COPY orders\|^INSERT INTO orders" mydb_backup.sql > orders_data.sql
psql -U postgres -d mydb -f orders_data.sql

自动化备份脚本

#!/bin/bash
# 自动备份脚本（每日执行）

DB_NAME="mydb"
BACKUP_DIR="/backup/daily"
DATE=$(date +%Y%m%d)
RETENTION_DAYS=30

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

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

# 删除 30 天前的备份
find "$BACKUP_DIR" -name "*.dump" -mtime +$RETENTION_DAYS -delete

echo "Backup completed: ${DB_NAME}_${DATE}.dump"

# crontab 每天凌晨 3 点执行
0 3 * * * /home/backup_script.sh

备份策略建议

场景	备份方式	频率	恢复时间	复杂度
个人开发	pg_dump SQL	按需	分钟级	低
小团队	pg_dump -Fc	每日	分钟级	低
企业生产	pg_basebackup + WAL	每日全量+连续增量	秒到分钟（PITR）	高
大型系统	流复制 + 备用服务器	持续	秒级	非常高

备份策略选择原则

1. 理解不同的备份类型

逻辑备份（pg_dump）：灵活，可恢复单表，跨版本兼容，适合中小型数据库
物理备份（pg_basebackup）：快，适合大型数据库，支持 PITR
快照备份：依赖存储系统（LVM、ZFS、云存储快照），最快速

2. 备份验证

# 定期测试备份是否可恢复（最重要的！）
pg_restore -U postgres -d test_verify --schema-only backup.dump

3. 备份大小估算

-- 估算单个表的大小
SELECT pg_size_pretty(pg_table_size('big_orders')) AS 表大小,
       pg_size_pretty(pg_total_relation_size('big_orders')) AS 含索引大小,
       pg_size_pretty(pg_database_size('current_database')) AS 库大小;

小结

备份是数据库管理的最后一道防线。小项目用 pg_dump 足够，生产环境必须用 WAL 归档实现 PITR。定期验证备份的可恢复性比备份本身更重要——一个不能恢复的备份等于没有备份。

下一篇文章将学习用户与权限管理，确保数据库安全。