CODEWITHSUNDEEP
databasepostgresqldatabase-connectionwindows-updateturkish
Mehmet Ali Öden
Mehmet Ali Öden

Reputation: 111

PostgreSQL Error: Database Locale Incompatibility Following Windows Update (From Turkish_Turkey.1254 to Turkish_Türkiye.1254)

I'm encountering an issue while trying to connect to a PostgreSQL database in a Flask web application. I'm getting an error message like "Database connection failed." Upon investigating the source of the problem, I noticed that the language settings in the postgresql.conf file are mismatched. Previously, Turkish_Turkey.1254 language was used when the database was created, but with the latest Windows update, this language is no longer available, and instead, Turkish_Türkiye.1254 is being used.

I've tried updating the language settings in the postgresql.conf file, but the issue persists. I'm unsure of the steps to follow to get the PostgreSQL service running or how to resolve this language incompatibility.

I'm sharing the relevant code snippets and error messages below: postgresql.conf:

lc_messages = 'Turkish_Türkiye.1254'

lc_monetary = 'Turkish_Türkiye.1254'

lc_numeric = 'Turkish_Türkiye.1254'

lc_time = 'Turkish_Türkiye.1254'

Error Message: psycopg2.OperationalError: connection to server at "localhost" (::1), port 5432 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.

I've tried updating the language settings in the postgresql.conf file according to the guidance provided by PostgreSQL documentation, but the issue persists. I expected that updating the language settings to match the new system locale would resolve the connection error and allow me to successfully connect to the PostgreSQL database. However, despite these changes, I continue to encounter the same error message.

Upvotes: 2

Views: 1817

Answers (2)

Necip Sunmaz
Necip Sunmaz

Reputation: 1995

I'm releasing the package I created with Local Builder so that not everyone has to deal with Local Builder. You can overcome this language issue by installing this package directly. Those who are stuck in the zero installation phase can follow the steps below in order to install successfully.

New Installation:

  1. Successfully install PostgreSQL by selecting English / United States language.
  2. Run CMD as Administrator.
  3. Stop the PostgreSQL service running with the net stop postgresql-x64-17 command in CMD. (The 17 value written here and in the following commands is my Postgresql version. Enter whichever version you have installed, for example 16 etc.)
  4. Go to “C:\Program Files\PostgreSQL\17” directory and delete the data folder (I don't recommend it if you have a database in it) or move it by backing it up somewhere else.
  5. Install the package I provided (you can install it earlier).
  6. In CMD, cd “C:\Program Files\PostgreSQL\17\bin” to the directory where PostgreSQL is installed.
  7. Run .\initdb.exe -D “C:\Program Files\PostgreSQL\17\data” -U postgres -E UTF8 --locale=Turkish_Turkey.1254 in CMD and wait for the result. (This will reconfigure your data folder to the new language configuration).
  8. Restart your PostgreSQL by running net start postgresql-x64-17 in CMD.

Once you have successfully performed the above tasks, you can directly create a database with Turkish language support with the following SQL commands. If you wish, you can add Turkish support by moving or updating databases created in another language.

Creating a Turkish Language Supported Database:

CREATE DATABASE your_database_name 
  WITH OWNER = postgres 
       ENCODING = 'UTF8' 
       LC_COLLATE = 'Turkish_Turkey.1254' 
       LC_CTYPE = 'Turkish_Turkey.1254' 
       TABLESPACE = pg_default 
       CONNECTION LIMIT = -1;

Support Package:

Package: https://drive.google.com/file/d/1F9wAyUM8KxOL3JLmBeZZTNfTA6Qr8rmz/view?usp=sharing

VirusTotal: https://www.virustotal.com/gui/url/205768754eb568dad440909158e7b9e939dada74bc197c9af897ed7632337daf

Upvotes: 0

Medeni Baykal
Medeni Baykal

Reputation: 4333

I found a very crude solution; proceed at your own peril. A better solution might be to use Locale Builder 2.0 to create a new locale named Turkish_Turkey.1254 and install it on the system as prescribed here.

First, I verified the behavior I was suspecting using the following C++ program:

#include <locale.h>
#include <process.h>
#include <windows.h>
#include <stdio.h>
#include <time.h>

int main()
{
    printf("The locale is set to: %s\n", setlocale(LC_ALL, "Turkish.1254"));
    printf("The locale is set to: %s\n", setlocale(LC_ALL, "Turkish_Türkiye.1254"));
}

I received this output:

The locale is set to: Turkish_Türkiye.1254
The locale is set to: Turkish_Türkiye.1254

After seeing that Turkish.1254 and Turkish_Türkiye.1254 are identical on my server, I took the following steps:

  1. Uninstalled the offending update, which was KB5035857 for me.
  2. Connected to the database as a superuser and ran the following SQL statement: 
    UPDATE pg_database SET datcollate='Turkish.1254', datctype='Turkish.1254'
  3. Reinstalled the update.
  4. Ran following sql statement: 
    UPDATE pg_database SET datcollate='Turkish_Türkiye.1254', datctype='Turkish_Türkiye.1254'

Upvotes: 0

Related Questions