PostgreSQL's `initdb` fails with "invalid locale settings; check LANG and LC_* environment variables"

Question

Already found a solution for this (see answer below), but I am not sure that it is the appropriate one; plus this may help someone else too.

Tried to set up PostgreSQL by following the documentation (18.2 Creating a Database Cluster), but got the following error on Ubuntu 18.04 (kernel: 4.15.0-22-generic):

$ initdb -D /usr/local/pgsql/data
  (...)
initdb: invalid locale settings; check LANG and LC_* environment variables

Found a couple answers on Stackoverflow (1, 2) that were relevant, but these did not resolve the issue and the one on Serverfault suggested to restart the service, but PostgreSQL wasn't even running.

Tried passing the locale explicitly in every variation that I found on the system, but these failed too,

 3617  2018/06/07-08:36 initdb -D ~/Downloads/ --locale=en_US.utf8
 3618  2018/06/07-08:36 initdb -D ~/Downloads/ --locale=en_US.UTF8
 3621  2018/06/07-08:37 initdb -D ~/Downloads/ --locale=en_US.UTF-8
 3622  2018/06/07-08:37 initdb -D ~/Downloads/ --locale="en_US.UTF-8"
 3623  2018/06/07-08:37 initdb -D ~/Downloads/ --locale="en_US.utf8"
 3645  2018/06/07-09:24 initdb -D ~/Downloads/ --locale="en_US.utf8"

with

initdb: invalid locale name <the_option_value_above>

There was an Arch Linux forum discussion about this, but there were no solution.

2018/06/07 1214 UPDATE

I linked answers above, but perhaps wasn't explicit enough: I did look at locale -a and locale (not listing the former's output because I installed ALL of them in my attempts below):

$ locale
LANG=en_US.UTF-8
LANGUAGE=
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=en_US.UTF-8

What have been tried, but did not work (and terminal has been restarted for every iteration):

https://askubuntu.com/questions/454260/how-to-solve-locale-problem

Selected and configured ALL locales.
```
$ sudo locale-gen en_US.UTF-8

$ sudo dpkg-reconfigure locales.
```

https://github.com/singularityware/singularity/issues/11

neither

echo "LC_ALL=en_US.UTF-8" >> /etc/environment
echo "en_US.UTF-8 UTF-8" >> /etc/locale.gen
echo "LANG=en_US.UTF-8" > /etc/locale.conf
locale-gen en_US.UTF-8

nor

sudo apt clean
sudo apt update
sudo apt upgrade
sudo apt-get install -y locales language-pack-fi language-pack-en
export LANGUAGE=en_US.UTF-8
export LANG=en_US.UTF-8
export LC_ALL=en_US.UTF-8
sudo locale-gen en_US.UTF-8
sudo dpkg-reconfigure locales

https://askubuntu.com/questions/162391/how-do-i-fix-my-locale-issue/229512#229512
https://askubuntu.com/questions/114759/warning-setlocale-lc-all-cannot-change-locale

(Basically variations of the github link above, tried it anyway.)

TODO: https://unix.stackexchange.com/questions/294845/bash-warning-setlocale-lc-all-cannot-change-locale-en-us-utf-8

toraritte · Accepted Answer · 2018-06-07T20:35:11.870

From this thread:

initdb -D <your_data_location> --no-locale --encoding=UTF8

where

  --locale=LOCALE       set default locale for new databases
  --no-locale           equivalent to --locale=C

There are caveats (see warning below), but an all-utf8 database can be created using template0 (see 21.3. Template Databases).

From the client (psql):

postgres=# create database test LC_COLLATE "en_US.UTF-8" LC_CTYPE "en_US.UTF-8" template template0;

Or via createdb:

createdb --lc-collate="en_US.UTF-8" --lc-ctype="en_US.UTF-8" --template="template0" test2

Check:

$ psql
psql (10.3)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | C           | C           | 
 template0 | postgres | UTF8     | C           | C           | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | C           | C           | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 test2     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

WARNING: This is probably not the correct solution and the workaround above is just that, a workaround.

Note the "Collate" and "Ctype" fields below in a database created with the above solution and this may cause issues, because "The results of comparisons between strings depends on LC_CTYPE. In practice, the most visible effect is the sort order." (see DBA StackExchange thread). This is also confirmed on the PostgreSQL mailing list (see this thread about this issue on a database in production). Probably the easiest way to solve this would be re-initializing/recreating the database.

postgres=# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | C       | C     | 
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
(3 rows)

RichardW · Answer 2 · 2021-07-18T05:14:51.690

5

Although the question does not mention Nix, the original poster linked to this issue from the Nix discourse site so I believe this is a Nix-related issue.

I ran into this issue when running under Nix shell and found the solution here after much searching. I just had to add glibcLocales to my environment. I.e. either run nix-shell -p glibcLocales or add glibcLocales to buildInputs.

edited Jul 18 '21 at 05:14

answered Jul 18 '21 at 01:20

RichardW

899
10
15

Thank you! Yes, I originally had this issue on NixOS but then on an older Ubuntu as well so it warranted the creation of a general question. I can't try this out right now but really appreciate your efforts - this can be an arduous issue to find a solution for.. – toraritte Jul 18 '21 at 11:32

Laurenz Albe · Answer 3 · 2018-11-03T04:35:45.863

2

You can get a listing of the locales available in Linux with

locale -a

Use one of these.

You have to choose a locale that matches your encoding, for example

initdb -E UTF8 --locale=en_US.utf8

or

initdb -E LATIN9 --locale=et_EE.iso885915

As far as I know, you can install additional locales with

sudo apt-get install language-pack-XX

edited Nov 03 '18 at 04:35

answered Jun 07 '18 at 19:06

Laurenz Albe

209,280
17
206
263

This answer set me on a death spiral. Isn't the -U flag used to define the initial superuser? https://www.postgresql.org/docs/current/static/app-initdb.html – Jakov Nov 03 '18 at 00:56
This should probably be `initdb -E UTF8 ...`, not `-U`. – Erwin Brandstetter Nov 03 '18 at 01:18

score 2 · Answer 4 · answered Jan 24 '21 at 18:43

Check if the locale is enabled in /etc/locale.gen. On my fresh install of Arch Linux ARM the following line was commented out:
```
en_US.UTF-8 UTF-8
```
Run locale-gen without any arguments. It will list all the uncommented locales as it generates them.
Optional: edit /etc/locale.conf to set the system locale:
```
echo "LANG=en_US.UTF-8" > /etc/locale.conf
```
Restart the system to make all services pick up the new setting.
Run your initdb command.

score 0 · Answer 5 · answered Nov 25 '22 at 13:19

for me it was an issue when I tried to upgrade postgres from 9.6 to 15. locale should be replaced with local-provider and icu locale, e.g.:

POSTGRES_INITDB_ARGS="--locale=nl_NL --encoding=UTF8"

->

POSTGRES_INITDB_ARGS="--locale-provider=icu --icu-locale=nl_NL --encoding=UTF8

PostgreSQL's `initdb` fails with "invalid locale settings; check LANG and LC_* environment variables"

5 Answers5