pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Eugene M. Zheganin <eugene@zhegan.in>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: 13.x, stream replication and locale(?) issues
Date: Tue, 28 Feb 2023 10:21:55 -0500
Message-ID: <744349.1677597715@sss.pgh.pa.us> (raw)
In-Reply-To: <2020842e-1841-4a9b-63fd-58a3628ede68@zhegan.in>
References: <2020842e-1841-4a9b-63fd-58a3628ede68@zhegan.in>

"Eugene M. Zheganin" <eugene@zhegan.in> writes:
> I have a 13.4 pgsql instance on Linux which has a bunch of databases 
> with UTF-8/ru_RU.utf8 encoding/collation set.

> I've stream replicated it to the 13.10 instance on FreeBSD (may be this 
> is the part where it all has gone wrong way, but at this moment I 
> believe streaming replication should work since both run one major 
> version). And the funny things started to happen.

Sadly, ru_RU.utf8 on Linux and ru_RU.utf8 on FreeBSD are almost
certainly *not* exactly compatible.  You could probably ignore the
differences if you were using logical replication, but with physical
replication any difference in sort order is going to mean that indexes
on text columns appear corrupt on the standby.  Which is exactly what
your troubles sound like.

You could verify this theory by seeing whether contrib/amcheck reports
any ordering problems in the indexes of the troublesome tables.
(You'll probably have to promote the standby to primary in order to
install the amcheck extension, but you need to treat that installation
as hosed anyway ...)

There's more info about this general class of problems at

https://wiki.postgresql.org/wiki/Locale_data_changes

although that focuses on the even-more-annoying case where locale
sort order changes between releases of a single OS.

			regards, tom lane





view thread (4+ messages)  latest in thread

Message-ID: <744349.1677597715@sss.pgh.pa.us>
Permalink:  ../744349.1677597715@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/744349.1677597715@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: tgl@sss.pgh.pa.us, eugene@zhegan.in
  Subject: Re: 13.x, stream replication and locale(?) issues
  In-Reply-To: <744349.1677597715@sss.pgh.pa.us>
  References: <2020842e-1841-4a9b-63fd-58a3628ede68@zhegan.in> <744349.1677597715@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox