获课地址：xingkeit.top/15582/

PostgreSQL 批量数据导入导出方案：从个人视角谈数据迁移

在日常工作中，我经常需要处理大批量数据的导入导出。无论是系统迁移时的数据转移、定时的数据备份，还是数据分析前的数据抽取，PostgreSQL 都是我最常用的数据库。经过多次实践和踩坑，我逐渐摸索出了一些适合不同场景的批量数据处理方案。今天想从个人经验出发，聊聊这些方案的适用场景和背后的考量。

COPY 命令：简单场景的首选

接触 PostgreSQL 之后，我最早学会的批量操作就是 COPY 命令。它直接与文件系统交互，绕开了客户端-服务器的逐条传输，速度远胜于 INSERT 语句。在我看来，COPY 最适合的场景是：数据量中等、格式规整、并且数据库服务器可以直接访问文件系统。

使用 COPY 导出时，可以生成 CSV、TEXT 或二进制格式。CSV 格式最通用，几乎任何数据处理工具都能读取；二进制格式最紧凑，但仅限于 PostgreSQL 之间交换数据。我自己的习惯是：如果数据要交给其他系统使用，选 CSV；如果只是在 PostgreSQL 实例之间迁移，选二进制，速度更快且没有类型转换的风险。

COPY 的导入也有明显的优势，尤其是错误处理。可以设置遇到错误时跳过某行继续执行，而不是整个事务回滚。这在处理“脏数据”时非常有用——你不想因为几行格式不对的数据而放弃整批导入。当然，这个特性需要谨慎使用，你要清楚自己在容忍什么样的数据质量问题。

不过 COPY 也有明显的限制。最麻烦的一点是：文件必须放在数据库服务器上，或者从客户端使用 STDIN。在云数据库环境里，你往往没有服务器文件系统的访问权限，这时候 COPY 就比较尴尬了。

pg_dump 与 pg_restore：数据库级别的迁移

当需要迁移整个数据库或者大量表的时候，我认为 pg_dump 和 pg_restore 是更合适的选择。它们不是为了“导入导出数据”而设计的，而是为了“迁移数据库”而存在的。

pg_dump 的妙处在于它导出的不仅是数据，还包括表结构、索引、约束、触发器等所有数据库对象。这一点在系统升级或环境迁移时格外重要——你不仅要搬走货物，还要把货架也原样搭建起来。

我个人最常用的组合是：使用自定义格式（-Fc）导出，然后用 pg_restore 并行恢复。自定义格式是压缩的，体积更小；支持选择性恢复，可以在恢复时只选择部分表；最吸引我的是支持并行恢复，几个 CPU 核心就开几个并行进程，速度提升明显。

但 pg_dump 也不是万能的。导出大库时，它会产生一个巨大的文件，磁盘空间可能成为瓶颈。另外，它本质上是逻辑备份，导出和恢复的速度都不如基于文件系统快照的物理备份。对于超大数据库，我通常会考虑其他方案。

外部数据包装器：跨库实时操作

对于需要“跨数据库查询或导入”的场景，我觉得外部数据包装器是一个非常优雅的方案。它让你可以在一个 PostgreSQL 实例中直接查询另一个数据库（甚至其他数据库系统）的表，就像查询本地表一样。

使用 FDW 做批量导入的流程通常是：创建外部服务器、用户映射，然后通过 CREATE TABLE AS 或者 INSERT INTO SELECT 把外部表的数据拉到本地。这种方式的好处是不产生中间文件，数据的传输在数据库内核中完成，而且可以结合 WHERE 条件只导入需要的数据。

我曾经用 FDW 做过一次数据聚合：把分布在三个 PostgreSQL 实例上的数据汇总到一个报表库中。整个过程可以用一句 SQL 完成，而不需要写脚本循环读取再写入。FDW 还支持把数据写回远程，实现双向的数据同步。

不过 FDW 也有性能开销。对于超大规模的批量传输，它的速度可能不如先导出成文件再导入。因为 FDW 本质上是逐行传输，网络延迟和协议开销在大数据量下会逐渐显现。

pg_bulkload：极限速度的追求

如果数据量大到 GB 甚至 TB 级别，普通的 COPY 都显得不够快时，pg_bulkload 是我会考虑的方案。这是一个第三方工具，专门为极速数据加载而设计。

pg_bulkload 的原理是绕过 PostgreSQL 的共享缓冲区和 WAL 日志，直接把数据写入数据文件。正因为跳过了这些常规流程，它才能达到惊人的速度。代价是：牺牲了一致性保证和崩溃恢复能力。如果在导入过程中数据库崩溃，数据可能是损坏的。而且绕过 WAL 意味着即使开启了归档，这部分数据也不会被记录。

基于这些权衡，我认为 pg_bulkload 只适合特定的场景：初始化一个分析型数据库、数据仓库的批量刷新、一次性数据迁移。在这些场景下，如果导入失败，你可以重新再来，丢失的数据成本可控。但对于生产系统的增量数据导入，我绝不会用它，安全比速度重要得多。

选择的标准：数据量与环境约束

回顾这些方案，我发现选择的根本依据是两个变量：数据量级和环境约束。

几百万行的数据，用 COPY 或者 FDW 都足够，主要看你的文件放在哪里、网络条件如何。几亿行甚至更大的数据集，就需要考虑分区导出、并行处理，或者直接上 pg_bulkload。

环境约束也很关键。如果你有数据库服务器的文件访问权限，COPY 是最简单直接的选择。如果用的是云数据库，可能只有 pg_dump 和 FDW 可用。如果需要长期、定期的数据同步，也许建立逻辑复制才是长久之计，而不是反复做全量导入导出。

批量数据导入导出这件事，其实没有“最好”的方案，只有“最合适”的方案。理解每种方案背后的设计思想——COPY 追求简单直接，pg_dump 追求完整迁移，FDW 追求跨库透明，pg_bulkload 追求极致速度——你才能在面对实际场景时，做出合理的判断。毕竟，数据迁移做完了不是终点，数据完整、过程可控才是真正的目标。