Hi,

we've made mistake and initdb database cluster in wrong locale :-(

Now it's full of data. I've read in the docs that it's not possible to
change locale.

But I guess something like this would work:

a)
1) drop all indexes on text/varchar columns
2) change cluster locale
3) create all indexes on text/varchar columns

or even

b)
1) change cluster locale
2) reindex all indexes on text/varchar columns [I'm aware that before
reindex queries on top of these indexes would return wrong answers]

Is it possible/safe to do a) or b)? How to do step "change cluster
locale"? Where is this information stored?

Or the only way is to rebuild the database cluster from scratch?

Thanks,

Kuba


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Mon, Feb 19, 2007 at 09:27:06AM +0100, Jakub Ouhrabka wrote:
> But I guess something like this would work:
>
> a)
> 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. Also, there are shared tables which all used in every DB.
You need to log into every DB in the cluster (don't forget template[01]
and reindex everything.

So, REINDEX DATABASE; seems to be a safer bet. In general this doesn't
actually work since changing the locale may make two strings equal that
wern't before, thus possibly breaking a unique index, but it may be
possible.

I'd suggest single user mode at least, and make backups!

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFF2XO9IB7bNG8LQkwRAs5SAJ418md9bb/+4VOZ/cz81Y8+YZ2DeACbBqPd
F+5+eO502ks2wRpwe5s/4qM=
=Iy7N
-----END PGP SIGNATURE-----

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Hi Tom,

> 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.

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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Thanks for your answer. Is there any other risk than wrong answers when
running with wrong locale?

So maybe the best bet would be:

1) drop all text/varchar user indexes
2) stop database, change the locale
3) in single user mode reindex shared tables and system tables in all
databases and templates
4) start the database
5) create all text/varchar user indexes

Sounds this about right? I'd like to minimize downtime...

How to do step 2) - change the locale??

Thanks a lot,

Kuba

Martijn van Oosterhout napsal(a):
> On Mon, Feb 19, 2007 at 09:27:06AM +0100, Jakub Ouhrabka wrote:
>> But I guess something like this would work:
>>
>> a)
>> 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. Also, there are shared tables which all used in every DB.
> You need to log into every DB in the cluster (don't forget template[01]
> and reindex everything.
>
> So, REINDEX DATABASE; seems to be a safer bet. In general this doesn't
> actually work since changing the locale may make two strings equal that
> wern't before, thus possibly breaking a unique index, but it may be
> possible.
>
> I'd suggest single user mode at least, and make backups!
>
> Have a nice day,

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org