Re: Moving from Linux to Linux? - Mailing list pgsql-general

Hi Joe,

> On 13 Mar 2025, at 03:55, Joe Conway <mail@joeconway.com> wrote:
>
> If you are desperate you could start with https://github.com/awslabs/compat-collation-for-glibc and create a new
branchfor your current version of SLES/glibc and deploy the resulting rpm to RHEL. At least in theory. FWIW I was able
toget the glibc locale compatibility library for AL2 (glibc 2.26) to work fine on Linux Mint system (glibc 2.34). 

I'm not desperate. Moving from SUSE to Red Hat is an option to make especially building PostGIS easier to handle. We
wereadvised this way by a consultant, who also told us that he had never seen anyone actually running PostGIS on SUSE.
Weneed to build PostGIS from source because we have to meet some special requirements. SUSE is usually hopelessly out
ofdate as far as dependency packages are concerned. So we have to build them too from source (cgal, SFCGAL, gdal, proj,
…you name it). The idea is that Red Hat makes those things easier to handle because their repository is more current
thanSUSE's. 

> For more on the compatibility library you could watch a presentation[1] starting here:
>  https://youtu.be/0E6O-V8Jato?t=1749

I'm going to watch this one later. Thanks very much.

> The other option, which may be equally untenable, is to upgrade in-place to pg17 and convert everything to use the
newbuilt-in collation provider. That ought to be portable across different versions of Linux. 

We have now on PostgreSQL 15.12:

postgres=# select version();
                                      version
-------------------------------------------------------------------------------------
 PostgreSQL 15.12 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux) 7.5.0, 64-bit
(1 row)

postgres=# \l postgres
                                                List of databases
   Name   |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | postgres=CTc/postgres
(1 row)

And we are going to PostgreSQL 17.4:

postgres=# select version();
                                      version
------------------------------------------------------------------------------------
 PostgreSQL 17.4 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux) 7.5.0, 64-bit
(1 row)

postgres=# \l postgres
                                                   List of databases
   Name   |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Locale | ICU Rules | Access privileges
----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-------------------
 postgres | postgres | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US  |           |  (1 row)

Is C.UTF8 really the same as en_US.UTF8? I ask because though we use en_US.UTF8, we are located in Switzerland and
usingnon English characters is not exactly the exception. We have characters from all over the world in our databases.
Theremust be no sorting differences between en_US.UTF8 and C.UTF8. Otherwise we will run into trouble with unhappy
customers.So, C.UTF8 would only be an option if the collation would be identical. 

> The problem you might find with libicu is that different versions of ICU can have the same issues as different
versionsof glibc, and you might not have the same ICU version available on SLES and RHEL. 

Yes, I know. As far as I have been told, libicu is far less prone to major collation changes than glibc is. Also,
libicuoffers the possibility to pin a version for a certain time. Our sysadmins will naturally not be able to pin a
glibcversion without wrecking an inevitable server upgrade. 

> If you want to explore the compatibility library approach contact me off list and I will try to get you started. It
hasbeen a couple of years since I touched it, but when I did it took me a couple of days to get from the AL2 (glibc
2.26)branch (which was done first) to the RHEL 7 (glibc 2.17) branch. 

I just took a quick glance. I don't have a Github account (and also don't want one 🤣). I can do a git clone, but that's
basicallyall I know. Also, right now, I'm just exploring possibilities. As far as I understand the readme on Github,
thiswill replace the glibc on Red Had with one with adapted collation rules? If this is the case, then our admins will
definitelysay no to this. 

> [1] https://www.joeconway.com/presentations/2025-PGConf.IN-glibc.pdf

This is a really good one. Thanks very much for this.

Cheers,
Paul