Content-Type: text/plain; charset="iso-8859-15"
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Subject: Re: Help. The database was created using collation version 2.17, but
 the operating system provides version 2.34.
From: "Daniel Verite" <daniel@manitou-mail.org>
To: "Dmitry O Litvintsev" <litvinse@fnal.gov>
Cc: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
In-Reply-To: <SA1PR09MB73117897B54EBA26D2530CC1B9C82@SA1PR09MB7311.namprd09.prod.outlook.com>
Date: Thu, 20 Jun 2024 14:02:23 +0200
Message-Id: <7e5c7d2b-e322-4133-a083-2f76898adff2@manitou-mail.org>
Archived-At: <https://www.postgresql.org/message-id/7e5c7d2b-e322-4133-a083-2f76898adff2%40manitou-mail.org>
Precedence: bulk

	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://wiki.postgresql.org/wiki/Locale_data_changes

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

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)=20
FROM (SELECT indexrelid, indrelid, indcollation[i] coll FROM pg_index,
generate_subscripts(indcollation, 1) g(i)) s=20
  JOIN pg_collation c ON coll=3Dc.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,
--=20
Daniel V=E9rit=E9
https://postgresql.verite.pro/
Twitter: @DanielVerite