Re: BUG #18196: Databases Created in Turkish Language Will Not Run on the Latest Version of Windows - Mailing list pgsql-bugs

From Laurenz Albe
Subject Re: BUG #18196: Databases Created in Turkish Language Will Not Run on the Latest Version of Windows
Date
Msg-id d00d9a5c1f6a1a80d481be18499e28660ec97b23.camel@cybertec.at
Whole thread Raw
In response to Re: BUG #18196: Databases Created in Turkish Language Will Not Run on the Latest Version of Windows  (Halil Han Badem <halilhanbadem@gmail.com>)
Responses Re: BUG #18196: Databases Created in Turkish Language Will Not Run on the Latest Version of Windows  (Halil Han Badem <halilhanbadem@gmail.com>)
List pgsql-bugs
On Wed, 2023-11-15 at 21:47 +0300, Halil Han Badem wrote:
> In this case, the service starts, but you cannot establish a connection to the database.
> In other words, it gives the following error. This error is expected because the
> database was created with Turkish_Turkey.1254. Due to the name change to
> Turkish_Türkiye.1254, it cannot find the local name.
>
> failed: FATAL: database locale is incompatible with operating system
> DETAIL: The database was initialized with LC_COLLATE "Turkish_Turkey.1254", which is not recognized by setlocale().
> HINT: Recreate the database with another locale or install the missing locale.

That was to be expected...

I cannot think of a good solution except to be Microsoft to unbreak their
software.  If you have not performed the Windows update on a system yet,
and you are bold enough, you could try the following hack:

- connect as superuser

- modify the catalog table with

    UPDATE pg_database
    SET datcollate = 'Turkish_Türkiye.1254',
        datctype = 'Turkish_Türkiye.1254'
    WHERE datcollate = 'Turkish_Turkey.1254';

- stop the PostgreSQL service

- edit "postgresql.conf" and change "Turkish_Turkey.1254" to "Turkish_Türkiye.1254" everywhere

- install the Windows update

- start the service

- in case the collation changed, REINDEX all indexes on string expressions

A safer option would be to dump the database before the upgrade
and restore the dump to a newly created cluster after the upgrade.

If you have used "Turkish_Turkey.1254" as explicit column collation
or somewhere else in your code, you will still run into problems.

This should really be fixed by Microsoft.

Yours,
Laurenz Albe



pgsql-bugs by date:

Previous
From: Pavel Stehule
Date:
Subject: Re: BUG #18196: Databases Created in Turkish Language Will Not Run on the Latest Version of Windows
Next
From: Halil Han Badem
Date:
Subject: Re: BUG #18196: Databases Created in Turkish Language Will Not Run on the Latest Version of Windows