使用PGLoader迁移SQL Server到PostgreSQL时西班牙字符引发类型错误

问题场景 #

使用PGLoader工具将SQL Server数据迁移至PostgreSQL，所用配置如下：

load database
    from mssql://sa:password@ms-server:1433/Identity
    into postgresql://postgres:password@172.24.224.1:5433/Identity

    alter schema 'dbo' rename to 'public'

    including only table names like 'Companies' in schema 'dbo'

    WITH quote identifiers

    set work_mem to '1024MB', maintenance_work_mem to '1024 MB'
    before load do $$ drop schema if exists dbo cascade; $$;

执行后触发如下错误：

THREAD:MUTEX "Anonymous lock" (free)> :NOTIFY-PUSH NIL :NOTIFY-POP NIL) 1) [fast-method]
8: ((FLET "BODY-FN0" :IN LPARALLEL.KERNEL::MAKE-CHANNELED-TASK))
9: ((LAMBDA NIL :IN LPARALLEL.KERNEL::MAKE-CHANNELED-TASK))
10: (LPARALLEL.KERNEL::EXEC-TASK/WORKER #<unavailable argument> #S(LPARALLEL.KERNEL::WORKER :HANDSHAKE/FROM-WORKER #S(LPARALLEL.CONS-QUEUE:CONS-QUEUE :IMPL #S(LPARALLEL.RAW-QUEUE:RAW-QUEUE :HEAD NIL :TAIL NIL) :LOCK #<SB-THREAD:MUTEX #1="Anonymous lock" (free)> :CVAR #<SB-THREAD:WAITQUEUE #2=Anonymous condition variable {1009C5C413}> :HANDSHAKE/TO-WORKER #S(LPARALLEL.CONS-QUEUE:CONS-QUEUE :IMPL #S(LPARALLEL.RAW-QUEUE:RAW-QUEUE :HEAD NIL :TAIL NIL) :LOCK #<SB-THREAD:MUTEX #1# (free)> :CVAR #<SB-THREAD:WAITQUEUE #2# {1009C60CA3}> :EXIT-NOTIFICATION #S(LPARALLEL.CONS-QUEUE:CONS-QUEUE :IMPL #S(LPARALLEL.RAW-QUEUE:RAW-QUEUE :HEAD NIL :TAIL NIL) :LOCK #<SB-THREAD:MUTEX #1# (free)> :CVAR NIL) :THREAD #<SB-THREAD:THREAD "lparallel" RUNNING {1009C5B3B3}> :RUNNING-CATEGORY :DEFAULT :INDEX 1 :TASKS #S(LPARALLEL.SPIN-QUEUE:SPIN-QUEUE :HEAD (LPARALLEL.SPIN-QUEUE::DUMMY (#<FUNCTION #3=(LAMBDA NIL :IN LPARALLEL.KERNEL::MAKE-CHANNELED-TASK) {100E98640B}> . :DEFAULT) (#<FUNCTION #3# {100E98725B}> . :DEFAULT) . #4=((#<FUNCTION #3# {100EC9C14B}> . :DEFAULT))) :TAIL #4#)))
11: (LPARALLEL.KERNEL::WORKER-LOOP #<LPARALLEL.KERNEL:KERNEL :NAME "lparallel" :WORKER-COUNT 4 :USE-CALLER NIL :ALIVE T :SPIN-COUNT 2000 {1009C5A7D3}> #S(LPARALLEL.KERNEL::WORKER :HANDSHAKE/FROM-WORKER #S(LPARALLEL.CONS-QUEUE:CONS-QUEUE :IMPL #S(LPARALLEL.RAW-QUEUE:RAW-QUEUE :HEAD NIL :TAIL NIL) :LOCK #<SB-THREAD:MUTEX #1="Anonymous lock" (free)> :CVAR #<SB-THREAD:WAITQUEUE #2=Anonymous condition variable {1009C5C413}> :HANDSHAKE/TO-WORKER #S(LPARALLEL.CONS-QUEUE:CONS-QUEUE :IMPL #S(LPARALLEL.RAW-QUEUE:RAW-QUEUE :HEAD NIL :TAIL NIL) :LOCK #<SB-THREAD:MUTEX #1# (free)> :CVAR #<SB-THREAD:WAITQUEUE #2# {1009C60CA3}> :EXIT-NOTIFICATION #S(LPARALLEL.CONS-QUEUE:CONS-QUEUE :IMPL #S(LPARALLEL.RAW-QUEUE:RAW-QUEUE :HEAD NIL :TAIL NIL) :LOCK #<SB-THREAD:MUTEX #1# (free)> :CVAR NIL) :THREAD #<SB-THREAD:THREAD "lparallel" RUNNING {1009C5B3B3}> :RUNNING-CATEGORY :DEFAULT :INDEX 1 :TASKS #S(LPARALLEL.SPIN-QUEUE:SPIN-QUEUE :HEAD (LPARALLEL.SPIN-QUEUE::DUMMY (#<FUNCTION #3=(LAMBDA NIL :IN LPARALLEL.KERNEL::MAKE-CHANNELED-TASK) {100E98640B}> . :DEFAULT) (#<FUNCTION #3# {100E98725B}> . :DEFAULT) . #4=((#<FUNCTION #3# {100EC9C14B}> . :DEFAULT))) :TAIL #4#)))
12: (LPARALLEL.KERNEL::%CALL-WITH-TASK-HANDLER #<unavailable argument>)
13: ((LAMBDA NIL :IN LPARALLEL.KERNEL::CALL-WITH-WORKER-CONTEXT))
14: (LPARALLEL.KERNEL::CALL-WITH-WORKER-CONTEXT #<FUNCTION (LAMBDA NIL :IN LPARALLEL.KERNEL::ENTER-WORKER-LOOP) {1009C60C8B}> #<FUNCTION FUNCALL> #<LPARALLEL.KERNEL:KERNEL :NAME "lparallel" :WORKER-COUNT 4 :USE-CALLER NIL :ALIVE T :SPIN-COUNT 2000 {1009C5A7D3}> #S(LPARALLEL.KERNEL::WORKER :HANDSHAKE/FROM-WORKER #S(LPARALLEL.CONS-QUEUE:CONS-QUEUE :IMPL #S(LPARALLEL.RAW-QUEUE:RAW-QUEUE :HEAD NIL :TAIL NIL) :LOCK #<SB-THREAD:MUTEX #1="Anonymous lock" (free)> :CVAR #<SB-THREAD:WAITQUEUE #2=Anonymous condition variable {1009C5C413}> :HANDSHAKE/TO-WORKER #S(LPARALLEL.CONS-QUEUE:CONS-QUEUE :IMPL #S(LPARALLEL.RAW-QUEUE:RAW-QUEUE :HEAD NIL :TAIL NIL) :LOCK #<SB-THREAD:MUTEX #1# (free)> :CVAR #<SB-THREAD:WAITQUEUE #2# {1009C60CA3}> :EXIT-NOTIFICATION #S(LPARALLEL.CONS-QUEUE:CONS-QUEUE :IMPL #S(LPARALLEL.RAW-QUEUE:RAW-QUEUE :HEAD NIL :TAIL NIL) :LOCK #<SB-THREAD:MUTEX #1# (free)> :CVAR NIL) :THREAD #<SB-THREAD:THREAD "lparallel" RUNNING {1009C5B3B3}> :RUNNING-CATEGORY :DEFAULT :INDEX 1 :TASKS #S(LPARALLEL.SPIN-QUEUE:SPIN-QUEUE :HEAD (LPARALLEL.SPIN-QUEUE::DUMMY (#<FUNCTION #3=(LAMBDA NIL :IN LPARALLEL.KERNEL::MAKE-CHANNELED-TASK) {100E98640B}> . :DEFAULT) (#<FUNCTION #3# {100E98725B}> . :DEFAULT) . #4=((#<FUNCTION #3# {100EC9C14B}> . :DEFAULT))) :TAIL #4#)))
15: ((LAMBDA NIL :IN LPARALLEL.KERNEL::MAKE-WORKER-THREAD))
16: ((LAMBDA NIL :IN BORDEAUX-THREADS::BINDING-DEFAULT-SPECIALS))
17: ((FLET SB-UNIX::BODY :IN SB-THREAD::RUN))
18: ((FLET "WITHOUT-INTERRUPTS-BODY-10" :IN SB-THREAD::RUN))
19: ((FLET SB-UNIX::BODY :IN SB-THREAD::RUN))
20: ((FLET "WITHOUT-INTERRUPTS-BODY-3" :IN SB-THREAD::RUN))
21: (SB-THREAD::RUN)
22: ("foreign function: call_into_lisp")
23: ("foreign function: funcall1")

What I am doing here?

The value
  #.(SB-SYS:INT-SAP #X7F7730027C28)
is not of type
  VECTOR

经排查，该错误仅在数据包含西班牙语特殊字符（如Ó，示例内容“DE INVESTIGACIÓN”）时触发，无特殊字符时迁移可正常执行。

解决方法 #

• 显式指定SQL Server连接编码：在SQL Server连接字符串中添加编码参数，确保PGLoader正确解析特殊字符。若SQL Server数据库使用UTF-8，修改from行如下：

from mssql://sa:password@ms-server:1433/Identity?encoding=utf-8

若数据库使用Latin1编码，则指定encoding=latin1。

• 禁用并行加载：该错误与并行处理时的字符类型转换异常相关，强制单线程执行可规避问题。在WITH语句中添加workers = 1：

WITH quote identifiers, workers = 1

• 升级PGLoader版本：此问题可能是旧版本PGLoader的字符解析bug，更新至最新稳定版可修复底层处理逻辑。

• 预处理SQL Server数据：若上述方法无效，先在SQL Server中将含特殊字符的字段转换为标准格式，再通过自定义查询迁移：

SELECT CONVERT(NVARCHAR(MAX), company_name) AS company_name FROM dbo.Companies;

修改PGLoader配置为加载自定义查询结果，而非直接整表迁移。

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