On Thu, Feb 9, 2023 at 3:55 PM Paul McGarry <paul@paulmcgarry.com> wrote:
> Will the amcheck reliably identify all issues that may arise from a collation change?
Theoretically it might not. In practice I'd be very surprised if it
ever failed to detect such an inconsistency. If you want to be extra
careful, and can afford to block concurrent writes, then I suggest
using bt_index_parent_check instead of bt_index_check.
> and therefore I can just use that to identify bad indexes and recreate them, or should I recreate all btree indexes
involvingtext fields?
It might be easier to just reindex them all. Hard to say.
> We planned to do a dump/restore upgrade to PG14 in a month or so (already done in dev).
> I am wondering whether it will be less work overall to bring that forward than rebuild these indexes...
pg_amcheck is available on 14. It offers a much simpler interface for
running amcheck routine, so maybe look into that once you upgrade.
--
Peter Geoghegan