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

On Thu, Jun 20, 2024 at 3:23 AM Dmitry O Litvintsev <litvinse(at)fnal(dot)gov>
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
>
> 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.

You "only" need to REINDEX indices with TEXT (including CHAR and VARCHAR)
columns. That may be most of your indices, or very few.

I use this view and query to find such indices:

create or replace view dba.all_indices_types as
select tbcl.relnamespace::regnamespace::text||'.'||tbcl.relname as
table_name
, ndcl.relname as index_name
, array_agg(ty.typname order by att.attnum) as index_types
from pg_class ndcl
inner join pg_index nd
on (ndcl.oid = nd.indexrelid and ndcl.relkind = 'i')
inner join pg_class tbcl
on (nd.indrelid = tbcl.oid and tbcl.relkind = 'r')
inner join pg_attribute att
on att.attrelid = nd.indexrelid
inner join pg_type ty
on att.atttypid = ty.oid
where tbcl.relnamespace::regnamespace::text != 'pg_catalog'
group by tbcl.relnamespace::regnamespace::text||'.'||tbcl.relname
, ndcl.relname
order by 1, 2;

select *
from dba.all_indices_types
where index_types && '{"text","varchar","char"}';