Re: Hot Standby setup - collation version mismatch

On 2/25/25 12:47, Ron Johnson wrote:
> On Tue, Feb 25, 2025 at 12:43 PM Sbob wrote:

>     I have 2 aws ec2 nodes and I want to setup a hot standby.
> 
>     The master is running amazon linux

>     The standby is running Alma 8

>     Once I run pg_basebackup I start the standby and get this:

>     WARNING:  database "postgres" has a collation version mismatch
>     DETAIL:  The database was created using collation version 2.34, but
>     the operating system provides version 2.28.
>     HINT:  Rebuild all objects in this database that use the default
>     collation and run ALTER DATABASE postgres REFRESH COLLATION VERSION,
>     or build PostgreSQL with the right library version.

> One is RHEL 8, and the other is RHEL 9.  Never do that using streaming 
> replication, for the very reason you're seeing:
>   WARNING:  database "postgres" has a collation version mismatch
> 
> Use logical replication, or upgrade the hot standby server to Alma 9.

He actually said Amazon Linux (based on glibc version I would guess 
AL2023) & Alma 8, but the point remains the same.

Do not run a streaming replica which does not match the primary with 
respect to glibc version. The two versions of glibc have different sort 
orders, which will potentially result in incorrect query results on your 
replica (for indexes on collatable columns, and depending on the actual 
characters in use). In fact, if possible it would be best to match the 
replica distro to the primary distro.

Additionally if you ever promote the replica to a writable primary, the 
indexes are effectively corrupt and will allow actual heap data 
corruption occur in that you could end up with duplicate values in 
otherwise unique columns/primary keys.

If you are interested in the gory details, you can watch them here: 
https://www.youtube.com/watch?v=kvsQza6TA70

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