What is going wrong with postgresql initdb? Why is the `UTF-8` encoding not getting enforced?

Question

I am using PostgreSQL 9.1. Trying to enforce UTF8 encoding as default.

This is what I am doing.

service postgresql initdb -E 'UTF-8' --lc-collate='en_US.UTF-8' --lc-ctype=locale='en_US.UTF-8';

Although the initilization process goes on without any problem,

a \l at the psql prompt gives there details.

                         List of databases
   Name    |  Owner   |Encoding  | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
  postgres | postgres | LATIN1 | en_US&nbsp;| en_US|

Why is the UTF-8 encoding not getting enforced?

Martin · Accepted Answer · 2019-02-01 19:37:29Z

17

Looks like you are calling initdb through a runlevel script of the OS. This script might not pass on the parameters. You better try executing initdb directly, you will need to perform the following steps starting as root and assuming the OS user account for the database is postgres.

mkdir <your data dir>
chown postgres <your data dir>
su postgres
initdb --pgdata=<your data dir> -E 'UTF-8' --lc-collate='en_US.UTF-8' --lc-ctype='en_US.UTF-8'

edited Feb 1, 2019 at 19:37

Martin

3,06231 silver badges25 bronze badges

answered Aug 5, 2012 at 8:10

Eelke

22.3k5 gold badges57 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ThinkingMonkey Over a year ago

You are right. Although I couldn't run initdb directly as a superuser. gave an

initdb: cannot be run as root Please log in (using, e.g., "su") as the (unprivileged) user that will own the server process.

error. After logging in as an unprivileged user I was able to initialize the db with the proper encoding.

Eelke Over a year ago

Forgot about it being a bit picky, updated the answer with the full set of commands that might be needed.

Sorin Postelnicu Over a year ago

Hello guys! Do you have any idea why I get: initdb: invalid locale name "'en_US.UTF-8'" ? I have also tried without the quotes, but then I get initdb: invalid locale name "en_US.UTF-8". I have downloaded the Postgresql9.4.1 binaries (not the installer) for Windows. Is the locale en_US.UTF-8 not available in the Windows binaries?

Alkanshel Over a year ago

Thank you @Eelke, en-US works... and absolutely nothing else does.

jaydip · Accepted Answer · 2015-10-31 04:38:41Z

Debian PostgreSQL installation automatically calls the initdb i.e. it initializes the cluster with default encoding and locale. Encoding can be changed later but the locale cannot. To change the locale (an possibly other options in initdb), delete the existing default cluster and create a new one:

Take root privileges.
Run the following command:



 pg_dropcluster --stop <version> main

For example:

pg_dropcluster --stop 8.3 main

Run the initdb with your options. For example:

pg_createcluster --locale de_DE.UTF-8 --start 8.3 main

Warning!

The previous operation obviously deletes everything you had in cluster databases. Perform this operation right after you have installed the base package. Check the PostgreSQL manual if you need to change locale for an existing database (it is not a trivial operation).

Collectives™ on Stack Overflow

What is going wrong with postgresql initdb? Why is the `UTF-8` encoding not getting enforced?

2 Answers 2

4 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Related