pgloader数据迁移遇日期格式错误及PostgreSQL协议异常求助

问题场景 #

使用pgloader从MySQL迁移数据到PostgreSQL时，同时遇到日期格式解析错误和PostgreSQL协议异常，具体信息如下：

迁移配置 #

LOAD DATABASE
    FROM mysql://root:xxxxxx@localhost:3326/originaldb
    INTO postgresql://postgres:xxxxx@localhost:15432/newdb

WITH data only, include no drop, truncate, disable triggers,
     create no indexes, drop indexes, reset sequences,
     batch rows = 1000, batch size = 500 MB, prefetch rows = 1000

ALTER SCHEMA 'originaldb' RENAME TO 'public'

ALTER TABLE NAMES MATCHING ~/.*/
    SET SCHEMA 'public'

SET timezone TO '+00:00'

SET PostgreSQL PARAMETERS
     maintenance_work_mem to '4096MB',
     work_mem to '2048MB';

报错信息 #

PROTOCOL-ERROR: PostgreSQL protocol error: Unexpected message received: Z 90
An unhandled error condition has been signalled:
   PostgreSQL protocol error: Unexpected message received: Z 90

2024-03-12T02:49:06.768132-04:00 ERROR Date/time: 2024-03-12-07:49!
An unhandled error condition has been signalled:
   PostgreSQL protocol error: Unexpected message received: Z 90

解决方案 #

1. 修复日期格式错误 #

PostgreSQL不支持YYYY-MM-DD-HH:MI这种非标准日期格式，需在pgloader配置中明确指定解析规则：

• 在WITH子句中添加date format = '%Y-%m-%d-%H:%M'，全局适配该异常格式；若仅个别字段存在问题，也可针对单字段单独设置转换规则。
• 或者提前在MySQL中将异常日期转换为标准格式（如YYYY-MM-DD HH:MI:SS），再执行迁移。

修改后的配置示例（新增日期格式指定）：

LOAD DATABASE
    FROM mysql://root:xxxxxx@localhost:3326/originaldb
    INTO postgresql://postgres:xxxxx@localhost:15432/newdb

WITH data only, include no drop, truncate, disable triggers,
     create no indexes, drop indexes, reset sequences,
     batch rows = 1000, batch size = 500 MB, prefetch rows = 1000,
     date format = '%Y-%m-%d-%H:%M'  -- 指定异常日期的解析格式

ALTER SCHEMA 'originaldb' RENAME TO 'public'

ALTER TABLE NAMES MATCHING ~/.*/
    SET SCHEMA 'public'

SET timezone TO '+00:00'

SET PostgreSQL PARAMETERS
     maintenance_work_mem to '4096MB',
     work_mem to '2048MB';

2. 解决PostgreSQL协议错误（Z 90） #

该错误多由协议版本不兼容、批量写入过大导致连接断开或会话超时引发，可尝试以下操作：

• 缩小批量写入规模：将batch rows = 1000改为batch rows = 100，batch size = 500 MB调整为batch size = 64 MB，降低单次写入的数据量，避免PostgreSQL主动终止连接。
• 更新pgloader版本：确保使用最新稳定版，旧版本可能存在协议交互的bug。
• 调整PostgreSQL超时参数：在postgresql.conf中增大statement_timeout和idle_in_transaction_session_timeout的值，防止会话因长时间未响应被断开。
• 禁用预取机制：移除prefetch rows = 1000配置，部分场景下预取会干扰协议交互流程。

3. 辅助排查步骤 #

• 单独迁移异常表：定位到包含错误日期的表，单独执行迁移，验证日期格式修复是否生效。
• 开启调试日志：添加--verbose或--debug参数运行pgloader，获取更详细的错误细节，精准定位出错的数据行或字段。

内容的提问来源于stack exchange，提问作者klaucode