Re: PostgreSQL v15.12 fails to perform PG_UPGRADE from v13 and v9 on Windows

Hi Manika,

Thank you for the detailed explanation.

After reviewing the installer commit, I believe there might be a larger
issue that might affect many users.
I noticed that this commit was merged into REL-13, REL-14, REL-15, and
REL-16.

I anticipate that users could face an upgrade issue if they installed a
cluster on a Windows machine using the default locale before this change
and then attempt to upgrade to a release that includes this commit.

For example:

1.

Install a cluster on version v13.1 on a Windows machine with the en-US
locale (default). The cluster locale will be 'English_United States.1252'.
2.

Install a cluster on version v15.12 on a Windows machine with the en-US
locale (default). The cluster locale will be 'en-US'.

If you then try to migrate from the old cluster to the new one using the
pg_upgrade command, you'll encounter the following error:

*lc_collate values for database "template1" do not match: old
"English_United States.1252", new "en-US"*

I’m curious if other users have reported this issue, as it seems to be a
critical one.

As for your suggestion, it will work for my case since my locale name
doesn’t contain non-ASCII characters.

Thanks,

Avi

On Fri, Apr 4, 2025 at 3:30 PM Manika Singhal <
manika(dot)singhal(at)enterprisedb(dot)com> wrote:

>
> On Fri, Mar 21, 2025 at 4:43 PM Ben Caspi <benc(at)aidoc(dot)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!
>>
>> --
>>
>> [image: photo]
>>
>> Ben Caspi
>> DevOps Engineer
>>
>> www.aidoc.com | benc(at)aidoc(dot)com
>>
>> [image: linkedin] <https://www.linkedin.com/company/aidoc/>
>>
>> [image: twitter] <https://twitter.com/aidocmed>
>>
>> [image: App Banner Image] <https://www.aidoc.com/book-a-demo/>
>>
>> [image: __tpx__]
>>
>
>
> 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
>