Re: 13.x, stream replication and locale(?) issues

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Eugene M(dot) Zheganin" <eugene(at)zhegan(dot)in>
Cc: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: 13.x, stream replication and locale(?) issues
Date: 2023-02-28 15:21:55
Message-ID: 744349.1677597715@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"Eugene M. Zheganin" <eugene(at)zhegan(dot)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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Rob Sargent 2023-02-28 15:38:56 Re: Quit currently running query
Previous Message celati Laurent 2023-02-28 14:39:16 Move all elements toward another schema?