Original-Subject: Re: Importing a Windows database (in en_GB.CP1252) to linux
Original-From: Jean-Christophe BOGGIO <postgresql@thefreecat.org>
Message-ID: <4856dd3a-f42b-48ce-8837-7792a1114206@thefreecat.org>
Date: Tue, 2 Dec 2025 14:15:15 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Importing a Windows database (in en_GB.CP1252) to linux
To: Laurenz Albe <laurenz.albe@cybertec.at>,
  "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
References: <5d7b5d51-a7c7-4cf2-943c-49b93441b8fa@thefreecat.org>
 <f6306ad9fe30e09ceb06900c856dfab57afe8c85.camel@cybertec.at>
 <74bbf2e7-0120-4b77-96a6-e5d6f4d75619@thefreecat.org>
 <b82ebbc1fa3eb5cb175ff6669260fa95d3bbe6b7.camel@cybertec.at>
From: Jean-Christophe BOGGIO <postgresql@thefreecat.org>
Content-Language: en-US, fr
In-Reply-To: <b82ebbc1fa3eb5cb175ff6669260fa95d3bbe6b7.camel@cybertec.at>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/4856dd3a-f42b-48ce-8837-7792a1114206%40thefreecat.org>
Precedence: bulk

Laurenz,

Thanks a lot for your quick reply.

Le 02/12/2025 à 12:26, Laurenz Albe a écrit :
>> I double-checked with the client: the database is in en_GB.CP1252.
> The DB2 database or the PostgreSQL database?
>
> It must be the DB2 database, because otherwise the dump would contain

No: the PostgreSQL one. I guess the DB2 is encoded (internally) in 
EBCDIC... Yes... At least that's what CSV exports directly made from the 
DB shows.

> My best guess is that odbc_fdw has a bug that does not check if the strings are
> properly encoded, and you somehow got corrupted data in your PostgreSQL database.
> But I am not sure.

I don't know really but I could have the client do some tests on tables 
that we know contain invalid byte sequences. Maybe I could try doing 
manual pg_dumps with different -E parameter to verify that it's not 
something enforced by pgAdmin.

> You can try the following:
>
> - convert the custom format dump into an SQL script with
>
>    pg_restore -f script.sql imlocal20251127.backup
>
> - edit script.sql and change the line to read
>
>    SET client_encoding = 'WIN1252';
>
> - restore that dump with "psql":
>
>    psql -f script.sql -d newdb
>
> That should work if *all* the strings are in WINDOWS-1252 encoding.

Thanks a lot for the proposal. It seems to be doing the job!

It's running right now and so far no "bad encoding" error appeared (it's 
a 311Gb backup so it will take some time). I'll let you know.

Gratefully,

JC