PostgreSQL COPY命令实战：批量数据导入导出与性能优化

1. 为什么我劝你认真学一下 COPY 命令干 PostgreSQL 运维和开发这些年我越来越觉得 COPY 命令是被低估的一个功能。很多人一提到数据导入导出第一反应就是 pg_dump 或者 Navicat 之类的图形工具但真正到了需要处理几百万甚至上千万行数据的时候那些工具要么慢得让人抓狂要么在中间环节出了各种性格诡异的问题。而 COPY TO 和 COPY FROM 这对命令才是 PostgreSQL 里处理大批量数据搬家的正统答案。先说它解决的核心问题你需要在 PostgreSQL 和外部文件之间高效地搬运数据。注意关键词是“高效”。如果只是几十条几百条数据INSERT 语句一条条写完全没问题但数据量一旦上了十万、百万级别逐条 INSERT 的开销会把你拖垮。COPY 之所以快是因为它绕过了很多逐条 SQL 执行时的额外开销——比如词法分析、计划生成、事务日志的逐条刷新这些环节整批数据像流水线一样灌进去或者倒出来速度差别可以到一到两个数量级。适合谁来学凡是跟 PostgreSQL 打交道的同学都应该掌握尤其是这三类人第一类是做数据迁移和同步的工程师经常要在异构系统之间搬数据第二类是做数据分析的需要把数据库结果导出成文件再交给 Python、R 或者其他工具处理第三类是 DBA日常备份、初始化数据、修复数据都得靠它。即使是刚入门的新手学会这一对命令也会让你在处理批量数据时少走很多弯路。这篇文章不讲虚的直接把 COPY TO 和 COPY FROM 从语法细节到实战踩坑一次讲透包括各种参数的作用原理、文件格式的选择逻辑、常见报错的排查思路以及我在实际项目中总结出来的经验和教训。内容会比较长但保证每一段都有实际价值。2. 先搞懂 COPY 命令的本质和适用场景2.1 COPY 到底是什么它和普通 INSERT 有什么本质区别很多初学者第一次看到 COPY 的语法时会觉得它不过就是个批量 INSERT 的语法糖。这个理解不能说全错但太表面了。COPY 和 INSERT 在底层执行路径上有本质差别。当你执行一条 INSERT 语句时PostgreSQL 要经历接收 SQL 文本、词法分析、语法分析、语义分析、生成执行计划、执行插入、刷新事务日志这一整套过程。即使你用了预处理语句或者批处理每条 INSERT 仍然要经过大部分环节。更关键的是每条 INSERT 默认都是一个独立事务涉及事务提交的开销。而 COPY 命令走的是另一条路。它把数据当作一个整体流来处理服务端直接从文件或者标准输入读取数据按照协议解析然后批量写入表。整个过程中事务开销被摊薄到一大批数据上。实测下来COPY 导入数据的速度比逐条 INSERT 快五到二十倍是常态数据量越大、表结构越简单差距越明显。注意COPY 命令在 PostgreSQL 内部使用专门的二进制/文本协议与前端通信在服务端执行时直接由后端进程读取文件不走 SQL 解析器。这也是它能做到高性能的核心原因。另外还有个实际区别COPY 是原子的。一条 COPY 命令要么全部成功要么全部失败不存在导入到一半停下来这种状态前提是你在单个事务里执行它默认就是这么运行的。这一点在数据初始化、数据迁移场景里非常重要你不用自己写一堆事务控制逻辑去保障一致性。2.2 COPY 的典型应用场景什么时候该用它什么时候不该用搞清楚原理之后我们得明确它的适用范围。我列一下实际工作中最常遇到的使用场景数据初始化与重置新建一张配置表、字典表需要把一批初始数据灌进去。用 COPY FROM 从 CSV 或者 SQL 格式的文件导入比写几百条 INSERT 清爽得多。数据迁移与同步把某张表的数据导出成文件再在另一套环境里导入。跨 PostgreSQL 实例、跨版本升级、从测试环境同步到生产环境都可以用这种方式。备份与恢复的补充手段pg_dump 是全库或整库的逻辑备份但如果你只需要备份一张大表用 COPY TO 导出某个表的数据文件速度和灵活性都更好。有些 DBA 会把它当作大表备份的补充方案。对接外部数据处理链路数据分析师经常要把库表导出成 CSV喂给 Python 脚本或者 Excel 做进一步处理反过来清洗好的结果文件又需要导回数据库。COPY 是最直接的通道。大数据量导入的初始化加载比如从旧系统导出的历史数据文件几千万行那种。用 COPY FROM 配合分区表性能表现远优于逐条 INSERT。但也有不适合用 COPY 的场景这个我必须说清楚不然有人拿它去做不该做的事会踩大坑在线业务的小批量数据写入实时的用户操作比如订单创建、评论提交这种必须走应用层的 INSERT/UPDATE保证业务逻辑和事务边界COPY 不是替代品。数据结构需要转换的场景COPY 是纯数据搬运不做复杂的字段映射、类型转换、去重合并。如果数据在导入前需要清洗、关联、聚合你应该先用脚本处理成目标格式再 COPY 进去。跨异构数据库的直接迁移比如从 MySQL 导数据到 PostgreSQLCOPY 本身不能直接读 MySQL 的文件格式。你得先通过其他工具导出成通用格式CSV再让 COPY 接手。这里 COPY 是环节之一不是全部。2.3 和 pg_dump、其他导入导出方式的横向对比既然提到替代方案我干脆放个对比表让大家直观感受 COPY 在整个 PostgreSQL 工具生态里的位置。工具/方式主要用途速度表现灵活性典型适用场景pg_dump / pg_restore全库/整库逻辑备份恢复中等结构数据一体可选择性恢复数据库级备份、跨版本升级COPY TO / COPY FROM单表/查询结果的数据搬运非常快仅搬运数据不管表结构大批量数据导入导出、迁移、对接外部工具INSERT 批处理应用层写入较慢可执行任意业务逻辑在线交易、业务数据实时写入外部表file_fdw/postgres_fdw把外部文件/远端库映射为表读取时中等数据不落地实时查询外部数据源查询分析不导入本地一句话总结我的实践经验涉及表结构变更是 pg_dump 的主场涉及纯数据搬运尤其数据量大的时候COPY 是首选涉及业务逻辑写入老老实实用 INSERT。三者不是竞争关系而是互补关系。3. COPY TO把数据倒出来比你想的更有讲究3.1 基础语法与最小可用示例COPY TO 的语法并不复杂主干是COPY table_name [ ( column_name [, ...] ) ] TO { filename | PROGRAM command | STDOUS } [ [ WITH ] ( option [, ...] ) ]注意这里有个细节PostgreSQL 9.0 之前用的是旧式语法比如COPY table TO file WITH CSV HEADER9.0 之后推荐用带括号的新式选项写法COPY table TO file WITH (FORMAT CSV, HEADER true, DELIMITER ,);新式写法更规范选项之间的关系也更清晰我建议一律用新式。旧式虽然还能用但迟早会被淘汰。最小可用的示例长这样COPY users TO /tmp/users.csv WITH (FORMAT CSV, HEADER true);这条命令把users表的所有数据导出成 CSV 文件第一行是列名。就这么简单已经能应付很多日常需求了。但你真的以为 COPY TO 就这点东西那后面的内容可以跳过不用看了。3.2 格式选择的底层逻辑CSV、TEXT、BINARY 怎么选COPY 支持三种格式CSV、TEXT、BINARY。很多人只知道 CSV另外两种用得少但它们在特定场景下价值很大。TEXT 格式是 COPY 的默认格式。它和 CSV 的核心区别是TEXT 格式使用制表符Tab作为默认分隔符而且对特殊字符的处理规则不一样。TEXT 格式下NULL 值默认表示为\N这个反斜杠加 N 的组合这是 PostgreSQL 特有的表示法。如果你导出的文件是给另一个 PostgreSQL 实例用TEXT 格式往往比 CSV 更合适因为对各类特殊字符的转义规则更完备不容易出幺蛾子。CSV 格式则是通用性最强的选择因为它是不管什么数据库、什么编程语言都认的标准格式方便接各种外部工具。CSV 格式下NULL 值默认是空字符串也可以改成其他标记分隔符默认是逗号。导出给 Excel、Python pandas、数据分析平台看的一律用 CSV。BINARY 格式是另一种思路。它不是给人看的而是用 PostgreSQL 内部紧凑的二进制表示法来存储数据。它的优势有两点一是文件更紧凑二是导入导出的速度更快因为少了文本解析和类型转换的开销。但代价是文件完全不可读、不可编辑而且只能用在 PostgreSQL 到 PostgreSQL 的搬运场景离开了 PostgreSQL 环境就是一堆乱码。顺便提一句BINARY 格式的 COPY 和普通文本文件不同HEADER选项的行为也不一样——它会写一个固定结构的文件头包含一些元数据信息。我建议按这个逻辑来选给外部系统用、给人看的、要拿去编辑清洗的选 CSV。两个 PostgreSQL 之间搬运且不经过人工处理选 TEXT 或 BINARY。追求极致的导入导出速度文件不需要被任何外部工具读取选 BINARY。3.3 核心选项逐个拆解DELIMITER、HEADER、QUOTE、FORCE_QUOTE 等DELIMITER指定列分隔符。CSV 默认是逗号TEXT 默认是制表符。有些场景需要自定义分隔符比如数据本身包含大量逗号又不想加引号转义可以把分隔符换成|或者;。但注意两点一是分隔符必须是单个单字节字符不能是字符串二是\作为分隔符需要额外注意转义规则。COPY table TO /tmp/data.csv WITH (FORMAT CSV, DELIMITER |, HEADER true);HEADER这个选项很多人以为只有布尔值true/false其实还可以传列名列表。默认HEADER true是把实际的列名写进去但如果你导出的是查询结果可能希望用自定义的列名那么可以写COPY (SELECT id, name, created_at FROM users WHERE status active) TO /tmp/active_users.csv WITH (FORMAT CSV, HEADER 用户ID,用户名,创建时间);这个用法在做数据交付时非常实用免去了导完再改表头的麻烦。QUOTE指定字段引号字符CSV 默认是双引号。当字段值里包含分隔符、引号、换行符这些特殊字符时COPY 会自动用 QUOTE 字符把整个字段包起来。绝大多数场景用默认值就行但如果你要生成的文件需要给某个特定系统消费而那个系统对引号字符有特殊要求这个选项就派上用场了。FORCE_QUOTE是 COPY TO 特有的选项它强制对指定列的所有值都加引号不管值里有没有特殊字符。什么时候用这个一种场景是某些下游系统要求所有字段都被引号包裹否则解析异常另一种是导出成 CSV 给 Excel 用时强制引号可以避免一些格式上的意外表现。COPY users TO /tmp/users.csv WITH (FORMAT CSV, HEADER true, FORCE_QUOTE (id, name));ENCODING指定导出文件的字符编码。PostgreSQL 默认会按数据库的编码来输出但如果目标系统用的是另一种编码你可以在导出时直接转换。比如数据库是 UTF-8但 Excel 老版本对 UTF-8 不带 BOM 的支持有问题你可以导出成 GBKCOPY users TO /tmp/users_gbk.csv WITH (FORMAT CSV, HEADER true, ENCODING GBK);注意ENCODING 选项做的是转码不是简单标记。如果数据库里某些字符在目标编码中不存在导出会直接报错。出现这种情况就说明你的数据本身和目标编码不兼容需要处理数据而不是硬转。3.4 带查询条件的导出COPY (SELECT ...) TO 的妙用COPY TO 一个容易被忽略的能力是它不只是能导整张表还能导任意 SELECT 查询的结果。这个功能在实践中的价值非常大。COPY ( SELECT o.order_id, u.name AS user_name, o.total_amount, o.created_at FROM orders o JOIN users u ON u.id o.user_id WHERE o.created_at 2024-01-01 AND o.payment_status paid ORDER BY o.created_at ) TO /tmp/paid_orders_2024.csv WITH (FORMAT CSV, HEADER true);不需要先把查询结果塞进临时表不需要导出全表再筛选直接在导出时完成条件和关联处理这对于数据交付场景简直是神器。数据部门要个报表数据你一条命令就能交付不用费劲去建临时表、导数据、再清理临时表。还有一点值得夸的是这个子查询可以是任意复杂的只要权限允许子查询里甚至可以包含多个 CTECommon Table Expression。所以我强烈建议你把这个能力刻在脑子里——凡是能从库里查出来的数据几乎都能用 COPY 把它倒成文件。3.5 导出到标准输出和远程命令COPY ... TO STDOUT / PROGRAM文件路径不一定是必须的。COPY 支持把数据输出到标准输出STDOUT这个能力在和 psql 配合时特别有用psql -c COPY users TO STDOUT WITH (FORMAT CSV, HEADER true) /tmp/users.csv这种写法相当于让 psql 把数据从服务器拉回来再重定向到本地文件它的意义在于你不需要在数据库服务器上有写文件的权限在客户端机器上就能完成导出。对于云数据库、托管数据库这种你不能碰服务器文件系统的场景这是导出数据的主要手段。再看 PROGRAM 模式。它允许你指定一个 shell 命令让 COPY 直接写进命令的管道里COPY users TO PROGRAM gzip /tmp/users.csv.gz WITH (FORMAT CSV, HEADER true);或者反过来从远程服务器拉数据并压缩psql -c COPY users TO PROGRAM gzip /tmp/users.csv.gz WITH (FORMAT CSV, HEADER true)注意 PROGRAM 的命令是直接在数据库服务器上执行的不是客户端。所以用这个功能需要两个条件一是你有超级用户权限或者被赋予了相关权限二是在 shell 命令注入方面要非常谨慎绝不能让外部输入拼进命令字符串里。这是一个危险但强大的能力生产环境中除非必要建议少用。4. COPY FROM把数据灌进去细节决定成败4.1 基础语法和数据校验机制COPY FROM 是 COPY TO 的逆操作基础语法COPY table_name [ ( column_name [, ...] ) ] FROM { filename | PROGRAM command | STDIN } [ [ WITH ] ( option [, ...] ) ]它的核心执行逻辑读取文件按格式解析逐行校验转换成目标列的类型然后批量写入。对每一行数据PostgreSQL 会做类型检查、约束检查如果表上有 NOT NULL、CHECK 约束的话、触发器等。所以 COPY FROM 不是一个“不检查就硬灌”的工具它只是把解析和写入的开销分摊到大批量数据上而已。最小示例COPY users FROM /tmp/users.csv WITH (FORMAT CSV, HEADER true);这条命令要求/tmp/users.csv的列顺序、列名和users表的定义对得上。如果对不上你有两个选择一是把文件弄成和目标表完全一致的格式二是用 COPY 的列清单功能只导入指定列。COPY users (id, name, email, created_at) FROM /tmp/users.csv WITH (FORMAT CSV, HEADER true);4.2 列映射的策略文件列和表列不一致时怎么办在实际工作中你拿到的数据文件列顺序和目标表不一致是家常便饭。比如外部系统导出的 CSV 可能是姓名,邮箱,ID这种顺序而表结构是id, name, email。方案一在 COPY FROM 里写上目标列的顺序让 PostgreSQL 按位置去对应COPY users (name, email, id) FROM /tmp/users.csv WITH (FORMAT CSV, HEADER true);这里的关键理解是COPY 是按位置对应不是按名字对应。它把文件的第 1 列给name第 2 列给email第 3 列给id。所以列名清单的顺序写对比名字本身更重要。方案二如果文件里有些列你根本不需要导入或者文件里缺了表的某些列你可以只列需要的列缺的列会走默认值如果有 DEFAULT 定义的话否则填 NULLCOPY users (name, email) FROM /tmp/partial_users.csv WITH (FORMAT CSV, HEADER true);这种情况像是外部只提供了姓名和邮箱id 让数据库自增生成created_at 用默认的now()。但要注意如果目标列有 NOT NULL 约束且没有默认值缺列导入会直接失败。经验拿到外部数据先做一次抽样预览确认列数、分隔符、编码、表头格式都符合预期再执行 COPY。我见过太多人因为源文件中混入了 BOM 头、列数不一致、隐藏字符等问题导致 COPY 跑到一半报错回滚重来浪费大量时间。4.3 NULL 表示的坑CSV 的空值到底是 NULL 还是空字符串这是 COPY FROM 最容易让人栽跟头的地方。得先想明白一个概念CSV 里的“空”和数据库里的 NULL 是两回事。在 CSV 格式下COPY 默认把空字段连续两个分隔符中间什么都没有当作空字符串而不是 NULL。如果你想让它把空字段视为 NULL要明确告诉它COPY users FROM /tmp/users.csv WITH (FORMAT CSV, HEADER true, NULL );只要加上NULL 所有空字段就会被当作 NULL 导入。这里有个很微妙的点空字符串和 NULL 在业务上可能代表完全不同的意思——“没填”和“填了个空值”在很多业务逻辑里是不同的。所以你在导入之前必须先想清楚源文件里的空字段到底想表达什么。反过来如果源文件用某个特殊标记来表示 NULL比如\N、NA、NULL这些你只需要把 NULL 选项改成那个标记COPY users FROM /tmp/users.csv WITH (FORMAT CSV, HEADER true, NULL NA);注意NULL 选项可以指定一个字符串凡是和这个字符串完全一样的字段值都会被当作 NULL。但不要指定空字符串以外的东西否则可能产生你意料之外的替换。顺便说说 TEXT 格式。TEXT 格式下默认的 NULL 表示是\N这是 PostgreSQL 自己的规范。用 TEXT 格式在两个 PostgreSQL 之间搬运数据基本不用担心 NULL 值被搞混的问题。这也是我前面说的“PostgreSQL 之间搬运推荐 TEXT 格式”的原因之一。4.4 数据校验失败的处理ON_ERROR 逻辑PostgreSQL 15 特性很早就有人诟病 COPY FROM 的这个毛病一行数据有问题整批数据全部回滚。对于大规模数据导入来说这简直是灾难。比如你导入一个几千万行的文件跑到最后几万行遇到一个类型错误整个事务回滚一切重来。好消息是PostgreSQL 15 开始引入了ON_ERROR选项让 COPY FROM 可以跳过错误行继续往下跑。基本用法COPY users FROM /tmp/users.csv WITH (FORMAT CSV, HEADER true, ON_ERROR ignore);加上ON_ERROR ignore之后遇到类型错误、约束违反、解析错误等非致命问题时出错的行会被跳过导入继续执行。但这个东西是把双刃剑。跳过错误行的同时你根本不知道哪些行被跳过了——除非打开日志记录。所以从 PostgreSQL 17 开始有了更完善的方案LOG_ON_ERROR选项可以把错误详情记录到服务端日志REJECT_*选项可以把出错的行单独写到另一个文件里方便过后排查修补我的实践经验是**需要快速导入一堆脏数据时ON_ERROR ignore 是最佳选择但一定要配合日志记录导入完成后仔细查看日志确认被跳过的行是否影响了业务完整性。**不然你自以为导成功了回头发现关键数据少了几行才是真正的大事。4.5 大数据量导入时的性能优化技巧用 COPY FROM 导入几万行和导入几千万行完全是两个世界的问题。针对大数据量导入我给你几条经过实战检验的建议第一把索引和约束留到导入后再建。如果你在建好索引的表上 COPY 数据每插入一行PostgreSQL 都要同步维护索引。几百万行数据下来索引维护的开销可能比数据本身还大。正确做法导入前先删掉不需要的索引主键约束和唯一约束先留着还是先删要看你能否接受期间数据不完整COPY 完成后再重建索引。实测经验几千万行数据先删索引再重建整体时间能缩短一半以上。第二用表分区承接导入。如果你按时间分了区表导入时每一行只需进入对应分区索引维护范围缩小效率明显提高。更极端一点的优化是导入到一张临时空表再通过交换分区或者INSERT INTO ... SELECT切到业务表上这样可以完全避开业务表上的索引和触发器开销。第三关闭不必要的触发器。如果表上有审计触发器、同步触发器等COPY FROM 每行都会触发它们速度会被拖得很慢。临时的办法是禁用触发器再导入导完再启用ALTER TABLE users DISABLE TRIGGER ALL; COPY users FROM /tmp/users.csv WITH (FORMAT CSV, HEADER true); ALTER TABLE users ENABLE TRIGGER ALL;注意DISABLE TRIGGER ALL需要超级用户权限而且会让表上的外键约束也暂时失效。操作前务必想清楚业务影响最好在维护窗口执行。第四适当调整 wal_level 和归档策略。如果你在导入大批量初始化数据且这个过程中不需要 PITR时间点恢复可以临时把wal_level调成minimal配合wal_skip_threshold参数让 COPY 生成更少的 WAL 日志。但这个属于比较进阶的调优影响面大非必要不推荐在生产环境随意改动。5. 权限与安全为什么有时候 COPY 会突然报错5.1 服务端文件权限的坑很多新手第一次跑 COPY 命令时明明 SQL 语法没问题却报了一堆could not open file ... Permission denied之类的错误心态直接崩了。这个问题的根因是COPY 命令在 PostgreSQL 服务端执行文件路径是相对于数据库服务器而言的操作文件的也是服务端进程所以读文件需要服务端进程账号有相应权限。举个例子你的 PostgreSQL 跑在某个系统账号下比如postgres你想读取的 CSV 文件在/tmp/data.csv而这个文件的属主是你自己权限是600——只有你能读。这时执行 COPY FROM 就会失败因为postgres账号没有权限读这个文件。解决办法要么把文件权限改成644甚至打到更开放让服务端账号能读要么把文件放到 PostgreSQL 数据目录下因为服务端账号对数据目录肯定有权限要么用超级用户身份在 psql 或管理工具中执行操作。注意PostgreSQL 对 COPY 操作文件有一套权限机制。非超级用户想用 COPY FROM/TO 操作服务器文件需要拥有pg_read_server_files、pg_write_server_files或pg_execute_server_program这些角色权限。具体来说读文件需要pg_read_server_files写文件需要pg_write_server_files用 PROGRAM 选项需要pg_execute_server_program。普通用户默认都没有这些权限。所以你在管理后台或者云数据库上操作时如果 COPY 报权限错误先检查两件事一是服务端进程账号对目标文件或目录有没有读写权限二是当前数据库角色有没有对应的 server files 权限。5.2 为什么推荐用 \copy 替代远程场景下的 COPY如果你用的是 psql 客户端而且数据库不在本机上云数据库、远程服务器直接用COPY ... TO file会有一个很尴尬的问题文件路径解析在服务器端而你想要的往往是文件落在客户端。这时你用\copy命令。\copy是 psql 的元命令不是 SQL 命令它的行为是先在本地把数据读出来或接收数据再通过客户端传入服务器。所以文件路径是相对于你本机的权限也是用你本机的账号。psql -h your_host -U your_user -d your_db -c \copy users FROM /tmp/users.csv WITH (FORMAT CSV, HEADER true)注意 \copy 的语法和 COPY 几乎一致只是前面多了一个反斜杠。但它有个限制不支持 BINARY 格式也不支持部分服务端专属选项。另一个区别是\copy FROM 本质是把本地文件内容通过 STDIN 协议流式传给服务端因此整个导入过程要经过客户端和服务器之间的网络传输。如果文件很大传输时间会占比很高。我的经验是小文件几百 MB 以内用 \copy 没问题几个 GB 以上的大文件优先考虑先把文件传到服务器上再用 COPY或者用流式压缩配合管道能省不少时间。5.3 PROGRAM 命令注入风险重要安全提醒前面提过 PROGRAM 选项这里单独拉出来说说安全。PROGRAM 的本质是让 PostgreSQL 服务端执行你指定的 shell 命令。如果命令字符串里拼了不确定的外部输入就存在命令注入风险。举个例子COPY table TO PROGRAM cat /tmp/ || :filename如果:filename来自外部输入且没有经过严格校验恶意用户可能构造出; rm -rf /some/path之类的命令造成严重破坏。所以我的建议是生产环境中尽量不用 PROGRAM 选项。如果确实要用命令字符串必须写死禁止拼接任何外部变量。同时这个 capability 应该只授予完全可信的超级用户不要把pg_execute_server_program权限随便给出去。6. PostgreSQL 15 的新特性COPY FROM 的进阶玩法前面在讲 ON_ERROR 的时候提到过 PostgreSQL 15 的新特性这里系统梳理一下 COPY 相关的新能力让大家知道 PG 在持续进化。PostgreSQL 15 之前COPY FROM 遇到错误是“一票否决制”任何一行出错整个导入回滚。对大数据量导入来说这意味着你必须先把数据“洗干净”才能导入一旦有漏网之鱼代价惨重。15 版本引入的ON_ERROR ignore解决了这个痛点。PostgreSQL 17 在此基础上进一步强化LOG_ON_ERROR把错误行的详情写入服务端日志方便事后审计REJECT_LIMIT设置允许的最大错误行数超过这个数量就整体失败防止“错误太多但还在硬跑”的情况错误行可以写到单独的拒绝文件中格式类似这样COPY users FROM /tmp/users.csv WITH ( FORMAT CSV, HEADER true, ON_ERROR ignore, LOG_ON_ERROR true, REJECT_LIMIT 100, REJECT_FILE /tmp/users_rejected.csv );这套组合拳让 COPY FROM 变得非常“能打”数据不用提前洗先导入错误行单独拎出来导完再处理错误文件里的脏数据。这对于从外部系统拿数据、做数据清洗入库的场景极大的解放了生产力。不过我要提醒一点ON_ERROR ignore 跳过的行是“不做任何写入”的包括触发器、外键约束检查也不会执行。如果被跳过的行恰好是业务上的关键数据你事后必须人工处理补录。方案是完美的但你不能当甩手掌柜导入后的数据校验和错行排查绝不能缺。7. 实战案例一次完整的数据迁移全流程理论讲得再多不如一个完整的实战例子来得直观。我模拟一个常见场景某公司要把一套旧数据库中的用户历史订单数据迁移到新库数据量大约 3000 万行存在 CSV 文件里文件大概 2.4GB。7.1 场景描述与目标定义旧系统导出的 CSV 文件字段是这样的order_id,user_id,product_name,quantity,unit_price,order_time。目标表结构如下CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, order_no VARCHAR(32) NOT NULL, user_id BIGINT NOT NULL, product_name VARCHAR(128), quantity INT, unit_price NUMERIC(10,2), order_time TIMESTAMP, created_at TIMESTAMP DEFAULT now() );新旧字段对不上新表有id自增主键和order_no订单号字段而 CSV 里没有order_no只有order_id。所以导入前需要做一些转换处理。这个场景非常典型外部文件字段和目标表结构不一致需要清洗或转换。7.2 数据预处理与中间表策略我的处理思路是不直接往业务表里灌数据而是先建一张中间临时表结构完全贴合 CSV 文件导入临时表后再用 SQL 转换语句把数据搬到正式表。-- 创建临时表 CREATE UNLOGGED TABLE tmp_orders_import ( order_id BIGINT, user_id BIGINT, product_name VARCHAR(128), quantity INT, unit_price NUMERIC(10,2), order_time TIMESTAMP );这里用UNLOGGED临时表有两个好处一是写入不产生 WAL 日志COPY 导入更快二是后续不用时直接 DROP不留下任何痕迹。缺点是非崩溃安全但对于临时中转用途这个风险完全可接受。然后执行导入COPY tmp_orders_import FROM /data/history_orders.csv WITH (FORMAT CSV, HEADER true, NULL , ENCODING UTF8);在导入之前我建议先做一次快速的抽样验证比如用ON_ERROR ignore导入前 10000 行利用LIMIT不行COPY 不支持但可以先head -10000截取样本文件head -10000 history_orders.csv sample_orders.csv用样本文件跑一遍 COPY确认列数、类型、编码都没问题再正式灌全量。这个“先小样后全量”的习惯帮我避开过很多坑强烈推荐。7.3 目标表的数据转换与落地临时表导入完成接下来就是把数据从临时表转换到正式表。这里可以展开各种业务规则比如把order_id映射成order_noINSERT INTO orders (order_no, user_id, product_name, quantity, unit_price, order_time) SELECT OLD- || order_id AS order_no, user_id, product_name, quantity, unit_price, order_time FROM tmp_orders_import;如果数据量特别大建议分批插入而不是一把梭。比如按order_time的月份分批每批 200 万行左右。分批的好处是出问题时回滚范围小不会把几十分钟的工作瞬间变成零。INSERT INTO orders (...) SELECT ... FROM tmp_orders_import WHERE order_time 2024-01-01 AND order_time 2024-02-01;全部搬完校验行数一致再建索引、约束。最后把临时表 DROP 掉整个迁移完成。7.4 迁移后的数据校验清单数据迁移最怕“看着成功实际少数据或者数据错了”。我每次迁移完必做这几项校验对比源文件和目标表的行数确保没有丢行。抽几个关键业务维度订单总金额、订单总量、用户去重数量和旧系统核对。检查导入后表的序列sequence确保自增 ID 不会和已有数据冲突。随机抽查若干条具体记录人工对比字段值是否一致。-- 行数校验 SELECT count(*) FROM orders; -- 金额汇总校验 SELECT sum(unit_price * quantity) FROM orders; -- 检查序列 SELECT currval(orders_id_seq);这个迁移流程我用过很多次每次都能稳定落地。当然它也谈不上多惊艳但它体现了 COPY FROM 在真实项目中的正确打开方式不硬灌业务表而是先灌临时表再通过 SQL 做转换和校验。8. 常见问题与排查技巧实录8.1 典型报错速查表在实际操作中我遇到过各种 COPY 相关的报错。这里整理成一个速查表方便大家遇到问题时快速定位。报错信息可能原因解决方案could not open file ... Permission denied服务端进程账号没有文件读取权限调整文件权限或用 \copy、换路径invalid input syntax for type ...文件中的字段值和目标列类型不匹配检查数据格式、使用临时表导入再转换extra data after last expected column文件列数比目标表多检查文件是否混入多余分隔符或换行missing data for column ...文件列数比目标表少检查文件完整性、是否漏了列character with byte sequence ... has no equivalent in encoding编码不兼容用 ENCODING 选项显式指定编码cannot copy from/to a sequence试图 COPY 一个序列序列不能直接 COPYmust be superuser or have pg_read_server_files角色没有文件访问权限授权相应角色或换超级用户执行COPY with a binary source must not specify ...BINARY 格式和一些选项冲突BINARY 格式下不能指定某些文本选项8.2 列数对不上整批文件解析失败列数不匹配是最常见的 COPY FROM 失败原因。核心报错是extra data after last expected column文件比表多列或者missing data for column文件比表少列。出现这个问题的原因五花八门CSV 里某些字段值本身包含了换行符或分隔符而导出时引号处理不当文件是别的系统生成的分隔符和你指定的不一致文件末尾多了空行等。排查技巧不要直接看整个文件先用head -5看看前几行什么样再用awk -F, {print NF} file | sort | uniq -c统计每行列数就能快速定位哪些行异常。一个很隐蔽的坑CSV 里某个字段包含了换行符导致一行数据被拆成了两行。这会让行数统计完全对不上。这种文件的处理方案一般是用 Python 的 csv 模块重新解析清洗或者回源头修复导出逻辑。8.3 类型转换失败的定位方法invalid input syntax for type ...这种报错出现时麻烦在于 COPY 的可读性有限——它不会告诉你具体是第几行、哪个字段出了问题。在大文件里找这个“毒瘤”行非常痛苦。我的经验是先导出一个样本比如前 10000 行测试如果样本正常再用二分定位的思路缩小范围。或者干脆利用 PostgreSQL 15 的 ON_ERROR ignore 配合 LOG_ON_ERROR让 PostgreSQL 自己把错误行记录下来从日志里提取具体的行号和内容。另一个笨但有用的办法把问题字段单独拆出来用正则或者其他工具检查格式。比如 NUMERIC 类型字段如果文件里混入了12,345.67这种带千分位逗号的值解析必然失败。这时需要用脚本先把千分位逗号去掉再导入。8.4 性能瓶颈COPY 为什么越导越慢有读者反馈过COPY FROM 一开始速度飞快越到后面越慢这是典型的索引膨胀问题。前面说过导入数据到带索引的表时每一行都要更新索引。随着数据量增加索引越来越大索引页的缓存命中率下降维护成本就越来越高。这个场景的解决方案和我前面说的一样导入前删掉非必要索引导完再重建。如果索引实在不能删比如主键那就要做好心理准备导入时间会明显拉长可以考虑分区表来缩小每个索引的规模。另一个可能的原因是表上存在大量触发器或外键约束。每行数据都要触发检查逻辑性能自然上不去。排查方法很简单EXPLAIN ANALYZE看不出来但你可以临时禁用触发器测一次对比耗时就知道是不是这个原因了。8.5 关于 FREEZE 选项一个需要谨慎使用的加速技巧PostgreSQL 的 COPY 支持一个FREEZE选项作用是让导入的数据直接标记为“已冻结”frozen跳过 MVCC 可见性检查所需的额外开销。这个技巧能显著提升导入速度尤其适合一次性导入大量历史数据、之后这些行很少再被修改的场景。COPY history_data FROM /tmp/history.csv WITH (FORMAT CSV, HEADER true, FREEZE true);但使用 FREEZE 有几个硬性条件当前事务必须是在一个新建的表中 COPY或者这个表是当前事务刚刚创建的期间不能有并发的写事务如果违反条件PostgreSQL 会直接报错。即使条件满足也要想清楚冻结的行之后如果要做 UPDATE 或 DELETE会有额外的处理开销。所以 FREEZE 只适合那种“灌进去就再也不改”的历史数据表。9. 我的几点实操心得与补充建议COPY 命令本身语法不复杂但用好的关键在于理解它的原理和边界。最后分享一些我实际工作中沉淀下来的经验。第一任何时候做大数据量 COPY先跑样本。不管文件来源多可靠先截取几千行或几万行跑一遍确认字段映射、类型转换、编码都没问题再全量执行。这个习惯帮我节省了无数次的返工时间。一次全量导入可能要跑半小时如果中途发现格式问题回滚重来就太痛了。样本跑通再全量几分钟的投入换来的是稳妥。第二尽可能让 COPY 只做“搬运”不做“转换”。像我们刚才看到的案例外部文件字段和目标表结构不一致时最佳实践是把文件原样导入临时表再用 SQL 做转换和清洗。这有两个好处一是 COPY 的速度不会被复杂的类型转换拖慢二是转换逻辑是显式的 SQL出了问题容易定位、容易修改。把“搬运”和“转换”两个环节分开思考会让整个数据导入流程清晰很多。第三文件编码问题永远不要忽视。我在国内环境处理数据时CSV 文件经常出现 GBK/GB18030 编码。如果数据库是 UTF-8直接 COPY 会报编码错误。解决办法是统一在导出环节就指定好编码或者在导入时用 ENCODING 选项显式转换。不要依赖“文件看起来正常”来判断编码问题有些字符在特定编码下会静默变歪数据质量更隐蔽。第四熟悉 \copy 和服务端 COPY 的区别能避免很多路径相关的坑。记住一条SQL 里的COPY ... TO/FROM file操作的是数据库服务端的文件系统psql 的\copy操作的是客户端本地文件系统。云数据库用户、远程连接的场景默认用 \copy 更安心除非你明确知道服务端文件路径的含义。第五注意事务和原子性。COPY 默认是单条语句但如果你在事务块中执行多条 COPY任何一条失败都会让整个事务回滚。这个特性在某些场景是保障但在另一些场景会“伤及无辜”。比如你要导入 10 张表的数据第 8 张表出错前面 7 张全回滚。如果你是刻意的全量导入策略这没问题但如果你希望“能导多少算多少”就该考虑用 ON_ERROR ignore 或者拆分事务。最后再补充一个小技巧如果你经常要从库里导数据给同事或者下游系统不妨把常用的导出逻辑写成一个数据库函数比如export_orders(date_from, date_to)内部用COPY (SELECT ...) TO STDOUT配合 psql 输出到文件。把复杂查询封装起来避免每次临时翻历史命令团队协作效率会高不少。关于 PostgreSQL 的 COPY TO 和 COPY FROM能讲的内容其实还有很多比如和外部数据包装器FDW配合的用法、并行处理的思路等等。但最基本、最常用的核心逻辑我相信都在这篇文章里了。希望这篇内容能让你在实际工作中少踩几个坑把数据搬运这件事做得又快又稳。