Re: glibc initdb options vs icu compatibility questions (PG15)

On Mon, 2023-01-16 at 09:30 +0100, Robert Sjöblom wrote:
> We have a fleet of postgres 10 servers (1 primary, 2 replicas) that
> we're now planning to upgrade. We've historically been forced to use the
> same distro (centos7) and libc version, or rely on pg_dump/restore,
> across pg versions due to the fact that the servers/databases were
> initialized with the following options:
> --lc-collate=sv_SE.UTF-8
>
> We're upgrading all servers to pg15 through logical replication, and
> with that we'd like to remove our dependency on any specific libc
> version (to avoid corruption issues etc). We hope to do this by
> initializing our pg15 clusters with swedish icu locale
> (--icu-locale=sv-SE-x-icu). By using icu like this, we should have the
> same sorting behavior as the pg10 servers have today? By our
> understanding, we then should be able to use physical replication across
> different OS versions, without worrying about index corruption etc?
>
> According to
> http://peter.eisentraut.org/blog/2022/09/26/icu-features-in-postgresql-15,
> we still need to set a regular libc locale/option; does this mean that
> the dependency on libc is still present as it pertains to corruption
> issues and being forced to use the same libc version across all replicas?

I'd say no. The problem is the collation, and for that, the ICU collation
will be used. The libc locale is for other aspects of locale.

> Our current initdb arguments for the pg15 version looks like this:
> --lc-collate=C
> --lc-ctype=C
> --lc_monetary=C
> --lc-numeric=C
> --lc-time=C
> --encoding=UTF-8
> --locale-provider=icu
> --icu-locale=sv-SE-x-icu
> --lc-messages=en_US.UTF-8
>
> Does this correspond to the same behavior as our previous (pg10)
> servers, but using icu instead of glibc?

Not the exact same, since the libc collation and the ICU collation will not
be exactly identical. But you should not get any complaints from your users,
since the collations are presumably very similar.

Yours,
Laurenz Albe