pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgresql streaming replication
8+ messages / 3 participants
[nested] [flat]
* Postgresql streaming replication
@ 2024-05-28 14:50 Prasanth <dbadmin@pangburngroup.com>
0 siblings, 2 replies; 8+ messages in thread
From: Prasanth @ 2024-05-28 14:50 UTC (permalink / raw)
To: pgsql-admin
Hi,
We are running postgresql 16 on debian 12. I tried to create a hot standby on a different server that is running alma linux 9. After doing the backbackup and starting the server seems like the
replication is going fine. When I tried to login to the standby to run some queries got the below message. Postgresql is installed using rpm.
DETAIL: The database was created using collation version 2.36, but the operating system provides version 2.34.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE database_name REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
Thanks,
Prasanth
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Postgresql streaming replication
@ 2024-05-28 14:53 Matti Linnanvuori <mattilinnanvuori@protonmail.com>
parent: Prasanth <dbadmin@pangburngroup.com>
1 sibling, 0 replies; 8+ messages in thread
From: Matti Linnanvuori @ 2024-05-28 14:53 UTC (permalink / raw)
To: dbadmin@pangburngroup.com; +Cc: pgsql-admin
Hi!
You should not use streaming replication with radically different operating systems because of collation differences. Use logical replication instead.
Best regards, Matti Linnanvuori
On Tuesday, May 28th, 2024 at 17.50, Prasanth <dbadmin@pangburngroup.com> wrote:
> Hi,
>
> We are running postgresql 16 on debian 12. I tried to create a hot standby on a different server that is running alma linux 9. After doing the backbackup and starting the server seems like the replication is going fine. When I tried to login to the standby to run some queries got the below message. Postgresql is installed using rpm.
>
> DETAIL: The database was created using collation version 2.36, but the operating system provides version 2.34.
> HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE database_name REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
>
> Thanks,
> Prasanth
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Postgresql streaming replication
@ 2024-05-28 14:55 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Prasanth <dbadmin@pangburngroup.com>
1 sibling, 1 reply; 8+ messages in thread
From: Scott Ribe @ 2024-05-28 14:55 UTC (permalink / raw)
To: dbadmin@pangburngroup.com; +Cc: pgsql-admin
> On May 28, 2024, at 8:50 AM, Prasanth <dbadmin@pangburngroup.com> wrote:
>
> Hi,
>
> We are running postgresql 16 on debian 12. I tried to create a hot standby on a different server that is running alma linux 9. After doing the backbackup and starting the server seems like the replication is going fine. When I tried to login to the standby to run some queries got the below message. Postgresql is installed using rpm.
>
> DETAIL: The database was created using collation version 2.36, but the operating system provides version 2.34.
> HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE database_name REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
Yep. Different versions of Linux changed collation rules in the glibc locale implementations. You will not be able to do streaming replication between those two Linux versions with those two builds of PostgreSQL. You will have to either:
- get Linux versions with same (or close enough) glibc versions
- figure out how to build PostgreSQL yourself with a compatible version
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Postgresql streaming replication
@ 2024-05-28 15:00 Prasanth <dbadmin@pangburngroup.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 8+ messages in thread
From: Prasanth @ 2024-05-28 15:00 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: pgsql-admin
I ran few queries and the recent data seems to be there. Would this cause differences in sorting or are there bigger issues with this difference in glibc libraries?
Thanks,
Prasanth
On 5/28/24 9:55 AM, Scott Ribe wrote:
>> On May 28, 2024, at 8:50 AM, Prasanth<dbadmin@pangburngroup.com> wrote:
>>
>> Hi,
>>
>> We are running postgresql 16 on debian 12. I tried to create a hot standby on a different server that is running alma linux 9. After doing the backbackup and starting the server seems like the replication is going fine. When I tried to login to the standby to run some queries got the below message. Postgresql is installed using rpm.
>>
>> DETAIL: The database was created using collation version 2.36, but the operating system provides version 2.34.
>> HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE database_name REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
> Yep. Different versions of Linux changed collation rules in the glibc locale implementations. You will not be able to do streaming replication between those two Linux versions with those two builds of PostgreSQL. You will have to either:
>
> - get Linux versions with same (or close enough) glibc versions
>
> - figure out how to build PostgreSQL yourself with a compatible version
>
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Postgresql streaming replication
@ 2024-05-28 15:23 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Prasanth <dbadmin@pangburngroup.com>
0 siblings, 1 reply; 8+ messages in thread
From: Scott Ribe @ 2024-05-28 15:23 UTC (permalink / raw)
To: dbadmin@pangburngroup.com; +Cc: pgsql-admin
> On May 28, 2024, at 9:00 AM, Prasanth <dbadmin@pangburngroup.com> wrote:
>
> I ran few queries and the recent data seems to be there. Would this cause differences in sorting or are there bigger issues with this difference in glibc libraries?
A "difference" in sorting will literally mean some queries not finding the matching data--because indexes were created under one set of sorting rules, then traversed under a different set of sorting rules.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Postgresql streaming replication
@ 2024-05-28 15:30 Prasanth <dbadmin@pangburngroup.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 8+ messages in thread
From: Prasanth @ 2024-05-28 15:30 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: pgsql-admin
Does this mean that a postgresql installed on Debian vs one installed on Alma will give different results even if the data is the same? Assuming they are independent databases but with same data.
Thanks,
Prasanth
On 5/28/24 10:23 AM, Scott Ribe wrote:
>> On May 28, 2024, at 9:00 AM, Prasanth<dbadmin@pangburngroup.com> wrote:
>>
>> I ran few queries and the recent data seems to be there. Would this cause differences in sorting or are there bigger issues with this difference in glibc libraries?
> A "difference" in sorting will literally mean some queries not finding the matching data--because indexes were created under one set of sorting rules, then traversed under a different set of sorting rules.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Postgresql streaming replication
@ 2024-05-28 15:37 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Prasanth <dbadmin@pangburngroup.com>
0 siblings, 1 reply; 8+ messages in thread
From: Scott Ribe @ 2024-05-28 15:37 UTC (permalink / raw)
To: dbadmin@pangburngroup.com; +Cc: pgsql-admin
> On May 28, 2024, at 9:30 AM, Prasanth <dbadmin@pangburngroup.com> wrote:
>
> Does this mean that a postgresql installed on Debian vs one installed on Alma will give different results even if the data is the same? Assuming they are independent databases but with same data.
yes: https://www.joeconway.com/presentations/glibc_issues-PGCon-2023.pdf
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Postgresql streaming replication
@ 2024-05-28 15:46 Prasanth <dbadmin@pangburngroup.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 8+ messages in thread
From: Prasanth @ 2024-05-28 15:46 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: pgsql-admin
Thank you.
On 5/28/24 10:37 AM, Scott Ribe wrote:
>> On May 28, 2024, at 9:30 AM, Prasanth<dbadmin@pangburngroup.com> wrote:
>>
>> Does this mean that a postgresql installed on Debian vs one installed on Alma will give different results even if the data is the same? Assuming they are independent databases but with same data.
> yes:https://www.joeconway.com/presentations/glibc_issues-PGCon-2023.pdf
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2024-05-28 15:46 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-28 14:50 Postgresql streaming replication Prasanth <dbadmin@pangburngroup.com>
2024-05-28 14:53 ` Matti Linnanvuori <mattilinnanvuori@protonmail.com>
2024-05-28 14:55 ` Scott Ribe <scott_ribe@elevated-dev.com>
2024-05-28 15:00 ` Prasanth <dbadmin@pangburngroup.com>
2024-05-28 15:23 ` Scott Ribe <scott_ribe@elevated-dev.com>
2024-05-28 15:30 ` Prasanth <dbadmin@pangburngroup.com>
2024-05-28 15:37 ` Scott Ribe <scott_ribe@elevated-dev.com>
2024-05-28 15:46 ` Prasanth <dbadmin@pangburngroup.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox