Обсуждение: encoding problem while inictial copy in logical replication
Hi,
I have a database with UTF8 encoding. This database seems to be
receiving data in WIN1252 encoding from some client.
I even see this:
db=# select * from pg_db_role_setting ;
setdatabase | setrole | setconfig
-------------+----------+-------------------------------------------------------------------------------
119464 | 0 |
{client_encoding=WIN1252,bytea_output=escape,standard_conforming_strings=off}
(1 rows)
Some time ago I tried to create a logical replication, for a speed up
I used I created a physical replica and converted it in a logical one
and it works fine.
But the I tried to add a new table to the replica, so I added it to
the publication and when I "ALTER SUBSCRIPTION .... REFRESH
PUBLICATION" got this error.
2025-09-16 08:20:24.971 UTC [1535715] LOG: logical replication table
synchronization worker for subscription "sub1", table "new_table" has
started
2025-09-16 09:20:23.037 UTC [1535715] ERROR: character with byte
sequence 0x8d in encoding "WIN1252" has no equivalent in encoding
"UTF8"
2025-09-16 09:20:23.037 UTC [1535715] CONTEXT: COPY new_table, line 2489
2025-09-16 09:20:23.041 UTC [1463234] LOG: background worker "logical
replication worker" (PID 1535715) exited with exit code 1
But if I pg_dump the data directly to the table in the subscription
and avoid the initial copy using copy_data=false, it works fine.
I tried to "SET client_encoding=UTF8" before the refresh and also
created an user that defaults to client_encoding=UTF8 for using it in
the subscription connection, but I still see the problem during the
initial copy of data.
AFAICS, this doesn't ocurr during normal replication process.
Any idea? Currently my process is to manually copy the data.
--
Jaime Casanova
SYSTEMGUARDS
Director de servicios profesionales
On 9/16/25 04:01, Jaime Casanova wrote:
> Hi,
>
> I have a database with UTF8 encoding. This database seems to be
> receiving data in WIN1252 encoding from some client.
>
> I even see this:
>
> db=# select * from pg_db_role_setting ;
> setdatabase | setrole | setconfig
> -------------+----------+-------------------------------------------------------------------------------
> 119464 | 0 |
> {client_encoding=WIN1252,bytea_output=escape,standard_conforming_strings=off}
> (1 rows)
>
> Some time ago I tried to create a logical replication, for a speed up
> I used I created a physical replica and converted it in a logical one
> and it works fine.
> But the I tried to add a new table to the replica, so I added it to
> the publication and when I "ALTER SUBSCRIPTION .... REFRESH
> PUBLICATION" got this error.
>
> 2025-09-16 08:20:24.971 UTC [1535715] LOG: logical replication table
> synchronization worker for subscription "sub1", table "new_table" has
> started
> 2025-09-16 09:20:23.037 UTC [1535715] ERROR: character with byte
> sequence 0x8d in encoding "WIN1252" has no equivalent in encoding
> "UTF8"
> 2025-09-16 09:20:23.037 UTC [1535715] CONTEXT: COPY new_table, line 2489
> 2025-09-16 09:20:23.041 UTC [1463234] LOG: background worker "logical
> replication worker" (PID 1535715) exited with exit code 1
>
> But if I pg_dump the data directly to the table in the subscription
> and avoid the initial copy using copy_data=false, it works fine.
>
> I tried to "SET client_encoding=UTF8" before the refresh and also
> created an user that defaults to client_encoding=UTF8 for using it in
> the subscription connection, but I still see the problem during the
> initial copy of data.
> AFAICS, this doesn't ocurr during normal replication process.
>
> Any idea? Currently my process is to manually copy the data.
Read this?:
https://www.i18nqa.com/debug/bug-double-conversion.html
https://www.i18nqa.com/debug/utf8-debug.html
Bottom line 0x8d is unassigned in WIN1252 and there is no UTF8
equivalent for it and four other code points.
The solution would seem to be determining what is using this code point
and stopping it's use if possible.
>
> --
> Jaime Casanova
> SYSTEMGUARDS
> Director de servicios profesionales
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On Tue, Sep 16, 2025 at 12:44 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote: > > On 9/16/25 04:01, Jaime Casanova wrote: > > Hi, > > > > I have a database with UTF8 encoding. This database seems to be > > receiving data in WIN1252 encoding from some client. > > [...] > > > > Some time ago I tried to create a logical replication, for a speed up > > I used I created a physical replica and converted it in a logical one > > and it works fine. > > But the I tried to add a new table to the replica, so I added it to > > the publication and when I "ALTER SUBSCRIPTION .... REFRESH > > PUBLICATION" got this error. > > > > 2025-09-16 08:20:24.971 UTC [1535715] LOG: logical replication table > > synchronization worker for subscription "sub1", table "new_table" has > > started > > 2025-09-16 09:20:23.037 UTC [1535715] ERROR: character with byte > > sequence 0x8d in encoding "WIN1252" has no equivalent in encoding > > "UTF8" > > 2025-09-16 09:20:23.037 UTC [1535715] CONTEXT: COPY new_table, line 2489 > > 2025-09-16 09:20:23.041 UTC [1463234] LOG: background worker "logical > > replication worker" (PID 1535715) exited with exit code 1 > > [...] > > > > Any idea? Currently my process is to manually copy the data. > > Read this?: > > https://www.i18nqa.com/debug/bug-double-conversion.html > https://www.i18nqa.com/debug/utf8-debug.html > > Bottom line 0x8d is unassigned in WIN1252 and there is no UTF8 > equivalent for it and four other code points. > > The solution would seem to be determining what is using this code point > and stopping it's use if possible. > But then, why pg_dump works if it's also using COPY? -- Jaime Casanova SYSTEMGUARDS Director de servicios profesionales