56

While trying to install opennms :

/usr/share/opennms/bin/install -l /usr/local/lib -dis

I get the error:

ERROR: encoding UTF8 does not match locale en_US Detail: The chosen LC_CTYPE setting requires encoding LATIN1.

and I'm not sure how to proceed, as I've tried creating the DB several different ways (see below).

Full log:

==============================================================================
OpenNMS Installer
==============================================================================

Configures PostgreSQL tables, users, and other miscellaneous settings.

- searching for jicmp:
  - trying to load /usr/local/lib/libjicmp.so: NO
  - trying to load /usr/lib/jni/libjicmp.so: OK
- searching for jicmp6:
  - trying to load /usr/local/lib/libjicmp6.so: NO
  - trying to load /usr/lib/jni/libjicmp6.so: OK
- searching for jrrd:
  - trying to load /usr/local/lib/libjrrd.so: NO
  - trying to load /usr/lib/jni/libjrrd.so: NO
  - trying to load /usr/lib/jni/libjrrd.so: NO
  - trying to load /usr/lib/jvm/jdk1.6.0_34/jre/lib/amd64/server/libjrrd.so: NO
  - trying to load /usr/lib/jvm/jdk1.6.0_34/jre/lib/amd64/libjrrd.so: NO
  - trying to load /usr/lib/jvm/jdk1.6.0_34/jre/../lib/amd64/libjrrd.so: NO
  - trying to load /libjrrd.so: NO
  - trying to load /usr/share/opennms/lib/libjrrd.so: NO
  - trying to load /usr/share/opennms/lib/linux64/libjrrd.so: NO
  - trying to load /usr/java/packages/lib/amd64/libjrrd.so: NO
  - trying to load /usr/lib64/libjrrd.so: NO
  - trying to load /lib64/libjrrd.so: NO
  - trying to load /lib/libjrrd.so: NO
  - trying to load /usr/lib/libjrrd.so: NO
  - trying to load /usr/lib/jni/libjrrd.so: NO
  - trying to load /usr/lib/libjrrd.so: NO
  - trying to load /usr/local/lib/libjrrd.so: NO
  - trying to load /opt/NMSjicmp/lib/32/libjrrd.so: NO
  - trying to load /opt/NMSjicmp/lib/64/libjrrd.so: NO
  - trying to load /opt/NMSjicmp6/lib/32/libjrrd.so: NO
  - trying to load /opt/NMSjicmp6/lib/64/libjrrd.so: NO
- Failed to load the optional jrrd library.
  - This error is not fatal, since jrrd is only required for optional features.
  - For more information, see http://www.opennms.org/index.php/jrrd
- using SQL directory... /usr/share/opennms/etc
- using create.sql... /usr/share/opennms/etc/create.sql
* using 'postgres' as the PostgreSQL user for OpenNMS
* using 'opennms' as the PostgreSQL database name for OpenNMS
Exception in thread "main" org.opennms.core.schema.MigrationException: an error occurred creating the OpenNMS database
    at org.opennms.core.schema.Migrator.createDatabase(Migrator.java:428)
    at org.opennms.core.schema.Migrator.prepareDatabase(Migrator.java:444)
    at org.opennms.install.Installer.install(Installer.java:236)
    at org.opennms.install.Installer.main(Installer.java:949)
Caused by: org.postgresql.util.PSQLException: ERROR: encoding UTF8 does not match locale en_US
  Detail: The chosen LC_CTYPE setting requires encoding LATIN1.
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:366)
    at org.opennms.core.schema.Migrator.createDatabase(Migrator.java:425)
    ... 3 more

List of databases:

   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | LATIN1   | en_US   | en_US | 
 rhq       | rhqadmin | LATIN1   | en_US   | en_US | 
 template0 | postgres | LATIN1   | en_US   | en_US | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | LATIN1   | en_US   | en_US | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
(4 rows)

I have used the following 3 initdb options but none of them work

/usr/local/pgsql/bin/initdb -E UTF-8 --pgdata=/usr/local/pgsql/data

/usr/local/pgsql/bin/initdb -E LATIN1 --pgdata=/usr/local/pgsql/data

/usr/local/pgsql/bin/initdb -E en_US.UTF8  --pgdata=/usr/local/pgsql/data

Also, do i need to delete all data in /usr/local/pgsql/data before i use initdb ?

appending locale command stdout:

$locale

LANG=en_US
LANGUAGE=en_US:
LC_CTYPE="en_US"
LC_NUMERIC="en_US"
LC_TIME="en_US"
LC_COLLATE="en_US"
LC_MONETARY="en_US"
LC_MESSAGES="en_US"
LC_PAPER="en_US"
LC_NAME="en_US"
LC_ADDRESS="en_US"
LC_TELEPHONE="en_US"
LC_MEASUREMENT="en_US"
LC_IDENTIFICATION="en_US"
LC_ALL=
kamal
  • 9,637
  • 30
  • 101
  • 168
  • Can you show output of `locale` command? It should show lots of settings all equal to `en_US.UTF-8` – mvp Oct 29 '12 at 04:07
  • If you don't remove the contents of the old datadir before running initdb, initdb will refuse to run, complaining that the directory is non-empty. So yes, you must stop the PostgreSQL server then remove the old contents of the data directory before running initdb. – Craig Ringer Oct 29 '12 at 04:12
  • Can you check the PostgreSQL server logs to see what the full text of the `CREATE DATABASE` command that failed was? BTW, it's pretty terrible if OpenNMS doesn't support a utf-8 encoded database. – Craig Ringer Oct 29 '12 at 04:14
  • You *really* need to specify your OpenNMS version and PostgreSQL version. See this bug (closed as invalid) bug report / question http://issues.opennms.org/browse/NMS-3530 . Try searching http://dir.gmane.org/gmane.network.opennms.general and http://dir.gmane.org/gmane.network.opennms.install – Craig Ringer Oct 29 '12 at 04:24
  • @CraigRinger opennms/stable uptodate 1.10.6-1, postgres = 9.2. Well now i came to know that 9.2 is not supported – kamal Oct 29 '12 at 05:24

8 Answers8

116

I combined two solutions from other sites that did the job (this answer works for Ubuntu server 12.04 and PGSQL 9.1):

  • Create a file: nano /etc/profile.d/lang.sh.

  • Add the following:

    export LANGUAGE="en_US.UTF-8"
export LANG="en_US.UTF-8"
export LC_ALL="en_US.UTF-8"

  • Save the file

  • Restart the shell or run all export commands manually in current shell instance

  • Reconfigure so the encoding can be UTF8 ([got it from here][1])

    sudo su postgres

psql

update pg_database set datistemplate=false where datname='template1';
drop database Template1;
create database template1 with owner=postgres encoding='UTF-8'

  lc_collate='en_US.utf8' lc_ctype='en_US.utf8' template template0;

update pg_database set datistemplate=true where datname='template1';

  • Use template1 for db creation.

starball
  • 20,030
  • 7
  • 43
  • 238
Avia
  • 1,829
  • 2
  • 14
  • 15
  • Worked like a charm, thanks! I am making this a private gist for safe keeping. – Mosselman Aug 12 '13 at 19:26
  • 3
    Ok, I've tried to install polish UTF-8, I had changed locale, but postgres kept to create ``LATIN1 | en_US | en_US`` coding, collate and ctype when installing from apt-get... Setting LC_ALL did the trick. I've done this by editing ``/etc/default/locale`` (adding 3 vars you are setting in lang.sh)... Thanks! – lechup Nov 24 '13 at 14:36
  • @lechup, thanks. Putting the three vars in `/etc/default/locale` did the trick on Ubuntu 12.04 where loading the environment variables did not. – Larry Gebhardt Jan 14 '14 at 22:31
  • Don't forget to restart postgres after updating locale ! – Erik255 Jul 05 '16 at 12:38
15

Thanks for locale output. OpenNMS seems to be using your en_US (non-UTF-8) locale in order to create postgres db, and this is wrong. This should work:

export LANG=en_US.UTF-8
locale   # confirm that it shows only en_US.UTF-8 for all settings
# finally, run your opennms installer
/usr/share/opennms/bin/install -l /usr/local/lib -dis
mvp
  • 111,019
  • 13
  • 122
  • 148
  • it worked, but then i got the error: Exception in thread "main" org.opennms.core.schema.MigrationException: Unsupported database version "9.200000" -- you need at least 7.400000 and less than 9.200000. Use the "-Q" option to disable this check if you feel brave and are willing to find and fix bugs found yourself. at org.opennms.core.schema.Migrator.validateDatabaseVersion(Migrator.java:219) at org.opennms.install.Installer.install(Installer.java:226) at org.opennms.install.Installer.main(Installer.java:949) which i worked around by using the -Q option – kamal Oct 29 '12 at 05:26
  • Glad to see it worked out. Maybe you should file bug for OpenNMS about this – mvp Oct 29 '12 at 05:34
8

this worked for me:

CREATE DATABASE mydb WITH ENCODING='UTF8' LC_CTYPE='en_US.UTF-8' LC_COLLATE='en_US.UTF-8' OWNER=postgres TEMPLATE=template0 CONNECTION LIMIT=-1;
psychok7
  • 5,373
  • 9
  • 63
  • 101
6

This is happening because your system is setup to use Latin1 encoding instead of UTF-8. Your language is set correctly to en_US, but the encoding is not set to UTF-8. Try running this:$ 

localedef -v -c -i en_US -f UTF-8 en_US.UTF-8

This will set tell all of your applications that are installed after the change (i think) to use the English language with unicode encoding. More information can be found here: https://www.linux.com/learn/docs/ldp/790-Unicode-HOWTO#s3

I had a similar issue so I:

  1. aptitude purge postresql ...
  2. localedef -v -c -i en_US -f UTF-8 en_US.UTF-8
  3. aptitude install postresql ....

and then postregsql knew to set itself to use the english language with unicode encoding.

Of course, you could do this on a per database basis as described here: http://www.postgresql.org/docs/9.1/static/multibyte.html

Tomasz Iniewicz
  • 4,379
  • 6
  • 42
  • 47
  • Up-voted this one because step #2 is easily incorporated into deployment automation; it appears to do the same thing as locale-gen and dpkg-reconfigure without having to export to the bash environment. – bogeymin Jun 02 '15 at 01:44
3

I have the same error:

ERROR:  new collation (en_US.utf8) is incompatible with the collation of the template database (en_US.UTF-8)
HINT:  Use the same collation as in the template database, or use template0 as template.

Solution:

dpkg-reconfigure locales (choose en_US.UTF-8)

then:

sudo -u postgres psql  

postgres=# update pg_database set datistemplate=false where datname='template1';  
UPDATE 1  
postgres=# drop database Template1;  
DROP DATABASE  
postgres=# create database template1 with owner=postgres encoding='UTF-8'  
postgres-# lc_collate='en_US.utf8' lc_ctype='en_US.utf8' template template0;  
CREATE DATABASE  
postgres=# update pg_database set datistemplate=true where datname='template1';  
UPDATE 1

Test:

postgres=# CREATE DATABASE Person  
    WITH 
    OWNER = Person  
    ENCODING = 'UTF8'  
    LC_COLLATE = 'en_US.utf8'  
    LC_CTYPE = 'en_US.utf8'  
    TABLESPACE = pg_default  
    CONNECTION LIMIT = -1;  
CREATE DATABASE

It works :)

Philipp Ludwig
  • 3,758
  • 3
  • 30
  • 48
S.Bao
  • 191
  • 1
  • 6
1

Change the locales and languages of SO for compatibilty with BD.

dpkg-reconfigure locales

enjoy!

Community
  • 1
  • 1
Igor Palácio
  • 27
  • 1
0

I meet the similar error. old database LC_CTYPE is UTF-8, but new one is en_US.UTF-8, I solve the problem after reinit database.

initdb --pgdata=/path/to/postgresql -E utf8

You should choice the right -E parameter

Ahmed Ashour
  • 5,179
  • 10
  • 35
  • 56
jadeydi
  • 99
  • 1
  • 4
0

I have had the "same issue" while installing odoo. Turns out that before installing odoo you have to configure locale 

export LC_CTYPE="en_US.UTF-8"
sudo dpkg-reconfigure locales

And then configuler locale with UTF-8 BUT you have to configure locale before posgres installation.

Taras Vaskiv
  • 2,215
  • 1
  • 18
  • 17