On Fri, Jan 17, 2025 at 1:12 AM Kamen Kalchev <kalchev035@xxxxxxxxx> wrote:
Hi everyone, we're planning to upgrade the OS running Postgres from ubuntu jammy to ubuntu noble. As part of the OS change, the glibc version will be changed from glibc 2.35 to glibc 2.39..
Can someone confirm if changing the glibc between those versions will require a full reindex of the Postgres cluster?
You never have to reindex _everything_. Only (for some definition of "only") indices with text/char/varchar/name columns need to be rebuilt.
This is how I 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 = nd.indexrelid and ndcl.relkind in ('i', 'I')
inner join pg_class tbcl
on (nd.indrelid = tbcl.oid and tbcl.relkind in ('r', 'R', 'm'))
inner join pg_attribute att
on att.attrelid = nd.indexrelid
inner join pg_type ty
on att.atttypid = ty.oid
where tbcl.relnamespace::regnamespace::text != 'pg_catalog'
group by tbcl.relnamespace::regnamespace::text||'.'||tbcl.relname
, ndcl.relname
order by 1, 2;
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 = nd.indexrelid and ndcl.relkind in ('i', 'I')
inner join pg_class tbcl
on (nd.indrelid = tbcl.oid and tbcl.relkind in ('r', 'R', 'm'))
inner join pg_attribute att
on att.attrelid = nd.indexrelid
inner join pg_type ty
on att.atttypid = ty.oid
where tbcl.relnamespace::regnamespace::text != '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","text"}';
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
