Re: PG 9.6.20 -- query misbehaves in replica

On Sat, Nov 14, 2020 at 1:10 AM Ernesto Hernández-Novich <emhnemhn(at)gmail(dot)com>
wrote:

> On Sat, 2020-11-14 at 00:59 +0100, Magnus Hagander wrote:
> > On Sat, Nov 14, 2020 at 12:17 AM Ernesto Hernández-Novich <
> > emhnemhn(at)gmail(dot)com> wrote:
> [...]
> > > After updating from 9.6.19 to 9.6.20, we noticed the query was not
> > > working on D.
> [...]
> > > Looks like a the 9.6.20 over Debian 10 is the culprit, but I have
> > > nothing else to work on.
> >
> > These issues are almost certainly because of the glibc locale changes
> > between Debian 9 and Debian 10, and not because of the PostgreSQL
> > upgrade.
> >
> > If you have master and standby on different glibc versions (so debian
> > 9 vs 10), all text based indexes can behave differently. All the
> > nodes must run the same version for this to work. So in this
> > scenario, you need to reinstall B and D with debian 9, or you need to
> > upgrade your other nodes to debian 10 (and if you do that, then you
> > have to reindex the master node once it has been upgraded).
>
> I agree on the locale matching and will try your suggestion.
>
> I must say the Debian 10 machines were working just fine with PG 9.6.19
> for over two months or so. We noticed the issue *just* this week after
> upgrading from 9.6.19 onto 9.6.20.
>

Most likely they weren't, but they looked like they were. It could be that
the problem wasn't triggered until now, but it could also be that it
"sometimes worked and sometimes didn't", and you just didn't notice -- I've
come across several installations that had exactly that problem.

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