Re: db maintanance problem VACUUM FULL

2025-06-12 Thread Ron Johnson
On Thu, Jun 12, 2025 at 9:24 AM Laurenz Albe wrote: > On Thu, 2025-06-12 at 15:14 +0200, Pavol Sekeres wrote: > > We recently updated our production database to PostgreSQL 12.22 from the > 9.6.24 version. > > We didn't want to make a big jump. > > But you should have. v12 is out of support. > T

Re: db maintanance problem VACUUM FULL

2025-06-12 Thread Ron Johnson
t; Tables can be more than 100GB in size. > They are being updated daily. > Also due to GDPR old data is erased on a daily basis. > We think these tables might get eventually bloated. > > Can this be a problem? > What are your autovacuum settings? If they're aggressive,

Re: db maintanance problem VACUUM FULL

2025-06-12 Thread Laurenz Albe
aily. > Also due to GDPR old data is erased on a daily basis. > We think these tables might get eventually bloated. > > Can this be a problem? Yes. Check for bloat in suspicious tables using the "pgstattuple" extension. > If yes, is there any other solution outside l

db maintanance problem VACUUM FULL

2025-06-12 Thread Pavol Sekeres
this be a problem? If yes, is there any other solution outside locking the database? Should we try to solve this problem by creating a logical replica of the database? We would then promote the replica to primary. After that, we would drop the old database. Is this possible without a big downtim

Re: pg_rewind problem: cannot find WAL

2025-05-09 Thread Luca Ferrari
On Thu, May 8, 2025 at 5:11 PM Adrian Klaver wrote: > /usr/lib/postgresql/17/bin/pg_rewind --help > pg_rewind resynchronizes a PostgreSQL cluster with another copy of the > cluster. >--config-file=FILENAME use specified main server configuration shame on me! I was grepping config_fil

Re: pg_rewind problem: cannot find WAL

2025-05-08 Thread Adrian Klaver
On 5/8/25 04:26, Luca Ferrari wrote: On Thu, May 8, 2025 at 8:54 AM Luca Ferrari wrote: I've pgbackrest making backups, so I have an archive_command. I'm going to see if putting a restore_command can fix the problem. But I'm facing a quite trivial problem: in ubuntu i

Re: pg_rewind problem: cannot find WAL

2025-05-08 Thread Rob Sargent
> > Any idea? > Clearly, postgresql.auto.conf is within PGDATA, and since my > recovery_command is there, one trick could be to touch and empty > PGDATA/postgresql.conf, pg_rewind, remove the fake configurtion file. > But I'm sure there is a smarter solution. > > Thanks, > Luca > > A symlink

Re: pg_rewind problem: cannot find WAL

2025-05-08 Thread Luca Ferrari
On Thu, May 8, 2025 at 4:04 PM Rob Sargent wrote: > > > A symlink from $PGDATA to where actual file? > Could be, I need to experiment with pg_basebackup to ensure it is not conflicting with the /etc/ configuration file when creating a clone. Luca

Re: pg_rewind problem: cannot find WAL

2025-05-08 Thread Luca Ferrari
On Thu, May 8, 2025 at 8:54 AM Luca Ferrari wrote: > > I've pgbackrest making backups, so I have an archive_command. I'm > going to see if putting a restore_command can fix the problem. > But I'm facing a quite trivial problem: in ubuntu installation the configurati

Re: pg_rewind problem: cannot find WAL

2025-05-07 Thread Luca Ferrari
chive, you could define a restore_command on the server > you want to rewind. I've pgbackrest making backups, so I have an archive_command. I'm going to see if putting a restore_command can fix the problem. Thanks for the suggestion. Luca

Re: pg_rewind problem: cannot find WAL

2025-05-07 Thread Laurenz Albe
On Wed, 2025-05-07 at 12:51 +0200, Luca Ferrari wrote: > running 17.4 on ubuntu 24.04 machines. I've three hosts, pg-1 > (primary) and two physical replicas. > I then promote host pg-3 as a master (pg_promote()) and want to rewind > the pg-1 to follow the new master, so: > > ssh pg-3 'sudo -u post

pg_rewind problem: cannot find WAL

2025-05-07 Thread Luca Ferrari
Hi all, running 17.4 on ubuntu 24.04 machines. I've three hosts, pg-1 (primary) and two physical replicas. I then promote host pg-3 as a master (pg_promote()) and want to rewind the pg-1 to follow the new master, so: ssh pg-3 'sudo -u postgres /usr/lib/postgresql/17/bin/pg_rewind -D /var/lib/postg

Re: Streaming replication problem with collation

2024-12-20 Thread Tom Lane
aming replication than logical replication, and also I find it > more useful when you need to replicate the whole cluster. > So my question is: is there anything I'm missing here, some kind of problem > that could hit my face after moving to the new server? That will almost certa

Streaming replication problem with collation

2024-12-20 Thread Ekaterina Amez Gonzalez
Hi List, I'm making some tests in order to prepare a db migration. We have version 9.6 over CentOS 7 and we're going to migrate to version 15 over Rocky Linux 9. Of course there is a no downtime requirement or I wouldn't be here asking. I was previously aware of the problem with

Re: Problem with a Query

2024-08-26 Thread Ron Johnson
a long time. After some study, I >> > found that the SQL is using NESTED LOOP where the cost is too high. >> >> The core of your problem seems to be here: >> >> > -> Index Scan using >> marketing_a_cancel__55_idx on >>

Problem with a query

2024-08-26 Thread Siraj G
Hello! We have a couple of queries that all of a sudden became rather slow. I took explain analyze from one of the SQLs as bdlow. Can you please check and suggest if anything can be done? '-> Table scan on (actual time=0.019..71.526 rows=38622 loops=1)\n -> Aggregate using temporary table (act

Re: Problem with a Query

2024-08-26 Thread Siraj G
ime. After some study, I > > found that the SQL is using NESTED LOOP where the cost is too high. > > The core of your problem seems to be here: > > > -> Index Scan using marketing_a_cancel__55_idx > on > > marketing_app_leadhistory w0 (cos

Re: Problem with a Query

2024-08-12 Thread Tom Lane
Siraj G writes: > We migrated a PgSQL database from Cloud SQL to compute engine and since > then there is a SQL we observed taking a long time. After some study, I > found that the SQL is using NESTED LOOP where the cost is too high. The core of your problem seems t

Problem with a Query

2024-08-12 Thread Siraj G
Hello! We migrated a PgSQL database from Cloud SQL to compute engine and since then there is a SQL we observed taking a long time. After some study, I found that the SQL is using NESTED LOOP where the cost is too high. I tried VACUUM FULL and ANALYZE, but to no avail. Only when I disabled the nest

Re: Windows installation problem at post-install step

2024-08-07 Thread Sandeep Thakkar
On Thu, Aug 8, 2024 at 6:10 AM Thomas Munro wrote: > Thanks. The log didn't offer any more clues, and my colleague David R > has Windows and knows how to work its debugger so we sat down together > and chased this down (thanks David!). > > 1. It is indeed calling abort(), but it's not a PANIC o

Re: Windows installation problem at post-install step

2024-08-07 Thread Thomas Munro
Thanks. The log didn't offer any more clues, and my colleague David R has Windows and knows how to work its debugger so we sat down together and chased this down (thanks David!). 1. It is indeed calling abort(), but it's not a PANIC or Assert() in PostgreSQL, it's an assertion inside Windows' ow

Re: Windows installation problem at post-install step

2024-08-06 Thread Thomas Munro
On Tue, Aug 6, 2024 at 11:44 PM Peter J. Holzer wrote: > I assume that "1254" here is the code page. > But you specified --encoding=UTF-8 above, so your default locale uses a > different encoding than the template databases. I would expect that to > cause problems if the template databases contain

Re: Windows installation problem at post-install step

2024-08-06 Thread Thomas Munro
On Tue, Aug 6, 2024 at 10:38 PM Sandeep Thakkar wrote: > On Tue, Aug 6, 2024 at 4:06 PM Sandeep Thakkar > wrote: [v15] >>> XXX debug raw: setup_locale_encoding = "Turkish_Türkiye.1254" >>> XXX debug hex: setup_locale_encoding = { 54 75 72 6b 69 73 68 5f 54 fc 72 >>> 6b 69 79 65 2e 31 32 35

Re: Windows installation problem at post-install step

2024-08-06 Thread Peter J. Holzer
and also confirmed by @Ertan Küçükoglu at https://github.com/ > EnterpriseDB/edb-installers/issues/127#issuecomment-2268371442 > > Does that mean you can reproduce the problem with initdb.exe directly > in a shell?  That is, remove the EDB installer from the picture and >

Re: Windows installation problem at post-install step

2024-08-06 Thread Sandeep Thakkar
not the back branches (tested on 15 >> and 14) and also confirmed by @Ertan Küçükoglu at >> https://github.com/EnterpriseDB/edb-installers/issues/127#issuecomment-2268371442 >> >> Does that mean you can reproduce the problem with initdb.exe directly >> in a shell? That

Re: Windows installation problem at post-install step

2024-08-06 Thread Sandeep Thakkar
-installers/issues/127#issuecomment-2268371442 > > Does that mean you can reproduce the problem with initdb.exe directly > in a shell? That is, remove the EDB installer from the picture and > compare v15 and v16 with the exact command line options that > initcluster.vbs is using

Re: Windows installation problem at post-install step

2024-08-05 Thread Thomas Munro
n you can reproduce the problem with initdb.exe directly in a shell? That is, remove the EDB installer from the picture and compare v15 and v16 with the exact command line options that initcluster.vbs is using, or perhaps just: initdb.exe --locale="Turkish,Türkiye" --encoding=UTF-8 -D pgdata .

Re: Windows installation problem at post-install step

2024-08-05 Thread Sandeep Thakkar
table. >>>> >>>> Executing C:\Windows\System32\cscript //NoLogo "C:\Program >>>> Files\PostgreSQL\16/installer/server/initcluster.vbs" "NT >>>> AUTHORITY\NetworkService" "postgres" "" >>>> &qu

Re: Windows installation problem at post-install step

2024-08-02 Thread Sandeep Thakkar
eSQL\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_P

Re: Windows installation problem at post-install step

2024-07-23 Thread Sandeep Thakkar
On Tue, Jul 23, 2024 at 1:58 PM Dave Page wrote: > > > On Tue, Jul 23, 2024 at 1:27 AM Thomas Munro > wrote: > >> 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/bl

Re: Windows installation problem at post-install step

2024-07-23 Thread Dave Page
On Tue, Jul 23, 2024 at 1:27 AM Thomas Munro wrote: > 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

Re: Windows installation problem at post-install step

2024-07-22 Thread 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/Ente

Re: Windows installation problem at post-install step

2024-07-22 Thread 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)

Re: Windows installation problem at post-install step

2024-07-22 Thread 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 understand

Re: Windows installation problem at post-install step

2024-07-22 Thread 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 prod

Re: Windows installation problem at post-install step

2024-07-22 Thread 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 localizatio

Re: Windows installation problem at post-install step

2024-07-22 Thread Adrian Klaver
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 i

Re: Windows installation problem at post-install step

2024-07-22 Thread 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

Re: Windows installation problem at post-install step

2024-07-22 Thread 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.

Re: Windows installation problem at post-install step

2024-07-22 Thread 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

Re: Windows installation problem at post-install step

2024-07-22 Thread 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 "templat

Re: Windows installation problem at post-install step

2024-07-22 Thread 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                              

Re: Windows installation problem at post-install step

2024-07-22 Thread 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 Provide

Re: Windows installation problem at post-install step

2024-07-22 Thread 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

Re: Windows installation problem at post-install step

2024-07-22 Thread 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

Re: Windows installation problem at post-install step

2024-07-22 Thread 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 clus

Re: Windows installation problem at post-install step

2024-07-22 Thread Sandeep Thakkar
\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 fil

Re: Windows installation problem at post-install step

2024-07-22 Thread Ertan Küçükoglu
t; "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: Windows installation problem at post-install step

2024-07-22 Thread Dave Page
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.a

Re: Windows installation problem at post-install step

2024-07-22 Thread Sandeep Thakkar
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 an

Re: Windows installation problem at post-install step

2024-07-22 Thread 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 a

Re: Windows installation problem at post-install step

2024-07-22 Thread Sandeep Thakkar
> > > 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

Re: Windows installation problem at post-install step

2024-07-22 Thread 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

Re: Windows installation problem at post-install step

2024-07-21 Thread Thomas Munro
e > 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

Re: Windows installation problem at post-install step

2024-07-21 Thread Ertan Küçükoglu
econd 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

Re: Windows installation problem at post-install step

2024-07-21 Thread Thomas Munro
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

Re: Windows installation problem at post-install step

2024-07-21 Thread Adrian Klaver
nstall 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 d

Re: Windows installation problem at post-install step

2024-07-21 Thread Ertan Küçükoglu
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 installati

Re: Windows installation problem at post-install step

2024-07-21 Thread Adrian Klaver
On 7/21/24 10:52, Ertan Küçükoglu wrote: Adrian Klaver <mailto:adrian.kla...@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

Re: Windows installation problem at post-install step

2024-07-21 Thread Ertan Küçükoglu
Adrian Klaver , 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 ins

Re: Windows installation problem at post-install step

2024-07-21 Thread Adrian Klaver
On 7/21/24 10:21, Ertan Küçükoglu wrote: Adrian Klaver >, 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 V

Re: Windows installation problem at post-install step

2024-07-21 Thread Ertan Küçükoglu
Adrian Klaver , 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

Re: Windows installation problem at post-install step

2024-07-21 Thread Adrian Klaver
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

Windows installation problem at post-install step

2024-07-21 Thread Ertan Küçükoglu
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. During installation I get an error message “Problem running post-install step. Installation may not complete correctly The database cluster initialization failed

Re: problem with query

2024-05-27 Thread Sašo Gantar
upgrade to "PostgreSQL 16.3 (Debian 16.3-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit" solved the problem regards, s On Wed, 22 May 2024 at 06:04, Sašo Gantar wrote: > ANALYZE pg_class; doesn't help > also, query is from "Has

Re: problem with query

2024-05-21 Thread Sašo Gantar
ANALYZE pg_class; doesn't help also, query is from "Hasura", so I don't have much room to maneuver On Tue, 21 May 2024 at 16:18, Tom Lane wrote: > =?UTF-8?B?U2HFoW8gR2FudGFy?= writes: > > thanks for the info, but is there any solution, given that it's system > > tables? > > Given the complexity

Re: problem with query

2024-05-21 Thread Tom Lane
=?UTF-8?B?U2HFoW8gR2FudGFy?= writes: > thanks for the info, but is there any solution, given that it's system > tables? Given the complexity of the query, I wonder if you're running into problems with join_collapse_limit/from_collapse_limit preventing the planner from considering all options. Al

Re: problem with query

2024-05-21 Thread Sašo Gantar
sorry... SELECT COALESCE(Json_agg(Row_to_json(info)), '[]' :: JSON) AS TABLES FROM (WITH partitions AS (SELECT array (WITH partitioned_tables AS (SELECT array (SELECT oid FROM pg_class WHERE relkind = 'p') AS parent_tables) SELE

Re: problem with query

2024-05-21 Thread David Rowley
On Tue, 21 May 2024 at 23:14, Laurenz Albe wrote: > We still don't know the query. hmm, it was posted on this thread: https://postgr.es/m/CAGB0_6600w5C=hvhgfmwcqo9bcwcg+3s0pxxuoqv48nlqtp...@mail.gmail.com David

Re: problem with query

2024-05-21 Thread Laurenz Albe
On Tue, 2024-05-21 at 12:49 +0200, Sašo Gantar wrote: > thanks for the info, but is there any solution, given that it's system tables? We still don't know the query. Yours, Laurenz Albe

Re: problem with query

2024-05-21 Thread Sašo Gantar
thanks for the info, but is there any solution, given that it's system tables? regards On Tue, 21 May 2024 at 12:09, Laurenz Albe wrote: > On Mon, 2024-05-20 at 13:08 +0200, Sašo Gantar wrote: > [execution plan without query text or explanation] > > The time is lost here: > > -> WindowAgg (

Re: problem with query

2024-05-21 Thread David Rowley
ng. I don't really see a good way to convince the planner not to do this. The problem condition is: Recheck Cond: (relnamespace = pgn.oid) Filter: (relkind = ANY ('{r,v,f,m,p}'::"char"[])) if ANALYZE pg_class; does not help then you could maybe mess with the n_distinct

Re: problem with query

2024-05-21 Thread Laurenz Albe
On Mon, 2024-05-20 at 13:08 +0200, Sašo Gantar wrote: [execution plan without query text or explanation] The time is lost here: -> WindowAgg (cost=310.01..358.34 rows=537 width=888) (actual time=0.057..19.955 rows=473 loops=401) Buffers: shared hit=1710825 Yours, Laurenz Albe

Re: problem with query

2024-05-20 Thread Sašo Gantar
what helps is SET enable_nestloop = off; query takes less then 2seconds but it's probably not a good idea to change this flag On Wed, 15 May 2024 at 13:23, David Rowley wrote: > On Wed, 15 May 2024 at 21:08, Sašo Gantar wrote: > > this query takes more than 8 seconds, > > if i remove "AND ((pg

Re: problem with query

2024-05-20 Thread Sašo Gantar
> > > Aggregate (cost=512.53..512.54 rows=1 width=32) (actual > time=8430.692..8430.724 rows=1 loops=1) > Buffers: shared hit=2031540, temp read=954 written=956 > -> Subquery Scan on info (cost=510.85..512.52 rows=2 width=152) > (actual time=8257.310..8430.532 rows=57 loops=1) > Buff

Re: problem with query

2024-05-15 Thread David Rowley
On Wed, 15 May 2024 at 21:08, Sašo Gantar wrote: > this query takes more than 8 seconds, > if i remove "AND ((pgn.nspname='servicedesk'))" and test it, it takes <1s Including the EXPLAIN rather than EXPLAIN (ANALYZE, BUFFERS) isn't very useful as there's no way to tell if the planner's estimates

problem with query

2024-05-15 Thread Sašo Gantar
this query takes more than 8 seconds, if i remove "AND ((pgn.nspname='servicedesk'))" and test it, it takes <1s SELECT COALESCE(Json_agg(Row_to_json(info)), '[]' :: JSON) AS TABLES FROM (WITH partitions AS (SELECT array (WITH partitioned_tables AS (SELECT array

Re: problem loading shared lib pg_tde.so

2024-05-06 Thread Matthias Apitz
El día martes, mayo 07, 2024 a las 07:07:22 +0200, Matthias Apitz escribió: > # ls -l /usr/local/sisis-pap/lib/libcurl* > -rw-r--r-- 1 bin bin 1315526 May 6 10:29 /usr/local/sisis-pap/lib/libcurl.a > -rwxr-xr-x 1 bin bin1004 May 6 10:29 /usr/local/sisis-pap/lib/libcurl.la > -rwxr-xr-x 1 bin

Re: problem loading shared lib pg_tde.so

2024-05-06 Thread Matthias Apitz
su - $PGUSER -c "$LD_ENV $DAEMON_ENV $DAEMON -D '$PGDATA' >>$PGLOG 2>&1 &" echo "ok" ;; ... After looking carefully at the file /tmp/pg_tde.ldd I saw the problem: # egrep 'LD_LIBRARY_PATH|libcurl' /tmp/pg_tde.ldd LD_LI

Re: problem loading shared lib pg_tde.so

2024-05-06 Thread Adrian Klaver
On 5/6/24 07:42, Adrian Klaver wrote: On 5/6/24 04:05, Matthias Apitz wrote: I see three different versions of OpenSSL: OPENSSL_1_1_1d  -- From error messsage OpenSSL 1.1.1l-fips    -- SuSE 15 version OpenSSL 1.1.1t    -- Your built version? Are you sure you pointing at the same

Re: problem loading shared lib pg_tde.so

2024-05-06 Thread Adrian Klaver
On 5/6/24 04:05, Matthias Apitz wrote: I have a problem while loading the pg_tde.so shared lib. contrib/pg_tde was built with: cd postgresql-16.2/contrib/pg_tde || exit gmake clean export LDFLAGS="-L/usr/local/sisis-pap/lib -L/usr/lib64" export CFLAGS="-m64 -I/usr/local/si

problem loading shared lib pg_tde.so

2024-05-06 Thread Matthias Apitz
I have a problem while loading the pg_tde.so shared lib. contrib/pg_tde was built with: cd postgresql-16.2/contrib/pg_tde || exit gmake clean export LDFLAGS="-L/usr/local/sisis-pap/lib -L/usr/lib64" export CFLAGS="-m64 -I/usr/local/sisis-pap/include" export CPPFLAGS="-m

Re: pg_dumpall - restoration problem- resolved

2024-04-07 Thread Tony Bazeley
Thanks Tom, Dumped in Ubuntu 22.04_1 and restore attempted using Ubuntu 22.04.3 Editing the dump file to C.UTF8 didn't solve the problem. The default for the database was en_AU.utf8 so I should have changed the collation to that, but it was one field in one table of superseded data, so I

Re: pg_dumpall - restoration problem

2024-04-06 Thread Tom Lane
Tony Bazeley writes: > I've a problem with restoring a cluster created with pg_dump_all from 14.8 > ( pg_dumpall >pgall.out and then psql -f pgall.out postgres). > ... > Attempting to restore to postgresql-16 results in errors > 2024-04-05 22:17:15.418 ACDT [6565

pg_dumpall - restoration problem

2024-04-06 Thread Tony Bazeley
I've a problem with restoring a cluster created with pg_dump_all from 14.8 ( pg_dumpall >pgall.out and then psql -f pgall.out postgres). pgall.out was recovered after a hardware failure on the hosting machine. Attempting to restore to postgresql-16 results in errors . . . psql:/tmp/p

Re: Me again with an insert trigger problem

2024-02-27 Thread Adrian Klaver
On 2/27/24 14:38, Thiemo Kellner wrote: Am 27.02.2024 um 23:20 schrieb Adrian Klaver: I am not sure, what you want me to show with your test case. And I am not sure whether I could not make myself clear. Please bear with me if I try to make things clearer with an example. Your comment wa

Re: Me again with an insert trigger problem

2024-02-27 Thread Thiemo Kellner
Am 27.02.2024 um 23:20 schrieb Adrian Klaver: On 2/27/24 14:11, Thiemo Kellner wrote: It is a habit of mine to pad conditions in the where clause. This way, it is easy to comment/uncomment parts of the clause for testing purposes. Coming from Oracle, I missed that using "true" is also possib

Re: Me again with an insert trigger problem

2024-02-27 Thread Adrian Klaver
On 2/27/24 14:11, Thiemo Kellner wrote: Am 27.02.2024 um 21:42 schrieb Adrian Klaver: Also not sure what this: select NODE_TYPE⠒NAME into V⠒NODE_TYPE⠒NAME    from NODE⠒V   where 1 = 1     and ID = new.NODE⠒ID     and 1 = 1; is supposed to be doin

Re: Me again with an insert trigger problem

2024-02-27 Thread Thiemo Kellner
Am 27.02.2024 um 21:42 schrieb Adrian Klaver: Also not sure what this: select NODE_TYPE⠒NAME into V⠒NODE_TYPE⠒NAME   from NODE⠒V where 1 = 1    and ID = new.NODE⠒ID    and 1 = 1; is supposed to be doing especially the 1 = 1 tests? The selec

Re: Me again with an insert trigger problem

2024-02-27 Thread Thiemo Kellner
Thanks. 27.02.2024 19:09:50 Adrian Klaver : > > On 2/27/24 9:49 AM, Thiemo Kellner wrote: >> Hi >> >> I am surprised that my before insert trigger function does not insert any >> rows into NODE_GOOD. >> >> I was under the impression that the trigger function would do the insert >> with the n

Re: Me again with an insert trigger problem

2024-02-27 Thread Adrian Klaver
On 2/27/24 9:49 AM, Thiemo Kellner wrote: Hi I am surprised that my before insert trigger function does not insert any rows into NODE_GOOD. I was under the impression that the trigger function would do the insert with the new and possibly adapted values. In my case, to me at least, it is v

Me again with an insert trigger problem

2024-02-27 Thread Thiemo Kellner
Hi I am surprised that my before insert trigger function does not insert any rows into NODE_GOOD. I was under the impression that the trigger function would do the insert with the new and possibly adapted values. In my case, to me at least, it is very simple. Only records of node type "Drop-

Problem managing slots in Patroni

2024-02-06 Thread Fabrice Chapuis
Hi, I use patroni version 3.2.1. There is a point that I do not understand in the slots management with Patroni. Patroni creates a slot automatically on primary node when there is a standby attached, although this slot does not belong to the patroni configuration. How to prevent the automatic crea

Re: pg_basebackup Restore problem

2024-01-17 Thread Johnathan Tiamoh
Thank You very much for your time. On Wed, Jan 17, 2024 at 4:51 PM Ron Johnson wrote: > Perfectly understandable, but tar *did* fail. Time to start debugging > your shell script. > > On Wed, Jan 17, 2024 at 4:26 PM Johnathan Tiamoh < > johnathantia...@gmail.com> wrote: > >> Ok. >> >> I'm a litt

Re: pg_basebackup Restore problem

2024-01-17 Thread Ron Johnson
Perfectly understandable, but tar *did* fail. Time to start debugging your shell script. On Wed, Jan 17, 2024 at 4:26 PM Johnathan Tiamoh wrote: > Ok. > > I'm a little confused because has always work > > On Wed, Jan 17, 2024 at 4:11 PM Ron Johnson > wrote: > >> Then you've got a bug somewhere

Re: pg_basebackup Restore problem

2024-01-17 Thread Johnathan Tiamoh
Ok. I'm a little confused because has always work On Wed, Jan 17, 2024 at 4:11 PM Ron Johnson wrote: > Then you've got a bug somewhere in: > tar -h -zxvf $PATH_FOLDER/* .tar.gz > ls $PATH_FOLDER/*.tar.gz | xargs -I {} tar -h -zxvf {} > > On Wed, Jan 17, 2024 at 4:07 PM Johnathan Tiamoh <

Re: pg_basebackup Restore problem

2024-01-17 Thread Ron Johnson
Then you've got a bug somewhere in: tar -h -zxvf $PATH_FOLDER/* .tar.gz ls $PATH_FOLDER/*.tar.gz | xargs -I {} tar -h -zxvf {} On Wed, Jan 17, 2024 at 4:07 PM Johnathan Tiamoh wrote: > Yes. > > I am trying to restore the backups on a standby > > On Wed, Jan 17, 2024 at 4:04 PM Ron Johnson

Re: pg_basebackup Restore problem

2024-01-17 Thread Johnathan Tiamoh
Yes. I am trying to restore the backups on a standby On Wed, Jan 17, 2024 at 4:04 PM Ron Johnson wrote: > Wait a minute... *tar* is throwing the errors, not pg_basebackup, no? > > On Wed, Jan 17, 2024 at 3:27 PM Johnathan Tiamoh < > johnathantia...@gmail.com> wrote: > >> 1. What's in $PATH_FOLD

Re: pg_basebackup Restore problem

2024-01-17 Thread Ron Johnson
Wait a minute... *tar* is throwing the errors, not pg_basebackup, no? On Wed, Jan 17, 2024 at 3:27 PM Johnathan Tiamoh wrote: > 1. What's in $PATH_FOLDER? > > /tnt/backup/current > > 2. What pg_basebackup command did you use? > > pg_basebackup -D "$baseback_dir" --format=tar \ >

Re: pg_basebackup Restore problem

2024-01-17 Thread Johnathan Tiamoh
1. What's in $PATH_FOLDER? /tnt/backup/current 2. What pg_basebackup command did you use? pg_basebackup -D "$baseback_dir" --format=tar \ "${comp_opts[@]}" --wal-method=stream --no-password --verbose "${PG_DUMP_OPTS[@]}" /bin/mv "$baseback_dir"/* "/enf/backup/current/"

  1   2   3   4   5   6   7   >