Re: Re. Select with where condition times out

[Francisco Olarte]

Trying to trim to still relevant parts, as mail is becoming extremely
hard to read.

On Mon, 22 Jul 2024 at 07:08, sivapostg...@yahoo.com
 wrote:
> Actual Query:
>  select source_node_id, create_time from sym_data where table_name = 
> 'tx_combined_sales_header' and ((event_type = 'I' and row_data like 
> '"F92DD7AA237A45D99CA5741DF73EA3D1"%') or (event_type in ('U', 'D') and 
> pk_data like '"F92DD7AA237A45D99CA5741DF73EA3D1"')) and create_time >= 
> '2024-07-18 01:43:32.981' order by create_time desc

That is a complex query, you should probably insure tables are
properly analized, show your indexes and show explain, explain analyze
if possible, on both servers.

...
> I thought I'm clear. My bad.
>
> 2 computers were involved in total.  One Xeon Server with Windows 2019 
> Standard and other one is Intel i5 based Desktop with Windows 10.
> I took backup (pg_dump) from windows server machine.
> And restored in the same server as another database.  Now we have 2 databases 
> with identical data in Windows Server. The actual query (given above) is 
> taking more than 15 min in the original database and takes a second in the 
> restored database.

Assuming same cluster ( same postgres instance ) explain may shed some light.

> I have done Vacuum, Re-Index in the original database. No improvement. 
> Anything else that I can do to make the original database to perform just 
> like the restored database?

Insure analyze is the same in both. Test using explain. If you peruse
the archives you will notice explain output is what tells people what
the server is doing. IIRC explain (analyze,buffers) on both will show
how the query was done and where the time was spent.

> > I can easily replace the old database with the backup.  Is that only option?
> Ah, one clue. From the info I have in this and previous mails, that is
> the only option for me. Having more info someone may have ideas, but
...
> What else ?

Use explain, send appropriate info. You may have noticed there are few
responses to your mail. This is probably due to people not being able
to provide  too meaningful help with the data you provide and not
willing to try taking blind shots.

Postgres is a very complex piece of software, and even includes some
randomization when optimizing very complex queries, which is not your
case. With a query like yours having different times in similar
databases the first thing to do will be analyze both, explain analyze
on both, compare the results, to ensure both databases are doing the
same operation. If they are, then it is time to see why the old one
does it slower ( I assume you are not testing a busy production server
against an idling backup). If they do not, then the path to follow is
to compare plans and try to know why they differ.

> Regards.
> Francisco Olarte.
As an aside, I would personally appreciate it if you delete my
signature from the end of your message when replying to one sent by
me.

Francisco Olarte.

Re: Regarding publish_via_partiton_root with pglogical

[khan Affan]

Hi Durgamahesh

In pglogical extension, there is no direct equivalent to the
publish_via_partition_root option found in the built-in logical replication

you can achieve similar results when a new partition is added; you must add
it to the replication set using pglogical.replication_set_add_table.
Similarly existing partitions are reorganized or split, ensure to update
the replication sets accordingly.

Thanks & Regards


*Muhammad Affan (*아판*)*

*PostgreSQL Technical Support Engineer** / Pakistan R&D*

Interlace Plaza 4th floor Twinhub office 32 I8 Markaz, Islamabad, Pakistan

On Sat, Jul 20, 2024 at 12:00 PM Durgamahesh Manne <
maheshpostgr...@gmail.com> wrote:

> Hi
>
> Respected Team
>
> I know the use case of implementing the partitions with publication and
> subscription of built-in logical replication
> CREATE PUBLICATION dbz_publication FOR TABLE betplacement.bet WITH
> (publish_via_partition_root = true); This will use parent table to replica
> data changes to target from source
> But
> Could you please provide a logic to implement the same with pglogical ?
> Do i need to add this publish_via_partition_root to
> pglogical.replication_set_add_table functionality ?
> you response is valuable in this aspect
>
> Regards,
> Durga Mahesh
>

Re: Windows installation problem at post-install step

[Ertan Küçükoglu]

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

repomd.xml.asc missing in some Fedora 40 repos

[José María Terry Jiménez]

Hello

In some yum repos in Fedora 40, there is repomd.xml.asc missing file, at 
least in pgdg-common, pgdg13, pgdg12, other works.


Best,


Errors from dnf upgrade:


PostgreSQL common RPMs for Fedora 40 - x86_64 305  B/s | 146  B 00:00
PostgreSQL common RPMs for Fedora 40 - x86_64 2.4 MB/s | 2.4 kB 00:00
PostgreSQL common RPMs for Fedora 40 - x86_64 132  B/s | 146  B 00:01
Errors during downloading metadata for repository 'pgdg-common':
  - Status code: 404 for 
https://download.postgresql.org/pub/repos/yum/common/fedora/fedora-40-x86_64/repodata/repomd.xml.asc 
(IP: 72.32.157.246)
  - Status code: 404 for 
https://download.postgresql.org/pub/repos/yum/common/fedora/fedora-40-x86_64/repodata/repomd.xml.asc 
(IP: 147.75.85.69)
Error: Failed to download metadata for repo 'pgdg-common': GPG 
verification is enabled, but GPG signature is not available. This may be 
an error or the repository does not support GPG verification: Status 
code: 404 for 
https://download.postgresql.org/pub/repos/yum/common/fedora/fedora-40-x86_64/repodata/repomd.xml.asc 
(IP: 72.32.157.246)

PostgreSQL 13 for Fedora 40 - x86_64 208  B/s | 146  B 00:00
PostgreSQL 13 for Fedora 40 - x86_64 2.4 MB/s | 2.4 kB 00:00
PostgreSQL 13 for Fedora 40 - x86_64 184  B/s | 146  B 00:00
Errors during downloading metadata for repository 'pgdg13':
  - Status code: 404 for 
https://download.postgresql.org/pub/repos/yum/13/fedora/fedora-40-x86_64/repodata/repomd.xml.asc 
(IP: 147.75.85.69)
  - Status code: 404 for 
https://download.postgresql.org/pub/repos/yum/13/fedora/fedora-40-x86_64/repodata/repomd.xml.asc 
(IP: 217.196.149.55)
Error: Failed to download metadata for repo 'pgdg13': GPG verification 
is enabled, but GPG signature is not available. This may be an error or 
the repository does not support GPG verification: Status code: 404 for 
https://download.postgresql.org/pub/repos/yum/13/fedora/fedora-40-x86_64/repodata/repomd.xml.asc 
(IP: 217.196.149.55)

PostgreSQL 12 for Fedora 40 - x86_64 320  B/s | 146  B 00:00
PostgreSQL 12 for Fedora 40 - x86_64 2.4 MB/s | 2.4 kB 00:00
PostgreSQL 12 for Fedora 40 - x86_64 248  B/s | 146  B 00:00
Errors during downloading metadata for repository 'pgdg12':
  - Status code: 404 for 
https://download.postgresql.org/pub/repos/yum/12/fedora/fedora-40-x86_64/repodata/repomd.xml.asc 
(IP: 147.75.85.69)
  - Status code: 404 for 
https://download.postgresql.org/pub/repos/yum/12/fedora/fedora-40-x86_64/repodata/repomd.xml.asc 
(IP: 87.238.57.227)
Error: Failed to download metadata for repo 'pgdg12': GPG verification 
is enabled, but GPG signature is not available. This may be an error or 
the repository does not support GPG verification: Status code: 404 for 
https://download.postgresql.org/pub/repos/yum/12/fedora/fedora-40-x86_64/repodata/repomd.xml.asc 
(IP: 87.238.57.227)

Ignoring repositories: pgdg-common, pgdg13, pgdg12

Re: Bloated pg_catalog.pg_largeobjects

[khan Affan]

Hi

I would suggest to backup your DB before doing such a thing.

Run Vaccum Full, (VACUUM FULL pg_catalog.pg_largeobject) Running this on
the system table might be risky Make sure you backup the database.

& if you are using PG version above 9.1 use Pg_repack to reclaim the space.

Note:  It can be disruptive, so planning and preparing for potential
downtime is essential.

Thanks & regards


*Muhammad Affan (*아판*)*

*PostgreSQL Technical Support Engineer** / Pakistan R&D*

Interlace Plaza 4th floor Twinhub office 32 I8 Markaz, Islamabad, Pakistan

On Sun, Jul 21, 2024 at 3:46 AM  wrote:

> Hello All,
>
> I've got a cluster that's having issues with pg_catalog.pg_largeobject
> getting massively bloated. Vacuum is running OK and there's 700GB of free
> space in the table and only 100GB of data, but subsequent inserts seem to
> be not using space from the FSM and instead always allocating new pages.
> The table just keeps growing.
>
> Is this a known thing, maybe something special about LOs?
>
> Also, is the only way to recover space here a vacuum full on the table
> since it's a catalog table?
>
> Thanks,
> --
> Jon Erdman (aka StuckMojo on IRC)
> PostgreSQL Zealot
>

Re: Windows installation problem at post-install step

[Sandeep Thakkar]

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
  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  wrote:

> On Mon, Jul 22, 2024 at 11:58 AM Ertan Küçükoglu
>  wrote:
> > Thomas Munro , 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

Re: Windows installation problem at post-install step

[Sandeep Thakkar]

Hi,

On Mon, Jul 22, 2024 at 1:57 AM Thomas Munro  wrote:

> On Mon, Jul 22, 2024 at 7:29 AM Adrian Klaver 
> 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_Trkiye.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 ), 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
  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]
> 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

Re: Windows installation problem at post-install step

[Sandeep Thakkar]

On Mon, Jul 22, 2024 at 5:21 PM Sandeep Thakkar <
sandeep.thak...@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   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 
> wrote:
>
>> On Mon, Jul 22, 2024 at 11:58 AM Ertan Küçükoglu
>>  wrote:
>> > Thomas Munro , 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 

Re: Bloated pg_catalog.pg_largeobjects

[Priancka Chatz]

Vacuum Full will not help here if you have large objects present in the
pg_largeobjects table but not being referred to by any table. Vacuumlo
doesn't require downtime but based on the data it needs to remove can run
long and use resources and hence schedule it in off peak hours. You can do
a dry run to get an estimate.

On Mon, Jul 22, 2024 at 1:21 PM khan Affan  wrote:

> Hi
>
> I would suggest to backup your DB before doing such a thing.
>
> Run Vaccum Full, (VACUUM FULL pg_catalog.pg_largeobject) Running this on
> the system table might be risky Make sure you backup the database.
>
> & if you are using PG version above 9.1 use Pg_repack to reclaim the space.
>
> Note:  It can be disruptive, so planning and preparing for potential
> downtime is essential.
>
> Thanks & regards
>
>
> *Muhammad Affan (*아판*)*
>
> *PostgreSQL Technical Support Engineer** / Pakistan R&D*
>
> Interlace Plaza 4th floor Twinhub office 32 I8 Markaz, Islamabad, Pakistan
>
> On Sun, Jul 21, 2024 at 3:46 AM  wrote:
>
>> Hello All,
>>
>> I've got a cluster that's having issues with pg_catalog.pg_largeobject
>> getting massively bloated. Vacuum is running OK and there's 700GB of free
>> space in the table and only 100GB of data, but subsequent inserts seem to
>> be not using space from the FSM and instead always allocating new pages.
>> The table just keeps growing.
>>
>> Is this a known thing, maybe something special about LOs?
>>
>> Also, is the only way to recover space here a vacuum full on the table
>> since it's a catalog table?
>>
>> Thanks,
>> --
>> Jon Erdman (aka StuckMojo on IRC)
>> PostgreSQL Zealot
>>
>

Re: Windows installation problem at post-install step

[Dave Page]

Hi

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

>
>
> On Mon, Jul 22, 2024 at 5:21 PM Sandeep Thakkar <
> sandeep.thak...@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   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 
>> wrote:
>>
>>> On Mon, Jul 22, 2024 at 11:58 AM Ertan Küçükoglu
>>>  wrote:
>>> > Thomas Munro , 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.
>>

Re: Windows installation problem at post-install step

[Ertan Küçükoglu]

Sandeep Thakkar , 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

Re: Fwd: lost master password

[Afa Jamal]

Hello
can you help me with reset
I am not able to do this
what u caan advise


On Mon, Jun 10, 2024 at 5:06 AM Laurenz Albe 
wrote:

> On Sun, 2024-06-09 at 17:46 -0400, Afa Jamal wrote:
> > how you can help me with lost super password?
>
> Are you talking about a PostgreSQL superuser password or
> about the "master password" that the pgAdmin client uses
> to encrypt connection information?
>
> Yours,
> Laurenz Albe
>


-- 
*Afa J Zada*

Re: Windows installation problem at post-install step

[Sandeep Thakkar]

On Mon, Jul 22, 2024 at 5:52 PM Ertan Küçükoglu 
wrote:

> Sandeep Thakkar , 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

Re: repomd.xml.asc missing in some Fedora 40 repos

[José María Terry Jiménez]

This is solved now.


Thanks!


El 22/7/24 a las 12:35, José María Terry Jiménez escribió:

Hello

In some yum repos in Fedora 40, there is repomd.xml.asc missing file, 
at least in pgdg-common, pgdg13, pgdg12, other works.


Best,


Errors from dnf upgrade:


PostgreSQL common RPMs for Fedora 40 - x86_64 305  B/s | 146 B 00:00
PostgreSQL common RPMs for Fedora 40 - x86_64 2.4 MB/s | 2.4 kB 00:00
PostgreSQL common RPMs for Fedora 40 - x86_64 132  B/s | 146 B 00:01
Errors during downloading metadata for repository 'pgdg-common':
  - Status code: 404 for 
https://download.postgresql.org/pub/repos/yum/common/fedora/fedora-40-x86_64/repodata/repomd.xml.asc 
(IP: 72.32.157.246)
  - Status code: 404 for 
https://download.postgresql.org/pub/repos/yum/common/fedora/fedora-40-x86_64/repodata/repomd.xml.asc 
(IP: 147.75.85.69)
Error: Failed to download metadata for repo 'pgdg-common': GPG 
verification is enabled, but GPG signature is not available. This may 
be an error or the repository does not support GPG verification: 
Status code: 404 for 
https://download.postgresql.org/pub/repos/yum/common/fedora/fedora-40-x86_64/repodata/repomd.xml.asc 
(IP: 72.32.157.246)

PostgreSQL 13 for Fedora 40 - x86_64 208  B/s | 146  B 00:00
PostgreSQL 13 for Fedora 40 - x86_64 2.4 MB/s | 2.4 kB 00:00
PostgreSQL 13 for Fedora 40 - x86_64 184  B/s | 146  B 00:00
Errors during downloading metadata for repository 'pgdg13':
  - Status code: 404 for 
https://download.postgresql.org/pub/repos/yum/13/fedora/fedora-40-x86_64/repodata/repomd.xml.asc 
(IP: 147.75.85.69)
  - Status code: 404 for 
https://download.postgresql.org/pub/repos/yum/13/fedora/fedora-40-x86_64/repodata/repomd.xml.asc 
(IP: 217.196.149.55)
Error: Failed to download metadata for repo 'pgdg13': GPG verification 
is enabled, but GPG signature is not available. This may be an error 
or the repository does not support GPG verification: Status code: 404 
for 
https://download.postgresql.org/pub/repos/yum/13/fedora/fedora-40-x86_64/repodata/repomd.xml.asc 
(IP: 217.196.149.55)

PostgreSQL 12 for Fedora 40 - x86_64 320  B/s | 146  B 00:00
PostgreSQL 12 for Fedora 40 - x86_64 2.4 MB/s | 2.4 kB 00:00
PostgreSQL 12 for Fedora 40 - x86_64 248  B/s | 146  B 00:00
Errors during downloading metadata for repository 'pgdg12':
  - Status code: 404 for 
https://download.postgresql.org/pub/repos/yum/12/fedora/fedora-40-x86_64/repodata/repomd.xml.asc 
(IP: 147.75.85.69)
  - Status code: 404 for 
https://download.postgresql.org/pub/repos/yum/12/fedora/fedora-40-x86_64/repodata/repomd.xml.asc 
(IP: 87.238.57.227)
Error: Failed to download metadata for repo 'pgdg12': GPG verification 
is enabled, but GPG signature is not available. This may be an error 
or the repository does not support GPG verification: Status code: 404 
for 
https://download.postgresql.org/pub/repos/yum/12/fedora/fedora-40-x86_64/repodata/repomd.xml.asc 
(IP: 87.238.57.227)

Ignoring repositories: pgdg-common, pgdg13, pgdg12

Re: Fwd: lost master password

[Laurenz Albe]

On Fri, 2024-07-19 at 11:59 -0400, Afa Jamal wrote:
> can you help me with reset 
> I am not able to do this
> what u caan advise
> 
> On Mon, Jun 10, 2024 at 5:06 AM Laurenz Albe  wrote:
> > On Sun, 2024-06-09 at 17:46 -0400, Afa Jamal wrote:
> > > how you can help me with lost super password?
> > 
> > Are you talking about a PostgreSQL superuser password or
> > about the "master password" that the pgAdmin client uses
> > to encrypt connection information?

Only if you answer my qustion.

Yours,
Laurenz Albe

Re: Fwd: lost master password

[Afa Jamal]

master

Sent from Gmail Mobile


On Mon, Jul 22, 2024 at 9:30 AM Laurenz Albe 
wrote:

> On Fri, 2024-07-19 at 11:59 -0400, Afa Jamal wrote:
> > can you help me with reset
> > I am not able to do this
> > what u caan advise
> >
> > On Mon, Jun 10, 2024 at 5:06 AM Laurenz Albe 
> wrote:
> > > On Sun, 2024-06-09 at 17:46 -0400, Afa Jamal wrote:
> > > > how you can help me with lost super password?
> > >
> > > Are you talking about a PostgreSQL superuser password or
> > > about the "master password" that the pgAdmin client uses
> > > to encrypt connection information?
>
> Only if you answer my qustion.
>
> Yours,
> Laurenz Albe
>

Re: Windows installation problem at post-install step

[Adrian Klaver]

On 7/22/24 03:10, Ertan Küçükoglu wrote:
Adrian Klaver >, 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.kla...@aklaver.com

Re: Fwd: lost master password

[Adrian Klaver]

On 7/22/24 06:32, Afa Jamal wrote:

master


Read:

https://www.pgadmin.org/docs/pgadmin4/8.9/master_password.html


Sent from Gmail Mobile


On Mon, Jul 22, 2024 at 9:30 AM Laurenz Albe > wrote:


On Fri, 2024-07-19 at 11:59 -0400, Afa Jamal wrote:
 > can you help me with reset
 > I am not able to do this
 > what u caan advise
 >
 > On Mon, Jun 10, 2024 at 5:06 AM Laurenz Albe
mailto:laurenz.a...@cybertec.at>> wrote:
 > > On Sun, 2024-06-09 at 17:46 -0400, Afa Jamal wrote:
 > > > how you can help me with lost super password?
 > >
 > > Are you talking about a PostgreSQL superuser password or
 > > about the "master password" that the pgAdmin client uses
 > > to encrypt connection information?

Only if you answer my qustion.

Yours,
Laurenz Albe



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Windows installation problem at post-install step

[Ertan Küçükoglu]

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

Re: Windows installation problem at post-install step

[Adrian Klaver]

On 7/22/24 09:51, Ertan Küçükoglu wrote:
Adrian Klaver >, 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.


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.kla...@aklaver.com

Re: Windows installation problem at post-install step

[Ertan Küçükoglu]

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

Thanks & Regards,
Ertan

Re: Windows installation problem at post-install step

[Adrian Klaver]

On 7/22/24 10:09 AM, Ertan Küçükoglu wrote:
Adrian Klaver >, 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.kla...@aklaver.com

Unsuscribe

[Dunia Ramazani]

Unsuscribe

Re: Unsuscribe

[Adrian Klaver]

On 7/22/24 10:45 AM, Dunia Ramazani wrote:

Unsuscribe



Follow instructions here:

https://lists.postgresql.org/unsubscribe/

--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Windows installation problem at post-install step

[Ertan Küçükoglu]

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

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

Re: Windows installation problem at post-install step

[AC Gomez]

We

On Mon, Jul 22, 2024, 1:51 PM Ertan Küçükoglu 
wrote:

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

Re: Windows installation problem at post-install step

[Adrian Klaver]

On 7/22/24 10:51 AM, Ertan Küçükoglu wrote:
Adrian Klaver >, 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 

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.kla...@aklaver.com

Re: Windows installation problem at post-install step

[Ertan Küçükoglu]

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

Thanks & Regards,
Ertan

Re: Windows installation problem at post-install step

[Adrian Klaver]

On 7/22/24 11:48, Ertan Küçükoglu wrote:
Adrian Klaver >, 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.kla...@aklaver.com

Re: Windows installation problem at post-install step

[Ertan Küçükoglu]

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

Re: Windows installation problem at post-install step

[Adrian Klaver]

On 7/22/24 13:15, Ertan Küçükoglu wrote:
Adrian Klaver >, 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.kla...@aklaver.com

Re: Windows installation problem at post-install step

[Ertan Küçükoglu]

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

Thanks & Regards,
Ertan

Re: Windows installation problem at post-install step

[Adrian Klaver]

On 7/22/24 13:34, Ertan Küçükoglu wrote:
Adrian Klaver >, 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.kla...@aklaver.com

Re: Windows installation problem at post-install step

[Thomas Munro]

On Mon, Jul 22, 2024 at 11:51 PM Sandeep Thakkar
 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.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 
>  may know but I'm not sure if that piece of code is 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