Subject: Re: Moving from Linux to Linux?

From: Paul Foerster <[email-masked]>
Date: Tue, 11 Mar 2025 19:34:21 +0100

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

---

From: Ron Johnson <[email-masked]>
Date: Tue, 11 Mar 2025 15:34:45 -0400

On Tue, Mar 11, 2025 at 2:35 PM Paul Foerster <[email-masked]>
wrote:

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

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!

---

From: Thomas Boussekey <[email-masked]>
Date: Tue, 11 Mar 2025 21:49:05 +0100

Le mar. 11 mars 2025 à 20:35, Ron Johnson <[email-masked]> a

> 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

---

From: Paul Foerster <[email-masked]>
Date: Wed, 12 Mar 2025 12:01:09 +0100

Hi Ron,

> On 11 Mar 2025, at 20:34, Ron Johnson <[email-masked]> wrote:
> 

DDL during logical replication unfortunately is a show-stopper.

Cheers,
Paul

---

From: Christophe Pettus <[email-masked]>
Date: Wed, 12 Mar 2025 11:16:43 +0000

> On Mar 12, 2025, at 11:01, Paul Foerster <[email-masked]> 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.

---

From: Paul Foerster <[email-masked]>
Date: Wed, 12 Mar 2025 17:48:00 +0100

Hi Christophe,

> On 12 Mar 2025, at 12:16, Christophe Pettus <[email-masked]> wrote:
> 

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

---

From: Ron Johnson <[email-masked]>
Date: Wed, 12 Mar 2025 12:59:20 -0400

On Wed, Mar 12, 2025 at 12:48 PM Paul Foerster <[email-masked]>
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.

---

From: Paul Foerster <[email-masked]>
Date: Wed, 12 Mar 2025 21:16:18 +0100

Hi Ron,

> On 12 Mar 2025, at 17:59, Ron Johnson <[email-masked]> wrote:
> 

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

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 12 Mar 2025 13:26:23 -0700

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

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

-- 
Adrian Klaver
[email-masked]

---

From: Greg Sabino Mullane <[email-masked]>
Date: Wed, 12 Mar 2025 16:31:14 -0400

> 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

---

From: Paul Foerster <[email-masked]>
Date: Wed, 12 Mar 2025 21:49:22 +0100

Hi Greg,

> On 12 Mar 2025, at 21:31, Greg Sabino Mullane <[email-masked]> 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

---

From: Ron Johnson <[email-masked]>
Date: Wed, 12 Mar 2025 16:50:25 -0400

On Wed, Mar 12, 2025 at 4:16 PM Paul Foerster <[email-masked]>
wrote:

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

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.

---

From: Paul Foerster <[email-masked]>
Date: Wed, 12 Mar 2025 21:55:52 +0100

Hi Adrian,

> On 12 Mar 2025, at 21:26, Adrian Klaver <[email-masked]> wrote:
> 

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

---

From: Paul Foerster <[email-masked]>
Date: Wed, 12 Mar 2025 21:59:20 +0100

Hi Ron,

> On 12 Mar 2025, at 21:50, Ron Johnson <[email-masked]> 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

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 12 Mar 2025 14:37:33 -0700

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

What version of RH are you migrating to?

---

From: Joe Conway <[email-masked]>
Date: Wed, 12 Mar 2025 22:55:11 -0400

> 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

---

From: Paul Foerster <[email-masked]>
Date: Thu, 13 Mar 2025 11:10:40 +0100

Hi Joe,

> On 13 Mar 2025, at 03:55, Joe Conway <[email-masked]> 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:

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

---

From: Christophe Pettus <[email-masked]>
Date: Thu, 13 Mar 2025 10:25:12 +0000

> On Mar 13, 2025, at 10:10, Paul Foerster <[email-masked]> 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.