Windows invalid locale name en us utf 8

I use pg_restore on Windows 10 with a dump file made on Linux. I search on the web but I don't find answer. [NEW] : I install Ubuntu on my computer to use pg_restore but when I send pg_restor...

Your new ubuntu installation hasn’t defined the en_US.UTF-8 locale yet. So, when you’re trying to restore the dumpfile, the dumpfile attempts to do something like:

CREATE DATABASE <database> WITH TEMPLATE = ... LC_COLLATE = 'en_US.UTF-8'...

But, ‘en_US.UTF-8’ is not defined by your new ubuntu server. First, you can verify this:

# list all "known" locales. In my case, on new Ubuntu 20, I get:
$ locale -a
C
C.UTF-8
POSIX

Edit existing /etc/locale.gen file, which contains the list of possible locales. Most locales will be commented out. These will not be defined, so, un-comment the line with ‘en_US.UTF-8’.

Run (as root) locale-gen.

root# locale-gen
Generating locales (this might take a while)...
  en_US.UTF-8... done
Generation complete.

Notice it’s now a configured locale:

$ locale -a
C
C.UTF-8
POSIX
en_US.utf8

(Yes, it is lower case utf8, not a problem)

Restart your postgres server (so it sees the new locale — you do not need to restart the ubuntu server itself), and you restore show now work.

Источник

To confirm it is not a file permission issue you could double-check the permissions of the yugabyte data dir folder, and make sure the yb-tserver (and yb-master) processes are started with the appropriate user.

I can confirm the processes are started with the appropriate user (yugabyte) and the yugabyte user has read/write permissions to the directories specified by --fs_data_dirs=/mnt/store/yb/.... Just to clarify, this is what you mean by the yugabyte data dir folder, right?

For the initdb locale issue, the simplest way to debug is to manually run the command that the yb-tserver process runs internally and confirm if you get the same error or not.

so with en_US.UTF-8, there’s still a locale error:

LC_ALL=en_US.UTF-8 YB_PG_LOCAL_NODE_INITDB=1 /mnt/bin/yugabyte/postgres/bin/initdb -D /mnt/store/yb/pg_data -U postgres
invalid locale name: "en_US.UTF-8"

same with en_US.utf8:

LC_ALL=en_US.utf8 YB_PG_LOCAL_NODE_INITDB=1 /mnt/bin/yugabyte/postgres/bin/initdb -D /mnt/store/yb/pg_data -U postgres
invalid locale name: "en_US.utf8"

but with C, we’re okay, and the postgres process does init (and if you perform initdb before the yugabyte process starts, you can get the master/tserver processes running. This is because yugabyte sees that the pg_data/ dir exists, so it starts up):

LC_ALL=C YB_PG_LOCAL_NODE_INITDB=1 /mnt/bin/yugabyte/postgres/bin/initdb -D /mnt/store/yb/pg_data -U postgres
...

going further with LC_ALL=C

Out of curiosity I kept going further to see what would happen. If you do what’s listed above and manually override --locale=C (LC_ALL='C') the yugabyte processes will start; but when you try to connect via ysqlsh (or use a sample app, etc.), you’ll see the following:

/mnt/bin/yugabyte/bin/ysqlsh -p 5433 -h 10.156.89.41
ysqlsh: FATAL:  database locale is incompatible with operating system
DETAIL:  The database was initialized with LC_CTYPE "en_US.UTF-8",  which is not recognized by setlocale().
HINT:  Recreate the database with another locale or install the missing locale.

which I’m not even sure how that’d happen since if you check mnt/store/yugabyte/pg_data/postgres.conf there’s no mention of en_US.UTF-8:

...
#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------
...
# These settings are initialized by initdb, but they can be changed.
lc_messages = 'C'                       # locale for system error message
                                        # strings
lc_monetary = 'C'                       # locale for monetary formatting
lc_numeric = 'C'                        # locale for number formatting
lc_time = 'C'                           # locale for time formatting
...
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

# Add settings for extensions here
... # (empty)
...

or in ysql_pg.conf (which appears the same as postgres.conf):

...
#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------
...
# These settings are initialized by initdb, but they can be changed.
lc_messages = 'C'                       # locale for system error message
                                        # strings
lc_monetary = 'C'                       # locale for monetary formatting
lc_numeric = 'C'                        # locale for number formatting
lc_time = 'C'                           # locale for time formatting
...
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

# Add settings for extensions here
... # (empty)
...

or in the postmaster.opts:

cat postmaster.opts
/mnt/bin/yugabyte/postgres/bin/postgres "-D" "/mnt/store/yb/pg_data" "-p" "5433" "-h" "10.156.89.41" "-k" "" "-c" "logging_collector=on" "-c" "log_directory=/mnt/log/yb/yb-tserver" "-c" "shared_preload_libraries=pg_stat_statements,yb_pg_metrics" "-c" "yb_pg_metrics.node_name=DEFAULT_NODE_NAME" "-c" "yb_pg_metrics.port=13000" "-c" "config_file=/mnt/store/yb/pg_data/ysql_pg.conf" "-c" "hba_file=/mnt/store/yb/pg_data/ysql_hba.conf"

My guess(?) would be because it’s being initialized with the ../share/initial_sys_catalog_snapshot/.. directory, which perhaps is initialized with en_US.UTF-8. But regardless, that doesn’t matter too terribly much, since it seems the root of the problem is that the initdb process.

starting pure pg_ctl

Using the LC_ALL=C process above, if instead of running the yugabyte, we just start the «plain» postgres process, we can connect:

/mnt/bin/yugabyte/postgres/bin/pg_ctl -D /mnt/store/yb/pg_data start
/mnt/bin/yugabyte/postgres/bin/psql -h localhost -p 5432 -U postgres
postgres=#

and if we list all the tables, we see the following:

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)

and finally, here are the results of querying the collation table to see what options are available:

postgres-# select * FROM pg_collation;

 collname  | collnamespace | collowner | collprovider | collencoding | collcollate | collctype | collversion
-----------+---------------+-----------+--------------+--------------+-------------+-----------+-------------
 default   |            11 |        10 | d            |           -1 |             |           |
 C         |            11 |        10 | c            |           -1 | C           | C         |
 POSIX     |            11 |        10 | c            |           -1 | POSIX       | POSIX     |
 ucs_basic |            11 |        10 | c            |            6 | C           | C         |
(4 rows)

Alright so here’s why I bring this up— I also tested loading postgres-11.6 onto this same Ubuntu 16.04 instance, with the exact same system config (as far as I’m aware of), and ran the following:

/mnt/bin/postgres/bin/initdb -E utf8 --locale en_US.UTF-8 -D /mnt/store/postgres/pg_data

and it initialized. Then if we check the OSS postgres, we see the defaults are initialized with en_US.UTF-8:

postgres-# l
                              List of databases
   Name    | Owner     | Encoding |   Collate   |    Ctype    | Access privileges
-----------+-----------+----------+-------------+-------------+-------------------
 postgres  | yugabyte  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | yugabyte  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/yugabyte          +
           |           |          |             |             | yugabyte=CTc/yugabyte
 template1 | yugabyte  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/yugabyte          +
           |           |          |             |             | yugabyte=CTc/yugabyte
(6 rows)

and when we check pg_collation, we see en_US.utf8 as an option…

postgres-# select * FROM pg_collation;
  collname  | collnamespace | collowner | collprovider | collencoding | collcollate | collctype  | collversion
------------+---------------+-----------+--------------+--------------+-------------+------------+-------------
 default    |            11 |        10 | d            |           -1 |             |            |
 C          |            11 |        10 | c            |           -1 | C           | C          |
 POSIX      |            11 |        10 | c            |           -1 | POSIX       | POSIX      |
 ucs_basic  |            11 |        10 | c            |            6 | C           | C          |
 C.UTF-8    |            11 |        10 | c            |            6 | C.UTF-8     | C.UTF-8    |
 en_US.utf8 |            11 |        10 | c            |            6 | en_US.utf8  | en_US.utf8 |
 en_US      |            11 |        10 | c            |            6 | en_US.utf8  | en_US.utf8 |
(7 rows)

… which is the same locale option when displaying locale -a:

C
C.UTF-8
en_US.utf8
POSIX

en_US.utf8 v. en_US.UTF-8

So on the Ubuntu 16.04 instance that’s being used for all this, it looks like en_US.utf8 and en_US.UTF-8 are treated effectively the same. I’ve tried doing all the steps like running localedef -i en_US -f UTF-8 en_US.UTF-8 and locale-gen en_US.UTF-8 and all that, but it still ultimately produces the same thing by default which is en_US.UTF-8 and en_US.utf8 kind of being treated the same. Here’s the results:

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=
locale -a
C
C.UTF-8
en_US.utf8
POSIX
localectl status
   System Locale: LANG=en_US.UTF-8
       VC Keymap: n/a
      X11 Layout: us
       X11 Model: pc105
cat /etc/default/locale
LANG=en_US.UTF-8

This makes me wonder, is it possible there’s something different with how initdb does things in upstream postgres vs. yugabyte?

Thanks again for all your time I appreciate it. Let me know if there’s any other logs that I can provide, etc.

Источник

I have a PostreSQL 9.x database on an Ubuntu 14.04 LTS production machine.
My development machine is Windows 7-based, providing PostreSQL 9.y.

I want to restore the Ubuntu PostreSQL database on my development machine.

I noticed that the Ubuntu database uses the following locale settings:

  • character set encoding=UTF8
  • collation order, string sort order=en_US.UTF-8
  • character type, character classification=en_US.UTF-8

When I restore the database on the Windows machine without specifying locales, it will be set up with

  • character set encoding=UTF8
  • collation order, string sort order=German_Germany.1252
  • character type, character classification=German_Germany.1252

My plan is to have the database on my development machine as similar as possible to the database on my Ubuntu machine. So my idea was to first create a database on my Windows machine with production-matching locales, then restore my Ubuntu database prod-db.backup backup file into that created database:

createdb --host=localhost --username=postgres --encoding=Unicode --lc-collate=en_US.UTF-8 --lc-ctype=en_US.UTF-8 --owner=prod prod-db
pg_restore --host=localhost --username=postgres --format=custom prod-db.backup --dbname=prod-db

This ideas does not work as the createdb on Windows will complain with the error invalid locale name en_US.UTF-8.

I went on a hunt to find the Windows locale names that match the Ubuntu locale names, including a solution to use the template0 template database, experimenting with different locale identifiers such as en_US.UTF-8 and en_us_utf8 I found scattered in the Internet … but no solution works.

  • Is there a locale identifier for Windows that matches Ubuntu’s en_US.UTF-8?
  • Or is locale German_Germany.1252 identical (enough) to en_US.UTF-8 so that I can stick to it and not worry about locales — I want to make sure that database queries behave identical when it comes to aspects such as query result set ordering.
Источник
  • Index
  • » Networking, Server, and Protection
  • » PostgreSQL initdb: invalid locale name «en_US.UTF-8»

#1 2015-10-10 17:05:43

Aegidius
Member
From: Italy
Registered: 2011-06-29
Posts: 288
Website

PostgreSQL initdb: invalid locale name «en_US.UTF-8»

Hello all,

I am following the wiki in order to install and run PostgreSQL but I am getting this error.

[postgres@archlinux ~]$ initdb --locale en_US.UTF-8 -E UTF8 -D '/var/lib/postgres/data'
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

initdb: invalid locale name "en_US.UTF-8"

Do you know why the locale name is not valid?

#2 2015-11-10 02:59:11

mencargo
Member
Registered: 2009-05-05
Posts: 58

Re: PostgreSQL initdb: invalid locale name «en_US.UTF-8»

I had the same problem.

Check that the correct LANG is set in /etc/locale.conf

cat /etc/locale.conf
LANG=en_US.UTF-8

Then do sudo locale-gen and try that again

#3 2015-11-10 04:24:50

Scimmia
Fellow
Registered: 2012-09-01
Posts: 10,030

Re: PostgreSQL initdb: invalid locale name «en_US.UTF-8»

Sounds like you don’t have that locale generated. Check /etc/locale.gen, then run locale-gen as mencargo suggested.

#4 2017-04-12 12:51:04

jramirezg
Member
Registered: 2017-02-28
Posts: 2

Re: PostgreSQL initdb: invalid locale name «en_US.UTF-8»

Hell have the same problem I have KDE and Archlinux installed and postgresql gives me the same error, this forum trend is about 2 years old and I can’t seem to find a solution for  this problem.
thanks for your help

#5 2017-04-12 12:52:33

jramirezg
Member
Registered: 2017-02-28
Posts: 2

Re: PostgreSQL initdb: invalid locale name «en_US.UTF-8»

[postgres@lockdurn data]$ initdb —locale $LANG -E UTF8 -D ‘/var/lib/postgres/data’
The files belonging to this database system will be owned by user «postgres».
This user must also own the server process.

initdb: invalid locale name «en_US.UTF-8»

#6 2017-04-12 15:08:40

WorMzy
Forum Moderator
From: Scotland
Registered: 2010-06-16
Posts: 11,016
Website

Re: PostgreSQL initdb: invalid locale name «en_US.UTF-8»

So do as mencargo & Scimmia suggested. If you still have trouble, open a new thread. Be sure to share with us the content of /etc/locale.conf and output of locale-gen, using code tags.

https://wiki.archlinux.org/index.php/Co … bumping.22

Closing.

Sakura:-
Mobo: MSI MAG X570S TORPEDO MAX // Processor: AMD Ryzen 9 5950X @4.9GHz // GFX: AMD Radeon RX 5700 XT // RAM: 32GB (4x 8GB) Corsair DDR4 (@ 3000MHz) // Storage: 1x 3TB HDD, 6x 1TB SSD, 2x 120GB SSD, 1x 275GB M2 SSD

Making lemonade from lemons since 2015.

Источник

Installation failed with ERROR: invalid locale name: «en_US.utf8»

Description

Trying to install Foreman on a fresh Debian Jessie failed as follow. From this point, I don’t really know how to debug this kind of installation. Help is appreciated.

Take note, the locale on this server is «LANG=en_CA.UTF-8».

$ sudo foreman-installer
Installing             -otice: /AddIcon /icons/odf6odm-20x22.png .odm            [4%] [.....                                                                                                                        Error executing SQL; psql returned pid 31265 exit 1: 'ERROR:  invalid locale name: "en_US.utf8" 
'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/util/errors.rb:106:in `fail'
/usr/share/foreman-installer/modules/postgresql/lib/puppet/type/postgresql_psql.rb:25:in `sync'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/transaction/resource_harness.rb:236:in `sync'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/transaction/resource_harness.rb:134:in `sync_if_needed'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/transaction/resource_harness.rb:88:in `block in perform_changes'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/transaction/resource_harness.rb:87:in `each'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/transaction/resource_harness.rb:87:in `perform_changes'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/transaction/resource_harness.rb:21:in `evaluate'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/transaction.rb:224:in `apply'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/transaction.rb:240:in `eval_resource'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/transaction.rb:163:in `call'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/transaction.rb:163:in `block (2 levels) in evaluate'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/util.rb:386:in `block in thinmark'
/opt/puppetlabs/puppet/lib/ruby/2.1.0/benchmark.rb:294:in `realtime'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/util.rb:385:in `thinmark'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/transaction.rb:163:in `block in evaluate'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/graph/relationship_graph.rb:118:in `traverse'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/transaction.rb:154:in `evaluate'
/usr/lib/ruby/vendor_ruby/kafo/modules/kafo_configure/lib/puppet/parser/functions/add_progress.rb:31:in `evaluate_with_trigger'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/resource/catalog.rb:222:in `block in apply'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/util/log.rb:155:in `with_destination'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/transaction/report.rb:137:in `as_logging_destination'
/usr/lib/ruby/vendor_ruby/kafo/modules/kafo_configure/lib/kafo/puppet/report_wrapper.rb:34:in `method_missing'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/resource/catalog.rb:221:in `apply'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/configurer.rb:171:in `block in apply_catalog'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/util.rb:223:in `block in benchmark'
/opt/puppetlabs/puppet/lib/ruby/2.1.0/benchmark.rb:294:in `realtime'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/util.rb:222:in `benchmark'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/configurer.rb:170:in `apply_catalog'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/configurer.rb:343:in `run_internal'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/configurer.rb:221:in `block in run'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/context.rb:65:in `override'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet.rb:241:in `override'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/configurer.rb:195:in `run'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/application/apply.rb:350:in `apply_catalog'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/application/apply.rb:274:in `block in main'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/context.rb:65:in `override'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet.rb:241:in `override'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/application/apply.rb:225:in `main'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/application/apply.rb:170:in `run_command'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/application.rb:344:in `block in run'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/util.rb:540:in `exit_on_fail'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/application.rb:344:in `run'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/util/command_line.rb:132:in `run'
/opt/puppetlabs/puppet/lib/ruby/vendor_ruby/puppet/util/command_line.rb:72:in `execute'
/opt/puppetlabs/puppet/bin/puppet:5:in `<main>'
 /Stage[main]/Foreman::Database::Postgresql/Postgresql::Server::Db[foreman]/Postgresql::Server::Database[foreman]/Postgresql_psql[Create db 'foreman']/command: change from notrun to CREATE DATABASE "foreman" WITH OWNER="foreman" TEMPLATE="template0" ENCODING='utf8' LC_COLLATE='en_US.utf8' LC_CTYPE='en_US.utf8'  failed: Error executing SQL; psql returned pid 31265 exit 1: 'ERROR:  invalid locale name: "en_US.utf8" 
'
Installing             Done                                               [100%] [...............................................................................................................................]
  Something went wrong! Check the log for ERROR-level output
  * Foreman is running at https://testfm.patrikdufresne.com
      Initial credentials are admin / zwCbiCH6c4R3V7M4
  * Foreman Proxy is running at https://testfm.patrikdufresne.com:8443
  * Puppetmaster is running at port 8140
  The full log is at /var/log/foreman-installer/foreman.log

History

#1

Updated by Patrik Dufresne over 6 years ago

With help of PiereW on IRC, I fix this issue as follow:

1. Run `sudo dpkg-reconfigure locales`
2. Check `en_US.UAT-8` in the list
3. Reboot the server (or reboot postgres)
4. Check if the local is properly installed using `locale -a`.
5. Re-run `foreman-installer`

#2

Updated by Ohad Levy over 6 years ago

  • Tracker changed from Bug to Support
  • Status changed from New to Resolved

thanks for the update.. closing.

Also available in: Atom
PDF

Источник

Для начала, чтобы получить доступ из Phpstorm к удаленному серверу через SSH нужно, чтобы у пользователя postgres в самой БД был пароль. Сделать это можно внутри psql консоли командой:

alter user postgres password 'enter_pass_here';

Теперь к ошибкам по типу:

encoding "UTF8" does not match locale "en_US"

new collation (ru_RU.UTF-8) is incompatible with the collation of the template database (ru_RU.utf8)

invalid locale name: "ru_RU.UTF-8"

Решение вот такое:

https://www.sinyawskiy.ru/invalid_locale.html

Если вкратце, то вот так (осторожно, это уничтожит структуру БД целиком):

locale-gen ru_RU.UTF-8
dpkg-reconfigure locales
pg_lsclusters
Из предыдущей команды берём версию и название кластера, первые два столбца
pg_dropcluster --stop 9.5 main
pg_createcluster --locale ru_RU.utf8 --start 9.5 main
И после создать всю структуру БД

Published by Максим Анархистов

http://ru-army.tk
View all posts by Максим Анархистов

Published
December 11, 2019December 11, 2019

Источник

Понравилась статья? Поделить с друзьями:
  • Windows internet explorer что это за программа
  • Windows internals 7th edition part 2 pdf
  • Windows internal low level app manager что это
  • Windows internal composableshell experiences textinput inputapp что это
  • Windows integrated authentication to allow all authenticated users