"LATIN1" does not match locale "en_US.UTF-8"

[noelmcloughlin]

Issue seen on Vagrant Ubuntu 18

[ERROR   ] Command '[u'/usr/bin/psql', u'--no-align', u'--no-readline', u'--no-psqlrc', u'--no-password', u'--dbname', u'postgres', u'-c', u'CREATE DATABASE "db1" WITH OWNER = "localUser" TEMPLATE = template0 LC_COLLATE = \'en_US.UTF-8\' LC_CTYPE = \'en_US.UTF-8\'']' failed with return code: 1
[ERROR   ] stderr: ERROR:  encoding "LATIN1" does not match locale "en_US.UTF-8"
DETAIL:  The chosen LC_CTYPE setting requires encoding "UTF8".
[ERROR   ] retcode: 1
[ERROR   ] Error connecting to Postgresql server
[ERROR   ] Failed to create database db1
[ERROR   ] Command '[u'/usr/bin/psql', u'--no-align', u'--no-readline', u'--no-psqlrc', u'--no-password', u'--dbname', u'postgres', u'-c', u'CREATE DATABASE "db2" WITH TABLESPACE = "my_space" OWNER = "remoteUser" TEMPLATE = template0 LC_COLLATE = \'en_US.UTF-8\' LC_CTYPE = \'en_US.UTF-8\'']' failed with return code: 1
[ERROR   ] stderr: ERROR:  encoding "LATIN1" does not match locale "en_US.UTF-8"
DETAIL:  The chosen LC_CTYPE setting requires encoding "UTF8".
[ERROR   ] retcode: 1
[ERROR   ] Error connecting to Postgresql server
[ERROR   ] Failed to create database db2

        ID: postgres_tablespace-my_space
    Function: postgres_tablespace.present
        Name: my_space
      Result: True
     Comment: Tablespace my_space is already present
     Started: 04:33:02.134758
    Duration: 76.869 ms
     Changes:
----------
          ID: postgres_database-db1
    Function: postgres_database.present
        Name: db1
      Result: False
     Comment: Failed to create database db1
     Started: 04:33:02.213957
    Duration: 193.199 ms
     Changes:
----------
          ID: postgres_database-db2
    Function: postgres_database.present
        Name: db2
      Result: False
     Comment: Failed to create database db2

[myii]

@noelmcloughlin Here's the relevant starting point in the last successful Travis run for centos-7-2019-2-py3:

• https://travis-ci.com/saltstack-formulas/postgres-formula/jobs/204586643#L1383

What's the configuration that you're using? Or could you compare it to the postgres.sls pillar being used by Kitchen? Specifically, compare with all definitions of en_US.UTF-8 in that file.

[noelmcloughlin]

I'll retest next week and see what cause it.

[noelmcloughlin]

Actually it's generic/ubuntu1804 vagrant image. The default locale is incorrect !!

root@ubuntu1804:/etc# 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=

[noelmcloughlin]

Fixed the locale worked

vagrant@ubuntu1804:~$ cat /etc/default/locale 
LANG="en_US.utf8"
LANGUAGE="en_US.utf8"

vagrant@ubuntu1804:~$ locale
LANG=en_US.utf8
LANGUAGE=en_US.utf8
LC_CTYPE="en_US.utf8"
LC_NUMERIC="en_US.utf8"
LC_TIME="en_US.utf8"
LC_COLLATE="en_US.utf8"
LC_MONETARY="en_US.utf8"
LC_MESSAGES="en_US.utf8"
LC_PAPER="en_US.utf8"
LC_NAME="en_US.utf8"
LC_ADDRESS="en_US.utf8"
LC_TELEPHONE="en_US.utf8"
LC_MEASUREMENT="en_US.utf8"
LC_IDENTIFICATION="en_US.utf8"
LC_ALL=

  ID: postgres_database-db1
    Function: postgres_database.present
        Name: db1
      Result: True
     Comment: The database db1 has been created
     Started: 12:51:17.150534
    Duration: 414.881 ms
     Changes:
              ----------
              db1:
                  Present

[noelmcloughlin]

I got caught by this again.

Maybe failure of postgres_database.present can be used to trigger a new check-locale state using onfail_in requisite. On Ubuntu that check would be locale | grep -i utf8 but would be managed by osfamilymap. The check will not run if postgres_database.present is successful!

Can postgres/mysql databases be created without utf8? If yes then some other generic solution would be needed - or we just live with environmental issue.

@myii @vutny I would appreciate any thoughts or guidance on this.

[noelmcloughlin]

pillar.example ..

 # databases to be created
  databases:
    db1:
      owner: 'localUser'
      template: 'template0'
      lc_ctype: 'en_US.UTF-8'
      lc_collate: 'en_US.UTF-8'
    db2:
      owner: 'remoteUser'
      template: 'template0'
      lc_ctype: 'en_US.UTF-8'
      lc_collate: 'en_US.UTF-8'
      tablespace: 'my_space'
      # set custom schema
      schemas:
        public:
          owner: 'localUser'
      # enable per-db extension
      extensions:
        uuid-ossp:
          schema: 'public'

Locales

vagrant@ubuntu1804:~$  localectl status   
   System Locale: LANG=en_US
                  LANGUAGE=en_US:
       VC Keymap: n/a
      X11 Layout: us
       X11 Model: pc105

vagrant@ubuntu1804:~$ locale -a
C
C.UTF-8
en_AG
en_AG.utf8
en_AU.utf8
en_BW.utf8
en_CA.utf8
en_DK.utf8
en_GB.utf8
en_HK.utf8
en_IE.utf8
en_IL
en_IL.utf8
en_IN
en_IN.utf8
en_NG
en_NG.utf8
en_NZ.utf8
en_PH.utf8
en_SG.utf8
en_US
en_US.iso88591
en_US.utf8
en_ZA.utf8
en_ZM
en_ZM.utf8
en_ZW.utf8
POSIX

vagrant@ubuntu1804:~$ sudo update-locale LANG=en_US.utf8

vagrant@ubuntu1804:~$ localectl status
   System Locale: LANG=en_US.utf8
                  LANGUAGE=en_US:
       VC Keymap: n/a
      X11 Layout: us
       X11 Model: pc105

hmm.

vagrant@ubuntu1804:~$ 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=

Rebooted

vagrant@ubuntu1804:~$ locale
LANG=en_US.utf8
LANGUAGE=en_US:                         <=== not correct
LC_CTYPE="en_US.utf8"
LC_NUMERIC="en_US.utf8"
LC_TIME="en_US.utf8"
LC_COLLATE="en_US.utf8"
LC_MONETARY="en_US.utf8"
LC_MESSAGES="en_US.utf8"
LC_PAPER="en_US.utf8"
LC_NAME="en_US.utf8"
LC_ADDRESS="en_US.utf8"
LC_TELEPHONE="en_US.utf8"
LC_MEASUREMENT="en_US.utf8"
LC_IDENTIFICATION="en_US.utf8"
LC_ALL=

Postgres create db state still fails.

root@ubuntu1804:/etc# cat /etc/default/locale 
LANG=en_US.utf8
LANGUAGE="en_US:"

vagrant@ubuntu1804:~$ sudo update-locale LANGUAGE=en_US.utf8
vagrant@ubuntu1804:~$ cat /etc/default/locale 
LANG=en_US.utf8
LANGUAGE=en_US.utf8
...

[ERROR   ] Command '['/usr/bin/psql', '--no-align', '--no-readline', '--no-psqlrc', '--no-password', '--dbname', 'postgres', '-c', 'CREATE DATABASE "db1" WITH OWNER = "localUser" TEMPLATE = template0 LC_COLLATE = \'en_US.UTF-8\' LC_CTYPE = \'en_US.UTF-8\'']' failed with return code: 1
[ERROR   ] stderr: ERROR:  encoding "LATIN1" does not match locale "en_US.UTF-8"
DETAIL:  The chosen LC_CTYPE setting requires encoding "UTF8".

Rebooted to see if LANGUAGE=en_US.utf8 will be applied

vagrant@ubuntu1804:~$ locale
LANG=en_US.utf8
LANGUAGE=en_US.utf8                     <=== yes
LC_CTYPE="en_US.utf8"
LC_NUMERIC="en_US.utf8"
LC_TIME="en_US.utf8"
LC_COLLATE="en_US.utf8"
LC_MONETARY="en_US.utf8"
LC_MESSAGES="en_US.utf8"
LC_PAPER="en_US.utf8"
LC_NAME="en_US.utf8"
LC_ADDRESS="en_US.utf8"
LC_TELEPHONE="en_US.utf8"
LC_MEASUREMENT="en_US.utf8"
LC_IDENTIFICATION="en_US.utf8"
LC_ALL=en_US.utf8

But state still fails.

I reinstalled the OS and changed locales/rebooted before running salt.

vagrant@ubuntu1804:~$ history
    1  sudo update-locale LANG=en_US.utf8
    2  sudo update-locale LANGUAGE=en_US.utf8
    3 reboot

After reboot the postgres state was successful.

[vutny]

Is this still an issue?

[noelmcloughlin]

The state postgres_database.present fails when OS locale is not utf8. I presume UTF-8 is the default when pillar data does not specific LC_TYPE.

However pillar data does support LC_TYPE according to pillar.example.

  # databases to be created
  databases:
    db1:
      owner: 'localUser'
      template: 'template0'
      lc_ctype: 'en_US.UTF-8'
      lc_collate: 'en_US.UTF-8'
    db2:
      owner: 'remoteUser'
      template: 'template0'
      lc_ctype: 'en_US.UTF-8'
      lc_collate: 'en_US.UTF-8'
      tablespace: 'my_space'

So a solution might be to update format_state macro to apply LC_TYPE from pillar data.

https://github.com/saltstack-formulas/postgres-formula/blob/fbd8f65561a9078b39e74e0ba40c82e93a6ee871/postgres/macros.jinja#L13

     {%- if "lc_ctype" in postgres.databases[name] }}
    - env:
         - LANGUAGE={{ postgres.databases[name]['lc_type'] }}
         - LANG={{ postgres.databases[name]['lc_type'] }}
         - LC_ALL={{ postgres.databases[name]['lc_type'] }}
     {%- endif %}

Here are a bunch of references to the general issue-

• https://stackoverflow.com/questions/13115692/encoding-utf8-does-not-match-locale-en-us-the-chosen-lc-ctype-setting-requires
• https://gist.github.com/joshteng/9895494 and https://gist.github.com/turboladen/6790847
• https://community.atlassian.com/t5/Questions/Trying-a-Postgresql-Migration-but-can-not-create-DB/qaq-p/211111