2

I'm running Debian 3.2 w/ PostgreSQL 9.2 installed. A co-worker of mine initialized the database cluster with japanese. So now, every single database created with createdb, no matter who the user is, is now instantiated with japanese as it's language.

I cannot find a way to reset the language back to english w/o having to run initdb again on a new cluster. I really don't want to do this as there is a lot of data that can't afford to go down.

From what I've read, the database cluster is set with japanese when the command with these flags are called initdb -D /place/for/data --locale=ja_JP. However when I create the same cluster and then use createdb to create a new database, the language of the error messages are in English. In my co-workers cluster, the error messages from this scenario would produce japanese error messages.

Can someone help me understand how the locales work in PostgreSQL 9.2? And what would be the way to change the cluster back into English?

Improve this question
1
  • 1
    When you create a new DB you could set lc_collate and lc_ctype (and encoding). Is that not enough in your case? Commented Jun 8, 2013 at 9:37

1 Answer 1

Reset to default
3

You can't fully change the cluster back into English without a full dump and reload.

In general the postmaster will emit messages in the language and encoding the database cluster was initdb'd in. Sessions on individual databases will emit messages in the language and encoding that database was created in, which may not be the same as the cluster defaults.

This can lead to logs in mixed languages and mixed text encodings, which is really pretty ugly. Despite repeated discussions on the mailing lists we've never come to an agreement on how to solve that - it's more complicated than it looks.

Because each session logs in its own locale settings, what you can do is CREATE DATABASE ... with the appropriate LC_CTYPE, ENCODING, LC_COLLATE, etc settings per the CREATE DATABASE manual page. You may have to specify TEMPLATE template0 for this to succeed. This will result in newly created databases being in the desired language and encoding; you can then dump each old DB into a corresponding new one, rename the old one, and rename the new one into the old one's place. The old one can be dropped when you're comfortable all's well.

Postmaster-level messages will still be in Japanese. I don't think there's a way around that without a re-initdb. Worse, if it's not jp_JP.UTF-8 but a a different encoding, you might have mixed encodings in your log files, something that can quite upset logfile processors etc.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.