MIME-Version: 1.0
From: Jaime Casanova <jcasanov@systemguards.com.ec>
Date: Tue, 16 Sep 2025 06:01:15 -0500
Message-ID: <CAJKUy5gJ-MCaZ4KGnhz4SLDZ2fecYxpvDHfgSgCm5n3f9OHx+A@mail.gmail.com>
Subject: encoding problem while inictial copy in logical replication
To: Postgres General <pgsql-general@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAJKUy5gJ-MCaZ4KGnhz4SLDZ2fecYxpvDHfgSgCm5n3f9OHx%2BA%40mail.gmail.com>
Precedence: bulk

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