MIME-Version: 1.0
References: <SA1PR09MB73117897B54EBA26D2530CC1B9C82@SA1PR09MB7311.namprd09.prod.outlook.com>
In-Reply-To: <SA1PR09MB73117897B54EBA26D2530CC1B9C82@SA1PR09MB7311.namprd09.prod.outlook.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Thu, 20 Jun 2024 08:25:19 -0400
Message-ID: <CANzqJaD1unoYjMpiDuyGd4MHRcxTZEKz6w04wFLsEJn18BBRhQ@mail.gmail.com>
Subject: Re: Help. The database was created using collation version 2.17, but
 the operating system provides version 2.34.
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000273a44061b5169c7"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaD1unoYjMpiDuyGd4MHRcxTZEKz6w04wFLsEJn18BBRhQ%40mail.gmail.com>
Precedence: bulk

--000000000000273a44061b5169c7
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Thu, Jun 20, 2024 at 3:23=E2=80=AFAM 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 collatio=
n
> 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 =3D nd.indexrelid and ndcl.relkind =3D 'i')
        inner join pg_class tbcl
            on (nd.indrelid =3D tbcl.oid and tbcl.relkind =3D 'r')
        inner join pg_attribute att
            on att.attrelid =3D nd.indexrelid
        inner join pg_type ty
            on att.atttypid =3D ty.oid
    where tbcl.relnamespace::regnamespace::text !=3D '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"}';

--000000000000273a44061b5169c7
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Jun 20, 2024 at 3:23=E2=80=AFAM D=
mitry O Litvintsev &lt;<a href=3D"mailto:litvinse@fnal.gov">litvinse@fnal.g=
ov</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D"=
gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(20=
4,204,204);padding-left:1ex">Hello, <br>
<br>
I am in the process of migrating DB to Alma9 host. The databse <br>
is rather large - few TBs. <br>
<br>
I have run pg_basebackup on Alma9 host and established replication from pro=
duction to it. The idea is to quickly switch from master to this new host d=
uring downtime. <br>
<br>
Establishing replication went fine. Source postgresql version is 15.6, dest=
ination is 15.7<br>
<br>
When I psql into replica I get:<br>
<br>
WARNING:=C2=A0 database &quot;xxx&quot; has a collation version mismatch<br=
>
DETAIL:=C2=A0 The database was created using collation version 2.17, but th=
e operating system provides version 2.34.<br>
HINT:=C2=A0 Rebuild all objects in this database that use the default colla=
tion and run ALTER DATABASE xxx REFRESH COLLATION VERSION, or build Postgre=
SQL with the right library version.<br>
<br>
Looking up the issue the solution seems to be <br>
<br>
=C2=A0 REINDEX database xxx <br>
=C2=A0 ALTER DATABASE xxx REFRESH COLLATION VERSION<br>
<br>
But this defeats the whole idea of having short downtime because REINDEX wi=
ll take forever. <br>
<br>
What is this &quot;or build PostgreSQL with the right library version&quot;=
?<br>
Is this about 15.7 vs 15.6 or is it about different glibc version between R=
H7 and Alma9?<br>
<br>
Is there a better way to handle it? I cannot afford long downtime.</blockqu=
ote><div><br></div><div>You &quot;only&quot; need to REINDEX indices with T=
EXT (including CHAR and VARCHAR) columns. That may be most of your indices,=
 or very few.</div><div><br></div><div>I use this view and query to find su=
ch indices:</div><div><br></div><div><font face=3D"monospace">create or rep=
lace view dba.all_indices_types as<br>=C2=A0 =C2=A0 select tbcl.relnamespac=
e::regnamespace::text||&#39;.&#39;||tbcl.relname as table_name<br>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 , ndcl.relname as index_name<br>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 , array_agg(ty.typname order by att.attn=
um) as index_types<br>=C2=A0 =C2=A0 from pg_class ndcl<br>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 inner join pg_index nd<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 on (ndcl.oid =3D nd.indexrelid and ndcl.relkind =3D &#39;i&#39;)<br>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 inner join pg_class tbcl<br>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 on (nd.indrelid =3D tbcl.oid and tbcl.relkind =3D &#39=
;r&#39;)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 inner join pg_attribute att<br>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 on att.attrelid =3D nd.indexrelid<br=
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 inner join pg_type ty<br>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 on att.atttypid =3D ty.oid<br>=C2=A0 =C2=A0 where tbcl=
.relnamespace::regnamespace::text !=3D &#39;pg_catalog&#39;<br>=C2=A0 =C2=
=A0 group by tbcl.relnamespace::regnamespace::text||&#39;.&#39;||tbcl.relna=
me<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 , ndcl.relname<br>=C2=A0 =
=C2=A0 order by 1, 2;<br><br></font></div><div><font face=3D"monospace">sel=
ect *=C2=A0</font></div><div><font face=3D"monospace">from dba.all_indices_=
types=C2=A0</font></div><div><font face=3D"monospace">where index_types &am=
p;&amp; &#39;{&quot;text&quot;,&quot;varchar&quot;,&quot;char&quot;}&#39;;<=
br></font></div><div><font face=3D"monospace"><br></font></div><br></div></=
div>

--000000000000273a44061b5169c7--