PostgreSQL 9.1 Installation and Database Encoding

Question

I just made my first install of PostgreSQL 9.1 on Ubuntu 10.04.

Note. I did this several times on Windows with the installer without any problems.

After a little effort, I set it up to connect remotely via pgAdminIII. However, I was very surprised after connecting to db that I received a coding warning. The postgres database itself was created with the encoding "SQL_ASCII". Each time I installed on windows, it created a postgres database with "UTF8" - it would seem to be much better and would stop the warning message when opening the database through pgAdminIII.

Is there something I did wrong? Is there a / param setting to set the default encoding?

And still, to fix it? I read some things on the Internet that say you need to be stupid and recover to change the encoding of the database, but I'm not sure if this is possible even in postgres db. It?

Thanks for your help!

Answer 1

The answer provided by Erwin Brandstreter was helpful, but for some reason this did not work for me. The reason is that I could not start initdb. I tried to get the error "bash: command not found" when trying to run it from a locale. What I ended up with was:

OS language standard change. For me it was:

$ update-locale LANG=en_US.UTF-8 

Note. Then I had to restart the server. To confirm that this worked, simply run:

 $ locale 

with this set, I stopped and dropped the cluster:

 $ su postgres $ pg_dropcluster --stop 9.1 main 

note: main - the default cluster created for me (your cluster name may differ)

 $ pg_createcluster --start 9.1 main 

Again, I just recreated the cluster with the same name (primary).

Note: since I selected the same cluster name (primary), I had to go back and update my .conf files. For me it was postgres.conf and pg_hba.conf to enable remote access to this box again. I am not going to include how to do this, since there is a lot of documentation on the Internet for this. But, if someone wants to edit this answer later to include it, that will be fine! :)

Answer 2

The corresponding option is --locale=locale for the initdb command, which initializes your database cluster. If you do not specify it explicitly, it defaults to the system language. (You are probably using Ubuntu in the "C" locale.)

Read more in the excellent guide here .

In PostgreSQL, you can still sneak into a database with different languages, based on the new template0 database instead of the standard templeate1 . I quote here here :

The encoding and locale settings must match the database template settings, unless template0 is used as a template.

But I would prefer to recreate the database cluster with the desired locale. Much cleaner.

---

Edit: information about available locales

You can only use locales that are provided by the operating system. I quote here here :

What locales are available on your system, under what name depends on what was provided by the operating system provider and what was installed. On most Unix systems, the -a locale will provide a list of available locations. Windows uses more detailed locale names such as German_Germany or Swedish_Sweden.1252, but the principles are the same.

Take a look at locale-gen on a Unix system if you want to use a locale that has not yet been generated. It is important to understand that several locales can be installed in your OS, but only one of them can be selected for system parameters such as LC_CTYPE , LC_COLLATE , etc. Look at the result of locale compared to locale -a in the shell. Usually it is the same for everyone, set via LC_ALL .

@David: What you did might have solved your problem, but it could have been easier for you. Also, remember that the LANG environment variable provides only the default for all locale settings. If either of them is set to something else, LANG will be overridden. Set LC_ALL to override any existing settings. Here is one of the many sites on the net telling you this.

To check all the current locale settings of your database (cluster), run in your database:

 SHOW ALL; 

Or more specifically:

 SELECT * FROM pg_settings WHERE name ~~ 'lc%'; 

Answer 3

Creating a database via initdb with the encoding option (-E or --encoding =) did the trick for me on Mac Os X Snow Leopard:

 initdb -D <database_directory> -E UTF8 

If you create the database this way, it will also create the database templates with the correct encoding.

Refer to the initdb documentation here and the coding documentation here to choose the correct encoding.