Subject: Re: Windows installation problem at post-install step

From: Adrian Klaver <[email-masked]>
Date: Sun, 21 Jul 2024 12:29:29 -0700

On 7/21/24 12:00, Ertan Küçükoglu wrote:
> Adrian Klaver <[email-masked] 
> <mailto:[email-masked]>>, 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
[email-masked]

---

From: Thomas Munro <[email-masked]>
Date: Mon, 22 Jul 2024 08:26:27 +1200

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

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

---

From: Ertan Küçükoglu <[email-masked]>
Date: Mon, 22 Jul 2024 13:10:24 +0300

Adrian Klaver <[email-masked]>, 21 Tem 2024 Paz, 22:29 tarihinde

> 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

---

From: Sandeep Thakkar <[email-masked]>
Date: Mon, 22 Jul 2024 17:30:54 +0530

Hi,

> 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
<[email-masked]>  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

> [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
>

-- 
Sandeep Thakkar

---

From: Adrian Klaver <[email-masked]>
Date: Mon, 22 Jul 2024 07:49:33 -0700

On 7/22/24 03:10, Ertan Küçükoglu wrote:
> <mailto:[email-masked]>>, 21 Tem 2024 Paz, 22:29 tarihinde 
> şunu yazdı:
> 
> 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 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?

---

From: Ertan Küçükoglu <[email-masked]>
Date: Mon, 22 Jul 2024 19:51:28 +0300

Adrian Klaver <[email-masked]>, 22 Tem 2024 Pzt, 17:49 tarihinde

> 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

---

From: Adrian Klaver <[email-masked]>
Date: Mon, 22 Jul 2024 10:04:42 -0700

On 7/22/24 09:51, Ertan Küçükoglu wrote:
> <mailto:[email-masked]>>, 22 Tem 2024 Pzt, 17:49 tarihinde 
> şunu yazdı:
> 
> 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
> 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?

---

From: Ertan Küçükoglu <[email-masked]>
Date: Mon, 22 Jul 2024 20:09:23 +0300

Adrian Klaver <[email-masked]>, 22 Tem 2024 Pzt, 20:04 tarihinde

> When you connect using psql do you see template0, template1 and postgres

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.
I need to remove postgresql and cluster for good and install back to fix
that.

Thanks & Regards,
Ertan

---

From: Adrian Klaver <[email-masked]>
Date: Mon, 22 Jul 2024 10:37:45 -0700

On 7/22/24 10:09 AM, Ertan Küçükoglu wrote:
> <mailto:[email-masked]>>, 22 Tem 2024 Pzt, 20:04 tarihinde 
> şunu yazdı:
> 
>     Name    |  Owner   | Encoding | Locale Provider |   Collate   |   
>   Ctype    | ICU Locale | ICU Rules |   Access privileges
> 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?

---

From: Ertan Küçükoglu <[email-masked]>
Date: Mon, 22 Jul 2024 20:51:30 +0300

Adrian Klaver <[email-masked]>, 22 Tem 2024 Pzt, 20:37 tarihinde

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

within psql I run \i <dump_file_name>

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

---

From: AC Gomez <[email-masked]>
Date: Mon, 22 Jul 2024 13:59:07 -0400

We

On Mon, Jul 22, 2024, 1:51 PM Ertan Küçükoglu <[email-masked]>
wrote:

> Adrian Klaver <[email-masked]>, 22 Tem 2024 Pzt, 20:37
> tarihinde şunu yazdı:
>
> 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.

---

From: Adrian Klaver <[email-masked]>
Date: Mon, 22 Jul 2024 11:10:06 -0700

On 7/22/24 10:51 AM, Ertan Küçükoglu wrote:
> <mailto:[email-masked]>>, 22 Tem 2024 Pzt, 20:37 tarihinde 
> şunu yazdı:
> 

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