Обсуждение: glibc initdb options vs icu compatibility questions (PG15)
Greetings, 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: -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 (default locale 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? 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? Best regards, Robert Sjöblom -- Innehållet i detta e-postmeddelande är konfidentiellt och avsett endast för adressaten.Varje spridning, kopiering eller utnyttjande av innehållet är förbjuden utan tillåtelse av avsändaren. Om detta meddelande av misstag gått till fel adressat vänligen radera det ursprungliga meddelandet och underrätta avsändaren via e-post
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
On 1/16/23 08:26, Laurenz Albe wrote: > 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. check > The libc locale is for other aspects of locale. Although I am not aware of any specific/known issues, keep in mind that there is still a possibility of changes across major glibc versions for things affected by LC_CTYPE, LC_TIME, LC_MONETARY, and LC_NUMERIC - lower()/upper() for example. -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com