第46期吐槽:PG copy不能跳过错误行

1、产品的问题点

• PG copy不能跳过错误行. 这个PG 17支持了.
• 不能记录被跳过的行.
• 不能配置最多跳过多少行, 然后才报错.

2、问题点背后涉及的技术原理

• PG 支持copy语法, 由于其高效的写入性能(功能单一, 协议精简, 节省了冗长的query parser,rewrite,plan,execute过程), 通常被用于高速数据导入导出.
• PG copy被作为1个事务来处理, 如果一次copy过程中出现了异常的行, 将导致整个copy任务回滚.

3、这个问题将影响哪些行业以及业务场景

• 导入、迁移、恢复数据等场景

4、会导致什么问题?

• 如果一个copy的表数据量很多, 已经导入了很多数据, 然后发现异常数据, 导致整个copy任务回滚, 浪费时间, 同时产生的写操作、wal都是浪费的. 必须重来一遍.  如果有standby, 那么wal复制和恢复也会重来一遍. 备份的wal也会增多.

5、业务上应该如何避免这个坑

• 使用pg_bulkload插件以及对应的导入工具, 支持多项配置, 甚至能直接写数据文件, 跳过wal, shared buffer等, 效率比普通copy还要高.
• http://ossc-db.github.io/pg_bulkload/pg_bulkload.html
• https://github.com/ossc-db/pg_bulkload/

6、业务上避免这个坑牺牲了什么, 会引入什么新的问题

• 使用pg_bulkload需要注意, 如果选择了direct模式导入, 会绕过wal, 那么以前的备份就无法使用wal来恢复新导入的数据. 必须在bulk load后做一次全量备份. 同时如果有基于流复制的逻辑订阅、物理standby, 统统都无法接受这些bulk load写入的数据.
• pg_bulkload属于第三方工具, 还需要多评估一下它的更新迭代、bugfix、支持的PG版本等.

7、数据库未来产品迭代如何修复这个坑

• 希望PG官方能支持copy是跳过并记录下异常的行到日志、指定文件、或表里面, 导入结束后方便进行修复.
• 例如, 可以设置批量大小, 批量提交, 遇到异常时异常批次的数据跳过异常重新写入一遍. 也比全部回滚好.
• 例如, 可以设置错误条数上限, 如果超过上限则回退整个copy事务.

2024.6.24补充: PG 17支持copy跳过错误行, 但是还不支持记录跳过的错误行, 以及跳过错误行的上限数配置.

• 《PostgreSQL 17 preview - Add new COPY option SAVE_ERROR_TO (copy跳过错误行)》
• 《PostgreSQL 17 preview - COPY LOG_VERBOSITY notice ERROR信息》