使用pgloader迁移MySQL至PostgreSQL时遇UNDEFINED-COLUMN错误求助

问题详情 #

使用pgloader将特定MySQL数据库迁移至PostgreSQL时，执行命令后报错，提示hir_2016_model_assessment表的output_ID列不存在，但该列实际存在于目标PostgreSQL表中。

错误日志 #

[root@38AA08C pgsql]# pgloader pg_load
2023-06-21T03:13:59.006000+01:00 LOG pgloader version "3.6.7~devel"
2023-06-21T03:13:59.893030+01:00 LOG Migrating from #<MYSQL-CONNECTION mysql://******* {10068EB193}>
2023-06-21T03:13:59.893030+01:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://******* {10068EB333}>
KABOOM!
UNDEFINED-COLUMN: Database error 42703: column ""output_ID"" of relation "hir_2016_model_assessment" does not exist
CONTEXT: PL/pgSQL function inline_code_block line 6 at FOR over SELECT rows
QUERY:
DO $$
DECLARE
  n integer := 0;
  r record;
BEGIN
  FOR r in
       SELECT 'select '
               || trim(trailing ')'

PostgreSQL目标表结构 #

Table "public.hir_2016_model_assessment"
         Column         |      Type      | Collation | Nullable |                            Default                             | Storage | Compression | Stats target | Description
------------------------+----------------+-----------+----------+----------------------------------------------------------------+---------+-------------+--------------+-------------
 output_ID              | bigint         |           | not null | nextval('"hir_2016_model_assessment_output_ID_seq"'::regclass) | plain   |             |              |
 project_ID             | integer        |           | not null |                                                                | plain   |             |              |

pgloader加载配置文件 #

LOAD DATABASE
   FROM      mysql://*****
   INTO postgresql://*****
   alter schema 'regen' rename to 'public'

   WITH
      include drop, create tables, create indexes, reset sequences, schema only, quote identifiers,
      multiple readers per thread, rows per range = 50000

   SET PostgreSQL PARAMETERS
      maintenance_work_mem to '1GB'

   SET MySQL PARAMETERS
      net_read_timeout  = '31536000',
      net_write_timeout = '31536000',
      lock_wait_timeout = '31536000'

   CAST
      type enum to text drop not null using empty-string-to-null,
      type date drop not null drop default using zero-dates-to-null,
      type datetime to timestamp drop default using zero-dates-to-null,
      type bigint when unsigned to numeric drop typemod,
      type bigint when (<= precision 20) to bigint drop typemod,
      type geography to bytea,
      type geometry  to point using convert-mysql-point,
      type point     to point using convert-mysql-point

   -- BEFORE LOAD DO
      -- $$ create extension if not exists postgis; $$
;

排查与解决方法 #

1. 标识符引号与大小写问题
   错误日志中列名被写为""output_ID""（双重引号），而PostgreSQL中双引号包裹的标识符严格区分大小写。你的目标表中output_ID实际是小写存储的（PostgreSQL默认会将未加双引号的标识符转为小写），但quote identifiers配置项强制pgloader给所有标识符添加双引号，导致查询时无法匹配到实际列名。

   • 解决：移除配置中的quote identifiers选项，或者确保MySQL源表的列名大小写与PostgreSQL目标表完全一致。

2. 验证MySQL源表结构
   检查MySQL中hir_2016_model_assessment表的output_ID列名是否存在大小写差异（比如MySQL中是Output_ID）。MySQL默认不区分大小写，迁移到PostgreSQL后，若未用双引号创建表/列，PostgreSQL会自动转为小写，此时带双引号的查询就会失败。

3. 更换pgloader稳定版本
   当前使用的是3.6.7~devel开发版，可能存在标识符处理的bug。建议切换到最新稳定版重试。

4. 手动验证SQL查询
   在PostgreSQL中执行SELECT "output_ID" FROM public.hir_2016_model_assessment LIMIT 1;，若报错则说明列名实际为小写；执行SELECT output_id FROM public.hir_2016_model_assessment LIMIT 1;若成功，即可确认是大小写与引号的问题。

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