Message-ID: <b82ebbc1fa3eb5cb175ff6669260fa95d3bbe6b7.camel@cybertec.at>
Subject: Re: Importing a Windows database (in en_GB.CP1252) to linux
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Jean-Christophe BOGGIO <postgresql@thefreecat.org>,
 "pgsql-admin@lists.postgresql.org"
	 <pgsql-admin@lists.postgresql.org>
Date: Tue, 02 Dec 2025 12:26:54 +0100
In-Reply-To: <74bbf2e7-0120-4b77-96a6-e5d6f4d75619@thefreecat.org>
References: <5d7b5d51-a7c7-4cf2-943c-49b93441b8fa@thefreecat.org>
	 <f6306ad9fe30e09ceb06900c856dfab57afe8c85.camel@cybertec.at>
	 <74bbf2e7-0120-4b77-96a6-e5d6f4d75619@thefreecat.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/b82ebbc1fa3eb5cb175ff6669260fa95d3bbe6b7.camel%40cybertec.at>
Precedence: bulk

On Tue, 2025-12-02 at 10:39 +0100, Jean-Christophe BOGGIO wrote:
> > That looks like pg_restore sets a wrong client_encoding, which is weird=
.
> >=20
> > What do you get for
> >=20
> > =C2=A0=C2=A0pg_restore -p 5433 -t csakafl -s -f - imlocal20251127.backu=
p | grep client_encoding
>=20
> SET client_encoding =3D 'UTF8';
>=20
>=20
> > How did that happen?  How exactly did you take that dump?
>=20
> This backup is a transfer from an iSeries DB2 database. It has been a nig=
htmare to
> get this working (and took around 10 days to finalize). We set up a FDW S=
erver using
> odbc_fdw, recreated all the tables (around 2k) and INSERTed the DB2 data =
to the PG tables.
>=20
> Then we used PgAdmin that came with PostgreSQL 17 on the Windows machine.
>=20
> 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

  SET client_encoding =3D 'WIN1252';

That is, unless you created the dump with

  pg_dump --encoding=3DUTF8

But then, the dump couldn't contain non-UTF-8 characters.

Having used pgAdmin, you probably don't know the pg_dump command line that =
was used.


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 dat=
abase.
But I am not sure.

> > Did you do anything (like an encoding conversion) with the dump after y=
ou took it?
>=20
> No, the backup is in custom format so I can't touch it (or at least I don=
't know how I could).
>=20
> Where can I go from here?

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 =3D 'WIN1252';

- restore that dump with "psql":

  psql -f script.sql -d newdb

That should work if *all* the strings are in WINDOWS-1252 encoding.

Yours,
Laurenz Albe