On Sun, December 16, 2012 15:13, Tom Lane wrote: > "James B. Byrne" <byrnejb@xxxxxxxxxxxxx> writes: >>>> PG::Error: ERROR: encoding "UTF8" does not match locale >>>> "en_CA@xxxxxxxxxxxxxxx-8" >>>> DETAIL: The chosen LC_CTYPE setting requires encoding "LATIN1". > >> This is what I see on the host running postgresql-9.2 >> # LC_ALL=en_CA@yyyy-mmm-dd.utf8 locale charmap >> UTF-8 > >> Running locale against the base en_CA@yyyy-mmm-dd on the PG host >> shows >> this. >> LC_ALL=en_CA@yyyy-mmm-dd locale charmap >> ISO-8859-1 > > You're showing us three different spellings of the locale name above. > Are you really sure they're all equivalent? > > Beyond that, you probably need to find a locale guru. I see no reason > to think there is anything wrong with the Postgres code for this, and > every reason to think there's something wrong with your locale > definition. But I don't know enough about custom locales to help you > identify exactly what. > > regards, tom lane > Well, I did eventually track this down and fix it. It was a number of issues which all sort of came together to drive me insane. First, the RDBMS was a remote host. On CentOS/RHEL ssh is configured to export the local session's LC environment to remote hosts. Problem, en_CA@yyyy-mmm-dd did not exist on the remote host. Running initdb in a ssh shell with the locale set to a non-existent local locale probably affected the database structure in some obscure way, although the postgresql.conf file said 'en_US.UTF-8'. However, perhaps the config file is ignored by the init.d script on RHEL in this respect as well. Of course, postgresql.conf does not yet exist when one runs initdb. Hmm. I will leave that one for the existentialists among us. In any case, installing the custom locale on the remote host did not solve the problem because when localedef could not find the UTF-8 character map it used ISO-8859-1 instead and kept that piece of information to itself because, I did not specify the -v option. Of course, if you do specify the -v option and there are any warnings then the existing character map is not replaced even when it does find the utf-8 character map. That is, unless you also specify the -c option. And finally, you need to archive the base character map (.utf8) and then create aliases for the rest (.utf-8, UTF8 and UTF-8). And the way one does that is to use the self-explanatory --no-archive option together with the -c option on the aliases but use --replace option on the localedef call to create the archived map. Really, localedef is perhaps the most Byzantine and under documented single function utility that I have run across since I stopped writing assembler. Anyway, after rebuilding the remote 9.2 database whilst having the remote session locale set to en_US.UTF-8, resetting the remote and local host's locale databases and aliases for en_CA@yyyy-mmm-dd, and then trying everything once more on the development host I ran into this error: PG::Error: ERROR: new encoding (UTF8) is incompatible with the encoding of the template database (LATIN1) HINT: Use the same encoding as in the template database, or use template0 as template. : CREATE DATABASE "hll_th_forex_development" ENCODING = 'UTF-8' So, I now must specify a template in the database connection, where I did not need to before. Furthermore, the template I must use is template0 because regardless of what locale initdb is run in template1 evidently is always created with LATIN1 encoding. At lease if there is a way to force template1 to utf8 during initdb I now lack the energy to discover it. However, specifying template0 in the db connection does allow the remote db creation to succeed. -- *** E-Mail is NOT a SECURE channel *** James B. Byrne mailto:ByrneJB@xxxxxxxxxxxxx Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
