PostgreSQL逻辑备份与恢复完全指南：pg_dump与pg_restore详解

PostgreSQL 逻辑备份与恢复完全指南pg_dump 与 pg_restore 详解在数据库管理中备份与恢复是最后的防线。PostgreSQL 提供的pg_dump和pg_restore是逻辑备份领域的核心工具。与物理备份不同逻辑备份将数据导出为 SQL 命令或归档文件这种形式赋予了它独特的跨版本兼容性和细粒度选择性恢复的能力使其在数据迁移、开发环境搭建等场景中不可替代。第一部分核心定位——为什么选择逻辑备份首先要明确pg_dump是在线备份工具。它利用 PostgreSQL 的 MVCC多版本并发控制机制导出的数据库快照具有一致性且不会长时间阻塞其他用户的读写操作。它的核心优势在于“灵活”跨版本与跨平台迁移逻辑备份生成的 SQL 或归档文件不受底层操作系统和 PostgreSQL 大版本的限制是升级数据库的首选方案。选择性恢复你可以从全量备份中只恢复某一个特定的表-t或模式-n这在处理“误删表”事故时极为高效。第二部分备份策略——选对格式是成功的一半pg_dump的输出格式直接决定了你后续将使用何种恢复手段。主要分为以下三类1. 纯文本格式Plain SQL,-Fp这是默认格式生成一个包含重建数据库所需全部 SQL 命令的.sql文件。这种格式的优点是人类可读且可以使用psql直接执行。但其缺点是恢复时无法利用并行能力且不支持选择性恢复。# 导出纯文本格式使用INSERT语句替代COPY用于兼容性差的场景pg_dump-Upostgres-Fp--inserts-fbackup.sql mydb2. 自定义格式Custom,-Fc这是生产环境最推荐的格式。它生成的是一个压缩的归档文件体积小且必须配合pg_restore使用。这种格式最大的魅力在于它允许你对恢复过程进行“编排”你可以先恢复表结构再恢复数据甚至只恢复某一张表。3. 目录格式Directory,-Fd这种格式会创建一个目录内部包含多个压缩文件。这是实现并行备份-j的前置条件。它同样支持pg_restore的所有高级功能。第三部分性能加速与最佳实践大数据量下的备份恢复往往受限于 I/O 和 CPU。以下参数是性能调优的关键。并行化与压缩并行作业-j在目录格式-Fd下使用-j参数可以开启多线程并行导出或恢复。建议值通常不超过数据库服务器的 vCPU 核数。关闭压缩-Z0在pg_dump阶段如果服务器 CPU 资源紧张建议将压缩级别设为 0不压缩以节省 CPU 资源用于数据导出提升速度。生产环境压测前的准备在生产服务器上执行pg_dump会消耗大量资源。微软 Azure 的实践指南建议清理膨胀表在导出前检查并清理VACUUM死元组过多的表可显著减小导出体积。使用 PITR 备用服务器对于海量数据库建议从生产环境创建一个时间点恢复PITR的只读副本在此副本上执行pg_dump彻底隔离对主库的性能影响。第四部分恢复的艺术——pg_restore 的精细化控制pg_restore不仅可以恢复数据更是一个强大的“恢复编排器”。1. 恢复流程详解常规恢复分为两步先恢复结构再恢复数据或者直接全量恢复。使用-C参数可以强制在恢复前创建数据库需要先连接到postgres或template1数据库。# 从自定义格式备份恢复并自动创建目标数据库pg_restore-hlocalhost-Upostgres-C-dpostgres-j4mydb_backup.dump注意如果使用-d指定了目标库名且开启了-C目标库名必须与备份文件中的原始库名一致-d参数此时仅用于发起初始连接。2. 选择性恢复TOC 清单这是pg_restore最强大的功能。你可以通过-l参数列出归档文件的内容清单TOC编辑该文件后仅恢复指定的对象。# 1. 导出清单文件pg_restore-lmydb_backup.dumptoc.txt# 2. 编辑 toc.txt删除你不想恢复的条目行或取消注释# 3. 仅恢复清单中标记的对象pg_restore-Ltoc.txt-dtarget_db mydb_backup.dump3. 环境参数调优恢复速度往往比备份更慢因为需要重建索引和约束。在恢复前可以调整目标数据库的以下参数以获得极致速度maintenance_work_mem调大此参数如 2GB可加速索引创建。max_parallel_maintenance_workers允许并行创建索引。autovacuum off恢复期间建议关闭自动清理避免不必要的资源争抢待恢复完成后再开启并执行ANALYZE。第五部分实战命令速查可直接复制运行场景一日常全量备份最推荐目标备份整个数据库压缩存储且支持后续任意选择性恢复。# 使用自定义格式-Fc压缩级别中等-Z6pg_dump-hlocalhost-p5432-Upostgres-Fc-Z6-f/backup/mydb_$(date%Y%m%d).dump mydb-Fc自定义格式必须用pg_restore恢复但体积小且灵活。-Z6压缩级别 1-96 是速度和压缩比的平衡点。文件名带日期便于归档管理。场景二超大规模数据库并行备份目标利用多核 CPU 加速备份适用于 TB 级数据。# 必须先使用目录格式-Fdpg_dump-hlocalhost-Upostgres-Fd-j4-Z0-f/backup/mydb_dir mydb-Fd目录格式备份后会生成一个文件夹/backup/mydb_dir。-j 4开启 4 个并行线程导出建议不超过 CPU 核数。-Z0关闭压缩将压缩压力从 CPU 转移到磁盘 I/O通常更快。场景三仅备份特定 Schema 或表目标只备份public模式和orders表。# 只备份 public 模式下的所有对象pg_dump-Upostgres-npublic-Fc-fpublic_only.dump mydb# 只备份 orders 和 users 两张表pg_dump-Upostgres-torders-tusers-Fc-ftables_only.dump mydb-n指定模式Schema。-t指定表可重复使用多次。场景四全量恢复最常见目标将自定义格式备份完整恢复到新数据库。# 第一步创建空数据库如果不存在createdb-Upostgres newdb# 第二步全量恢复-j 4 并行恢复pg_restore-hlocalhost-Upostgres-dnewdb-j4/backup/mydb_20260708.dump-d newdb指定目标数据库。-j 4并行恢复大幅缩短恢复时间仅目录和自定义格式支持。场景五恢复到不同名称的数据库含自动创建目标从备份中恢复并自动创建目标数据库无需手动createdb。pg_restore-hlocalhost-Upostgres-C-dpostgres /backup/mydb_20260708.dump-C恢复时自动创建数据库数据库名取自备份文件中的原始库名。-d postgres仅用于建立初始连接实际会创建并恢复到原始库名。重点如果备份的是mydb此命令会自动创建mydb库并恢复数据-d postgres只是一个“跳板”。场景六紧急恢复单张表误删表目标从全量备份中只恢复employees表不碰其他数据。pg_restore-hlocalhost-Upostgres-dmydb-temployees /backup/mydb_20260708.dump-t employees只恢复这张表及其索引、约束。如果表已存在默认会报错需先DROP TABLE employees;或使用--clean参数。场景七使用清单文件精细化恢复高级目标只恢复表结构不恢复数据或只恢复部分对象。# 1. 导出清单TOC 目录pg_restore-l/backup/mydb_20260708.dumptoc.txt# 2. 编辑 toc.txt在不想恢复的条目前加上分号注释掉# 比如只保留所有 SCHEMA 和 TABLE 条目删除 TABLE DATA 条目# 3. 按修改后的清单恢复pg_restore-Ltoc.txt-dnewdb /backup/mydb_20260708.dump典型用途只恢复表定义无数据用于开发环境模拟或跳过某个损坏的索引。场景八跨大版本迁移如 PG 12 → PG 16目标通过逻辑备份无视版本差异迁移数据。# 在旧库PG 12上导出pg_dump-hold_host-Upostgres-Fc-fmigrate.dump mydb# 在新库PG 16上恢复pg_restore-hnew_host-Upostgres-C-dpostgres migrate.dump无需任何特殊参数PostgreSQL 的pg_restore会自动处理系统表差异。场景九纯 SQL 文本备份用于脚本或跨数据库迁移目标生成一份可读的 SQL 文件可用于其他数据库如 MySQL 需手动调整语法。# 使用 INSERT 语句而非 COPY兼容性更强pg_dump-Upostgres-Fp--inserts--column-inserts-fbackup.sql mydb-Fp纯文本格式默认。--inserts使用INSERT INTO替代COPY便于跨数据库工具处理。--column-inserts显式列出列名增强兼容性。恢复时直接用psqlpsql-Upostgres-dnewdbbackup.sql场景十恢复前清理旧对象覆盖恢复目标如果目标库已有同名表先删除再重建。pg_restore-hlocalhost-Upostgres-dmydb--clean--if-exists /backup/mydb_20260708.dump--clean恢复前执行DROP语句。--if-exists避免对象不存在时报错。第六部分最终建议与验证备份完成后务必模拟恢复一次验证备份文件是否可用不实际写数据pg_restore--list/backup/mydb_20260708.dump|head-20如果能正常打印出归档目录说明文件头完整基本可用。最后的提醒备份只是数据安全的一半另一半是定期恢复演练。只有真正在测试环境成功恢复过的备份才算有效的备份。