Thread: Moving from Linux to Linux?

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

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

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
aspossible, the idea is to set up a streaming replication from the SUSE server to be replaced to a temporary Red Hat
serverand 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
binarycompatible.
 

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

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

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,
becauseit would mean too much application downtime. 

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

Thanks very much for your input.

Cheers,
Paul

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

> 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
meansapplying it to the subscriber before you apply it to the publisher, and making sure that any columns added to a
tableare either NULLable or have defaults. 

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
meansapplying it to the subscriber before you apply it to the publisher, and making sure that any columns added to a
tableare 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
doingtheir work. 

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

Cheers,
Paul

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
replicatethe 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
appreciatedby 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-productionbecause of automatic deployments and only possible on production databases. 

Anyway, this is going off-topic now.

Cheers,
Paul

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,
becauseit 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

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

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
alreadyon 15.6. Thank god, we didn't have that ugly database back then that we have now. But I see the next doomsday
comingon the horizon. 🤣 

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

Cheers,
Paul

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-proddatabases 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.
Nexttob working a normal day, capacities to to frequent things like these are limited. 

Cheers,
Paul

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
alreadyon 15.6. Thank god, we didn't have that ugly database back then that we have now. But I see the next doomsday
comingon 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

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
alreadyon 15.6. Thank god, we didn't have that ugly database back then that we have now. But I see the next doomsday
comingon 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

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

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

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

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