Hi. Today we run pg_ctl promote on a slave server (10.7) and started
using it as a master. The OS also got upgraded FreeBSD 10.4 -> FreeBSD
11.2. And you guessed it, most varchar indexes got corrupted because
system local changed in subtle ways. So I created the extension amcheck
and reindexed all bad indexes one by one. Is there any way to prevent
such things in the future? Will switching to ICU fix all such issues?
The problem with it is that ICU collations are absent in pg_collation,
initdb should be run to create them, but pg_basebackup only runs on an
empty base directory, so I couldn't run initdb + pg_basebackup to
prepare the replica server. I believe I can run the create collation
command manually, but what would it look like for en-x-icu?
CREATE COLLATION "en-ix-icu" (provider = icu, locale = 'en-x-icu');
is that it? But what about version etc?
collname | collnamespace | collowner | collprovider |
collencoding | collcollate | collctype | collversion
en-x-icu | 11 | 10 | i
| -1 | en | en | 153.88
Thanks.
