Re: Help. The database was created using collation version 2.17, but the operating system provides version 2.34.

hi

On 6/20/24 10:23, Dmitry O Litvintsev wrote:
> Hello,
>
> I am in the process of migrating DB to Alma9 host. The databse
> is rather large - few TBs.
>
> I have run pg_basebackup on Alma9 host and established replication from production to it. The idea is to quickly
switchfrom master to this new host during downtime.
 
>
> Establishing replication went fine. Source postgresql version is 15.6, destination is 15.7

You mean physical replication or logical ? In case of logical how did 
you initdb ?

Did you build postgresql from source or using a RH package ?

sorry for not being able to provide anything helpful.

>
> When I psql into replica I get:
>
> WARNING:  database "xxx" has a collation version mismatch
> DETAIL:  The database was created using collation version 2.17, but the operating system provides version 2.34.
> HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE xxx REFRESH
COLLATIONVERSION, or build PostgreSQL with the right library version.
 
>
> Looking up the issue the solution seems to be
>
>    REINDEX database xxx
>    ALTER DATABASE xxx REFRESH COLLATION VERSION
>
> But this defeats the whole idea of having short downtime because REINDEX will take forever.
>
> What is this "or build PostgreSQL with the right library version"?
> Is this about 15.7 vs 15.6 or is it about different glibc version between RH7 and Alma9?
>
> Is there a better way to handle it? I cannot afford long downtime.
> This came up rather unexpectedly and I am now in a tight situation having to find solution fast. I do not recall
havingsimilar issue when going from RH6 to RH7.
 
>
> Thank you for your help.
>
>