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

Thank you very much for help and pointers to useful information.

Just want to make clear (sorry I am slow on uptake). I should first REINDEX and then ALTER DATABASE xxx REFRESH
COLLATIONVERSION, or first ALTER and then REINDEX or does the order of these action matter at all? 

Thank you,
Dmitry

________________________________________
From: Daniel Verite <daniel@manitou-mail.org>
Sent: Thursday, June 20, 2024 7:02 AM
To: Dmitry O Litvintsev
Cc: pgsql-generallists.postgresql.org
Subject: Re: Help. The database was created using collation version 2.17, but the operating system provides version
2.34.

[EXTERNAL] – This message is from an external sender

        Dmitry O Litvintsev wrote:

> 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.

This upgrade comprises the major change in GNU libc 2.28,
so indeed text indexes created by 2.17 are very likely unsafe to use
on your new server.
See
https://urldefense.proofpoint.com/v2/url?u=https-3A__wiki.postgresql.org_wiki_Locale-5Fdata-5Fchanges&d=DwIFaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=NhYMQQwCXq5TXHFVjyJeB1eB9KkyHC8FU3lyswgVpUMpAzADq2cffu-xjSUeNCND&s=WKdD4hr8nBJTkQtIcLMagxuGK1yAPTyU2VOmQARksl8&e=

>  REINDEX database xxx
> ALTER DATABASE xxx REFRESH COLLATION VERSION
> But this defeats the whole idea of having short downtime because REINDEX
> will take forever.

The indexes that don't involve collatable types (text,varchar), and those
that use the C collation don't need to be reindexed.
Maybe you can reduce significantly the downtime by including only
the ones that matter.

The wiki page gives the query to obtain the list of affected indexes:

SELECT DISTINCT indrelid::regclass::text, indexrelid::regclass::text,
collname, pg_get_indexdef(indexrelid)
FROM (SELECT indexrelid, indrelid, indcollation[i] coll FROM pg_index,
generate_subscripts(indcollation, 1) g(i)) s
  JOIN pg_collation c ON coll=c.oid
WHERE collprovider IN ('d', 'c') AND collname NOT IN ('C', 'POSIX');

> I do not recall having similar issue when going from RH6 to RH7.

This warning was added relatively recently, in Postgres 15 (october 2022).


Best regards,
--
Daniel Vérité

https://urldefense.proofpoint.com/v2/url?u=https-3A__postgresql.verite.pro_&d=DwIFaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=NhYMQQwCXq5TXHFVjyJeB1eB9KkyHC8FU3lyswgVpUMpAzADq2cffu-xjSUeNCND&s=yED6Nru4eGTULRzJymNtMgJjXhgirkjOuDzCQnae9Go&e=
Twitter: @DanielVerite