Postgresql / EPAS Logging to English

This article shows you how to modify the logging settings in order to display logs in English.

It is not unreasonable to expect that a customer's cluster to have been initialised in their local locale, however, especially from a support perspective, having the logs in English would be a great help. To have the logs in English is actually very simple, and we will work through with examples.

On Linux, you would use the localectl command to view the current configuration as shown below:, including the output.

[root@pg1 ~]# localectl status
System Locale: LANG=en_GB.utf8
VC Keymap: gb
X11 Layout: gb

Obviously in the above example any Postgresql or EPAS cluster will be initialised by default with the relevant lc settings.

You can list available locales on your server by executing the command below, again, displaying a sample of the output.

[root@pg1 ~]# locale -a
aa_DJ
aa_DJ.iso88591
aa_DJ.utf8
aa_ER
aa_ER@saaho
aa_ER.utf8
aa_ER.utf8@saaho
aa_ET
aa_ET.utf8
af_ZA
af_ZA.iso88591
af_ZA.utf8
agr_PE
agr_PE.utf8
ak_GH
ak_GH.utf8
am_ET
am_ET.utf8
an_ES
an_ES.iso885915
an_ES.utf8
anp_IN

As can be seen there are a number of different locales.

Below, is an example of how to change the locale, in this instance to Spanish.

localectl set-locale LANG=es_ES.utf8

We can check the change has taken place:

[root@pg1 ~]# localectl status
System Locale: LANG=es_ES.utf8
VC Keymap: gb
X11 Layout: gb

Initialise the cluster, an example is shown below, including output.

[postgres@pg1 13]$ /usr/pgsql-13/bin/initdb -D spanish_data_dir
Los archivos de este cluster serán de propiedad del usuario «postgres».
Este usuario también debe ser quien ejecute el proceso servidor.
El cluster será inicializado con configuración regional «es_ES.utf8».
La codificación por omisión ha sido por lo tanto definida a «UTF8».
La configuración de búsqueda en texto ha sido definida a «spanish».
Las sumas de verificación en páginas de datos han sido desactivadas.
creando el directorio spanish_data_dir ... hecho
creando subdirectorios ... hecho
seleccionando implementación de memoria compartida dinámica ...posix
seleccionando el valor para max_connections ... 100
seleccionando el valor para shared_buffers ... 128MB
seleccionando el huso horario por omisión ... America/New_York
creando archivos de configuración ... hecho
ejecutando script de inicio (bootstrap) ... hecho
realizando inicialización post-bootstrap ... hecho
sincronizando los datos a disco ... hecho
initdb: precaución: activando el método de autentificación «trust» para conexiones locales
Puede cambiar esto editando pg_hba.conf o usando el parámetro -A,
o --auth-local y --auth-host la próxima vez que ejecute initdb.
Completado. Ahora puede iniciar el servidor de bases de datos usando:
/usr/pgsql-13/bin/pg_ctl -D spanish_data_dir -l archivo_de_registro start
[postgres@pg1 13]$ 

Lets start the cluster and check the contents of the log file..

Below is a section of the log..

[postgres@pg1 log]$ cat postgresql-Thu.log 
2023-04-20 03:36:18.797 EDT [18133] LOG: iniciando PostgreSQL 13.10 (2ndQPG 13.10r1.1.12) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-16), 64-bit
2023-04-20 03:36:18.798 EDT [18133] LOG: escuchando en la dirección IPv6 «::1», port 5432
2023-04-20 03:36:18.798 EDT [18133] LOG: escuchando en la dirección IPv4 «127.0.0.1», port 5432
2023-04-20 03:36:18.799 EDT [18133] LOG: escuchando en el socket Unix «/var/run/postgresql/.s.PGSQL.5432»
2023-04-20 03:36:18.804 EDT [18133] LOG: escuchando en el socket Unix «/tmp/.s.PGSQL.5432»
2023-04-20 03:36:18.809 EDT [18135] LOG: el sistema de bases de datos fue apagado en 2023-04-20 03:33:38 EDT
2023-04-20 03:36:18.811 EDT [18133] LOG: el sistema de bases de datos está listo para aceptar conexiones

You can double check the postgres lc settings either by viewing postgresql.conf or using the query below:

postgres=# select name,setting from pg_settings where name like 'lc%';
name | setting 
lc_collate | es_ES.utf8
lc_ctype | es_ES.utf8
lc_messages | es_ES.utf8
lc_monetary | es_ES.utf8
lc_numeric | es_ES.utf8
lc_time | es_ES.utf8
(6 filas)

We now have a cluster initialized with the Spanish locale.

Alternatively the locale can be explicitly set when initializing the cluster using the --locale switch, as shown in the example below:

[postgres@pg1 13]$ /usr/pgsql-13/bin/initdb -D japanese_data_dir --locale=ja_JP.eucjp
Los archivos de este cluster serán de propiedad del usuario «postgres».
Este usuario también debe ser quien ejecute el proceso servidor.
El cluster será inicializado con configuración regional «ja_JP.eucjp».
La codificación por omisión ha sido por lo tanto definida a «EUC_JP».
initdb: no se pudo encontrar una configuración para búsqueda en texto apropiada
para la configuración regional «ja_JP.eucjp»
La configuración de búsqueda en texto ha sido definida a «simple».
Las sumas de verificación en páginas de datos han sido desactivadas.
creando el directorio japanese_data_dir ... hecho
creando subdirectorios ... hecho
seleccionando implementación de memoria compartida dinámica ...posix
seleccionando el valor para max_connections ... 100
seleccionando el valor para shared_buffers ... 128MB
seleccionando el huso horario por omisión ... America/New_York
creando archivos de configuración ... hecho
ejecutando script de inicio (bootstrap) ... hecho
realizando inicialización post-bootstrap ... hecho
sincronizando los datos a disco ... hecho
initdb: precaución: activando el método de autentificación «trust» para conexiones locales
Puede cambiar esto editando pg_hba.conf o usando el parámetro -A,
o --auth-local y --auth-host la próxima vez que ejecute initdb.
Completado. Ahora puede iniciar el servidor de bases de datos usando:
/usr/pgsql-13/bin/pg_ctl -D japanese_data_dir -l archivo_de_registro start

The messages to the screen, will still be of the current OS locale settings. However, upon starting the cluster, you will now be presented with something akin to that shown below:

[postgres@pg1 13]$ /usr/pgsql-13/bin/pg_ctl -D japanese_data_dir start
esperando que el servidor se inicie....2023-04-20 03:47:33.011 EDT [18675] LOG: ログ出力をログ収集プロセスにリダイレクトしています
2023-04-20 03:47:33.011 EDT [18675] ヒント: ここからのログ出力はディレクトリ"log"に現れます。
listo
servidor iniciado

A snippet of the postgresql log file is shown below:

2023-04-20 03:47:33.011 EDT [18675] LOG: PostgreSQL 13.10 (2ndQPG 13.10r1.1.12) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-16), 64-bit を起動しています
2023-04-20 03:47:33.011 EDT [18675] LOG: IPv6アドレス"::1"、ポート5433で待ち受けています
2023-04-20 03:47:33.011 EDT [18675] LOG: IPv4アドレス"127.0.0.1"、ポート5433で待ち受けています
2023-04-20 03:47:33.016 EDT [18675] LOG: Unixソケット"/var/run/postgresql/.s.PGSQL.5433"で待ち受けています
2023-04-20 03:47:33.019 EDT [18675] LOG: Unixソケット"/tmp/.s.PGSQL.5433"で待ち受けています
2023-04-20 03:47:33.021 EDT [18677] LOG: データベースシステムは 2023-04-20 03:47:17 EDT にシャットダウンしました
2023-04-20 03:47:33.024 EDT [18675] LOG: データベースシステムの接続受け付け準備が整いました

For the sake of completeness below are the cluster lc settings, as defined in the the postgresql.conf file.

[postgres@pg1 log]$ /usr/pgsql-13/bin/psql -p 5433
psql (13.10 (2ndQPG 13.10r1.1.12), servidor 13.10 (2ndQPG 13.10r1.1.12))
Digite «help» para obtener ayuda.
postgres=# select name,setting from pg_settings where name like 'lc%';
name | setting 
lc_collate | ja_JP.eucjp
lc_ctype | ja_JP.eucjp
lc_messages | ja_JP.eucjp
lc_monetary | ja_JP.eucjp
lc_numeric | ja_JP.eucjp
lc_time | ja_JP.eucjp
(6 filas)

If we know what is causing a particular error in the log file, lc_messages can be set at the session level. Below is an example of the log output before and after setting lc_messages we will engineer a deliberate error.

The following wrong SQL is applied as follows, along with sample output.

postgres=# crete table tryem (a int);
ERROR: "crete"またはその近辺で構文エラー
LÍNEA 1: crete table tryem (a int);

The resulting error is written to the postgresql log as shown below:

2023-04-20 03:54:16.125 EDT [18796] ERROR: "crete"�ޤ��Ϥ��ζ��դǹ�ʸ���顼(1ʸ����)
2023-04-20 03:54:16.125 EDT [18796] ʸ: crete table tryem (a int);

For this session let's set the lc_messages to be 'C' If set to 'C' the logging output will always be in English.

This time setting lc_messages at the session level, we will try the failing SQL once more:

postgres=# set lc_messages='C';
SET
postgres=# crete table tryem (a int);
ERROR: syntax error at or near "crete"
LÍNEA 1: crete table tryem (a int);

This time, the error on screen is displayed in the OS locale language, which we set to Spanish earlier. However, the log snippet for this error now looks like:

2023-04-20 03:57:22.655 EDT [19011] ERROR: syntax error at or near "crete" at character 1
2023-04-20 03:57:22.655 EDT [19011] STATEMENT: crete table tryem (a int);

As can be seen from the above the error is written in English to the postgres log file.

The effect on the lc_messages is shown below:

1. Initial log on to a database.

postgres=# select name,setting from pg_settings where name like 'lc%';
name | setting 
lc_collate | ja_JP.eucjp
lc_ctype | ja_JP.eucjp
lc_messages | ja_JP.eucjp
lc_monetary | ja_JP.eucjp
lc_numeric | ja_JP.eucjp
lc_time | ja_JP.eucjp
(6 filas)

2. At the session level set lc_messages to write to the log in English, for this session.

postgres=# set lc_messages='C';
SET

3. View the lc settings for this session

postgres=# select name,setting from pg_settings where name like 'lc%';
name | setting 
lc_collate | ja_JP.eucjp
lc_ctype | ja_JP.eucjp
lc_messages | C
lc_monetary | ja_JP.eucjp
lc_numeric | ja_JP.eucjp
lc_time | ja_JP.eucjp
(6 filas)

4. You can reset the lc_messages to the default in the curent session:

postgres=# reset lc_messages;
RESET

Or, you can close the session and open a new one.

5. Check the lc settings after a reset or new session.

postgres=# select name,setting from pg_settings where name like 'lc%';
name | setting 
lc_collate | ja_JP.eucjp
lc_ctype | ja_JP.eucjp
lc_messages | ja_JP.eucjp
lc_monetary | ja_JP.eucjp
lc_numeric | ja_JP.eucjp
lc_time | ja_JP.eucjp
(6 filas)

As can be seen from the above example, we have only been setting the lc_messages setting at the session level to make the change permanent the postgresql.conf file has to be amended and the cluster reloaded or restarted.

Simply edit postgresql.conf and change the entry for lc_messages as shown below:

lc_messages = 'C' 

Then reload or restart the cluster:

[postgres@pg1 13]$ /usr/pgsql-13/bin/pg_ctl -D japanese_data_dir restart
esperando que el servidor se detenga.... listo
servidor detenido
esperando que el servidor se inicie....2023-04-20 04:17:22.601 EDT [19948] LOG: ログ出力をログ収集プロセスにリダイレクトしています
2023-04-20 04:17:22.601 EDT [19948] ヒント: ここからのログ出力はディレクトリ"log"に現れます。
listo
servidor iniciado

Let's check the lc settings* now in the cluster:

[postgres@pg1 13]$ /usr/pgsql-13/bin/psql -p 5433
psql (13.10 (2ndQPG 13.10r1.1.12), servidor 13.10 (2ndQPG 13.10r1.1.12))
Digite «help» para obtener ayuda.
postgres=# select name,setting from pg_settings where name like 'lc%';
name | setting 
lc_collate | ja_JP.eucjp
lc_ctype | ja_JP.eucjp
lc_messages | C
lc_monetary | ja_JP.eucjp
lc_numeric | ja_JP.eucjp
lc_time | ja_JP.eucjp
(6 filas)

As can be see the lc_messages setting is now C by default.

Now ensure something is errored in the log.

postgres=# drop table tryem (a int);
ERROR: syntax error at or near "("
LÍNEA 1: drop table tryem (a int);
^
postgres=# 

A snippet from the postgresql log file from starting the cluster and also showing our enforced error

2023-04-20 04:19:04.853 EDT [20057] LOG: starting PostgreSQL 13.10 (2ndQPG 13.10r1.1.12) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-16), 64-bit
2023-04-20 04:19:04.853 EDT [20057] LOG: listening on IPv6 address "::1", port 5433
2023-04-20 04:19:04.853 EDT [20057] LOG: listening on IPv4 address "127.0.0.1", port 5433
2023-04-20 04:19:04.856 EDT [20057] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
2023-04-20 04:19:04.859 EDT [20057] LOG: listening on Unix socket "/tmp/.s.PGSQL.5433"
2023-04-20 04:19:04.862 EDT [20059] LOG: database system was shut down at 2023-04-20 04:19:04 EDT
2023-04-20 04:19:04.864 EDT [20057] LOG: database system is ready to accept connections
2023-04-20 04:21:49.807 EDT [20069] ERROR: syntax error at or near "(" at character 18
2023-04-20 04:21:49.807 EDT [20069] STATEMENT: drop table tryem (a int);

NOTE: there is no need to execute a restart of the cluster, only a cluster reload is needed to take effect:

2023-04-21 14:22:54.829 -03 [79315] ERROR: error de sintaxis en o cerca de «creae» en carácter 1
2023-04-21 14:22:54.829 -03 [79315] SENTENCIA: creae table tempo;
2023-04-21 14:29:05.814 -03 [78677] LOG: se recibió SIGHUP, volviendo a cargar archivos de configuración
2023-04-21 14:29:05.815 -03 [78677] LOG: parameter "lc_messages" changed to "C"
2023-04-21 14:29:40.601 -03 [80172] ERROR: syntax error at or near "creae" at character 1
2023-04-21 14:29:40.601 -03 [80172] STATEMENT: creae table tempo

Before the SIGHUP signal (cluster reload) the error was in Spanish as the cluster was created with es_ES and after the reload it changed to C (English default).

For the log file to be written in English then only the setting lc_messages needs to be set to 'C'.

This can be done at session level:

set lc_messages='C'

Or to make it permanent, change the entry in postgresql.conf to read:

lc_messages = 'C'

No matter what language the OS is configured in, setting lc_messages to C will always ensure the log output is in English.