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

[Ron Johnson]

On Thu, Jun 20, 2024 at 3:23 AM Dmitry O Litvintsev <litvi...@fnal.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"}';