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 switch from 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 COLLATION VERSION, 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 having similar issue when going from RH6 to RH7.
>
> Thank you for your help.
>
>