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

Adrian Klaver <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.

Thanks & Regards,
Ertan

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

Adrian Klaver <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"

Thanks & Regards,
Ertan

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

Adrian Klaver <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.

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.

Thanks for the help.

Regards,
Ertan

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]https://en.wikipedia.org/wiki/%C3%9C#Computing_codes), 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]/messages/by-id/CA+hUKGJTOgnTzu4VD6Am0X6g67atkQHFVk+C-w5wkGrGiao-=Q@mail.gmail.com 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]/messages/by-id/CA+hUKGJ=XThErgAQRoqfCy1bKPxXVuF0=2zDbB+SxDs59pv7Fw@mail.gmail.com 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]: /messages/by-id/CA+hUKGJTOgnTzu4VD6Am0X6g67atkQHFVk+C-w5wkGrGiao-=Q@mail.gmail.com
[3]: /messages/by-id/CA+hUKGJ=XThErgAQRoqfCy1bKPxXVuF0=2zDbB+SxDs59pv7Fw@mail.gmail.com

Thomas Munro <thomas.munro@gmail.com>, 21 Tem 2024 Paz, 23:27 tarihinde
şunu yazdı:

I guess this is where EDB installer issues should go:

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

Thanks. I just added a new issue there.

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 broken PostgreSQL.
I believe most people simply choose this path.
There are also several blogs/articles written in Turkish about the problem.

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.
I can help with the testing part. Let me know the details, please.

Thanks & Regards,
Ertan

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 broken PostgreSQL.
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]https://pubs.opengroup.org/onlinepubs/9699919799/basedefs/V1_chap08.html (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

Adrian Klaver <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.

Thanks & Regards,
Ertan

Hi,

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.cpp
and
then substitute some patterns (
https://github.com/EnterpriseDB/edb-installers/blob/REL-16/server/pgserver.xml.in#L2850)
I'm not sure why we do that but that is the old code and probably @Dave Page
<dave.page@enterprisedb.com> may know but I'm not sure if that piece of
code is responsible for this change in encoding in this case.

When I checked the installation log shared by Ertan, I do see that the
locale passed to initcluster script is the same as returned by the
getlocales executable.

Executing 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" 0

On Mon, Jul 22, 2024 at 6:43 AM Thomas Munro <thomas.munro@gmail.com> wrote:

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 broken PostgreSQL.

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

--
Sandeep Thakkar

Hi,

On Mon, Jul 22, 2024 at 1:57 AM Thomas Munro <thomas.munro@gmail.com> wrote:

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.

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.cpp
and
then substitute some patterns (
https://github.com/EnterpriseDB/edb-installers/blob/REL-16/server/pgserver.xml.in#L2850)
I'm not sure why we do that but that is the old code and probably @Dave Page
<dave.page@enterprisedb.com> may know but I'm not sure if that piece of
code is responsible for this change in encoding in this case.

When I checked the installation log shared by Ertan, I do see that the
locale passed to initcluster script is the same as returned by the
getlocales executable.

Executing 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" 0

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]
/messages/by-id/CA+hUKGJTOgnTzu4VD6Am0X6g67atkQHFVk+C-w5wkGrGiao-=Q@mail.gmail.com
[3]
/messages/by-id/CA+hUKGJ=XThErgAQRoqfCy1bKPxXVuF0=2zDbB+SxDs59pv7Fw@mail.gmail.com

--
Sandeep Thakkar

On Mon, Jul 22, 2024 at 5:21 PM Sandeep Thakkar <
sandeep.thakkar@enterprisedb.com> wrote:

Hi,

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.cpp and
then substitute some patterns (
https://github.com/EnterpriseDB/edb-installers/blob/REL-16/server/pgserver.xml.in#L2850)
I'm not sure why we do that but that is the old code and probably @Dave
Page <dave.page@enterprisedb.com> may know but I'm not sure if that
piece of code is responsible for this change in encoding in this case.

When I checked the installation log shared by Ertan, I do see that the
locale passed to initcluster script is the same as returned by the
getlocales executable.

Executing 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" 0

Apology about the top posting. Please ignore this thread. I've replied to

another thread.

On Mon, Jul 22, 2024 at 6:43 AM Thomas Munro <thomas.munro@gmail.com>
wrote:

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 broken PostgreSQL.

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

--
Sandeep Thakkar

--
Sandeep Thakkar

Hi

On Mon, Jul 22, 2024 at 1:02 PM Sandeep Thakkar <
sandeep.thakkar@enterprisedb.com> wrote:

On Mon, Jul 22, 2024 at 5:21 PM Sandeep Thakkar <
sandeep.thakkar@enterprisedb.com> wrote:

Hi,

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.cpp and
then substitute some patterns (
https://github.com/EnterpriseDB/edb-installers/blob/REL-16/server/pgserver.xml.in#L2850)
I'm not sure why we do that but that is the old code and probably @Dave
Page <dave.page@enterprisedb.com> may know but I'm not sure if that
piece of code is responsible for this change in encoding in this case.

It was to work around limitations in the way we could return data from an
external program to BitRock InstallBuilder. I forget the precise details as
it was something like 15 years ago, but essentially BitRock couldn't read
output that contained (certain?) non-alphanumeric characters, so I had to
do that crazy encode/decode dance.

When I checked the installation log shared by Ertan, I do see that the
locale passed to initcluster script is the same as returned by the
getlocales executable.

Executing 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" 0

Apology about the top posting. Please ignore this thread. I've replied to

another thread.

On Mon, Jul 22, 2024 at 6:43 AM Thomas Munro <thomas.munro@gmail.com>
wrote:

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 broken PostgreSQL.

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

--
Sandeep Thakkar

--
Sandeep Thakkar

--
Dave Page
VP, Chief Architect, Database Infrastructure
EDB: https://www.enterprisedb.com

Sandeep Thakkar <sandeep.thakkar@enterprisedb.com>, 22 Tem 2024 Pzt, 15:01
tarihinde şunu yazdı:

When I checked the installation log shared by Ertan, I do see that the
locale passed to initcluster script is the same as returned by the
getlocales executable.

Executing 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" 0

That is log file line no 5544 and is cscript logging. There is no problem
here.
If you check log file line no 5606 you will see that the encoding is not
correct just before initdb
Maybe this is related to BAT file usage? I don't know.

Thanks & Regards,
Ertan

On Mon, Jul 22, 2024 at 5:52 PM Ertan Küçükoglu <ertan.kucukoglu@gmail.com>
wrote:

Sandeep Thakkar <sandeep.thakkar@enterprisedb.com>, 22 Tem 2024 Pzt,
15:01 tarihinde şunu yazdı:

When I checked the installation log shared by Ertan, I do see that the
locale passed to initcluster script is the same as returned by the
getlocales executable.

Executing 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" 0

That is log file line no 5544 and is cscript logging. There is no problem
here.
If you check log file line no 5606 you will see that the encoding is not
correct just before initdb
Maybe this is related to BAT file usage? I don't know.

Ah, I see it now. Let me take a closer look

Thanks & Regards,
Ertan

--
Sandeep Thakkar

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

Adrian Klaver <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 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.

Thanks & Regards,
Ertan