Original-Subject: Re: Importing a Windows database (in en_GB.CP1252) to linux
Original-From: Jean-Christophe BOGGIO <postgresql@thefreecat.org>
Content-Type: multipart/alternative;
 boundary="------------0RmP1pywMXtW7pL0X1AUtDNb"
Message-ID: <74bbf2e7-0120-4b77-96a6-e5d6f4d75619@thefreecat.org>
Date: Tue, 2 Dec 2025 10:39:04 +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>
From: Jean-Christophe BOGGIO <postgresql@thefreecat.org>
Content-Language: en-US, fr
In-Reply-To: <f6306ad9fe30e09ceb06900c856dfab57afe8c85.camel@cybertec.at>
Archived-At: 
 <https://www.postgresql.org/message-id/74bbf2e7-0120-4b77-96a6-e5d6f4d75619%40thefreecat.org>
Precedence: bulk

This is a multi-part message in MIME format.
--------------0RmP1pywMXtW7pL0X1AUtDNb
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Laurenz,

> That looks like pg_restore sets a wrong client_encoding, which is weird.
>
> What do you get for
>
>    pg_restore -p 5433 -t csakafl -s -f - imlocal20251127.backup | grep client_encoding

SET client_encoding = 'UTF8';

> If the dump was taken from a WINDOWS-1252 encoded database, that line should
> read
>
>    SET client_encoding = 'WIN1252';
>
> and everything should work fine.  But apparently, the client_encoding is set to
> UTF-8 in your case.

Yes, exactly.

> How did that happen?  How exactly did you take that dump?

This backup is a transfer from an iSeries DB2 database. It has been a 
nightmare to get this working (and took around 10 days to finalize). We 
set up a FDW Server using odbc_fdw, recreated all the tables (around 2k) 
and INSERTed the DB2 data to the PG tables.

Then we used PgAdmin that came with PostgreSQL 17 on the Windows machine.

I double-checked with the client: the database is in en_GB.CP1252.

BTW, if I try to import the same backup on a UTF8 database I get the 
exact same error (invalid byte sequence 0x92)

> Did you do anything (like an encoding conversion) with the dump after you took it?

No, the backup is in custom format so I can't touch it (or at least I 
don't know how I could).

Where can I go from here?


--------------0RmP1pywMXtW7pL0X1AUtDNb
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>Laurenz,</p>
    <blockquote type="cite"
cite="mid:f6306ad9fe30e09ceb06900c856dfab57afe8c85.camel@cybertec.at">
      <pre wrap="" class="moz-quote-pre">That looks like pg_restore sets a wrong client_encoding, which is weird.

What do you get for

  pg_restore -p 5433 -t csakafl -s -f - imlocal20251127.backup | grep client_encoding</pre>
    </blockquote>
    <p><span style="font-family:monospace"><span
          style="color:#000000;background-color:#ffffff;">SET
          client_encoding = 'UTF8';</span></span></p>
    <blockquote type="cite"
cite="mid:f6306ad9fe30e09ceb06900c856dfab57afe8c85.camel@cybertec.at">
      <pre wrap="" class="moz-quote-pre">If the dump was taken from a WINDOWS-1252 encoded database, that line should
read

  SET client_encoding = 'WIN1252';

and everything should work fine.  But apparently, the client_encoding is set to
UTF-8 in your case.</pre>
    </blockquote>
    <p>Yes, exactly.</p>
    <blockquote type="cite"
cite="mid:f6306ad9fe30e09ceb06900c856dfab57afe8c85.camel@cybertec.at">
      <pre wrap="" class="moz-quote-pre">How did that happen?  How exactly did you take that dump?</pre>
    </blockquote>
    <p>This backup is a transfer from an iSeries DB2 database. It has
      been a nightmare to get this working (and took around 10 days to
      finalize). We set up a FDW Server using odbc_fdw, recreated all
      the tables (around 2k) and INSERTed the DB2 data to the PG tables.</p>
    <p>Then we used PgAdmin that came with PostgreSQL 17 on the Windows
      machine.</p>
    <p>I double-checked with the client: the database is in
      en_GB.CP1252.</p>
    <p>BTW, if I try to import the same backup on a UTF8 database I get
      the exact same error (invalid byte sequence 0x92)</p>
    <blockquote type="cite"
cite="mid:f6306ad9fe30e09ceb06900c856dfab57afe8c85.camel@cybertec.at">
      <pre wrap="" class="moz-quote-pre">Did you do anything (like an encoding conversion) with the dump after you took it?</pre>
    </blockquote>
    <p>No, the backup is in custom format so I can't touch it (or at
      least I don't know how I could).</p>
    <p>Where can I go from here?</p>
    <p><br>
    </p>
  </body>
</html>

--------------0RmP1pywMXtW7pL0X1AUtDNb--