Changed locale - error

[Afox]

Hello,

I changed my locale on the pmg some time ago and now I get the following error when running the pre-upgrade script pmg7to8:

psql: error: FATAL: database locale is incompatible with operating system
DETAIL: The database was initialized with LC_COLLATE "de_DE.UTF-8", which is not recognized by setlocale().
HINT: Recreate the database with another locale or install the missing locale.

Current default is en_US.utf8 and I removed the original locale (preferably keep it that way).

What can I do to convert the database to the new locale?

Thanks and best regards

 

[Stoiko Ivanov]

could you please post a bit more context around the error-message and also the journal when you get it?
The PMG databases should all get created with 'C' collation.

* any additional software you installed on your PMG
* how did you change the locale on your PMG
* how did you setup the PMG?

Thanks!

 

[Afox]

pmg7to8
= CHECKING VERSION INFORMATION FOR PMG PACKAGES =

Checking for package updates..
PASS: all packages up-to-date

Checking proxmox-mailgateway package version..
PASS: proxmox-mailgateway package has version >= 7.3-2

Checking running kernel version..
PASS: running kernel '5.15.108-1-pve' is considered suitable for upgrade.
INFO: Checking if the cluster nodes are in sync
SKIP: no cluster, no sync status to check
psql: error: FATAL: database locale is incompatible with operating system
DETAIL: The database was initialized with LC_COLLATE "de_DE.UTF-8", which is not recognized by setlocale().
HINT: Recreate the database with another locale or install the missing locale.
Unable to determine currently running Postgresql server version

I installed it on top of Debian 10 (Buster) some time ago.

Locale was changed like this (I believe): dpkg-reconfigure locales

No additional software was installed on the PMG.

 

[Stoiko Ivanov]

How was the PMG setup originally - which version - on top of Debian - or from the ISO - or from the container-template for PVE?

* please - check the journal when the script runs
* please also check the postgresql logs /var/log/postgresql
* please post the output of `psql -l`
* please post the output of ` grep -v '^#' /etc/locale.gen`

 

[Afox]

Before I provide all that info, one question:
Would it work if I just backup and restore the PMG to a new install? I was about to do it but canceled because I thought the issues remain the same. After install I then would change the locale to English.

If this causes less trouble I would go this way.

 

[Stoiko Ivanov]

If it is possible I would appreciate if you could provide the information - just in case it is indeed an issue that can occur in more installations (which I currently don't think should happen)

But if you're in a hurry - installing a fresh 8.0 PMG and restoring a backup from the other one is certainly quite fast.
(you'll lose the quarantined mails and greylist db though - since this transient information is not backed up)

 

[Afox]

How was the PMG setup originally - which version - on top of Debian - or from the ISO - or from the container-template for PVE?

* please - check the journal when the script runs
* please also check the postgresql logs /var/log/postgresql
* please post the output of `psql -l`
* please post the output of ` grep -v '^#' /etc/locale.gen`

I believe it was originally installed on top of a Debian 10 (Buster) minimal (official netinst-ISO)

journalctl does not show anything special

postgresql logs show the same as posted above:

2023-06-30 13:19:11.949 CEST [7042] FATAL: database locale is incompatible with operating system
2023-06-30 13:19:11.949 CEST [7042] DETAIL: The database was initialized with LC_COLLATE "de_DE.UTF-8", which is not recognized by setlocale().
2023-06-30 13:19:11.949 CEST [7042] HINT: Recreate the database with another locale or install the missing locale.

psql -l
psql: error: FATAL: database locale is incompatible with operating system
DETAIL: The database was initialized with LC_COLLATE "de_DE.UTF-8", which is not recognized by setlocale().
HINT: Recreate the database with another locale or install the missing locale.

grep -v '^#' /etc/locale.gen


en_US.UTF-8 UTF-8

 

[Stoiko Ivanov]

Ok - I think I understand the issue - and don't think it's too serious.
(please correct me if any of the following is wrong):
* You originally setup the system and selected de_DE.UTF8 as locale
* You installed PMG on top of debian (though this is not relevant)
* then you ran `dpkg-reconfigure locales`
** removed de_DE.UTF8
** added en_US.UTF-8 UTF-8
rebooted (not sure if this is needed)
and then the issues started happening

If that's the case the following is at fault:
* during installation of postgresql it creates the initial main cluster, with whatever locale your shell has set (or is available on the system - did not check that explicitly) - so the base-databases 'postgres', 'template0', 'template1' all have de_DE.UTF8 as their language and collation setting.
* once your removed that locale from your system postgres refuses to start or provide answers.

The easiest solution (I think) is:
* dpkg-reconfigure locales
** enable both en_US.UTF-8 and de_DE.UTF-8
** in the next selection use en_US.UTF-8 (then your system will display messages and most things you want to have in English and postgresql should be happy again
* log out and log in again
* run `psql -l` and post the results

having multiple locales generated on a system should not be a problem (and not hurt you in selecting the language you want to have) - as far as I know.

If in doubt - create a backup of the system before running those steps.

 

[Afox]

Most if not any of your assumptions are correct. I wonder how the locales look like when I install from the ISO provided by Proxmox and select a German timezone and keyboard-layout. Unfortunately the installer doesn´t let you chose the locale/language of the system.

Edit: If anyone is interested: The Proxmox Mail Gateway ISO installs English as default locale.