1

I have a big Postgres server with 2 Tb of data that has been created with the wrong locale. How can I correct it, without recreating the whole server with pg_dump and pg_restore. It would take a longtime to do so, and would need a lot of disk space.

3
  • 1
    There is no alternative. Jul 5, 2019 at 12:57
  • 1
    yeah, LC_COLLATE and LC_CTYPE cannot be changed,
    – Jasen
    Jul 6, 2019 at 11:29
  • There is no reason this should not be doable somehow. Because locale and collation data can change on the OS for other reasons, and there are instructions for PostgreSQL to compensate for that wiki.postgresql.org/wiki/Locale_data_changes May 30, 2023 at 14:47

1 Answer 1

1

This question is reasonable and it deserves an answer. Whether or not this is "supported" or whether you "should" (not) do it, it has an answer.

I do not have a full answer, as in I don't care about collations, but I have the beginning breakthrough of what nobody has thought possible.

One of the biggest issues I had was that while I used encoding UTF8, I used the locale and collation "C" hoping that it would just do whatever without being specific to any language. But that was a mistake. I should instead have used en_US.UTF-8 or for that matter turkish anatolia UTF-8. This is where I got it wrong. C doesn't care about any UNICODE character info, and that has effects in upper/lower case folding and also in the way regular expressions work. With the locale C, greek characters aren't considered \w word letters.

Test case is

select upper('árvore ação αβγ'), 'árvore ação αβγ' ~ '^[\s\w]+$'; 

      upper      | ?column?
-----------------+----------
 áRVORE AçãO αβγ | f
(1 row)

Bad.

And I found that it is quite easy to fix this by

update pg_database set datctype = 'en_US.UTF-8' where datname = 'test';

Then after that is done I connect again with \c and I can see the effect.

      upper      | ?column?
-----------------+----------
 ÁRVORE AÇÃO ΑΒΓ | t
(1 row)

Good!

And since I don't care about the collation at all right now anyway, I can just use only this change and have things work nicely for me.

So this is step one.

But there are more steps. Now I have indexes, some of them function based (case folding to upper or lower for case insensitive lookup.) Those I would have to rebuilt.

Let's do a test table with random strings:

create table test1 as 
select id, array_to_string(array(
             select substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', trunc(random() * 62)::integer + 1, 1) 
               from generate_series(1, len)), '') as name
  from (select (random() * 60 + 10)::integer as len, * 
          from generate_series(1, 10000) as id) x;

update test1 set name = 'Árvore ação Αβγ' where id = 1234;

create index on test1(upper(name));

This was created while datctype was 'C' and doesn't work correctly:

select * from test1 where upper(name) = 'ÁRVORE AÇÃO ΑΒΓ';

nothing returned, bad. Now let's do my database "fix" above, then after connecting with \c I see that it is still not right. But now I reindex:

reindex table test1;

and now it works!

select * from test1 where upper(name) = 'ÁRVORE AÇÃO ΑΒΓ';
  id  |      name
------+-----------------
 1234 | Árvore ação Αβγ
(1 row)

Note I have not touched the collation, it could become more complicated with that, because the collation is stored in various other places in the system catalog pg_attribute for example, which might mean that we can anyway change the collation of individual columns, but again, that is really not such a big deal as long as the really deficient behavior of the locale C has been resolved.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.