Re: BUG #16593: pg_upgrade make corrupt UK indexes

On Wed, Aug 26, 2020 at 7:38 AM Nick Cleaton <nick(at)cleaton(dot)net> wrote:

> On Tue, 25 Aug 2020 at 23:55, PG Bug reporting form <
> noreply(at)postgresql(dot)org> wrote:
>
>> We upgrade from 9.6 Centos7 to 12.3 Centos 8
>>
>
> This from https://www.postgresql.org/docs/12/amcheck.html#id-1.11.7.11.9
> might be relevent:
>
> > Comparisons of datums of a collatable type like text must be immutable
> (just as all comparisons used for B-Tree index scans must be immutable),
> which implies that operating system collation rules must never change.
> Though rare, updates to operating system collation rules can cause these
> issues. More commonly, an inconsistency in the collation order between a
> master server and a standby server is implicated, possibly because the
> *major* operating system version in use is inconsistent
>

And in particular also see
https://wiki.postgresql.org/wiki/Locale_data_changes, which shows that the
step from Centos7 to Centos8 is one that causes this problem. You need to
reindex all text/varchar indexes when you make that upgrade.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>