Re: Hot Standby setup - collation version mismatch

On Tue, Feb 25, 2025 at 12:43 PM Sbob <sbob(at)quadratum-braccas(dot)com> wrote:

> 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(at)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(at)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(at)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(at)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
>

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.

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!