Re: ICU, locale and collation question

On Mon, 2023-05-08 at 16:35 +0200, Oscar Carlberg wrote:
> We have a bunch of existing Postgres 10 clusters running on CentOS 7,
> which have been initialized (initdb) with these collation options;
>
> -E 'UTF-8'
> --lc-collate=sv_SE.UTF-8
> --lc-ctype=sv_SE.UTF-8
> --lc_monetary=sv_SE.UTF-8
> --lc-numeric=sv_SE.UTF-8
> --lc-time=sv_SE.UTF-8
> --lc-messages=en_US.UTF-8
>
> And createdb were provided with these locale options when setting up
> databases:
> --lc-collate=sv_SE.UTF-8
> --lc-ctype=sv_SE.UTF-8
>
> We're upgrading the servers using logical replication, and would like to
> take the opportunity to switch to ICU rather than relying on glibc, to
> avoid future problems with index corruption if using physical
> replication between servers with different versions of glibc.
>
> We're trying to figure out the most correct way to configure postgres to
> achieve this. Currently we have:
>
> -E 'UTF-8'
> --locale-provider=icu
> --icu-locale=sv-SE-x-icu
>
> And createdb are provided with locale options:
> --lc-collate=C
> --lc-ctype=C
>
> Is this a safe configuration to avoid index corruption, and other
> problems, while still being compatible with the previous locale
> settings?

Yes, that is safe.

But it is not compatible with the old setup when it comes to lc_time,
lc_messages and the others. You should use sv_SE.UTF-8 for these
locale categories.

Note that that won't cause problems with upgrading the C library.

Yours,
Laurenz Albe