public inbox for [email protected]
help / color / mirror / Atom feedFrom: Dmitry O Litvintsev <[email protected]>
To: Daniel Verite <[email protected]>
Cc: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: Help. The database was created using collation version 2.17, but the operating system provides version 2.34.
Date: Sat, 22 Jun 2024 14:01:55 +0000
Message-ID: <SA1PR09MB7311E24901D1B718583739DFB9CA2@SA1PR09MB7311.namprd09.prod.outlook.com> (raw)
In-Reply-To: <[email protected]>
References: <SA1PR09MB73117897B54EBA26D2530CC1B9C82@SA1PR09MB7311.namprd09.prod.outlook.com>
<[email protected]>
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 COLLATION VERSION, or first ALTER and then REINDEX or does the order of these action matter at all?
Thank you,
Dmitry
________________________________________
From: Daniel Verite <[email protected]>
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-5Fchange...
> 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=gRgGj...
Twitter: @DanielVerite
view thread (2+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Help. The database was created using collation version 2.17, but the operating system provides version 2.34.
In-Reply-To: <SA1PR09MB7311E24901D1B718583739DFB9CA2@SA1PR09MB7311.namprd09.prod.outlook.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox