From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Determining Indexes to Rebuild on new libc
Date: Thu, 4 Aug 2022 09:55:50 -0500
Message-ID: <5816758c-a8e5-1076-5c9f-7e59539f0696@gmail.com> (raw)
In-Reply-To: <CAHJZqBBWUAzKKkTaOf_QDYtmuMavntg4ba-_ScNJRKkuxtRsyw@mail.gmail.com>
References: <CAHJZqBBWUAzKKkTaOf_QDYtmuMavntg4ba-_ScNJRKkuxtRsyw@mail.gmail.com>
On 8/4/22 09:29, Don Seiler wrote:
> Good morning,
>
> As we're staring down the eventuality of having to migrate to a newer OS
> (currently on Ubuntu 18.04 LTS), we're preparing for the collation change
> madness that will ensue. We're looking at logical replication but there is
> a lot to unpack there as well given the number of databases and the
> massive size of a few of them. I had been inclined to bite the bullet and
> do logical replication (or dump/restore on smaller DBs) but the timeframe
> for the project is being pushed up so I'm looking for shortcuts where
> possible (obviously without risking DB integrity). This would also give me
> the opportunity for other modifications like enabling data checksums on
> the new DBs that I had been sorely wanting for years now.
>
> One question that gets asked is if we could do physical replication, cut
> over, and then only rebuild indexes that "need it" in order to minimize
> the subsequent downtime. i.e. can we determine which indexes will actually
> have a potential problem. For example, a lot of indexes are on
> text/varchar datatype fields that hold UUID data and nothing more (basic
> alphanumeric characters and hyphens embedded). If we can be certain that
> these fields truly only hold this type of data, could we skip rebuilding
> them after the cutover to a newer OS (eg Ubuntu 22.04 LTS with the newer
> libc collation)?
Join pg_indexes to information_schema to get only the indices with character
fields.
If *ALL* your UUID indices helpfully have "uuid" embedded in their names,
then you can exclude them, too.
Otherwise, you must scan each indexed text field looking for anything
besides low-ASCII characters, and hope that no such data has been added to
since you scanned.
For example: SELECT field_1 from foo where field_1 >= CHR(128);
CHECK constraints would of course also ensure that it can never happen...
--
Angular momentum makes the world go 'round.
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: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Determining Indexes to Rebuild on new libc
In-Reply-To: <5816758c-a8e5-1076-5c9f-7e59539f0696@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox