PgLoader Migrate data from SQL Server to PostgreSQL server got error with character "ñ/í/..."

Question

I'm using pgloader to migrate data from SQL Server to PostgreSQL server, the database is huge and I can't export to CSVv due to special characters. So I migrate db to db directly, almost done but it gets stuck at columns that have ñ/í/... data, and everything breaks. If I remove all above characters, then everything is good again, below is my setup .load script:

-- Single Table Migration Template: Data Only, No Drop Table
-- Replace: HOST_IP, username, password, database names, and TABLE_NAME as needed

LOAD DATABASE
     FROM mssql://sa:[email protected]/ADB
     INTO postgresql://postgres:[email protected]/ADB

-- Performance and migration settings
WITH
     data only,                -- Only copy data, don't drop or create tables
     disable triggers,
     no foreign keys,
     quote identifiers,
     batch rows = 50000,
     prefetch rows = 10000

CAST
    type varchar TO text ,
    type nvarchar TO text,
    type text TO text 

SET 
    client_encoding = 'WIN1252',
    work_mem to '256MB',
    maintenance_work_mem to '1GB'

ALTER SCHEMA 'dbo' RENAME TO 'public'

INCLUDING ONLY TABLE NAMES LIKE 'DRecords' IN SCHEMA 'dbo'

BEFORE LOAD DO
  $$ SET session_replication_role = replica; $$
AFTER LOAD DO
  $$ SET session_replication_role = DEFAULT; $$;

It used to work well when running inside docker desktop for windows, but windows server does not support docker desktop. The error message contains:

7: ((FLET "H1" :IN PGLOADER.SOURCES:MAP-ROWS) #<BABEL-ENCODINGS:INVALID-UTF8-STARTER-BYTE {10082879B3}>) 8: (SB-KERNEL::%SIGNAL #<BABEL-ENCODINGS:INVALID-UTF8-STARTER-BYTE {10082879B3}>) 9: (ERROR BABEL-ENCODINGS:INVALID-UTF8-STARTER-BYTE :OCTETS #(176) :ENCODING :UTF-8 :BUFFER #.(SB-SYS:INT-SAP #X72DEDC01FB60) :POSITION 252) 10: ((LABELS BABEL-ENCODINGS::UTF-8-DECODER :IN

Can someone please advise.

Dale K · Accepted Answer · 2025-09-04 21:42:55Z

0

I can't export to CSVv due to special characters

eh? that statement makes no sense.

The error says INVALID-UTF8-STARTER-BYTE - you have it configured to transfer UTF8 and aren't supplying UTF8.

edited Sep 4 at 21:42

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Sep 4 at 12:47

Richard Huxton

23.6k5 gold badges43 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PgLoader Migrate data from SQL Server to PostgreSQL server got error with character "ñ/í/..."

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related