Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.400.131.1.6\))
Subject: Re: Moving from Linux to Linux?
From: Paul Foerster <paul.foerster@gmail.com>
In-Reply-To: <c60b0558-290b-4fe8-b839-6a7725c980b3@joeconway.com>
Date: Thu, 13 Mar 2025 11:10:40 +0100
Cc: Adrian Klaver <adrian.klaver@aklaver.com>,
 Pgsql-General List <pgsql-general@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <6C78D9E9-2EC3-4FBF-AC22-268DCE8F6A91@gmail.com>
References: <6E6059F6-E5A1-4A8D-8A5E-A41B921BBE67@gmail.com>
 <E0EC05FB-783D-4A24-81B9-0C345A5EC45A@gmail.com>
 <9a8d92de-228c-43b0-b66c-c3390b41044a@aklaver.com>
 <B6135108-EA9F-4E75-80EE-1077D0909F00@gmail.com>
 <c60b0558-290b-4fe8-b839-6a7725c980b3@joeconway.com>
To: Joe Conway <mail@joeconway.com>
Archived-At: <https://www.postgresql.org/message-id/6C78D9E9-2EC3-4FBF-AC22-268DCE8F6A91%40gmail.com>
Precedence: bulk

Hi Joe,

> On 13 Mar 2025, at 03:55, Joe Conway <mail@joeconway.com> wrote:
>=20
> If you are desperate you could start with =
https://github.com/awslabs/compat-collation-for-glibc and create a new =
branch for your current version of SLES/glibc and deploy the resulting =
rpm to RHEL. At least in theory. FWIW I was able to get 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 were advised this way =
by a consultant, who also told us that he had never seen anyone actually =
running PostGIS on SUSE. We need to build PostGIS from source because we =
have to meet some special requirements. SUSE is usually hopelessly out =
of date as far as dependency packages are concerned. So we have to build =
them too from source (cgal, SFCGAL, gdal, proj, =E2=80=A6 you name it). =
The idea is that Red Hat makes those things easier to handle because =
their repository is more current than SUSE's.

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

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 new built-in =
collation provider. That ought to be portable across different versions =
of Linux.

We have now on PostgreSQL 15.12:

postgres=3D# 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=3D# \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=3DCTc/postgres
(1 row)

And we are going to PostgreSQL 17.4:

postgres=3D# 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=3D# \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 using non English =
characters is not exactly the exception. We have characters from all =
over the world in our databases. There must 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 versions of 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, libicu offers the =
possibility to pin a version for a certain time. Our sysadmins will =
naturally not be able to pin a glibc version 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 has been 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 =F0=9F=A4=A3). I can do a git clone, but that's basically =
all I know. Also, right now, I'm just exploring possibilities. As far as =
I understand the readme on Github, this will replace the glibc on Red =
Had with one with adapted collation rules? If this is the case, then our =
admins will definitely say 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