Re: pg_basebackup cannot compress to STDOUNT

Hi Admin, > On 08. May, 2020, at 21:31, Support wrote: > 2) Command run? > ssh postgres@nodeXXX "pg_basebackup -h /run/postgresql -Ft -D- | pigz -c -p2 > " | pigz -cd -p2 | tar -xf- -C /usr/local/pgsql/data I don't get it, sorry. Do I understand you correctly here that you want an online backu

Re: Best way to use trigger to email a report ?

David G. Johnston writes: > On Fri, May 8, 2020 at 9:26 AM David Gauthier > wrote: > >> psql (9.6.0, server 11.3) on linux >> >> Looking for ideas. I want a trigger to... >> 1) compose an html report based on DB content >> 2) email the report to a dist list (dl = value of a table column) >> >

Re: Best way to use trigger to email a report ?

On Fri, May 8, 2020 at 8:05 PM David G. Johnston wrote: > On Fri, May 8, 2020 at 10:19 AM Christophe Pettus > wrote: > >> If you don't want to periodically poll the table, you can use NOTIFY >> within the trigger to wake up a process that is waiting on NOTIFY. >> > > Kinda. > > "With the libpq l

Re: Thoughts on how to avoid a massive integer update.

> On May 8, 2020, at 3:52 PM, Fehrle, Brian wrote: > > David’s assessment is correct (and I think we’re all on the same page). The > value of the foreign keys that tie the tables together must be changed, and > yeah that value _should_ simply be an additional column in the info_table and > t

Re: Thoughts on how to avoid a massive integer update.

David’s assessment is correct (and I think we’re all on the same page). The value of the foreign keys that tie the tables together must be changed, and yeah that value _should_ simply be an additional column in the info_table and the foreign key be an arbitrary integer, but since it wasn’t set u

Re: pg_basebackup cannot compress to STDOUNT

On 5/8/20 1:42 PM, Support wrote: On 5/8/2020 1:24 PM, Adrian Klaver wrote: On 5/8/20 12:31 PM, Support wrote: On 5/8/2020 12:18 PM, Adrian Klaver wrote: On 5/8/20 12:14 PM, Support wrote: Hi, Despite of the --help saying that it's possible to gzip to STDOUT and pipe it for another proce

Re: AutoVacuum and growing transaction XID's

autovacuum_naptime being only 5 seconds seems too frequent. A lock_timeout might be 1-5 seconds depending on your system. Usually, DDL can fail and wait a little time rather than lock the table for minutes and have all reads back up behind the DDL. Given you have autovacuum_vacuum_cost_limit set t

Re: Thoughts on how to avoid a massive integer update.

> On May 8, 2020, at 2:57 PM, David G. Johnston > wrote: > > On Fri, May 8, 2020 at 1:51 PM Rob Sargent > wrote: > >> On May 8, 2020, at 2:43 PM, David G. Johnston > > wrote: >> >> On Fri, May 8, 2020 at 1:41 PM Rob Sargent >

Re: Thoughts on how to avoid a massive integer update.

On Fri, May 8, 2020 at 1:51 PM Rob Sargent wrote: > > On May 8, 2020, at 2:43 PM, David G. Johnston > wrote: > > On Fri, May 8, 2020 at 1:41 PM Rob Sargent wrote: > >> My understanding is the keys in the info_table need to change. That >> causes the very expensive update in the update in the d

Reuse an existing slot with a new initdb

I normalized my replislots with the name of my nodes. I have 2 options in my recovery script that tries first pg_basebackup to recover and sync the hot standby, but unfortunately big DB fails sometimes due to very slow or unstable network. So my second option is to completely make a new inidb an

Re: Thoughts on how to avoid a massive integer update.

> On May 8, 2020, at 2:43 PM, David G. Johnston > wrote: > > On Fri, May 8, 2020 at 1:41 PM Rob Sargent > wrote: > My understanding is the keys in the info_table need to change. That causes > the very expensive update in the update in the data tables. No? > >

Re: Thoughts on how to avoid a massive integer update.

On Fri, May 8, 2020 at 1:41 PM Rob Sargent wrote: > My understanding is the keys in the info_table need to change. That > causes the very expensive update in the update in the data tables. No? > The keys in the info_table need to change because their contents are no longer legal to be stored (O

Re: pg_basebackup cannot compress to STDOUNT

On 5/8/2020 1:24 PM, Adrian Klaver wrote: On 5/8/20 12:31 PM, Support wrote: On 5/8/2020 12:18 PM, Adrian Klaver wrote: On 5/8/20 12:14 PM, Support wrote: Hi, Despite of the --help saying that it's possible to gzip to STDOUT and pipe it for another process pg_basebackup fails saying tha

Re: Thoughts on how to avoid a massive integer update.

> On May 8, 2020, at 2:37 PM, David G. Johnston > wrote: > > On Fri, May 8, 2020 at 12:49 PM Rob Sargent > wrote: > Well as I said, I think you could add a column to info_table > alter table info_table add orig_id int; > update info_table set orig_id = info_table

Re: Thoughts on how to avoid a massive integer update.

On Fri, May 8, 2020 at 12:49 PM Rob Sargent wrote: > Well as I said, I think you could add a column to info_table > > alter table info_table add orig_id int; > update info_table set orig_id = info_table_sid; > > update info_table set info_table_sid = 456 where info_table_sid = 456; > > huh? alte

Re: pg_basebackup cannot compress to STDOUNT

On 5/8/20 12:31 PM, Support wrote: On 5/8/2020 12:18 PM, Adrian Klaver wrote: On 5/8/20 12:14 PM, Support wrote: Hi, Despite of the --help saying that it's possible to gzip to STDOUT and pipe it for another process pg_basebackup fails saying that it's not possible to gzip to STDOUT. 1) Po

Re: Memory footprint diff between 9.5 and 12

On Thu, May 7, 2020 at 11:39 PM Laurenz Albe wrote: > On Thu, 2020-05-07 at 13:33 -0700, Tory M Blue wrote: > > I hadn't noticed this until today, but a running 9.5 system with buffers > at 10GB starts > > and has been running years without issues. (15GB available) > > > > Postgres 12 will not st

Re: Thoughts on how to avoid a massive integer update.

Well as I said, I think you could add a column to info_table alter table info_table add orig_id int; update info_table set orig_id = info_table_sid; update info_table set info_table_sid = 456 where info_table_sid = 456; alter table data_table drop reference NOT SQL alter table data_table make re

Re: Thoughts on how to avoid a massive integer update.

From: Rob Sargent Date: Friday, May 8, 2020 at 11:05 AM To: "Fehrle, Brian" Cc: Adrian Klaver , "pgsql-gene...@postgresql.org" Subject: Re: Thoughts on how to avoid a massive integer update. [External Email] Could you show an example table relationship? It’s a simple one-to-many relatio

Re: Thoughts on how to avoid a massive integer update.

From: "David G. Johnston" Date: Friday, May 8, 2020 at 11:48 AM To: "Fehrle, Brian" Cc: "pgsql-gene...@postgresql.org" Subject: Re: Thoughts on how to avoid a massive integer update. [External Email] On Mon, May 4, 2020 at 2:33 PM Fehrle, Brian mailto:bfeh...@comscore.com>> wrote: I NEED to

Re: Odd postgres12 upgrade is changing or munging a password?

On 5/8/20 12:28 PM, Tory M Blue wrote: So what are you using to do the ALTER you mentioned earlier? alter user 'user' with password 'password';   I run this after, because as I've shown the upgrade is changing this single password, the why is the question. I can recover, by changing it

Re: pg_basebackup cannot compress to STDOUNT

On 5/8/2020 12:18 PM, Adrian Klaver wrote: On 5/8/20 12:14 PM, Support wrote: Hi, Despite of the --help saying that it's possible to gzip to STDOUT and pipe it for another process pg_basebackup fails saying that it's not possible to gzip to STDOUT. 1) Postgres version? 2) Command run? 3

Re: Odd postgres12 upgrade is changing or munging a password?

On 5/8/20 12:16 PM, Tory M Blue wrote: Please reply to list also. Ccing list. To maybe answer that: 1) Can you find out what the clear text version of the password is? Not necessary to share here, just indicate anything special about it. I can't, MD5 hashed, I don't know of a wa

Re: pg_restore V12 fails consistently against piped pg_dumps

On Fri, May 8, 2020 at 10:59 AM Adrian Klaver wrote: > > On 5/7/20 11:47 PM, Laurenz Albe wrote: > > On Thu, 2020-05-07 at 11:04 -0400, Mohamed Wael Khobalatte wrote: > >> Thanks Tom, I will review those changes tonight. In the meantime, to > >> reproduce, I run the following: > >> > >> - createdb

Re: pg_basebackup cannot compress to STDOUNT

On 5/8/20 12:14 PM, Support wrote: Hi, Despite of the --help saying that it's possible to gzip to STDOUT and pipe it for another process pg_basebackup fails saying that it's not possible to gzip to STDOUT. 1) Postgres version? 2) Command run? 3) Error reported? Who to believe then?

Re: Odd postgres12 upgrade is changing or munging a password?

On 5/8/20 12:11 PM, Tory M Blue wrote: On Thu, May 7, 2020 at 11:41 PM Laurenz Albe > wrote: On Thu, 2020-05-07 at 11:55 -0700, Tory M Blue wrote: > Going from 9.5 to 12 and 2 times now, I've had a password either go missing or munged. > I've

pg_basebackup cannot compress to STDOUNT

Hi, Despite of the --help saying that it's possible to gzip to STDOUT and pipe it for another process pg_basebackup fails saying that it's not possible to gzip to STDOUT. Who to believe then?

Re: Odd postgres12 upgrade is changing or munging a password?

On 5/8/20 12:03 PM, Tory M Blue wrote: On Thu, May 7, 2020 at 12:32 PM Adrian Klaver > wrote: On 5/7/20 12:24 PM, Tory M Blue wrote: > Yes same password, I'm using a basic alter command to put the right > password back. > > I'm doing a

Re: Odd postgres12 upgrade is changing or munging a password?

On Thu, May 7, 2020 at 11:41 PM Laurenz Albe wrote: > On Thu, 2020-05-07 at 11:55 -0700, Tory M Blue wrote: > > Going from 9.5 to 12 and 2 times now, I've had a password either go > missing or munged. > > I've had to add an alter statement at the end of the upgrade. > > > > The DB is functioning

Re: Odd postgres12 upgrade is changing or munging a password?

On Thu, May 7, 2020 at 12:32 PM Adrian Klaver wrote: > On 5/7/20 12:24 PM, Tory M Blue wrote: > > Yes same password, I'm using a basic alter command to put the right > > password back. > > > > I'm doing another upgrade in an hour, and will do some more checks to > > see if it's trying to use anot

Re: Best way to use trigger to email a report ?

Got it. On Fri, May 8, 2020 at 2:05 PM David G. Johnston wrote: > On Fri, May 8, 2020 at 10:19 AM Christophe Pettus > wrote: > >> If you don't want to periodically poll the table, you can use NOTIFY >> within the trigger to wake up a process that is waiting on NOTIFY. >> > > Kinda. > > "With th

Re: Best way to use trigger to email a report ?

On Fri, May 8, 2020 at 10:19 AM Christophe Pettus wrote: > If you don't want to periodically poll the table, you can use NOTIFY > within the trigger to wake up a process that is waiting on NOTIFY. > Kinda. "With the libpq library, the application issues LISTEN as an ordinary SQL command, and th

Re: Thoughts on how to avoid a massive integer update.

On Mon, May 4, 2020 at 2:33 PM Fehrle, Brian wrote: > I NEED to update every single row in all these tables, changing the > integer value to a different integer. > > > Does anyone have any hackery ideas on how to achieve this in less time? > Probably the only solution that would perform computa

Re: Best way to use trigger to email a report ?

On Fri, May 8, 2020 at 9:26 AM David Gauthier wrote: > psql (9.6.0, server 11.3) on linux > > Looking for ideas. I want a trigger to... > 1) compose an html report based on DB content > 2) email the report to a dist list (dl = value of a table column) > > If this will involve hybrid coding, I pr

Re: Best way to use trigger to email a report ?

> On May 8, 2020, at 09:26, David Gauthier wrote: > > psql (9.6.0, server 11.3) on linux > > Looking for ideas. I want a trigger to... > 1) compose an html report based on DB content > 2) email the report to a dist list (dl = value of a table column) You probably *don't* want to actually se

Re: Best way to use trigger to email a report ?

On Fri, 2020-05-08 at 12:26 -0400, David Gauthier wrote: > psql (9.6.0, server 11.3) on linux > > Looking for ideas. I want a trigger to... > 1) compose an html report based on DB content > 2) email the report to a dist list (dl = value of a table column) > > If this will involve hybrid coding,

Re: Thoughts on how to avoid a massive integer update.

>Could you show an example table relationship? > > It’s a simple one-to-many relationship: > *Info_table* > info_table_sid integer > > > *data_table* > data_table_sid integer, > info_table_id integer references info_table(info_table_sid), > > > Right, and now you wish to change the value

Re: Thoughts on how to avoid a massive integer update.

On 5/4/20, 3:56 PM, "Adrian Klaver" wrote: [External Email] On 5/4/20 2:32 PM, Fehrle, Brian wrote: > Hi all, > > This is a shot in the dark in hopes to find a magic bullet to fix an > issue I have, I can’t personally think of any solution myself. > > I

Best way to use trigger to email a report ?

psql (9.6.0, server 11.3) on linux Looking for ideas. I want a trigger to... 1) compose an html report based on DB content 2) email the report to a dist list (dl = value of a table column) If this will involve hybrid coding, I prefer PL/perl. The linux env has both "mail" and "mutt" (if this is

Re: pg_restore V12 fails consistently against piped pg_dumps

On 5/7/20 11:47 PM, Laurenz Albe wrote: On Thu, 2020-05-07 at 11:04 -0400, Mohamed Wael Khobalatte wrote: Thanks Tom, I will review those changes tonight. In the meantime, to reproduce, I run the following: - createdb test - create some tables, I used a simple loop to create 10 empty ones. - pg

Re: pg_temp schema created while using DB Link

On 5/8/20 12:32 AM, Jill Jade wrote: Hello We do not have any temporary tables in the Oracle database. The temporary schema is created only while using the db_link. These temp schemas (pg_temp, pg_toast) are not disappearing even after restarting the database. These are empty schemas that d

Re: pg_temp schema created while using DB Link

Jill Jade schrieb am 08.05.2020 um 09:32: > We do not have any temporary tables in the Oracle database. The > temporary schema is created only while using the db_link. These temp > schemas (pg_temp, pg_toast) are not disappearing even after > restarting the database. The temp tables would be creat

Re: pg_temp schema created while using DB Link

Hello We do not have any temporary tables in the Oracle database. The temporary schema is created only while using the db_link. These temp schemas (pg_temp, pg_toast) are not disappearing even after restarting the database. These are empty schemas that do not contain anything (tables, views etc).

Re: [GENERAL] import .sql file into PostgreSQL database

Just going thru old emails and came across this one. Thought to text you. How have you been? What are you up to? How can I rejoin this group? Gaurav On Wed, Apr 9, 2014 at 7:32 PM Michael Paquier wrote: > On Thu, Apr 10, 2014 at 1:02 AM, Raymond O'Donnell wrote: > > I think that message is com