Thread: Windows installation problem at post-install step

On 7/21/24 09:16, Ertan Küçükoglu wrote:
> Hello,
> 
> I am trying to install posgreql-16.3-2-windows-x64.exe on Windows 10 
> English VM with all updates installed and up to date.

What is the host OS and version?

What is the locale in the VM?

> 
> During installation I get an error message “Problem running post-install 
> step. Installation may not complete correctly The database cluster 
> initialization failed.”
> 
> I attached the compressed installation log file just in case. What I see 
> relevant is below line
> 
> The database cluster will be initialized with locale "Turkish_Trkiye.1254".
> 
> The cscript command line has all characters correctly logged in UTF8 
> encoding, but the above is not actually correct. It should read 
> “Turkish_Türkiye.1254” the “ü” (u with double dot at top) character is 
> not correct.
> 
> I suspect that is the main reason for the cluster creation to fail. I 
> don’t know how I can manually fix this.
> 
> I tried to reach techsupport@enterprisedb.com 
> <mailto:techsupport@enterprisedb.com> but got no response for almost two 
> weeks now.
> 
> Any help would be appreciated.
> 
> Thanks & Regards,
> 
> Ertan
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 7/21/24 10:21, Ertan Küçükoglu wrote:
> Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>>, 21 Tem 2024 Paz, 20:04 tarihinde 
> şunu yazdı:
> 
>     On 7/21/24 09:16, Ertan Küçükoglu wrote:
>      > Hello,
>      >
>      > I am trying to install posgreql-16.3-2-windows-x64.exe on Windows 10
>      > English VM with all updates installed and up to date.
> 
>     What is the host OS and version?
> 
>     What is the locale in the VM?
> 
> 
> Host OS is Windows 11 English. Display language is English. Country is 
> Türkiye and everything else is set to US English.
> I am trying to install PostgreSQL on Windows 10 64bit English. 
> Everything including the country on the guest OS is also set to US English.

What happens if you set the VM to Türkiye and install?

> 
> Thanks & Regards,
> Ertan

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 7/21/24 10:52, Ertan Küçükoglu wrote:
> Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>>, 21 Tem 2024 Paz, 20:34 tarihinde 
> şunu yazdı:
> 
> 
>     What happens if you set the VM to Türkiye and install?
> 
> 
> Problem still exists even if I set everything to Türkiye and Turkish.
> 1- I tried to manually select default locale to "Turkey, Türkiye"
> 2- I tried to install using [Default locale]
> both of above fails and both installation log files have identical wrong 
> text "Turkish_T rkiye.1254"

I don't know enough about Windows locales and the EDB installer to be of 
further help in that direction.

Is it feasible to install a Linux VM and install Postgres there?

> 
> Thanks & Regards,
> Ertan

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 7/21/24 12:00, Ertan Küçükoglu wrote:
> Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>>, 21 Tem 2024 Paz, 21:48 tarihinde 
> şunu yazdı:
> 
>     I don't know enough about Windows locales and the EDB installer to
>     be of
>     further help in that direction.
> 
>     Is it feasible to install a Linux VM and install Postgres there?
> 
> 
> My main purpose was and still is to reach EDB people using the forum and 
> let them know about the problem.
> I believe it is something to be fixed for future installations. I would 
> like to provide additional information if needed.

You could try a back door method and post here:

https://www.postgresql.org/list/pgadmin-support/

pgAdmin comes from EDB also, maybe someone on that list could pass your 
issue on.

> 
> On the other hand, I have a backup taken on another Windows system that 
> I need to restore after installation.
> I am not sure if it will be restored without any issues if I am to use a 
> Linux VM. I will try.

If the backup was done using pg_dump it should work. If you are talking 
about a file level backup then it would not work.

> 
> Thanks for the help.
> 
> Regards,
> Ertan
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Mon, Jul 22, 2024 at 7:29 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> On 7/21/24 12:00, Ertan Küçükoglu wrote:
> > My main purpose was and still is to reach EDB people using the forum and
> > let them know about the problem.
> > I believe it is something to be fixed for future installations. I would
> > like to provide additional information if needed.
>
> You could try a back door method and post here:
>
> https://www.postgresql.org/list/pgadmin-support/
>
> pgAdmin comes from EDB also, maybe someone on that list could pass your
> issue on.

I guess this is where EDB installer issues should go:

https://github.com/EnterpriseDB/edb-installers/issues

It seems like there are about 3 different problems associated with the
new Turkish_Türkiye.1254 locale name:

1. EDB's installer apparently has a problem with the encoding of the
name of the locale itself.  Looking at your log file with my pager, it
shows:

The database cluster will be initialized with locale
"Turkish_T<U+0081>rkiye.1254".

I think that means that it had the name of the locale encoded as
"CP437" at some point (where ü is 0x81, apparently[1]), but then
somewhere it was reencoded to the sequence 0xc2 0x81 (shown by my
pager as <U+0081>), which is nonsense.  The way to get there would be
to believe falsely that the source encoding was Latin1, I guess.

I'm not even sure what encoding it should be giving to initdb (maybe
the ACP of your system?), and in fact it's a bit undefined for
PostgreSQL at least, but that seems to be double-confused.  I suspect
the solution to this might be for  EDB's installer to somehow convert
your selected language to the modern short code format, like "tr-TR".
Those are pure ASCII.  I don't know where it should get the list from.

2.  Some existing database clusters which had been installed with the
name "Turkish_Turkey.1254" became unstartable when the OS upgrade
renamed that locale to "Turkish_Türkiye.1254".  I'm trying to provide
a pathway[2] to fix such systems in core PostgreSQL in the next minor
release.  Everyone affected probably already found another way but at
least next time a country is renamed this might help with the next
point too.

3.  I'd also like to teach initdb to use BCP47 names like "tr-TR"
instead of those names by default (ie if you don't specify a locale
name explicitly), and have proposed that before[3] but it hasn't gone
in due to lack of testing/reviews from Windows users.  It seems like
that doesn't matter much in practice to all the people using the
popular EDB installer, since it apparently takes control of picking
the locale and explicitly passes it in (and screws up the encoding as
we have now learned).

As for your immediate problem, you can also use initdb.exe directly to
set up a cluster, and tell it to use locale tr-TR.  I can't recommend
all the switches you'd need to pass it for best compatibility with the
EDB GUI tools though, but maybe the ones from your log.

[1] https://en.wikipedia.org/wiki/%C3%9C#Computing_codes
[2]
https://www.postgresql.org/message-id/flat/CA%2BhUKGJTOgnTzu4VD6Am0X6g67atkQHFVk%2BC-w5wkGrGiao-%3DQ%40mail.gmail.com#556557efd6b83cd7a336b62507efe347
[3]
https://www.postgresql.org/message-id/flat/CA%2BhUKGJ%3DXThErgAQRoqfCy1bKPxXVuF0%3D2zDbB%2BSxDs59pv7Fw%40mail.gmail.com

On Mon, Jul 22, 2024 at 11:58 AM Ertan Küçükoglu
<ertan.kucukoglu@gmail.com> wrote:
> Thomas Munro <thomas.munro@gmail.com>, 21 Tem 2024 Paz, 23:27 tarihinde şunu yazdı:
>> 2.  Some existing database clusters which had been installed with the
>> name "Turkish_Turkey.1254" became unstartable when the OS upgrade
>> renamed that locale to "Turkish_Türkiye.1254".  I'm trying to provide
>> a pathway[2] to fix such systems in core PostgreSQL in the next minor
>> release.  Everyone affected probably already found another way but at
>> least next time a country is renamed this might help with the next
>> point too.
>
> I was also hit by that OS update.
> There is a Microsoft tool for creating a locale installer
> https://www.microsoft.com/en-us/download/details.aspx?id=41158
> Using that tool and adding a second locale Turkish_Turkey.1254 (name before Microsoft update) in the OS can fix your
brokenPostgreSQL. 
> I believe most people simply choose this path.
> There are also several blogs/articles written in Turkish about the problem.

If that's easy and good enough then maybe I should abandon that
on-the-fly renaming patch and we should just do a little documentation
note...

>> 3.  I'd also like to teach initdb to use BCP47 names like "tr-TR"
>> instead of those names by default (ie if you don't specify a locale
>> name explicitly), and have proposed that before[3] but it hasn't gone
>> in due to lack of testing/reviews from Windows users.  It seems like
>> that doesn't matter much in practice to all the people using the
>> popular EDB installer, since it apparently takes control of picking
>> the locale and explicitly passes it in (and screws up the encoding as
>> we have now learned).
>
> If I am not mistaken BCP47 names are already used in Linux systems.
> Using them would make PostgreSQL use the same locale names across Linux and Windows systems.

Not exactly.  POSIX systems use
[language[_territory][.codeset][@modifier]], but POSIX doesn't say
what any of those components are[1] (are they ISO country codes?
English words?  Hieroglyphs?), so, curiously, those Windows names like
"English_United States.1252" are probably POSIX-conforming.  Every
real POSIX system of course uses ISO language and country codes these
days (though I still recall other names being used years ago), so they
look similar to the simpler kinds of BCP47 tags, which are just
language-country with the same ISO codes but a different separator.
They diverge further once you get into the finer points with more
components.  Incidentally that lack of standardisation is the reason
you can't say that the glibc ".utf8" ending is "wrong", even though it
is obviously stupid :-p (all systems I know accept .UTF-8, 'cause
that's what Ken Thompson, Rob Pike and the Unicode standard called
it).  I suspect that Windows accepts the POSIX style en_US too, but
it's not what the manual tells you to use.

But really we shouldn't have to know or care how locales are named; we
should get the names from the OS in the first place, and then we
should remember them and give them back to the OS at the right times.
The two problems here is that Windows has two kinds, one unstable over
time and with illegal (for us) characters in the name, and one stable;
we need to find all the places where the old unstable ones can get
into our system, and block them off.  I'm aware of two places now: the
EDB installer, and initdb's default for people who run it on the
command line with giving an explicit name.

> I can help with the testing part. Let me know the details, please.

Thanks!  I will rebase that patch, and CC you on the thread.

[1] https://pubs.opengroup.org/onlinepubs/9699919799/basedefs/V1_chap08.html

On 7/22/24 03:10, Ertan Küçükoglu wrote:
> Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>>, 21 Tem 2024 Paz, 22:29 tarihinde 
> şunu yazdı:
> 
>     If the backup was done using pg_dump it should work. If you are talking
>     about a file level backup then it would not work.
> 
> 
> Backup file is from a cluster backup taken using pg_dumpall.
> When I try to restore it on Linux, I get below errors
> 
> psql:/cluster.dump.sql:88: ERROR:  database "template1" does not exist
> psql:/cluster.dump.sql:93: ERROR:  invalid LC_COLLATE locale name: 
> "Turkish_Turkey.1254"
> HINT:  If the locale name is specific to ICU, use ICU_LOCALE.
> psql:/cluster.dump.sql:96: ERROR:  database "template1" does not exist
> psql:/cluster.dump.sql:98: error: \connect: connection to server on 
> socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  database 
> "template1" does not exist
> 
> I am not sure if there is a way to change the locale on restore.
> I am not sure about the "database "template1" does not exist" error 
> either. Maybe it is because the locale is missing.

Provide the following info:

1) Linux distro and version.

2) How did you install Postgres?

3) Versions of Postgres that was dumped from and restored to.

4) How did you initdb the Postgres cluster?

5) Can you connect to cluster using psql?

> 
> Thanks & Regards,
> Ertan

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 7/22/24 09:51, Ertan Küçükoglu wrote:
> Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>>, 22 Tem 2024 Pzt, 17:49 tarihinde 
> şunu yazdı:
> 
>     Provide the following info:
> 
>     1) Linux distro and version.
> 
>     2) How did you install Postgres?
> 
>     3) Versions of Postgres that was dumped from and restored to.
> 
>     4) How did you initdb the Postgres cluster?
> 
>     5) Can you connect to cluster using psql?
> 
> 
> 1- Debian 12.6
> 2- apt install postgresql-16 (from postgresql.org 
> <http://postgresql.org> directly)
> 3- Dumped from version 16.3 on Windows and restore tried on 16.3 on 
> Linux Debian
> 4- apt install did the initialization. Locale is en-US.UTF8
> 5- Before my restore trial yes. After a restore trial, the cluster was 
> broken. I had to uninstall PostgreSQL and reinstall again. I have access 
> to the cluster now.

When you connect using psql do you see template0, template1 and postgres 
when you do \l?

Does the restore work?

> 
> Thanks & Regards,
> Ertan

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 7/22/24 10:09 AM, Ertan Küçükoglu wrote:
> Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>>, 22 Tem 2024 Pzt, 20:04 tarihinde 
> şunu yazdı:
> 
>     When you connect using psql do you see template0, template1 and
>     postgres
>     when you do \l?
> 
> 
> Yes
> postgres=# \l
>                                                         List of databases
>     Name    |  Owner   | Encoding | Locale Provider |   Collate   |   
>   Ctype    | ICU Locale | ICU Rules |   Access privileges
>
-----------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
>   postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | 
> en_US.UTF-8 |            |           |
>   template0 | postgres | UTF8     | libc            | en_US.UTF-8 | 
> en_US.UTF-8 |            |           | =c/postgres          +
>             |          |          |                 |             |     
>          |            |           | postgres=CTc/postgres
>   template1 | postgres | UTF8     | libc            | en_US.UTF-8 | 
> en_US.UTF-8 |            |           | =c/postgres          +
>             |          |          |                 |             |     
>          |            |           | postgres=CTc/postgres
> (3 rows)
> 
>     Does the restore work?
> 
> 
> Restore fails and complaints about the Windows locale name.
> Moreover, it is a cluster backup and restore deletes template1 which 
> breaks psql connection.

What is the command you use to restore the pg_dumpall file?

template1 should not be dropped in the pg_dumpall file.

Is there output that shows that happening?

Was template1 dropped in the Windows Postgres instance?

> I need to remove postgresql and cluster for good and install back to fix 
> that.
> 
> Thanks & Regards,
> Ertan
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

We

On 7/22/24 10:51 AM, Ertan Küçükoglu wrote:
> Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>>, 22 Tem 2024 Pzt, 20:37 tarihinde 
> şunu yazdı:
> 
>     What is the command you use to restore the pg_dumpall file?
> 
> 
> within psql I run \i <dump_file_name>
> 
>     template1 should not be dropped in the pg_dumpall file.
> 
>     Is there output that shows that happening?
> 
> 
> --
> -- Databases
> --
> 
> --
> -- Database "template1" dump
> --
> 
> --
> -- PostgreSQL database dump
> --
> 
> -- Dumped from database version 16.3
> -- Dumped by pg_dump version 16.3
> 
> SET statement_timeout = 0;
> SET lock_timeout = 0;
> SET idle_in_transaction_session_timeout = 0;
> SET client_encoding = 'UTF8';
> SET standard_conforming_strings = on;
> SELECT pg_catalog.set_config('search_path', '', false);
> SET check_function_bodies = false;
> SET xmloption = content;
> SET client_min_messages = warning;
> SET row_security = off;
> 
> UPDATE pg_catalog.pg_database SET datistemplate = false WHERE datname = 
> 'template1';
> DROP DATABASE template1;
> --
> -- Name: template1; Type: DATABASE; Schema: -; Owner: postgres
> --
> 
> CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UTF8' 
> LOCALE_PROVIDER = libc LOCALE = 'Turkish_Turkey.1254';
> 
> Above lines are taken from the dump file itself and it does indeed drop 
> the template1. I think this is because this is a cluster dump.

It is because you specified -c to the pg_dumpall command. This cleans 
the database you are restoring to by dropping the existing databases, 
roles and tablespaces before restoring the objects in the file

I am getting out of my depth here, but I am pretty sure that:

ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'Turkish_Turkey.1254'

is not going to work. That you will need to change the locale to a 
Turkish UTF8 name.

> Later it tries to create a new template1 and that command causes an 
> error because of Windows locale name.
> 
> 
>     Was template1 dropped in the Windows Postgres instance?
> 
> 
> No. It still is there.
> 
> BTW dump is taken using the below command line on Windows system.
> "C:\Program Files\PostgreSQL\16\bin\pg_dumpall.exe" -U postgres -h 
> 127.0.0.1 -p 5432 -c -f "c:\yedek\cluster.dump.sql"
> 
> Thanks & Regards,
> Ertan

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 7/22/24 11:48, Ertan Küçükoglu wrote:
> Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>>, 22 Tem 2024 Pzt, 21:10 tarihinde 
> şunu yazdı:
> 
>     I am getting out of my depth here, but I am pretty sure that:
> 
>     ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'Turkish_Turkey.1254'
> 
>     is not going to work. That you will need to change the locale to a
>     Turkish UTF8 name.
> 
> 
> I added tr_TR.UTF-8 to the system locales.
> I also changed all Turkish_Turkey.1254 to tr_TR.UTF-8 in the dump file.
> Unfortunately, I found that there are some databases created using 
> WIN1254 encoding.
> I do not know what I should do with them. I don't think simply changing 
> WIN1254 -> UTF8 will work.
> I will probably wait for a working Windows installer.

 From previous post of yours:

"I was also hit by that OS update.
There is a Microsoft tool for creating a locale installer
https://www.microsoft.com/en-us/download/details.aspx?id=41158
Using that tool and adding a second locale Turkish_Turkey.1254 (name before
Microsoft update) in the OS can fix your broken PostgreSQL.
I believe most people simply choose this path.
There are also several blogs/articles written in Turkish about the problem."

Why not use that?

> 
> Thanks & Regards,
> Ertan

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 7/22/24 13:15, Ertan Küçükoglu wrote:
> Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>>, 22 Tem 2024 Pzt, 22:56 tarihinde 
> şunu yazdı:
> 
> 
>     Why not use that?
> 
> There was already an installed PostgreSQL just failing to start.
> I used that localization tool and it started again.
> This was the production system where Windows update changed the name of 
> the Turkish localization.
> 
> I am trying to set myself a development and testing system now.
> I lost my old VMs and need to install PostgreSQL fresh.
> Unfortunately, the installer has issues. No cluster being initialized, 
> no service is installed.

It would seem to me the process would be:

1) Create Windows VM

2) Run the localizer tool in the VM to get the old locale name in place.

3) Run the installer.

> Restoring a Windows backup into Linux system failed due to encoding 
> problems.
> 
> I cannot clone from production systems. They are using different 
> virtualization.
> My internet connection cannot handle the raw disk image transfer anyway.
> 
> Thanks & Regards,
> Ertan
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 7/22/24 13:34, Ertan Küçükoglu wrote:
> Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>>, 22 Tem 2024 Pzt, 23:18 tarihinde 
> şunu yazdı:
> 
>     It would seem to me the process would be:
> 
>     1) Create Windows VM
> 
>     2) Run the localizer tool in the VM to get the old locale name in place.
> 
>     3) Run the installer.
> 
> 
> I just tested that and failed at the same step.
> 
> My understanding is
> - The installer has localization options to choose from or default 
> localization (which is Turkish_Türkiye.1254 in new VMs, it was 
> Turkish_Turkey.1254 in the past).
> - I cannot make the installer select something else other than 
> Turkish_Türkiye.1254 it is hardcoded in it. So Turkish_Turkey.1254 
> cannot be selected in the installer even if it is installed in the OS.
> - Even if I can add a new locale to the OS, I cannot make it the default.

How about taking:

C:\Windows\System32\cscript //NoLogo "C:\Program 
Files\PostgreSQL\16/installer/server/initcluster.vbs" "NT 
AUTHORITY\NetworkService" "postgres" "****" 
"C:\Users\User1\AppData\Local\Temp/postgresql_installer_cd79fad8b7" 
"C:\Program Files\PostgreSQL\16" "C:\DATA_PG16" 5432 "Turkish,Türkiye"

and changing  "Turkish,Türkiye" to "Turkish_Turkey.1254"

> 
> Thanks & Regards,
> Ertan

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Mon, Jul 22, 2024 at 11:51 PM Sandeep Thakkar
<sandeep.thakkar@enterprisedb.com> wrote:
> EDB's windows installer gets the locales on the system using the
https://github.com/EnterpriseDB/edb-installers/blob/REL-16/server/scripts/windows/getlocales/getlocales.cppand then
substitutesome patterns (https://github.com/EnterpriseDB/edb-installers/blob/REL-16/server/pgserver.xml.in#L2850) I'm
notsure why we do that but that is the old code and probably @Dave Page  may know but I'm not sure if that piece of
codeis responsible for this change in encoding in this case. 

Ah, so it's calling EnumSystemLocales().  Interestingly, the
documentation for that function says:

"Note  For interoperability reasons, the application should prefer the
EnumSystemLocalesEx function to EnumSystemLocales because Microsoft is
migrating toward the use of locale names instead of locale identifiers
for new locales. Any application that will be run only on Windows
Vista and later should use EnumSystemLocalesEx."

That seems to be talking about this exact issue, that we're supposed
to be using "locale names".  I'm a little confused about the
terminology for the various types of names and identifiers but if you
follow the link to a example program[1] you can see that it's talking
about the BCP47 "en-US" kind, that we want.  (That quote makes it
sound like a new thing, but Vista came out ~17 years ago.)

So one idea would be that in v18, we not only change initdb.exe to
pick a BCP47 locale name by default as I proposed in that other
thread[2], but also in the v18 version of the EDB installer you
consider switching that code over to EnumSystemLocalesEx().  Then we
can start to kiss goodbye to the bad old names.  People would still
propagate them into the future with pg_upgrade I guess, and it'd be up
to users to replace them by updating their catalogs manually.  Does
that make sense?

[1] https://learn.microsoft.com/en-us/windows/win32/intl/nls--name-based-apis-sample
[2]
https://www.postgresql.org/message-id/flat/CA%2BhUKGJ%3DXThErgAQRoqfCy1bKPxXVuF0%3D2zDbB%2BSxDs59pv7Fw%40mail.gmail.com

On Mon, Aug 5, 2024 at 8:50 PM Sandeep Thakkar
<sandeep.thakkar@enterprisedb.com> wrote:
> This issue is seen only on v16 and not the back branches (tested on 15 and 14) and also confirmed by @Ertan Küçükoglu
athttps://github.com/EnterpriseDB/edb-installers/issues/127#issuecomment-2268371442 

Does that mean you can reproduce the problem with initdb.exe directly
in a shell?  That is, remove the EDB installer from the picture and
compare v15 and v16 with the exact command line options that
initcluster.vbs is using, or perhaps just:

initdb.exe --locale="Turkish,Türkiye" --encoding=UTF-8 -D pgdata

. o O (Why does that locale name have a comma?)  If v15 works and v16
breaks, perhaps you could try comparing the output with the attached
patch?  It will give a hex dump of the contents of the locale name at
various points in the program, to see if/where it was corrupted, which
might also be a bit less confusing than looking at script output via
email (I don't even know how many onion layers of transcoding are
involved...)

On 2024-08-06 16:06:51 +0530, Sandeep Thakkar wrote:
> On Tue, Aug 6, 2024 at 10:57 AM Thomas Munro <thomas.munro@gmail.com> wrote:
>
>     On Mon, Aug 5, 2024 at 8:50 PM Sandeep Thakkar
>     <sandeep.thakkar@enterprisedb.com> wrote:
>     > This issue is seen only on v16 and not the back branches (tested on 15
>     and 14) and also confirmed by @Ertan Küçükoglu at https://github.com/
>     EnterpriseDB/edb-installers/issues/127#issuecomment-2268371442
>
>     Does that mean you can reproduce the problem with initdb.exe directly
>     in a shell?  That is, remove the EDB installer from the picture and
>     compare v15 and v16 with the exact command line options that
>     initcluster.vbs is using, or perhaps just:
>
>     initdb.exe --locale="Turkish,Türkiye" --encoding=UTF-8 -D pgdata
>
>
> yes, here is the output:
>
>     c:\Program Files\PostgreSQL\16\bin>initdb.exe --encoding=UTF-8 -A
>     scram-sha-256 -U postgres -D "c:\Program Files\PostgreSQL\16\data" --locale
>     "Turkish,Türkiye" -W
>     The files belonging to this database system will be owned by user
>     "sandeep".
>     This user must also own the server process.
>
>     The database cluster will be initialized with locale
>     "Turkish_Türkiye.1254".

I assume that "1254" here is the code page.
But you specified --encoding=UTF-8 above, so your default locale uses a
different encoding than the template databases. I would expect that to
cause problems if the template databases contain any charecters where
the encodings differ (such as "ü" in the locale name).


>     c:\Program Files\PostgreSQL\15\bin>initdb.exe --encoding=UTF-8 -A
>     scram-sha-256 -U postgres -D "c:\Program Files\PostgreSQL\15\data" --locale
>     "Turkish,Türkiye" -W
>     XXX debug raw: getopt optarg  = "Turkish,Türkiye"
>     XXX debug hex: getopt optarg  = { 54 75 72 6b 69 73 68 2c 54 fc 72 6b 69 79
>     65 }
>     XXX debug txt: getopt optarg  = { T  u  r  k  i  s  h  ,  T  ?  r  k  i  y
>      e  }

This also looks like window-1254 (or at least some ISO-8859 variant) to
me.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

On Tue, Aug 6, 2024 at 10:38 PM Sandeep Thakkar
<sandeep.thakkar@enterprisedb.com> wrote:
> On Tue, Aug 6, 2024 at 4:06 PM Sandeep Thakkar <sandeep.thakkar@enterprisedb.com> wrote:

[v15]

>>> XXX debug raw: setup_locale_encoding  = "Turkish_Türkiye.1254"
>>> XXX debug hex: setup_locale_encoding  = { 54 75 72 6b 69 73 68 5f 54 fc 72 6b 69 79 65 2e 31 32 35 34 }
>>> XXX debug txt: setup_locale_encoding  = { T  u  r  k  i  s  h  _  T  ?  r  k  i  y  e  .  1  2  5  4  }
>>> The database cluster will be initialized with locale "Turkish_Türkiye.1254".

[v16]

>>> XXX debug raw: setup_locale_encoding  = "Turkish_Türkiye.1254"
>>> XXX debug hex: setup_locale_encoding  = { 54 75 72 6b 69 73 68 5f 54 fc 72 6b 69 79 65 2e 31 32 35 34 }
>>> XXX debug txt: setup_locale_encoding  = { T  u  r  k  i  s  h  _  T  ?  r  k  i  y  e  .  1  2  5  4  }
>>> The database cluster will be initialized with locale "Turkish_Türkiye.1254".

OK so we see that the "Turkish,Türkiye" -> "Turkish_Türkiye.1254"
transformation happens in the "cannonname" step, and then the final
values are identical between the versions.

>>> performing post-bootstrap initialization ... child process was terminated by exception 0xC0000409

If I understand correctly that's abort(), so can you please run it
with -d so we can maybe see some information about that?  Also -n
might be useful to stop it from deleting the data directory at the end
in case something useful for diagnosis is in there.

On Tue, Aug 6, 2024 at 11:44 PM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
> I assume that "1254" here is the code page.
> But you specified --encoding=UTF-8 above, so your default locale uses a
> different encoding than the template databases. I would expect that to
> cause problems if the template databases contain any charecters where
> the encodings differ (such as "ü" in the locale name).

It's weird, but on Windows, PostgreSQL allows UTF-8 encoding with any
locale, and thus apparent contradictions:

    /* See notes in createdb() to understand these tests */
    if (!(locale_enc == user_enc ||
          locale_enc == PG_SQL_ASCII ||
          locale_enc == -1 ||
#ifdef WIN32
          user_enc == PG_UTF8 ||
#endif
          user_enc == PG_SQL_ASCII))
    {
        pg_log_error("encoding mismatch");

... and createdb's comments say that is acceptable because:

 * 3. selected encoding is UTF8 and platform is win32. This is because
 * UTF8 is a pseudo codepage that is supported in all locales since it's
 * converted to UTF16 before being used.

At the time PostgreSQL was ported to Windows, UTF-8 was not a
supported encoding in "char"-based system interfaces like strcoll_l(),
and the port had to convert to "wchar_t" interfaces and call (in that
example) wcscoll_l().  On modern Windows it is, and there are two
locale names, with and without ".UTF-8" suffix (cf. glibc systems that
have "en_US" and "en_US.UTF-8" where the suffix-less version uses
whatever traditional encoding was used for that language before UTF-8
ate the world).

If we were doing the Windows port today, we'd probably not have that
special case for Windows, and we wouldn't have the wchar_t
conversions.  Then I think we'd allow only:

--locale=tr-TR (defaults to --encoding=WIN1254)
--locale=tr-TR --encoding=WIN1254
--locale-tr-TR.UTF-8
--locale=tr-TR.UTF-8 --encoding=UTF-8

If we come up with an automated (or even manual but documented) way to
perform the "Turkish_Türkiye.1254" -> "tr-TR" upgrade as Dave was
suggesting upthread, we'll probably want to be careful to tidy up
these contradictory settings.  For example I guess that American
databases initialised by EDB's installer must be using
--locale="English_United States.1252" and --encoding=UTF-8, and should
be changed to "en-US.UTF-8", while those initialised by letting
initdb.exe pick the encoding must be using --locale="English_United
States.1252" and --encoding=WIN1252 (implicit) and should be changed
to "en-US" to match the WIN1252 encoding.

Only if we did that update would we be able to consider removing the
extra UTF-16 conversions that are happening very frequently inside
PostgreSQL code, which is a waste of CPU cycles and programmer sanity.
(But that's all just speculation from studying the locale code -- I've
never really used Windows.)

Thanks.  The log didn't offer any more clues, and my colleague David R
has Windows and knows how to work its debugger so we sat down together
and chased this down (thanks David!).

1.  It is indeed calling abort(), but it's not a PANIC or Assert() in
PostgreSQL, it's an assertion inside Windows' own setlocale():

minkernel\crts\ucrt\src\appcrt\convert\mbstowcs.cpp(245) : Assertion
failed: (pwcs == nullptr && sizeInWords == 0) || (pwcs != nullptr &&
sizeInWords > 0)

2.  It is indeed confused about the encoding of the string
"Turkish_Türkiye.1254" itself, and works fine if you use "tr-TR".

3.  It doesn't happen on 15, because 16 added a key ingredient:

commit bf03cfd162176d543da79f9398131abc251ddbb9
Author: Peter Eisentraut <peter@eisentraut.org>
Date:   Tue Jan 3 14:21:40 2023 +0100

    Windows support in pg_import_system_collations

That causes it to spin through a bunch of system locales and switch to
them, and the first one is "aa".  After it calls:

    setlocale(2, "aa");

... then the next call to restore the previous locale is something like:

    setlocale(2, "Turkish_T\252rkiye.1254");

(That \252 == 0xfc probably depends on your system's default
encoding.)  It doesn't like that name anymore, and aborts.  A minimal
program with just those two lines shows that.

It appears that after switching to "aa", it interprets the string
passed to the next call to setlocale() as some other encoding
(probably UTF-8, I dunno).  I don't know why it doesn't fail and
return NULL, but there is a more general point that it's a bit bonkers
to use non-ASCII byte sequences in the library calls that are used to
control how non-ASCII byte sequences are interpreted.  Maybe it can be
done if you're careful, but in particular a naive save-and-restore
sequence just won't work.

I guess a save-and-restore done with wsetlocale() could fix that.  But
I decline to work on that, we need less Windows kludgery in the tree,
not more.  I think a better answer is "don't do that".

Really, we *have* to chase all these non-BCP-47 locales out of the
installer (I hope you can work on that?), out of PostgreSQL (testers
wanted[1]), and out of the world's existing clusters (maybe with
Dave's pg_upgrade idea, someone would need to write a patch, or maybe
someone could write a stand-alone locale migration program that just
connects to a cluster and (using some authoritative source, that's the
key bit to research) and replaces bad old names with nice new ones).

[1] https://www.postgresql.org/message-id/flat/CA+hUKGJ=XThErgAQRoqfCy1bKPxXVuF0=2zDbB+SxDs59pv7Fw@mail.gmail.com