Feedback-ID: i76984098:Fastmail
Message-ID: <235410a2-99dd-47ae-9f26-86b00b7d0623@aklaver.com>
Date: Tue, 16 Sep 2025 10:44:04 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: encoding problem while inictial copy in logical replication
To: Jaime Casanova <jcasanov@systemguards.com.ec>,
 Postgres General <pgsql-general@postgresql.org>
References: <CAJKUy5gJ-MCaZ4KGnhz4SLDZ2fecYxpvDHfgSgCm5n3f9OHx+A@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAJKUy5gJ-MCaZ4KGnhz4SLDZ2fecYxpvDHfgSgCm5n3f9OHx+A@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/235410a2-99dd-47ae-9f26-86b00b7d0623%40aklaver.com>
Precedence: bulk

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