postgresql数据库备份与迁移

PostgreSQL 数据库备份与迁移

PostgreSQL 提供了 pg_dump 和 pg_restore 两个强大的工具用于数据库备份与恢复。本文将详细介绍各种备份策略和实际操作指南。

pg_dump 参数详解

# 基本参数说明
-U <用户名>          # 数据库用户
-h <host>            # 数据库主机地址
-p <端口>            # 数据库端口（默认 5432）
-d <数据库名>        # 要导出的数据库
-f <文件名>          # 导出文件名（可带路径）
-F <格式>            # 导出格式：p(plain SQL)、c(custom)、d(directory)、t(tar)
-s                   # 只导出表结构（不含数据）
-x                   # 忽略权限相关配置
-O                   # 忽略 owner（不含 OWNER TO 语句）
-W                   # 强制输入密码
--no-owner           # 不包含所有者信息
--if-exists          # 使用 IF EXISTS 语句

备份方式

方式一：导出为 tar 格式（推荐）

# 只备份表结构
pg_dump -U username -h localhost -p 5432 -d dbname \
  -f backup_struct.tar -F tar -s -O -x

# 备份完整数据（含数据）
pg_dump -U username -h localhost -p 5432 -d dbname \
  -f backup_full.tar -F tar -O -x

推荐使用 tar 格式，因为它保留了元数据信息，恢复时可以通过 pg_restore 灵活选择恢复哪些对象。

方式二：导出为 SQL 文件

# 纯文本格式，可用文本编辑器查看和修改
pg_dump -U username -h localhost -p 5432 -d dbname \
  -f backup.sql -x -s -O

# 压缩导出（推荐生产环境使用）
pg_dump -U username -h localhost -d dbname \
  | gzip > backup_$(date +%Y%m%d).sql.gz

方式三：目录格式（适合大型数据库）

# 导出为目录（每个表一个文件）
pg_dump -U username -h localhost -d dbname \
  -F d -f /backup/dir_backup -j 4   # -j 并行度

数据恢复

从 tar 文件恢复

# 方式一：使用 pg_restore（推荐）
pg_restore -U username -h host -p 5432 -d dbname \
  -O -x < backup_full.tar

# 如果目标数据库已存在，使用 -c 先清理再恢复
pg_restore -U username -h host -p 5432 -d dbname \
  -c -O -x < backup_full.tar

从 SQL 文件恢复

# 方式二：直接使用 psql 执行
psql -U username -h host -p 5432 -d dbname < backup.sql

# 或从压缩文件恢复
gunzip -c backup.sql.gz | psql -U username -d dbname

自动化备份脚本

以下脚本实现每日自动备份，保留最近 7 天的备份：

#!/bin/bash

DB_NAME="yourdb"
DB_USER="youruser"
BACKUP_DIR="/data/backup/postgresql"
RETENTION_DAYS=7
DATE=$(date +%Y%m%d_%H%M%S)

mkdir -p $BACKUP_DIR

# 备份并压缩
pg_dump -U $DB_USER -d $DB_NAME -F tar -O -x \
  | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.tar.gz

# 删除过期备份
find $BACKUP_DIR -name "*.tar.gz" -mtime +$RETENTION_DAYS -delete

# 记录日志
echo "[$(date)] Backup completed: ${DB_NAME}_${DATE}.tar.gz" >> $BACKUP_DIR/backup.log

数据库迁移

跨服务器迁移

# 直接通过管道传输（不落盘）
pg_dump -U source_user -h source_host -d sourcedb \
  | psql -U target_user -h target_host -d targetdb

# 或分步操作
# 1. 源服务器导出
pg_dump -U user -d sourcedb -F tar -O -x > backup.tar

# 2. 传输文件
scp backup.tar user@target:~/

# 3. 目标服务器恢复
pg_restore -U user -d targetdb -O -x < backup.tar

迁移注意事项

1. 版本兼容性：目标服务器版本应不低于源服务器
2. 字符编码：确保两端的编码一致（通常为 UTF8）
3. 扩展插件：检查源库使用的扩展（\dx），确保目标库已安装
4. 测试验证：迁移后检查表数量、行数、索引等是否一致

备份策略建议

备份类型	频率	保留时间	存储位置
全量备份	每日	7 天	本地磁盘
每周全量	每周	30 天	本地 + 远程
每月归档	每月	6 个月	对象存储（OSS/S3）

总结

PostgreSQL 的备份体系非常成熟，pg_dump + pg_restore 组合可以满足绝大多数场景。关键是要建立自动化备份流程，并定期验证备份文件的可用性。