Re: [External] postgres 9.5 DB corruption: invalid byte sequence for encoding "UTF8"

Thomas Tignor <tptignor(at)yahoo(dot)com> wrote on 03/25/2019 08:25:49 PM:

>
> Hi Brad,
> Thanks for writing. As I mentioned to Vijay, the "source" is a JVM
> using the postgres v42.0.0 JDBC driver. I do not believe we have any
> explicit encoding set, and so I expect the client encoding is
> SQL_ASCII. The DB is most definitely UTF8.

These statements are contradictory.

The value of client_encoding from your select on pg_settings is SQL_ASCII.

The docs clearly state:
https://www.postgresql.org/docs/9.5/runtime-config-client.html
"Sets the client-side encoding (character set). The default is to use the
database encoding. "

If you don't have client_encoding explicitly, then it is using the database
encoding.

Run this on both the primary and replica to confirm the DB's are indeed
both in UTF8 encoding:

SELECT datname, pg_encoding_to_char(encoding) FROM pg_database ;

Brad