Thread: PostgreSQL v15.12 fails to perform PG_UPGRADE from v13 and v9 on Windows
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"
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'
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'
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!
| |||||||||||||
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
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?
Thanks!
Manika Singhal
EDB India