Re: Howto change db cluster locale on-the-fly

Hi Tom,
> Hacking pg_control would be the hard part; you'll never get the CRC> right if you do it manually.  Possibly
pg_resetxlogcould be adapted> to the purpose.
 

thanks for your valuable answer! I looked at pg_resetxlog.c but I'm no 
pg internals' expert - would something like this work?

1) normally shut down database
2) hack pg_resetxlog to set locale to wanted value
3) run pg_resetxlog -f (rewrite pg_control - everything would be guessed 
with the exception of overloaded locale)
4) start database

We won't miss any transactions and there won't be any inconsistency in 
data because server was normally shut down, right?

Thanks,

Kuba

Tom Lane napsal(a):
> Martijn van Oosterhout <kleptog@svana.org> writes:
>>> But I guess something like this would work:
>>> 1) drop all indexes on text/varchar columns
>>> 2) change cluster locale
>>> 3) create all indexes on text/varchar columns
> 
>> You're going to miss the "name" columns, ie. every string index in
>> pg_catalog.
> 
> But "name" is not locale-aware --- it just uses strcmp().  AFAIR there
> aren't any locale-dependent indexes in the system catalogs.  So in
> principle you could hack pg_control, restart the postmaster, and then
> reindex every locale-dependent index.  Hacking pg_control would be the
> hard part; you'll never get the CRC right if you do it manually.  Possibly
> pg_resetxlog could be adapted to the purpose.
> 
>> I'd suggest single user mode at least, and make backups!
> 
> Yup, a filesystem backup would be a *real* good idea.  Not to mention
> testing the procedure on a toy installation.
> 
>             regards, tom lane