PostgreSQL 入门学习教程，从入门到精通，PostgreSQL 16 数据备份与还原详解 —语法、案例与实战(16）

PostgreSQL 16 数据备份与还原详解 —语法、案例与实战

✅ 一、数据备份（Backup）

PostgreSQL 提供逻辑备份（SQL/自定义格式）和物理备份（文件系统级），本章聚焦逻辑备份工具：pg_dump,pg_dumpall,pgAdmin 4。

1.1 使用 pgAdmin 4 备份数据库（图形化操作）

适用场景：开发、测试环境，不熟悉命令行的用户

📌 操作步骤：

1. 打开 pgAdmin 4 → 连接服务器 → 展开数据库
2. 右键目标数据库 →Backup...
3. 设置参数：
   • Filename：备份文件路径（如C:\backup\mydb_backup.sql）
   • Format：
     • Plain→ SQL 脚本（可读，兼容性好）
     • Custom→ 二进制格式（支持选择性还原，压缩率高）
     • Directory→ 目录格式（大库推荐）
     • Tar→ tar 归档格式
   • Encoding：UTF8（推荐）
   • Dump Options：
     • Only data→ 仅数据
     • Only schema→ 仅结构
     • Only schema + data→ 默认（全量）
4. 点击Backup→ 等待完成

✅优点：图形化、易用
❌缺点：不适合自动化、大库效率低

1.2 使用 pg_dump 工具备份数据库（命令行）

核心工具：备份单个数据库

📌 基本语法：

pg_dump[选项]数据库名>备份文件

📌 常用选项：

✅ 案例1：备份为 SQL 脚本（Plain 格式）

# 备份整个数据库为 SQL 文件（默认格式）pg_dump-hlocalhost-p5432-Upostgres mydb>mydb_full_backup.sql# 仅备份结构（无数据）pg_dump-Upostgres --schema-only mydb>mydb_schema.sql# 仅备份数据（无结构）pg_dump-Upostgres --data-only mydb>mydb_data.sql# 备份指定表pg_dump-Upostgres-temployees-tdepartments mydb>mydb_selected_tables.sql# 排除日志表pg_dump-Upostgres --exclude-table=logs mydb>mydb_no_logs.sql

✅适用场景：

• 小型数据库
• 需要人工编辑备份文件
• 跨版本迁移（SQL 格式兼容性最好）

✅ 案例2：备份为自定义格式（Custom Format - 推荐）

# 备份为自定义二进制格式（压缩、支持选择性恢复）pg_dump-Upostgres-Fc-fmydb_backup.custom mydb# 并行备份（PostgreSQL 9.3+，仅 directory 格式）pg_dump-Upostgres-Fd-j4-fmydb_backup_dir mydb# 带压缩的自定义格式（-Z 0~9，9为最高压缩）pg_dump-Upostgres-Fc-Z6-fmydb_compressed.backup mydb

✅优势：

• 压缩率高（节省空间）
• 支持pg_restore选择性还原
• 支持并行备份/恢复（加速大库操作）

1.3 使用 pg_dumpall 备份整个服务器

备份所有数据库 + 全局对象（角色、表空间、配置等）

📌 基本语法：

pg_dumpall[选项]>备份文件

✅ 案例3：备份整个 PostgreSQL 实例

# 备份所有数据库 + 全局对象（角色、表空间等）pg_dumpall-Upostgres-hlocalhost>full_server_backup.sql# 仅备份全局对象（角色、表空间）pg_dumpall-Upostgres --globals-only>globals_backup.sql# 仅备份某个数据库（等同 pg_dump）pg_dumpall-Upostgres-lmydb>mydb_via_dumpall.sql# 带清理命令（恢复时先 DROP 再 CREATE）pg_dumpall-Upostgres-c>full_server_with_clean.sql

⚠️注意：

• pg_dumpall只支持 Plain 格式（SQL 脚本）
• 不能并行备份
• 恢复时需超级用户权限

✅ 二、数据还原（Restore）

2.1 使用 pgAdmin 4 还原数据库

适用场景：开发、测试环境

📌 操作步骤：

1. 右键目标数据库 →Restore...
2. 选择备份文件（必须与备份格式匹配）
3. 设置参数：
   • Format：自动识别或手动选择
   • Restore options：
     • Clean before restore→ 先删除现有对象（危险！）
     • Single transaction→ 失败则全回滚
     • Include CREATE DATABASE→ 从备份创建新库
4. 点击Restore

✅注意事项：

• 还原前确保目标数据库存在（除非勾选 CREATE DATABASE）
• 还原用户需有目标库的 CREATE 权限
• 大文件还原可能超时 → 调整Preferences > Miscellaneous > Process timeout

2.2 使用 psql 还原数据库（SQL 格式）

适用：pg_dump或pg_dumpall生成的.sql文件

📌 基本语法：

psql[选项]数据库名<备份文件

✅ 案例4：还原 SQL 备份

# 还原到现有数据库（需先创建空库）createdb-Upostgres mydb_restored psql-Upostgres-dmydb_restored<mydb_full_backup.sql# 还原 pg_dumpall 备份（需超级用户）psql-Upostgres-ffull_server_backup.sql postgres# 注意：pg_dumpall 备份包含 CREATE DATABASE，会自动创建库# 带事务还原（失败则回滚）psql-Upostgres-dmydb_restored-vON_ERROR_STOP=1<mydb_full_backup.sql# 详细模式 + 错误停止psql-Upostgres-dmydb_restored-vON_ERROR_STOP=1-q-fmydb_full_backup.sql

✅常用 psql 选项：

• -v ON_ERROR_STOP=1→ 遇错停止（推荐）
• -q→ 静默模式（不显示 SQL）
• -1→ 单事务模式（等同--single-transaction）

2.3 使用 pg_restore 快速还原数据库（自定义/目录格式）

核心优势：支持选择性还原、并行还原、灵活控制

📌 基本语法：

pg_restore[选项]-d数据库名 备份文件

📌 常用选项：

✅ 案例5：还原自定义格式备份

# 创建目标数据库createdb-Upostgres mydb_restored# 完整还原自定义格式备份pg_restore-Upostgres-dmydb_restored mydb_backup.custom# 并行还原（4个进程）pg_restore-Upostgres-dmydb_restored-j4mydb_backup.custom# 仅还原结构pg_restore-Upostgres-dmydb_restored-smydb_backup.custom# 仅还原数据（需结构已存在）pg_restore-Upostgres-dmydb_restored-amydb_backup.custom# 还原指定表pg_restore-Upostgres-dmydb_restored-temployees mydb_backup.custom# 清理后还原（危险！先 DROP 所有对象）pg_restore-Upostgres-dmydb_restored-cmydb_backup.custom# 单事务模式（失败全回滚）pg_restore-Upostgres-dmydb_restored --single-transaction mydb_backup.custom

✅最佳实践：

• 生产环境优先使用pg_restore（性能好、控制灵活）
• 大库还原使用-j并行加速
• 重要操作前先-s仅还原结构测试

✅ 三、数据库迁移（Migration）

3.1 相同版本 PostgreSQL 之间迁移

最简单场景：直接备份还原

✅ 案例6：同版本迁移（源 → 目标）

# 在源服务器备份pg_dump-Upostgres-Fc-fmydb_migration.backup mydb# 拷贝备份文件到目标服务器scpmydb_migration.backup user@target-server:/tmp/# 在目标服务器还原createdb-Upostgres mydb pg_restore-Upostgres-dmydb /tmp/mydb_migration.backup

✅推荐：使用自定义格式 +pg_restore，支持并行

3.2 不同版本 PostgreSQL 之间迁移

原则：从低版本 → 高版本（兼容），高→低需特殊处理

✅ 案例7：从 PostgreSQL 14 → 16 迁移

# 在旧版本（14）服务器备份为 SQL 格式（兼容性最好）pg_dump-Upostgres --no-sync-fmydb_v14.sql mydb# 拷贝到新服务器（16）scpmydb_v14.sql user@pg16-server:/tmp/# 在新版本（16）创建数据库并还原createdb-Upostgres mydb psql-Upostgres-dmydb-vON_ERROR_STOP=1-f/tmp/mydb_v14.sql# ✅ 验证数据psql-Upostgres-dmydb-c"SELECT COUNT(*) FROM employees;"

⚠️注意：

• 使用--no-sync避免旧版本不支持的选项
• 还原后运行ANALYZE更新统计信息
• 检查扩展是否兼容（如 postgis）

❌ 从高版本 → 低版本？

不推荐！可能因语法/功能不兼容失败。
替代方案：

1. 导出为 CSV + 手动建表
2. 使用中间工具（如 pgloader）
3. 升级目标服务器版本

3.3 不同数据库之间迁移（如 MySQL → PostgreSQL）

常用工具：pgloader,ETL 工具, 手动导出导入

✅ 案例8：使用 pgloader 迁移 MySQL → PostgreSQL

# 安装 pgloader（Linux）sudoapt-getinstallpgloader# 创建迁移配置文件 migrate.load/* load database from mysql://user:pass@localhost/mydb into postgresql://postgres:pass@localhost/mydb_pg with include drop, create tables, create indexes, reset sequencessetMySQL PARAMETERS net_read_timeout='600';;*/# 执行迁移pgloader migrate.load# ✅ 验证psql-Upostgres-dmydb_pg-c"\dt"psql-Upostgres-dmydb_pg-c"SELECT COUNT(*) FROM users;"

✅ 案例9：手动 CSV 迁移

-- 在源数据库（MySQL）导出 CSVSELECT*FROMemployeesINTOOUTFILE'/tmp/employees.csv'FIELDSTERMINATEDBY','OPTIONALLYENCLOSEDBY'"'LINESTERMINATEDBY'\n';-- 在 PostgreSQL 创建表（需手动匹配结构）CREATETABLEemployees(idSERIALPRIMARYKEY,nameVARCHAR(100),emailVARCHAR(100),salaryNUMERIC(10,2));-- 导入 CSVCOPY employees(id,name,email,salary)FROM'/tmp/employees.csv'WITH(FORMAT csv,HEADERtrue,DELIMITER',',QUOTE'"');-- ✅ 验证SELECTCOUNT(*)FROMemployees;

✅推荐工具：

• pgloader：自动化迁移，支持多种源
• AWS DMS/Azure Data Factory：云环境
• Python pandas + SQLAlchemy：定制化迁移

✅ 四、综合实战案例

🎯 案例1：生产环境每日自动备份脚本

#!/bin/bash# 文件名：daily_backup.sh# 功能：每日自动备份数据库，保留7天# 配置DB_NAME="production_db"BACKUP_DIR="/backup/postgresql"DATE=$(date+%Y%m%d_%H%M%S)USER="backup_user"# 需有数据库连接权限# 创建备份目录mkdir-p$BACKUP_DIR# 执行备份（自定义格式 + 压缩）pg_dump-U$USER-Fc-Z6-f"$BACKUP_DIR/${DB_NAME}_${DATE}.backup"$DB_NAME# 记录日志echo"[$(date)] 备份完成:${DB_NAME}_${DATE}.backup">>/var/log/pg_backup.log# 删除7天前的备份find$BACKUP_DIR-name"*.backup"-mtime+7-delete# 发送通知（可选）# echo "备份完成" | mail -s "PostgreSQL Backup" admin@company.com

✅设置定时任务（crontab）：

# 每天凌晨2点执行02* * * /path/to/daily_backup.sh

🎯 案例2：灾难恢复演练（完整流程）

# 1. 模拟灾难：删除数据库dropdb-Upostgres production_db# 2. 从最近备份恢复# 假设最新备份：production_db_20250912_020000.backup# 3. 创建新数据库createdb-Upostgres production_db# 4. 并行还原（4进程）pg_restore-Upostgres-dproduction_db-j4\/backup/postgresql/production_db_20250912_020000.backup# 5. 验证关键表psql-Upostgres-dproduction_db-c"SELECT COUNT(*) FROM orders;"psql-Upostgres-dproduction_db-c"SELECT COUNT(*) FROM users;"# 6. 更新统计信息（优化查询计划）psql-Upostgres-dproduction_db-c"ANALYZE;"# 7. 通知团队恢复完成echo"灾难恢复完成！数据已还原至 2025-09-12 02:00"|mail-s"RECOVERY COMPLETE"team@company.com

🎯 案例3：跨云迁移（AWS RDS PostgreSQL → Azure PostgreSQL）

# 步骤1：从 AWS RDS 备份pg_dump-hmy-rds-instance.xxxxxx.us-east-1.rds.amazonaws.com\-p5432-Umaster_user\-Fc-Z6-faws_db.backup mydb# 步骤2：上传到 Azure Blob（或直接下载到本地）az storage blob upload\--account-name mystorage\--container-name backups\--fileaws_db.backup\--nameaws_db.backup# 步骤3：从 Azure VM 下载并还原到 Azure PostgreSQLwgethttps://mystorage.blob.core.windows.net/backups/aws_db.backup# 创建数据库createdb-hmy-azure-pg.postgres.database.azure.com\-p5432-Uadmin_user@my-azure-pg mydb_restored# 还原（Azure 需 SSL）pg_restore-hmy-azure-pg.postgres.database.azure.com\-p5432-Uadmin_user@my-azure-pg\-dmydb_restored\--sslmode=require\-j4\aws_db.backup# ✅ 验证连接psql"host=my-azure-pg.postgres.database.azure.com port=5432 dbname=mydb_restored user=admin_user@my-azure-pg sslmode=require"

✅ 五、常见问题及解答（FAQ）

❓ 疑问1：pg_dump 备份的文件只能在 PostgreSQL 中使用吗？

答案：基本是的，但有例外：

• Plain 格式（.sql）：
  • 可在其他数据库尝试执行（如 MySQL、SQL Server）
  • 但大概率失败！因为：
    • PostgreSQL 特有语法（如SERIAL,JSONB,ILIKE）
    • 函数不同（NOW()vsCURRENT_TIMESTAMP）
    • 数据类型不兼容

• Custom/Directory/Tar 格式：
  • 只能用pg_restore还原到 PostgreSQL
  • 是 PostgreSQL 专有二进制格式

✅跨数据库迁移正确姿势：

1. 使用pg_dump --data-only --column-inserts导出 INSERT 语句
2. 手动修改 SQL 语法适配目标数据库
3. 使用 ETL 工具（如 pgloader, Talend, Informatica）
4. 导出 CSV + 手动建表

# 导出为兼容性较好的 INSERT 语句pg_dump-Upostgres --data-only --column-inserts mydb>mydb_inserts.sql

❓ 疑问2：使用 pgAdmin 恢复数据库时需要注意什么问题？

关键注意事项：

1. 目标数据库必须存在（除非勾选 “Create Database”）
   • 否则报错：database "xxx" does not exist

2. 用户权限不足
   • 需对目标库有CREATE权限
   • 全局对象（角色、表空间）需超级用户

3. 备份格式不匹配
   • Custom 格式备份不能用 “Plain” 格式还原
   • pgAdmin 通常能自动识别，但手动选错会失败

4. 大文件超时
   • 默认超时 120 秒 → 修改：File > Preferences > Miscellaneous > Process timeout

5. 编码问题
   • 备份和目标库编码需一致（推荐 UTF8）
   • 否则出现乱码或报错

6. 对象冲突
   • 表已存在时，需勾选 “Clean before restore”（危险！）
   • 或手动先删除目标表

7. 扩展依赖
   • 备份中包含CREATE EXTENSION，目标库需已安装扩展

✅安全还原步骤：

1. 创建空数据库：CREATE DATABASE mydb_restored;
2. 在 pgAdmin 中右键该库 → Restore
3. 选择备份文件 → Format 选 “Custom”（如适用）
4. 不勾选 “Clean”（除非确定要删除现有数据）
5. 点击 Restore → 监控日志

✅ 六、备份还原最佳实践总结

1. 备份策略：

   • 全量备份+WAL 归档（实现 PITR）
   • 每日全备，保留 7-30 天
   • 关键库增加备份频率

2. 工具选择：

   • 日常备份：pg_dump自定义格式 +pg_restore
   • 全实例备份：pg_dumpall
   • 图形化操作：pgAdmin 4（开发环境）
   • 跨库迁移：pgloader / CSV

3. 还原测试：

   • 定期演练灾难恢复（至少每季度一次）
   • 验证备份文件完整性（pg_restore -l file.backup）

4. 安全与监控：

   • 备份文件加密存储
   • 监控备份任务是否成功（日志 + 告警）
   • 限制备份用户权限（非超级用户）

5. 性能优化：

   • 大库使用-j并行备份/恢复
   • 使用directory格式支持并行
   • 避开业务高峰期执行

🚀黄金法则：
没有验证的备份 = 没有备份！
定期测试还原流程，确保关键时刻能救命！

📚 建议结合pg_basebackup（物理备份）和WAL-G（云存储归档）构建企业级备份体系！