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 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
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
Show quoted text
Cheers,
PaulHTH, Thomas
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
Show quoted text
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
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
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!
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, 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
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!
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
Show quoted text
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
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 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.
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
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!
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
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
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
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
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!
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
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