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

Поиск
Список
Период
Сортировка
От Ron Johnson
Тема Re: Help. The database was created using collation version 2.17, but the operating system provides version 2.34.
Дата
Msg-id CANzqJaD1unoYjMpiDuyGd4MHRcxTZEKz6w04wFLsEJn18BBRhQ@mail.gmail.com
обсуждение исходный текст
Ответ на Help. The database was created using collation version 2.17, but the operating system provides version 2.34.  (Dmitry O Litvintsev <litvinse@fnal.gov>)
Список pgsql-general
Дерево обсуждения
On Thu, Jun 20, 2024 at 3:23 AM Dmitry O Litvintsev <litvinse@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"}';


В списке pgsql-general по дате отправления:

Предыдущее
От: "Daniel Verite"
Дата:
Сообщение: Re: Help. The database was created using collation version 2.17, but the operating system provides version 2.34.
Следующее
От: Karsten Hilbert
Дата:
Сообщение: Re: Transaction issue