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.
> >
> > What do you get for
> >
> > pg_restore -p 5433 -t csakafl -s -f - imlocal20251127.backup | grep client_encoding
>
> SET client_encoding = 'UTF8';
>
>
> > 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.
The DB2 database or the PostgreSQL database?
It must be the DB2 database, because otherwise the dump would contain
SET client_encoding = 'WIN1252';
That is, unless you created the dump with
pg_dump --encoding=UTF8
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 database.
But I am not sure.
> > 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?
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.
Yours,
Laurenz Albe
