public inbox for [email protected]
help / color / mirror / Atom feedRe: Help. The database was created using collation version 2.17, but the operating system provides version 2.34.
2+ messages / 2 participants
[nested] [flat]
* Re: Help. The database was created using collation version 2.17, but the operating system provides version 2.34.
@ 2024-06-20 12:02 Daniel Verite <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Daniel Verite @ 2024-06-20 12:02 UTC (permalink / raw)
To: Dmitry O Litvintsev <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
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
> 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://postgresql.verite.pro/
Twitter: @DanielVerite
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Help. The database was created using collation version 2.17, but the operating system provides version 2.34.
@ 2024-06-22 14:01 Dmitry O Litvintsev <[email protected]>
parent: Daniel Verite <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Dmitry O Litvintsev @ 2024-06-22 14:01 UTC (permalink / raw)
To: Daniel Verite <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-06-22 14:01 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-20 12:02 Re: Help. The database was created using collation version 2.17, but the operating system provides version 2.34. Daniel Verite <[email protected]>
2024-06-22 14:01 ` Dmitry O Litvintsev <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox