Upgrade from 4.4-9 to 5.x fails (fatal issue during postgresql upgrade procedure)

UCS - Univention Corporate Server
#1

Hi,

I am struggling with the upgrade from the obsolete 4.4-9 to the 5.x version.
I followed this upgrade procedure here: Updating from PostgreSQL 9.4 to PostgreSQL 9.6

however, the step pg_upgradecluster 9.4 main fails with the following message:
Job for postgresql@9.4-main.service failed because the control process exited with error code.
See “systemctl status postgresql@9.4-main.service” and “journalctl -xe” for details.
Error: Could not restart old cluster

When looking at the journalctl for more details, I get the following output:

Sep 21 21:48:57 server-dc01 postgresql@9.4-main[3901]: The PostgreSQL server failed to start. Please check the log output:

Sep 21 21:48:57 server-dc01 postgresql@9.4-main[3901]: 2023-09-21 21:48:56 CEST [3906-1] LOG: invalid value for parameter “lc_messages”: “fr_FR.UTF-8”

Sep 21 21:48:57 server-dc01 postgresql@9.4-main[3901]: 2023-09-21 21:48:56 CEST [3906-2] LOG: invalid value for parameter “lc_monetary”: “fr_FR.UTF-8”

Sep 21 21:48:57 server-dc01 postgresql@9.4-main[3901]: 2023-09-21 21:48:56 CEST [3906-3] LOG: invalid value for parameter “lc_numeric”: “fr_FR.UTF-8”

Sep 21 21:48:57 server-dc01 postgresql@9.4-main[3901]: 2023-09-21 21:48:56 CEST [3906-4] LOG: invalid value for parameter “lc_time”: “fr_FR.UTF-8”

Sep 21 21:48:57 server-dc01 postgresql@9.4-main[3901]: 2023-09-21 21:48:56 CEST [3906-5] FATAL: configuration file "/etc/postgresql/9.4/main/postgresql.

Sep 21 21:48:57 server-dc01 systemd[1]: postgresql@9.4-main.service: Control process exited, code=exited status=1

Sep 21 21:48:57 server-dc01 systemd[1]: Failed to start PostgreSQL Cluster 9.4-main.

– Subject: Unit postgresql@9.4-main.service has failed

It looks loke a locale-related issue. Honestly I don’t understand how this would impact the postgresql DB, but maybe you guys have a clue?

shall I just modify the /etc/postgresql/9.4/main/postgresql config file? what is the expected locale parameter set?

thanks a lot!

Cheers
Denis

#2

Different countries have different rules to sort names, e.g. collation rules: Depending on your locale tools like ls and sort might sort upper-case letters before lower-case letters before umlauts and other special characters, or in any other combination preferred by the country and language. Databases like PostgreSQL are also affected by this as they also sort their results, e.g. ORDER BY in SQL. They may re-use the implementation from glibc or icu instead of re-implementing the logic on their own. See Collation support.

The error message indicates that your system now lacks support for the locale fr_FR which probably was used back then when you (or someone else) created the database and its tables: PostgreSQL recorded the collation rule back then and now is unhappy to no longer find it on your system.

I see three options:

  1. Append fr_FR.UTF-8:UTF-8 to the UCR variable locale by running the following command: sudo ucr set locale="$(ucr get locale) fr_FR.UTF-8:UTF-8"
  2. Login to the PostgreSQL database using su -c psql postgres and use ALTER TABLE … ALTER COLUMN … SET DATA TYPE … COLLATE … to update all columns in all tables in all databases. See ALTER TABLE
  3. Manually dump the database using pg_dumpall to a file; change the collation rules there, drop the old database and re-initialize it by importing the changes file again.
#3

Very interesting! Actually, when the Univention server was set up years ago, the locale has been set to French automatically during the installation. I later switched the system’s locale to EN, but it looks like the DB was affected too.

I will try the first option tonight, regarding options 2 and 3 I will have to make some reading first because this completely out of my league.

I’ll keep you posted tonight how it went.

#4

Alright! Option 1 worked perfectly! as you suggested, the command sudo ucr set locale="$(ucr get locale) fr_FR.UTF-8:UTF-8" has to be entered just before pg_upgradecluster 9.4 main

I am now happily running 5.0-5 :slight_smile:
thanks for your support!!
Denis

Mastodon