Re: PostgreSQL v15.12 fails to perform PG_UPGRADE from v13 and v9 on Windows - Mailing list pgsql-bugs

On Fri, Mar 21, 2025 at 4:43 PM Ben Caspi <benc@aidoc.com> wrote:

Hello!

We have an environment with numerous client Windows machines. The machines have PSQL v9.6/v13.13/v15.6 installed.

We've been working on slowly upgrading the 9/13 machines to use PSQL 15, which has been going smoothly until recently.

We've been made aware of an issue with 15.6 which prompted us to change our latest version to 15.12 instead (we can't go past 15 due to many of our machines being Windows Server 2016, which from our understanding is not supported in v16 and onwards).

Upgrading our 15.6 machines to 15.12 has been going smoothly.

However, running PG_UPGRADE on PSQL v9.6/13.13 to v15.12 has been failing.

This is the error message:
lc_collate values for database "template1" do not match:  old "English_United States.1252", new "en-US"

Upon investigation, we came to understand that the PSQL v9.6/v13.13 config contains the following configuration by default:
lc_messages = 'English_United States.1252'
lc_monetary = 'English_United States.1252'
lc_numeric = 'English_United States.1252'
lc_time = 'English_United States.1252'

But when installing PSQL 15.12 it's changed to:
lc_messages = 'en-US'
lc_monetary = 'en-US'
lc_numeric = 'en-US'
lc_time = 'en-US'

Is a failure in upgrading to this version intended?

We can workaround this issue by upgrading to 15.6 and then to 15.12, but I'd like to avoid this solution as it lengthens potential downtime for our clients.

Any help would be appreciated.

Thanks!

--

Ben Caspi DevOps Engineer www.aidoc.com  |  benc@aidoc.com

Thanks for the report!

Copying Thomas Munro and Tom Lane as they were involved in the discussions

referenced in this email, and their inputs will be useful as well. Please copy other

contributors too, if needed.

This change in the behaviour of the installer is the result of this commit [1].

 As a part of this change, the installer would convert the chosen locale to its corresponding BCP-47

[2] code name before passing it on to initdb.exe. This was helpful for users where the locale name

contained non-ascii characters and initdb would fail. We received a significant number of tickets

from users after Microsoft made that change (to add non-ascii characters) in their updates.

Reading the thread [3], it seems it's probably not recommended to update the pg_database.datcollate

or datctype. I am thinking if it might help if installer converted the chosen locale name to BCP-47 only

when it contains non-ascii characters, otherwise, it should use the name as is during initdb run.

Will this help?

[1]https://github.com/EnterpriseDB/edb-installers/commit/e6404b5194051e20cfc0e7f268a69091e6445a73 [2]https://www.postgresql.org/message-id/CA%2BhUKGL5mBN3JQuebAPbX0yxDNtpui04J%2BKSy2F7KBbhLGaJig%40mail.gmail.com 

[3]https://www.postgresql.org/message-id/2694195.1700072765%40sss.pgh.pa.us

Thanks!

Manika Singhal

EDB India