Setting server log messages to fr_FR (or any non-English)

Hello,

I'm helping out a user who reported an issue with a piece of software I
wrote when confronted with a Postgres instance using server messages in
French.

https://mailman-mail5.webfaction.com/pipermail/zato-discuss/2014-January/000195.html

The thing I'm struggling with is how to create a test environment to
reproduce this situation in.

The goal is not to store data in French in the tables, rather how to
make Postgres output its own messages in French.

So for instance, after attempting to insert a duplicate value in a
column, I'd prefer to see 'la valeur d'une clé dupliquée rompt la
contrainte unique' rather than 'duplicate key value violates unique
constraint'. This should be both logged on the server side as well as
returned to the client.

To that end, I set up a small Ubuntu 12.04 instance with Postgres
9.1.11, set all the locale information, including all the optional ones
to fr_FR.UTF-8.

Please find all the information below - OS, PG version, locales, the
command to create a database, that its lc_messages are set to
fr_FR.UTF-8, how it's started, along with a sample psql session that
still uses English instead of French even after issuing a set
lc_messages="fr_FR.UTF-8"; command.

I'm at my wit's end - about the only thing is that springs to mind is
that the translation is not available on this particular system or is
somehow silently ignored and messages reverted to English.

Many thanks for any input!

# ####################################################################

postgres(at)ubuntu:~$ uname -a
Linux ubuntu 3.8.0-29-generic #42~precise1-Ubuntu SMP Wed Aug 14
15:31:16 UTC 2013 i686 i686 i386 GNU/Linux
postgres(at)ubuntu:~$

# ####################################################################

postgres(at)ubuntu:~$ cat /etc/issue
Ubuntu 12.04.3 LTS \n \l

postgres(at)ubuntu:~$

# ####################################################################

postgres(at)ubuntu:~$ /usr/lib/postgresql/9.1/bin/postgres --version
postgres (PostgreSQL) 9.1.11
postgres(at)ubuntu:~$

# ####################################################################

postgres(at)ubuntu:~$ export | grep fr_FR
declare -x LANG="fr_FR.UTF-8"
declare -x LANGUAGE="fr_FR.UTF-8"
declare -x LC_ADDRESS="fr_FR.UTF-8"
declare -x LC_ALL="fr_FR.UTF-8"
declare -x LC_COLLATE="fr_FR.UTF-8"
declare -x LC_CTYPE="fr_FR.UTF-8"
declare -x LC_IDENTIFICATION="fr_FR.UTF-8"
declare -x LC_MEASUREMENT="fr_FR.UTF-8"
declare -x LC_MESSAGES="fr_FR.UTF-8"
declare -x LC_MONETARY="fr_FR.UTF-8"
declare -x LC_NAME="fr_FR.UTF-8"
declare -x LC_NUMERIC="fr_FR.UTF-8"
declare -x LC_PAPER="fr_FR.UTF-8"
declare -x LC_TELEPHONE="fr_FR.UTF-8"
declare -x LC_TIME="fr_FR.UTF-8"
postgres(at)ubuntu:~$

# ####################################################################

postgres(at)ubuntu:~$ locale
LANG=fr_FR.UTF-8
LANGUAGE=fr_FR.UTF-8
LC_CTYPE="fr_FR.UTF-8"
LC_NUMERIC="fr_FR.UTF-8"
LC_TIME="fr_FR.UTF-8"
LC_COLLATE="fr_FR.UTF-8"
LC_MONETARY="fr_FR.UTF-8"
LC_MESSAGES="fr_FR.UTF-8"
LC_PAPER="fr_FR.UTF-8"
LC_NAME="fr_FR.UTF-8"
LC_ADDRESS="fr_FR.UTF-8"
LC_TELEPHONE="fr_FR.UTF-8"
LC_MEASUREMENT="fr_FR.UTF-8"
LC_IDENTIFICATION="fr_FR.UTF-8"
LC_ALL=fr_FR.UTF-8
postgres(at)ubuntu:~$

# ####################################################################

postgres(at)ubuntu:~$ /usr/lib/postgresql/9.1/bin/initdb
--locale=fr_FR.UTF-8 -D fr-dir/
The files belonging to this database system will be owned by user
"postgres".
This user must also own the server process.

The database cluster will be initialized with locale fr_FR.UTF-8.
The default database encoding has accordingly been set to UTF8.
The default text search configuration will be set to "french".

creating directory fr-dir ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 24MB
creating configuration files ... ok
creating template1 database in fr-dir/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

Success. You can now start the database server using:

/usr/lib/postgresql/9.1/bin/postgres -D fr-dir
or
/usr/lib/postgresql/9.1/bin/pg_ctl -D fr-dir -l logfile start

postgres(at)ubuntu:~$

# ####################################################################

postgres(at)ubuntu:~$ cat fr-dir/postgresql.conf | grep lc_
lc_messages = 'fr_FR.UTF-8' # locale for system error message
lc_monetary = 'fr_FR.UTF-8' # locale for monetary formatting
lc_numeric = 'fr_FR.UTF-8' # locale for number formatting
lc_time = 'fr_FR.UTF-8' # locale for time formatting
postgres(at)ubuntu:~$

# ####################################################################

postgres(at)ubuntu:~$ /usr/lib/postgresql/9.1/bin/postgres
--config-file=fr-dir/postgresql.conf -D fr-dir/
LOG: database system was shut down at 2014-01-09 00:41:38 CET
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
ERROR: duplicate key value violates unique constraint "x_a_key"
DETAIL: Key (a)=(1) already exists.
STATEMENT: insert into x(a) values(1);

# ####################################################################

postgres(at)ubuntu:~$ psql
psql (9.1.11)
Type "help" for help.

postgres=# set lc_messages="fr_FR.UTF-8";
SET
postgres=# create table x(a int unique); insert into x(a) values(1);
insert into x(a) values(1);
NOTICE: CREATE TABLE / UNIQUE will create implicit index "x_a_key" for
table "x"
CREATE TABLE
INSERT 0 1
ERROR: duplicate key value violates unique constraint "x_a_key"
DETAIL: Key (a)=(1) already exists.
postgres=#

# ####################################################################

--
Dariusz Suchojad

https://zato.io
ESB, SOA and cloud integrations in Python