Thread

Moving from Linux to Linux?

Paul Foerster <paul.foerster@gmail.com> — 2025-03-11T16:22:26Z

Hi,

we are considering changing the PostgreSQL platform from SUSE SLE to Red Hat. To keep service interruptions as short as possible, the idea is to set up a streaming replication from the SUSE server to be replaced to a temporary Red Hat server and then replace that SUSE server with the newly setup Red Hat server.

My idea is to set up a streaming replication for this. But this of course only works if the data files would be binary compatible.

So, I wonder, if this is possible. We have a lot of databases, some of them need to be highly available and some are large too.

Are there any obstacles that definitely make that a no-go? Do I risk corruption? It's both Linux, just a different distribution.

Cheers,
Paul

Re: Moving from Linux to Linux?

Thomas Boussekey <thomas.boussekey@gmail.com> — 2025-03-11T16:30:27Z

Hello,

Le mar. 11 mars 2025 à 17:23, Paul Foerster <paul.foerster@gmail.com> a
écrit :

> Hi,
>
> we are considering changing the PostgreSQL platform from SUSE SLE to Red
> Hat. To keep service interruptions as short as possible, the idea is to set
> up a streaming replication from the SUSE server to be replaced to a
> temporary Red Hat server and then replace that SUSE server with the newly
> setup Red Hat server.
>
> My idea is to set up a streaming replication for this. But this of course
> only works if the data files would be binary compatible.
>
It seems to be possible, see this conference talk:
https://youtu.be/LFHI58JCm0Q?si=EWeXrKmCURTz_VN-
They have performed a linux migration with physical replication, but there
are many pitfalls around!

I have performed the same kind of migration with logical replication and,
in my context, it was far easier!

>
> So, I wonder, if this is possible. We have a lot of databases, some of
> them need to be highly available and some are large too.
>
> Are there any obstacles that definitely make that a no-go? Do I risk
> corruption? It's both Linux, just a different distribution.
>
The version of the glibc binary seems to be a determining factor

>
> Cheers,
> Paul
>
> HTH, Thomas

Re: Moving from Linux to Linux?

Devrim GÜNDÜZ <devrim@gunduz.org> — 2025-03-11T16:30:29Z

Hi,

(Sorry for the top posting)

Streaming replication won't work between SLES and RHEL, mostly because of glibc incompatibilities. Use logical replication.


Regards, Devrim

On 11 March 2025 16:22:26 GMT, Paul Foerster <paul.foerster@gmail.com> wrote:
>Hi,
>
>we are considering changing the PostgreSQL platform from SUSE SLE to Red Hat. To keep service interruptions as short as possible, the idea is to set up a streaming replication from the SUSE server to be replaced to a temporary Red Hat server and then replace that SUSE server with the newly setup Red Hat server.
>
>My idea is to set up a streaming replication for this. But this of course only works if the data files would be binary compatible.
>
>So, I wonder, if this is possible. We have a lot of databases, some of them need to be highly available and some are large too.
>
>Are there any obstacles that definitely make that a no-go? Do I risk corruption? It's both Linux, just a different distribution.
>
>Cheers,
>Paul
>

Re: Moving from Linux to Linux?

Adrian Klaver <adrian.klaver@aklaver.com> — 2025-03-11T16:32:28Z

On 3/11/25 09:22, Paul Foerster wrote:
> Hi,
> 
> we are considering changing the PostgreSQL platform from SUSE SLE to Red Hat. To keep service interruptions as short as possible, the idea is to set up a streaming replication from the SUSE server to be replaced to a temporary Red Hat server and then replace that SUSE server with the newly setup Red Hat server.

What versions of each?

> 
> My idea is to set up a streaming replication for this. But this of course only works if the data files would be binary compatible.

Unless you use logical replication.

> 
> So, I wonder, if this is possible. We have a lot of databases, some of them need to be highly available and some are large too.
> 
> Are there any obstacles that definitely make that a no-go? Do I risk corruption? It's both Linux, just a different distribution.
> 
> Cheers,
> Paul
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Moving from Linux to Linux?

Ron <ronljohnsonjr@gmail.com> — 2025-03-11T16:32:39Z

On Tue, Mar 11, 2025 at 12:23 PM Paul Foerster <paul.foerster@gmail.com>
wrote:

> Hi,
>
> we are considering changing the PostgreSQL platform from SUSE SLE to Red
> Hat. To keep service interruptions as short as possible, the idea is to set
> up a streaming replication from the SUSE server to be replaced to a
> temporary Red Hat server and then replace that SUSE server with the newly
> setup Red Hat server.
>
> My idea is to set up a streaming replication for this. But this of course
> only works if the data files would be binary compatible.
>
> So, I wonder, if this is possible. We have a lot of databases, some of
> them need to be highly available and some are large too.
>
> Are there any obstacles that definitely make that a no-go? Do I risk
> corruption? It's both Linux, just a different distribution.
>

The same version of PG will be on both, right?

What version of RHEL?  What version of SLES?  Those questions are proxy
for: what version of glibc on each system?

If they're the same, and you use libc for collation, then you're (probably)
good to go.

If they're different, then you should use logical replication.  Otherwise,
string collation mismatch could bite you on any indices on text fields.

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

Re: Moving from Linux to Linux?

Joe Conway <mail@joeconway.com> — 2025-03-11T16:36:40Z

On 3/11/25 12:30, Devrim Gündüz wrote:
> Streaming replication won't work between SLES and RHEL, mostly because 
> of glibc incompatibilities. Use logical replication.

To be clear, it probably will "work" for some definition of "work" as 
long as the architecture is the same. In other words if you try it, it 
might well appear to work ok.

However as Devrim correctly points out, differences in glibc versions 
almost certainly will mean that you will have corrupt indexes on 
collatable columns. These indexes can be fixed via REINDEX, but if you 
don't do that *before* allowing any DML you risk further complications 
such as inability to REINDEX due to duplicate primary key values.

HTH,

-- 
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

Re: Moving from Linux to Linux?

Paul Foerster <paul.foerster@gmail.com> — 2025-03-11T18:34:21Z

Hi Devrim, Thomas, Adrian, Ron, Joe,

answering to myself as answering to five postings in one go is impossible. 🤣

> Are there any obstacles that definitely make that a no-go? Do I risk corruption? It's both Linux, just a different distribution.

The question was a bit of an idea. So the glibc version in not known yet, but I'm highly confident that they will differ. A reindex could in theory be possible in most cases, but is a definite show stopper on some of our databases, because it would mean too much application downtime.

So, it's either logical replication or close to impossible.

Thanks very much for your input.

Cheers,
Paul

Re: Moving from Linux to Linux?

Ron <ronljohnsonjr@gmail.com> — 2025-03-11T19:34:45Z

On Tue, Mar 11, 2025 at 2:35 PM Paul Foerster <paul.foerster@gmail.com>
wrote:

> Hi Devrim, Thomas, Adrian, Ron, Joe,
>
> answering to myself as answering to five postings in one go is impossible.
> 🤣
>
> > Are there any obstacles that definitely make that a no-go? Do I risk
> corruption? It's both Linux, just a different distribution.
>
> The question was a bit of an idea. So the glibc version in not known yet,
> but I'm highly confident that they will differ. A reindex could in theory
> be possible in most cases, but is a definite show stopper on some of our
> databases, because it would mean too much application downtime.
>
> So, it's either logical replication or close to impossible.
>

If you don't do much DDL, the LR should be quite workable.

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

Re: Moving from Linux to Linux?

Thomas Boussekey <thomas.boussekey@gmail.com> — 2025-03-11T20:49:05Z

Le mar. 11 mars 2025 à 20:35, Ron Johnson <ronljohnsonjr@gmail.com> a
écrit :

> On Tue, Mar 11, 2025 at 2:35 PM Paul Foerster <paul.foerster@gmail.com>
> wrote:
>
>> Hi Devrim, Thomas, Adrian, Ron, Joe,
>>
>> answering to myself as answering to five postings in one go is
>> impossible. 🤣
>>
>> > Are there any obstacles that definitely make that a no-go? Do I risk
>> corruption? It's both Linux, just a different distribution.
>>
>> The question was a bit of an idea. So the glibc version in not known yet,
>> but I'm highly confident that they will differ. A reindex could in theory
>> be possible in most cases, but is a definite show stopper on some of our
>> databases, because it would mean too much application downtime.
>>
>> So, it's either logical replication or close to impossible.
>>
>
> If you don't do much DDL, the LR should be quite workable.
>
Stop DDL during the copy process is mandatory, but there are so other
restrictions to consider also (large objects, sequences...), see this page
for an exhaustive list:
https://www.postgresql.org/docs/current/logical-replication-restrictions.html


>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>

Re: Moving from Linux to Linux?

Paul Foerster <paul.foerster@gmail.com> — 2025-03-12T11:01:09Z

Hi Ron,

> On 11 Mar 2025, at 20:34, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
> 
> If you don't do much DDL, the LR should be quite workable.

DDL during logical replication unfortunately is a show-stopper.

Cheers,
Paul

Re: Moving from Linux to Linux?

Christophe Pettus <xof@thebuild.com> — 2025-03-12T11:16:43Z

> On Mar 12, 2025, at 11:01, Paul Foerster <paul.foerster@gmail.com> wrote:
> DDL during logical replication unfortunately is a show-stopper.

You *can* apply DDL while logical replication is going on, as long as you do so in a disciplined way.  This generally means applying it to the subscriber before you apply it to the publisher, and making sure that any columns added to a table are either NULLable or have defaults.

Re: Moving from Linux to Linux?

Paul Foerster <paul.foerster@gmail.com> — 2025-03-12T16:48:00Z

Hi Christophe,

> On 12 Mar 2025, at 12:16, Christophe Pettus <xof@thebuild.com> wrote:
> 
> You *can* apply DDL while logical replication is going on, as long as you do so in a disciplined way.  This generally means applying it to the subscriber before you apply it to the publisher, and making sure that any columns added to a table are either NULLable or have defaults.

Yes, I know, but this is a non issue in real life with dozens of databases per instance and a few hundred developers doing their work.

So, logical replication online for me is more of an academic case study. In real life I need downtime.

Cheers,
Paul

Re: Moving from Linux to Linux?

Ron <ronljohnsonjr@gmail.com> — 2025-03-12T16:59:20Z

On Wed, Mar 12, 2025 at 12:48 PM Paul Foerster <paul.foerster@gmail.com>
wrote:

> Hi Christophe,
>
> > On 12 Mar 2025, at 12:16, Christophe Pettus <xof@thebuild.com> wrote:
> >
> > You *can* apply DDL while logical replication is going on, as long as
> you do so in a disciplined way.  This generally means applying it to the
> subscriber before you apply it to the publisher, and making sure that any
> columns added to a table are either NULLable or have defaults.
>
> Yes, I know, but this is a non issue in real life with dozens of databases
> per instance and a few hundred developers doing their work.
>

Developers making DDL changes on production databases?

Or are there prod and dev databases on the same instance?  If so, then know
that you don't have to logically replicate the whole instance.

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

Re: Moving from Linux to Linux?

Paul Foerster <paul.foerster@gmail.com> — 2025-03-12T20:16:18Z

Hi Ron,

> On 12 Mar 2025, at 17:59, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
> 
> Developers making DDL changes on production databases?

Of course not. But I can't block developer databases. That'd make a few hundred developers happy.

> Or are there prod and dev databases on the same instance?  If so, then know that you don't have to logically replicate the whole instance.

Also of course not. There is development, pre-production and production.

Outages on development databases make a few hundred developers happy, while outages of production databases are appreciated by up to almost 40K users, depending on the application.

Anyway, this is our concern. In our environment, logical replication is impossible for development databases, hard for pre-production because of automatic deployments and only possible on production databases.

Anyway, this is going off-topic now.

Cheers,
Paul

Re: Moving from Linux to Linux?

Adrian Klaver <adrian.klaver@aklaver.com> — 2025-03-12T20:26:23Z

On 3/11/25 11:34, Paul Foerster wrote:
> Hi Devrim, Thomas, Adrian, Ron, Joe,
> 
> answering to myself as answering to five postings in one go is impossible. 🤣
> 
>> Are there any obstacles that definitely make that a no-go? Do I risk corruption? It's both Linux, just a different distribution.
> 
> The question was a bit of an idea. So the glibc version in not known yet, but I'm highly confident that they will differ. A reindex could in theory be possible in most cases, but is a definite show stopper on some of our databases, because it would mean too much application downtime.

A good summary of the glibc issue:

https://wiki.postgresql.org/wiki/Locale_data_changes

With distro information:

https://wiki.postgresql.org/wiki/Locale_data_changes#What_Linux_distributions_are_affected

> 
> So, it's either logical replication or close to impossible.
> 
> Thanks very much for your input.
> 
> Cheers,
> Paul
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Moving from Linux to Linux?

Greg Sabino Mullane <htamfids@gmail.com> — 2025-03-12T20:31:14Z

On Tue, Mar 11, 2025 at 2:35 PM Paul Foerster <paul.foerster@gmail.com>
wrote:

> The question was a bit of an idea. So the glibc version in not known yet,
> but I'm highly confident that they will differ. A reindex could in theory
> be possible in most cases, but is a definite show stopper on some of our
> databases, because it would mean too much application downtime.
>

Keep in mind that you only need to reindex text-based indexes. Ints are
still gonna int. So it might not be too bad.

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

Re: Moving from Linux to Linux?

Paul Foerster <paul.foerster@gmail.com> — 2025-03-12T20:49:22Z

Hi Greg,

> On 12 Mar 2025, at 21:31, Greg Sabino Mullane <htamfids@gmail.com> wrote:
> 
> Keep in mind that you only need to reindex text-based indexes. Ints are still gonna int. So it might not be too bad.

Yes, I know, but unfortunately the worst case index of them all will still take a few hours.

Cheers,
Paul

Re: Moving from Linux to Linux?

Ron <ronljohnsonjr@gmail.com> — 2025-03-12T20:50:25Z

On Wed, Mar 12, 2025 at 4:16 PM Paul Foerster <paul.foerster@gmail.com>
wrote:

> Hi Ron,
>
> > On 12 Mar 2025, at 17:59, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
> >
> > Developers making DDL changes on production databases?
>
> Of course not. But I can't block developer databases. That'd make a few
> hundred developers happy.
>
> > Or are there prod and dev databases on the same instance?  If so, then
> know that you don't have to logically replicate the whole instance.
>
> Also of course not. There is development, pre-production and production.
>
> Outages on development databases make a few hundred developers happy,
> while outages of production databases are appreciated by up to almost 40K
> users, depending on the application.
>
> Anyway, this is our concern. In our environment, logical replication is
> impossible for development databases, hard for pre-production because of
> automatic deployments and only possible on production databases.
>
> Anyway, this is going off-topic now.
>

No, I think it's 100% on point: logically replicate the Prod databases,
while pg_dump/pg_restore of the dev and pre-prod databases happen on
weekends.

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

Re: Moving from Linux to Linux?

Paul Foerster <paul.foerster@gmail.com> — 2025-03-12T20:55:52Z

Hi Adrian,

> On 12 Mar 2025, at 21:26, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> 
> A good summary of the glibc issue:
> 
> https://wiki.postgresql.org/wiki/Locale_data_changes
> 
> With distro information:
> 
> https://wiki.postgresql.org/wiki/Locale_data_changes#What_Linux_distributions_are_affected

I know the article, thanks. We needed a full reindex when upgrading from SLES 15.2 to 15.3. We're on 15.5 now, partly already on 15.6. Thank god, we didn't have that ugly database back then that we have now. But I see the next doomsday coming on the horizon. 🤣

Also, we try to get rid of the libc locale provider and switch to libicu, wherever possible.

Cheers,
Paul

Re: Moving from Linux to Linux?

Paul Foerster <paul.foerster@gmail.com> — 2025-03-12T20:59:20Z

Hi Ron,

> On 12 Mar 2025, at 21:50, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
> 
> No, I think it's 100% on point: logically replicate the Prod databases, while pg_dump/pg_restore of the dev and pre-prod databases happen on weekends.

Yes, I live for and in the company. 🤣 SNCR.

No, seriously, I'm one of only two PostgreSQL DBAs and our working capacity is limited by biological and other factors. Next tob working a normal day, capacities to to frequent things like these are limited.

Cheers,
Paul

Re: Moving from Linux to Linux?

Adrian Klaver <adrian.klaver@aklaver.com> — 2025-03-12T21:37:33Z

On 3/12/25 13:55, Paul Foerster wrote:
> Hi Adrian,
> 
>> On 12 Mar 2025, at 21:26, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>
>> A good summary of the glibc issue:
>>
>> https://wiki.postgresql.org/wiki/Locale_data_changes
>>
>> With distro information:
>>
>> https://wiki.postgresql.org/wiki/Locale_data_changes#What_Linux_distributions_are_affected
> 
> I know the article, thanks. We needed a full reindex when upgrading from SLES 15.2 to 15.3. We're on 15.5 now, partly already on 15.6. Thank god, we didn't have that ugly database back then that we have now. But I see the next doomsday coming on the horizon. 🤣

What version of RH are you migrating to?

> 
> Also, we try to get rid of the libc locale provider and switch to libicu, wherever possible.
> 
> Cheers,
> Paul

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Moving from Linux to Linux?

Joe Conway <mail@joeconway.com> — 2025-03-13T02:55:11Z

On 3/12/25 16:55, Paul Foerster wrote:
> Hi Adrian,
> 
>> On 12 Mar 2025, at 21:26, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>> 
>> A good summary of the glibc issue:
>> 
>> https://wiki.postgresql.org/wiki/Locale_data_changes
>> 
>> With distro information:
>> 
>> https://wiki.postgresql.org/wiki/Locale_data_changes#What_Linux_distributions_are_affected
> 
> I know the article, thanks. We needed a full reindex when upgrading from SLES 15.2 to 15.3. We're on 15.5 now, partly already on 15.6. Thank god, we didn't have that ugly database back then that we have now. But I see the next doomsday coming on the horizon. 🤣
> 
> Also, we try to get rid of the libc locale provider and switch to libicu, wherever possible.

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).

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

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.

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.

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.

HTH,

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

-- 
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

Re: Moving from Linux to Linux?

Paul Foerster <paul.foerster@gmail.com> — 2025-03-13T08:53:16Z

Hi Adrian,

> On 12 Mar 2025, at 22:37, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> 
> What version of RH are you migrating to?

That'd be currently Red Hat 9 with glibc 2.34.

Cheers,
Paul

Re: Moving from Linux to Linux?

Paul Foerster <paul.foerster@gmail.com> — 2025-03-13T10:10:40Z

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 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, … 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=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 new built-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 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 🤣). 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

Re: Moving from Linux to Linux?

Christophe Pettus <xof@thebuild.com> — 2025-03-13T10:25:12Z

> On Mar 13, 2025, at 10:10, Paul Foerster <paul.foerster@gmail.com> wrote:
> 
> Is C.UTF8 really the same as en_US.UTF8?

No.  C.UTF8 sorts on Unicode code points, which will be (somewhat) different from en_US.UTF8.

If you want a collation that is "good enough" across multiple languages, the ICU collation und-x-icu is the best bet.

Re: Moving from Linux to Linux?

Joe Conway <mail@joeconway.com> — 2025-03-13T13:14:46Z

On 3/13/25 06:10, Paul Foerster wrote:
>> 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.

> 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.

Definitely not exactly the same. It does handle all the same characters 
(UTF8).

How often do you really depend on the ordering to the client being 
exactly what the end user wants to see? Often the final ordering is done 
in the application. Or could be done. You can also use a COLLATE clause 
to get exactly the ordering you want when you need it.

The builtin collation has two big advantages -- 1) it should be stable 
and portable, and 2) it should perform substantially faster (in simple 
tests I have seen 10X speed ups).

>> 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.

Less prone, yes, but still it happens. And when it happens you can get 
corruption of your index, which can lead to things like duplicate 
primary/unique keys and data going to the wrong partition to cite two 
examples.

> 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.

Yes, in theory libicu can be pinned more easily than glibc, for sure. 
The reality is that you would either need your Linux distro to provide 
that pinned version as you upgrade, which I don't think any of them do 
(or in the case of SUSE to RHEL they would have to match from the get 
go), or you would have to take on maintaining your own pinned version 
going forward. That latter option is essentially the same as the glibc 
compatibility library approach though, so perhaps not horrible.

>> 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 🤣). 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.

No, it does not replace glibc. It extracts just the locale functionality 
from glibc into its own portable library, pretty much like libicu. Then 
you can link against it. So for example you wind up with "glibc 2.26 
locale semantics" with Postgres on your Linux distro which has glibc 
2.34 installed. All of the non-locale functionality comes from the 
system glibc 2.34.

>> [1] https://www.joeconway.com/presentations/2025-PGConf.IN-glibc.pdf
> This is a really good one. Thanks very much for this.

You should probably watch this presentation in its entirety:
   https://www.youtube.com/watch?v=KTA6oau7tl8
Jeremy does a really good job of dispelling misconceptions and if I 
remember correctly Jeff Davis talks about the builtin provider.

-- 
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com