Re: BUG #18196: Databases Created in Turkish Language Will Not Run on the Latest Version of Windows

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