Thread: Hot Standby setup - collation version mismatch

All;

I have 2 aws ec2 nodes and I want to setup a hot standby.

The master is running amazon linux and here are the postgres packages installed:

[root@ip-10-0-3-55 ~]# rpm -qa | grep postgres
postgresql-private-libs-15.2-1.module_el9+264+92dde3f0.x86_64
postgresql-15.2-1.module_el9+264+92dde3f0.x86_64
postgresql-server-15.2-1.module_el9+264+92dde3f0.x86_64

The standby is running Alma 8 and here are the postgres packages installed:

[root@ip-10-0-3-228 ~]# rpm -qa | grep postgres
postgresql15-server-15.11-1PGDG.rhel8.x86_64
postgresql15-libs-15.11-1PGDG.rhel8.x86_64
postgresql15-15.11-1PGDG.rhel8.x86_64
postgresql15-contrib-15.11-1PGDG.rhel8.x86_64

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

[postgres@ip-10-0-3-228 ~]$ /usr/pgsql-15/bin/pg_ctl start
waiting for server to start....2025-02-24 07:07:26.800 UTC [180985] LOG:  redirecting log output to logging collector process
2025-02-24 07:07:26.800 UTC [180985] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@ip-10-0-3-228 ~]$ psql
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.

and once the db is online I check and the standby is not in recovery mode:

psql (15.11)
Type "help" for help.

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 f
(1 row)

postgres=#

Thanks in advance for any advice

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

(Please reply-all on these lists per community norms -- i.e. do not 
remove recipients from the To: or CC:)

On 2/25/25 14:46, Ron Johnson wrote:
> On Tue, Feb 25, 2025 at 2:17 PM Joe Conway <mail@joeconway.com 
> <mailto:mail@joeconway.com>> wrote:
>     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

>     He actually said Amazon Linux (based on glibc version I would guess
>     AL2023) & Alma 8, but the point remains the same.
> 
> Isn't "_el9" in the package names a reference to RHEL9 compatibility?

Sort of. The Postgres packages appear to be from CentOS Stream:


https://rpmfind.net/linux/RPM/centos-stream/9/appstream/x86_64/postgresql-private-libs-15.2-1.module_el9+264+92dde3f0.x86_64.html

I don't have RHEL 9 handy at the moment to check what its repo has for 
Postgres packages.

But the OP clearly stated they were running Amazon Linux on the primary, 
so perhaps these Postgres packages were downloaded from somewhere that 
does not match the aforementioned distro -- yet another potential issue 
for various reasons.

Or perhaps they got their own distro wrong ¯\_(ツ)_/¯


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

I am tire of begging for people to be part of my life am not going to hold anyone or force anyone no body to be part of me or keep forcing to use my brain fuck this am not doing that anymore or look for the system i don't keep it is all yours if that crown is to heavy for someone dont look for me to help find your own way am tire of ignorant people.