Re: Index no longer being used, destroying and recreating it restores use.

[Koen De Groote]

Alright, I've done that, and that seems to be a very good result:
https://explain.depesz.com/s/xIph

The method I ended up using:

create or replace function still_needs_backup(shouldbebackedup bool,
backupperformed bool)
returns BOOLEAN as $$
   select $1 AND NOT $2;
$$
language sql immutable;

And the index is as suggested.

It seems the amount of rows we end up with has improved.

Thank you for your help. I wasn't aware functions could interact with
indexes in such a manner.

Regards,
Koen De Groote

On Mon, Jun 15, 2020 at 8:27 PM Michael Lewis  wrote:

> On Tue, Jun 9, 2020 at 6:24 AM Koen De Groote  wrote:
>
>> Right. In that case, the function I ended up with is this:
>>
>> create or replace function still_needs_backup(bool, bool)
>> returns BOOLEAN as $$
>> BEGIN
>> PERFORM 1 from item where shouldbebackedup=$1 and backupperformed=$2;
>>   IF FOUND THEN
>> RETURN TRUE;
>>   ELSE
>> RETURN FALSE;
>>   END IF;
>> END;
>> $$
>> language plpgsql;
>>
>
> I meant something like the below (not tested)-
>
> create or replace function still_needs_backup(shouldbebackedup bool,
> backupperformed bool)
> returns BOOLEAN as $$
> BEGIN
>return $1 AND NOT $2;
> END;
> $$
> language sql;
>
> CREATE INDEX CONCURRENTLY index_test ON item USING btree (itemCreated)
> WHERE still_needs_backup(shouldbebackedup, backupperformed);
> ANALYZE item;
>
>>

Re: Index no longer being used, destroying and recreating it restores use.

[Bruce Momjian]

On Tue, Jun 16, 2020 at 11:49:15AM +0200, Koen De Groote wrote:
> Alright, I've done that, and that seems to be a very good result: https://
> explain.depesz.com/s/xIph
> 
> The method I ended up using:
> 
> create or replace function still_needs_backup(shouldbebackedup bool,
> backupperformed bool)
> returns BOOLEAN as $$
>    select $1 AND NOT $2;
> $$
> language sql immutable;
> 
> And the index is as suggested.
> 
> It seems the amount of rows we end up with has improved.
> 
> Thank you for your help. I wasn't aware functions could interact with indexes
> in such a manner.

This blog entry explains how statistics on expression/function indexes
can help:

https://momjian.us/main/blogs/pgblog/2017.html#February_20_2017

-- 
  Bruce Momjian  https://momjian.us
  EnterpriseDB https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee

pg_repack: WARNING: relation must have a primary key or not-null unique keys

[Eugene Pazhitnov]

Hello everyone!

eugene@dignus:/var/www/html/health$ psql xbox
Timing is on.
psql (12.3 (Ubuntu 12.3-1.pgdg20.04+1))
Type "help" for help.

xbox=> \d herostat
   Table "public.herostat"
  Column  |   Type   | Collation | Nullable | Default
--+--+---+--+-
 xuid | bigint   |   | not null |
 titleid  | bigint   |   | not null |
 value| text |   |  |
 valfloat | double precision |   |  |
 heroid   | integer  |   | not null |
Indexes:
"herostat_pkey" PRIMARY KEY, btree (xuid, titleid, heroid) INCLUDE
(valfloat)
"herostat_heroid_idx" btree (heroid)
"herostat_titleid_idx" btree (titleid)
Triggers:
herostat_min_update BEFORE UPDATE ON herostat FOR EACH ROW EXECUTE
FUNCTION suppress_redundant_updates_trigger()

xbox=> \q
eugene@dignus:/var/www/html/health$ sudo -u postgres pg_repack -t herostat
-N -d xbox
INFO: Dry run enabled, not executing repack
WARNING: relation "public.herostat" must have a primary key or not-null
unique keys
eugene@dignus:/var/www/html/health$ pg_repack --version
pg_repack 1.4.5
eugene@dignus:/var/www/html/health$

What is wrong and what I can do to fix it?

-- 
Evgeny Pazhitnov

Re: Something else about Redo Logs disappearing

[Peter]

On Sun, Jun 14, 2020 at 03:05:15PM +0200, Magnus Hagander wrote:

! > You can see that all the major attributes (scheduling, error-handling,
! > signalling, ...) of a WAL backup are substantially different to that
! > of any usual backup.
! 
! > This is a different *Class* of backup object, therefore it needs an
! > appropriate infrastructure that can handle these attributes correctly.
! >
! 
! Yes, this is *exactly* why special-handling the WAL during the base backup
! makes a lot of sense.

Certainly. Only I prefer to do the special-handling *outside of* the
base backup.

! Is it required? No.
! Will it make your backups more reliable? Yes.

*shrug* I have no benefit in increasing reliability from 250% to 330%,
if that would be the case at all.

! But, if You never have considered *continuous* archiving, and only
! > intend to take a functional momentarily backup of a cluster, then You
! > may well have never noticed these differences. I noticed them mainly
! > because I did *BUILD* such an infrastructure (the 20 lines of shell
! > script, you know).
! >
! 
! Yes, if you take a simplistic view of your backups, then yes.

You appear to sound like an insurance salesman who desperately tries
to sell a third health insurance policy to somebody who already has
two of them, by trying to build on unfounded precariousness.

! ! There is *absolutely* no need for threading to use the current APIs. You
! > ! need to run one query, go do something else, and then run another
! > ! query.
! >
! > Wrong. The point is, I dont want to "go do something else", I have to
! > exit() and get back to the initiator at that place.
! >
! 
! That is not a requirement of the current PostgreSQL APIs.

We'll be done with that whole API in a few more lines now. (I'm getting
tired of this.)

! (in fact, using
! threading would add a significant extra burden there, as libpq does not
! allow sharing of connections between threads)

I never said one would need to thread the DB connections.

! That is a requirement, and indeed a pretty sharp limitation, of the *other*
! APIs you are working with, it sounds like.

What "other"?

! The PostgreSQL APIs discussed to *not* require you to do an exit(). Nor do
! they require any form of threading.

Ah, nice try! But, we're *NOT* shifting blame around. We do instead
get things working. We do proper engineering.
 
! And the fact that you need to do an exit() would negate any threading
! anyway, so that seems to be a false argument regardless.

You do know exactly what I'm talking about.

! This is also clearly visible in Laurenz' code: he utilizes two
! > unchecked background tasks (processes, in this case) with loose
! > coupling for the purpose, as it does not work otherwise.
! >
! 
! Yes, because he is also trying to work around a severely limited API *on
! the other side*.

There is no "other" side. There is only *one* side: to get things
working. And for interaction, Jon Postel's law applies:

  Be conservative in what you provide, and liberal in what you require.

This is how the Internet was built. The modern-day linux-youngsters
tend to forget that we all stand on the shoulders of giants.

! The most interesting point in there appears to be this:
! >   > that the backup label and tablespace map files are not written to
! >   > disk. Instead, their would-be contents are returned in *labelfile
! >   > and *tblspcmapfile,
! >
! > This is in do_pg_start_backup() - so we actually HAVE this data
! > already at the *START* time of the backup!
! 
! 
! > Then why in hell do we wait until the END of the backup before we
! > hand this data to the operator: at a time when the DVD with the
! >
! 
! Because it cannot be safely written *into the data directory*.
! 
! Now, it could be written *somewhere else*, that is true. And then you would
! add an extra step at restore time to rename it back. But then your restore
! would now also require a plugin.

Yes, and as it is now, it requires girl Friday to fetch them from
the line-printer and mix them up - which, as we already got explained,
can end up a *lot* worse. Or, equivalently and as here practically
demonstrated, some consultant trainee writing some script which, when
accidentially invoked twice, creates an inconsistent backup, and
this being invisible to the operator. That's indeed dangerous enough
for my taste.

But lets grab that from the start:
Yes, I didn't trust the docs. Because, as people here are so crazy
about the old API being troublesome and dangerous and must be
deprecated, and the whole thing being so imminent, then there should
be some REASON for that. And from the docs I could not see any reason
 - so I supposed there must be something else in pg_start_backup();
something that is not explained in the docs, and that would explain
the whole bohei.

But, in fact, there is no such thing.

First, the backup_label, which should not stay in the running cluster
tree. So, what bad does happen when it stays there? Nothing at all.
The cluster migh

Re: Something else about Redo Logs disappearing

[Peter]

On Mon, Jun 15, 2020 at 09:46:34PM +0200, Laurenz Albe wrote:
! On Mon, 2020-06-15 at 19:00 +0200, Peter wrote:
! > And that is one of a couple of likely pitfalls I perceived when
! > looking at that new API.
! 
! That is a property of my scripts, *not* of the non-exclusive
! backup API...

Then how would I have figured it before even knowing Your scripts?

The API requires such kind of programming, and such kind of
programming creates dangerous pitfalls. After You have trained soms
hundreds of system administrators, You will also see such things
right at first glance.

But then it's futile to discuss with religious people, as they are
blind to reasoning: AL II. 58, "The slaves shall serve."


cheerio,
PMc

Re: Something else about Redo Logs disappearing

[Laurenz Albe]

On Tue, 2020-06-16 at 00:28 +0200, Peter wrote:
> On Mon, Jun 15, 2020 at 09:46:34PM +0200, Laurenz Albe wrote:
> ! On Mon, 2020-06-15 at 19:00 +0200, Peter wrote:
> ! > And that is one of a couple of likely pitfalls I perceived when
> ! > looking at that new API.
> ! 
> ! That is a property of my scripts, *not* of the non-exclusive
> ! backup API...
> 
> Then how would I have figured it before even knowing Your scripts?

By reading the documentation and trying it?

> The API requires such kind of programming, and such kind of
> programming creates dangerous pitfalls. After You have trained soms
> hundreds of system administrators, You will also see such things
> right at first glance.

The API can be used by hand, calling two SQL functions.
I have trained some hundreds of administrators, and they could follow.

> But then it's futile to discuss with religious people, as they are
> blind to reasoning: AL II. 58, "The slaves shall serve."

Suspicion becomes certainty:
You are not out to understand, but to troll.

Bye.

Laurenz Albe

Re: pg_repack: WARNING: relation must have a primary key or not-null unique keys

[Michael Lewis]

On Tue, Jun 16, 2020, 4:52 AM Eugene Pazhitnov  wrote:

> xbox=> \d herostat
>Table "public.herostat"
> Indexes:
> "herostat_pkey" PRIMARY KEY, btree (xuid, titleid, heroid) INCLUDE
> (valfloat)
>
> WARNING: relation "public.herostat" must have a primary key or not-null
> unique keys
>
> What is wrong and what I can do to fix it?
>

I don't know, but I would guess that the "include" option is not supported
by pg_repack.

>

Re: pg_repack: WARNING: relation must have a primary key or not-null unique keys

[Tom Lane]

Eugene Pazhitnov  writes:
> xbox=> \d herostat
> ...
> "herostat_pkey" PRIMARY KEY, btree (xuid, titleid, heroid) INCLUDE 
> (valfloat)

> eugene@dignus:/var/www/html/health$ sudo -u postgres pg_repack -t herostat
> -N -d xbox
> INFO: Dry run enabled, not executing repack
> WARNING: relation "public.herostat" must have a primary key or not-null
> unique keys

Apparently pg_repack can't work with a primary key index that has INCLUDE
columns.  I have no idea whether that's a fundamental limitation or it's
just that pg_repack hasn't been taught about the INCLUDE feature.  In
either case, you need to go consult pg_repack's author(s), who may or
may not keep up on this list.

regards, tom lane

Minor Upgrade Question

[Susan Joseph]

 So when I first started working with PostgreSQL I was using the latest version 
(11.2).   I don't want to move to 12 yet but I would like to get my 11.2 up to 
11.8.  Due to my servers not being connected to the Internet I ended up 
downloading the libraries and building the files locally.  My question is how 
do I upgrade to 11.8?  I know how to go and get the rpms now and download those 
to a disconnected server and then install PostgreSQL that way.  I was able to 
install 11.8 on another server using the rpms.  But my directories are 
different.  The rpm install placed the files into the directory /data/pgsql-11. 
 My 11.2 database is in /data/pgsql.  I checked the rpm file and it says that 
the files are not relocatable.  So I can do a new install of 11.8 via the rpms 
and it will place the files into /data/pgsql-11, can just need to copy the 
executable files in the /data/pgsql-11/bin directory into my /data/pgsql/bin or 
are there other files that need to be copied over?  Is there a better way to do 
this rather than reinstalling postgreSQL again on a server that already has it? 
 

Thanks,  Susan

create batch script to import into postgres tables

[Pepe TD Vo]

good morning experts,
I nêd to set up a batch script to import multi csv files to import them to 
Postgres tables.  Each csv files will be named table1_todaydate.csv, 
table2_todaydate.csv, etc... tablen_todaydate.csv.  Each csv file will import 
to its table and how do I execute the script to called psql from AWS?  Do I 
need to create each batch file for import each table?
all export file is store in c:\export\files\
thank you.
Bach-Nga





Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their 
mistakes you will be alone. So judge less, love, and forgive more.To call him a 
dog hardly seems to do him justice though in as much as he had four legs, a 
tail, and barked, I admit he was, to all outward appearances. But to those who 
knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich 
*** Faithful talent *** Sharing success

Re: create batch script to import into postgres tables

[Adrian Klaver]

On 6/16/20 7:20 AM, Pepe TD Vo wrote:

good morning experts,

I nêd to set up a batch script to import multi csv files to import them 
to Postgres tables.  Each csv files will be named table1_todaydate.csv, 
table2_todaydate.csv, etc... tablen_todaydate.csv.  Each csv file will 
import to its table and how do I execute the script to called psql from 
AWS?  Do I need to create each batch file for import each table?


You have psql installed on your local(Windows?) machine?

Or are you using psql in your AWS instance?



all export file is store in c:\export\files\

thank you.

Bach-Nga






**
*Bach-Nga

*No one in this world is pure and perfect.  If you avoid people for 
their mistakes you will be alone. So judge less, love, and forgive 
more.EmojiEmojiEmoji
To call him a dog hardly seems to do him justice though in as much as he 
had four legs, a tail, and barked, I admit he was, to all outward 
appearances. But to those who knew him well, he was a perfect gentleman 
(Hermione Gingold)


**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success





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

PSQL console encoding

[Jean Gabriel]

Hello,

I am having some issues setting/using my PSQL console encoding to UTF-8 
under Windows 10.



I have a Windows server and client. The |Postgres 12| database contains 
tables with content in multiple languages (ex: English, French (with 
characters such as |é|), Korean (with characters such as |브|)).



select version();
  version

 PostgreSQL 12.1, compiled by Visual C++ build 1914, 64-bit


The DB is encoded as |UTF8|

|
|

|show server_encoding;
 server_encoding
-
 UTF8|

|
|

The |client_encoding| is set to |UTF8|

|
|

| show client_encoding;
 client_encoding
-
 UTF8|

|
|

The console code page is set to |65001| (Microsoft's UTF8 codepage), and 
has a suitable font ( |Lucida Console|).



I nevertheless get the different code page warning when logging to my DB

psql (12.1)
WARNING: Console code page (65001) differs from Windows code page (1252)
 8-bit characters might not work correctly. See psql reference
 page "Notes for Windows users" for details.



I must be able to properly display English and French text, query the DB 
in English and French and be able to "output something", i.e. not get 
any error, if touching text in another language.


The issue is I can't type / copy-paste any accented character. If I do, 
the special character acts as a comment but the input continues on the 
next line, and I need to close it again.


In the following example, note that |é_fg| is gone, that there is no 
prompt on the |hij| line and that the output concatenates |abcd_hij| on 
one line


|myuser=>select'abcd_é_fg';hij myuser'> 
klm';?column?--abcd_hij+klm (1row)|


The issue is really with the typing, because I can export and load files 
containing such chars.
Ex: save |select 'é';| in |sel.sql|, run |psql -U myuser -d mydb -f 
C:/sel.sql| will output


|?column?--é|



On the working well side, I can display what I need

|selectchr(8217);--sort of apostrophechr -’selectchr(48652);chr 
-브--(though displayed as a square - unknown character, ok)|




I have tried to set |Windows language for non-unicode program| to 
|UTF8|, which had no effect on the issue. The setting was recognized as 
the PSQL warning (Console code page (65001) differs from Windows code 
page (1252)) was not displayed anymore, but I still couldn't do select 'é';


If I set the code page to |1252| and the |client-encoding| to |win1252|, 
I can display the accented chars, I can type them, but if I want to 
display text in another language like Korean I get and error


|selectchr(48652);ERROR:character withbyte sequence 
0xeb0xb80x8cinencoding "UTF8"has no equivalent inencoding "WIN1252" |


||So back to the question, is there a way of having a working UTF8 psql 
console?


Thank you,
Jean Gabriel

Re: create batch script to import into postgres tables

[Pepe TD Vo]

I can run \copy in Linux with individual csv file into the table fine and run 
import using pgadmin into AWS instance.  I am trying to run \copy all csv files 
import into its own table in Linux and in AWS instance. If all csv files into 
one table is fine but each csv for each table.  Should I create one batch job 
for each imported table?  If each batch file import csv to its table would be 
fine via \copy table_name(col1, col2, ... coln) from '/path/tablename.csv' 
delimiter ',' csv header;  right?
Also, the problem is I can't pull/execute psql from window client to pull the 
psql in aws instance and don't know how to create the batch script for this 
run.  I tried simple \copy pull from c:\tes.csv and psql is unknown.


Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their 
mistakes you will be alone. So judge less, love, and forgive more.To call him a 
dog hardly seems to do him justice though in as much as he had four legs, a 
tail, and barked, I admit he was, to all outward appearances. But to those who 
knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich 
*** Faithful talent *** Sharing success

 

On Tuesday, June 16, 2020, 10:39:45 AM EDT, Adrian Klaver 
 wrote:  
 
 On 6/16/20 7:30 AM, Pepe TD Vo wrote:
Please post to list also.
Ccing list.

> using psql in AWS instance
> also psql in Linux.  I can run psql in linux if create a batch file, but 
> don't know how to pull psql in aws instance if the batch script run in 
> Window client.  Also, I need help to pull each csv import to its own 
> table.  Should I create each batch script for each import table?

So the AWS instance and Linux instance are different?

To me the simplest solution would be to push the CSV files to the AWS 
instance and work from there. The files will need to be run through a 
Postgres command to be imported into a table.

Are you familiar with 
COPY(https://www.postgresql.org/docs/12/sql-copy.html) or 
\copy(https://www.postgresql.org/docs/12/app-psql.html)?


> 
> **
> *Bach-Nga
> 
> *No one in this world is pure and perfect.  If you avoid people for 
> their mistakes you will be alone. So judge less, love, and forgive 
> more.EmojiEmojiEmoji
> To call him a dog hardly seems to do him justice though in as much as he 
> had four legs, a tail, and barked, I admit he was, to all outward 
> appearances. But to those who knew him well, he was a perfect gentleman 
> (Hermione Gingold)
> 
> **Live simply **Love generously **Care deeply **Speak kindly.
> *** Genuinely rich *** Faithful talent *** Sharing success
> 
> 
> 
> 
> On Tuesday, June 16, 2020, 10:25:03 AM EDT, Adrian Klaver 
>  wrote:
> 
> 
> On 6/16/20 7:20 AM, Pepe TD Vo wrote:
>  > good morning experts,
>  >
>  > I nêd to set up a batch script to import multi csv files to import them
>  > to Postgres tables.  Each csv files will be named table1_todaydate.csv,
>  > table2_todaydate.csv, etc... tablen_todaydate.csv.  Each csv file will
>  > import to its table and how do I execute the script to called psql from
>  > AWS?  Do I need to create each batch file for import each table?
> 
> You have psql installed on your local(Windows?) machine?
> 
> Or are you using psql in your AWS instance?
> 
>  >
>  > all export file is store in c:\export\files\
>  >
>  > thank you.
>  >
>  > Bach-Nga
>  >
>  >
>  >
>  >
>  >
>  >
>  > **
>  > *Bach-Nga
>  >
>  > *No one in this world is pure and perfect.  If you avoid people for
>  > their mistakes you will be alone. So judge less, love, and forgive
>  > more.EmojiEmojiEmoji
> 
>  > To call him a dog hardly seems to do him justice though in as much as he
>  > had four legs, a tail, and barked, I admit he was, to all outward
>  > appearances. But to those who knew him well, he was a perfect gentleman
>  > (Hermione Gingold)
>  >
>  > **Live simply **Love generously **Care deeply **Speak kindly.
>  > *** Genuinely rich *** Faithful talent *** Sharing success
> 
>  >
>  >
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com 
> 


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

Re: create batch script to import into postgres tables

[Adrian Klaver]

On 6/16/20 7:59 AM, Pepe TD Vo wrote:

Just noticed you cross posted to pgsql-admin listed. FYI, That is not a 
good practice.


I can run \copy in Linux with individual csv file into the table fine 
and run import using pgadmin into AWS instance. I am trying to run \copy 
all csv files import into its own table in Linux and in AWS instance. If 
all csv files into one table is fine but each csv for each table. Should 
I create one batch job for each imported table?  If each batch file 
import csv to its table would be fine via \copy table_name(col1, col2, 
... coln) from '/path/tablename.csv' delimiter ',' csv header; right?


Yes, you will need to copy each file into its own table.



Also, the problem is I can't pull/execute psql from window client to 
pull the psql in aws instance and don't know how to create the batch 
script for this run.  I tried simple \copy pull from c:\tes.csv and psql 
is unknown.


There is no good/easy way I know of to install just psql on Windows. You 
are better off copying the CSV files to the AWS instance.


Do you have PuTTY installed?:

https://www.chiark.greenend.org.uk/~sgtatham/putty/latest.html

If not I would take a look at above link. PuTTY brings SSH to Windows. 
The relevant part to this issue is:


https://the.earth.li/~sgtatham/putty/0.73/htmldoc/Chapter5.html#pscp

"PSCP, the PuTTY Secure Copy client, is a tool for transferring files 
securely between computers using an SSH connection. "





**
*Bach-Nga

*No one in this world is pure and perfect.  If you avoid people for 
their mistakes you will be alone. So judge less, love, and forgive 
more.EmojiEmojiEmoji
To call him a dog hardly seems to do him justice though in as much as he 
had four legs, a tail, and barked, I admit he was, to all outward 
appearances. But to those who knew him well, he was a perfect gentleman 
(Hermione Gingold)


**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success




On Tuesday, June 16, 2020, 10:39:45 AM EDT, Adrian Klaver 
 wrote:



On 6/16/20 7:30 AM, Pepe TD Vo wrote:
Please post to list also.
Ccing list.

 > using psql in AWS instance
 > also psql in Linux.  I can run psql in linux if create a batch file, but
 > don't know how to pull psql in aws instance if the batch script run in
 > Window client.  Also, I need help to pull each csv import to its own
 > table.  Should I create each batch script for each import table?

So the AWS instance and Linux instance are different?

To me the simplest solution would be to push the CSV files to the AWS
instance and work from there. The files will need to be run through a
Postgres command to be imported into a table.

Are you familiar with
COPY(https://www.postgresql.org/docs/12/sql-copy.html) or
\copy(https://www.postgresql.org/docs/12/app-psql.html)?


 >
 > **
 > *Bach-Nga
 >
 > *No one in this world is pure and perfect.  If you avoid people for
 > their mistakes you will be alone. So judge less, love, and forgive
 > more.EmojiEmojiEmoji
 > To call him a dog hardly seems to do him justice though in as much as he
 > had four legs, a tail, and barked, I admit he was, to all outward
 > appearances. But to those who knew him well, he was a perfect gentleman
 > (Hermione Gingold)
 >
 > **Live simply **Love generously **Care deeply **Speak kindly.
 > *** Genuinely rich *** Faithful talent *** Sharing success
 >
 >
 >
 >
 > On Tuesday, June 16, 2020, 10:25:03 AM EDT, Adrian Klaver
 > mailto:adrian.kla...@aklaver.com>> wrote:
 >
 >
 > On 6/16/20 7:20 AM, Pepe TD Vo wrote:
 >  > good morning experts,
 >  >
 >  > I nêd to set up a batch script to import multi csv files to import 
them
 >  > to Postgres tables.  Each csv files will be named 
table1_todaydate.csv,

 >  > table2_todaydate.csv, etc... tablen_todaydate.csv.  Each csv file will
 >  > import to its table and how do I execute the script to called psql 
from

 >  > AWS?  Do I need to create each batch file for import each table?
 >
 > You have psql installed on your local(Windows?) machine?
 >
 > Or are you using psql in your AWS instance?
 >
 >  >
 >  > all export file is store in c:\export\files\
 >  >
 >  > thank you.
 >  >
 >  > Bach-Nga
 >  >
 >  >
 >  >
 >  >
 >  >
 >  >
 >  > **
 >  > *Bach-Nga
 >  >
 >  > *No one in this world is pure and perfect.  If you avoid people for
 >  > their mistakes you will be alone. So judge less, love, and forgive
 >  > more.EmojiEmojiEmoji
 >
 >  > To call him a dog hardly seems to do him justice though in as much 
as he

 >  > had four legs, a tail, and barked, I admit he was, to all outward
 >  > appearances. But to those who knew him well, he was a perfect 
gentleman

 >  > (Hermione Gingold)
 >  >
 >  > **Live simply **Love generously **Care deeply **Speak kindly.
 >  > *** Genuinely rich *** Faithful talent *** Sharing success
 >
 >  >
 >  >
 >
 >
 > --
 > Adrian Klaver
 > adrian.kla...@aklaver.com  

autovacuum failing on pg_largeobject and disk usage of the pg_largeobject growing unchecked

[Jim Hurne]

We have a cloud service that uses PostgreSQL to temporarily store binary 
content. We're using PostgreSQL's Large Objects to store the binary 
content. Each large object lives anywhere from a few hundred milliseconds 
to 5-10 minutes, after which it is deleted.

Normally, this works just fine and we have no issues. However, roughly 
every 3 months or so, disk usage on our PostgreSQL database starts to 
increase at an alarming rate. More specifically, the pg_largeobject 
table's disk usage steadily increases even though we've deleted most of 
the large objects. This will continue until the database server runs out 
of disk space (and then all sorts of bad things happen of course).

It looks to us like autovacuum against the pg_largeobject table stops 
working because the pg_stat_all_tables.last_autovacuum column stops 
updating, or is updated infrequently, while it is still updated on other 
tables:


 schemaname | relname | n_live_tup | n_dead_tup | 
last_autovacuum
+-+++---
 pg_catalog | pg_largeobject  |  37205 |   92614852 | 
2020-06-15 01:55:09.037101+00
 pg_toast   | pg_toast_973434724  |281 |   3701 | 
2020-06-15 17:22:54.746452+00
 pg_catalog | pg_largeobject_metadata |320 |   1236 | 
2020-06-15 17:23:49.765878+00
 pg_catalog | pg_shdepend |344 |933 | 
2020-06-15 17:23:54.448855+00
 pg_toast   | pg_toast_2619   | 52 | 85 | 
2020-06-15 17:22:01.387933+00
 pg_catalog | pg_statistic|413 |100 | 
2020-06-15 17:15:52.656791+00
 pg_catalog | pg_class|349 |  2 | 
2020-06-12 17:58:13.147931+00
 pg_catalog | pg_attribute|   2633 |  1 | 
2020-06-12 17:58:13.000114+00


Looking at our PostgreSQL logs, it looks like the autovacuum task takes 
increasingly more time to run (against the pg_largeobject table):

2020-06-12T19:41:58.335931494Z stderr F system usage: CPU: user: 
0.02 s, system: 0.00 s, elapsed: 1.77 s
2020-06-12T19:42:59.704884752Z stderr F system usage: CPU: user: 
0.02 s, system: 0.02 s, elapsed: 3.06 s
2020-06-12T19:44:01.928300512Z stderr F system usage: CPU: user: 
0.06 s, system: 0.01 s, elapsed: 5.44 s
2020-06-12T19:45:14.124209167Z stderr F system usage: CPU: user: 
0.11 s, system: 0.05 s, elapsed: 17.13 s
2020-06-12T19:46:16.28758936Z stderr F  system usage: CPU: user: 0.13 s, 
system: 0.08 s, elapsed: 19.04 s
2020-06-12T19:47:34.264882626Z stderr F system usage: CPU: user: 
0.20 s, system: 0.19 s, elapsed: 36.22 s
2020-06-12T19:49:15.383436343Z stderr F system usage: CPU: user: 
0.28 s, system: 0.38 s, elapsed: 74.06 s
2020-06-12T19:53:47.229361981Z stderr F system usage: CPU: user: 
0.66 s, system: 1.06 s, elapsed: 214.12 s
2020-06-12T20:19:39.619748109Z stderr F system usage: CPU: user: 
4.13 s, system: 5.30 s, elapsed: 1461.16 s
2020-06-12T21:30:31.634637945Z stderr F system usage: CPU: user: 
13.73 s, system: 19.59 s, elapsed: 4225.61 s
2020-06-12T23:54:32.511015886Z stderr F system usage: CPU: user: 
33.57 s, system: 41.90 s, elapsed: 8514.23 s
2020-06-13T04:23:32.230960572Z stderr F system usage: CPU: user: 
66.09 s, system: 82.95 s, elapsed: 16011.25 s
2020-06-13T12:00:37.43434175Z stderr F  system usage: CPU: user: 99.42 s, 
system: 130.48 s, elapsed: 27296.71 s
2020-06-14T02:40:25.19122979Z stderr F  system usage: CPU: user: 202.96 s, 
system: 263.66 s, elapsed: 52653.66 s
2020-06-15T01:55:09.03766272Z stderr F  system usage: CPU: user: 317.54 s, 
system: 544.48 s, elapsed: 83550.21 s

We have several instances of our cloud service, and each instance has it's 
own database. On other instances, the autovacuum elapsed is consistently 
less than 25 seconds on every run.

Other than the increasing elapsed times for the autovacuum, we don't see 
any other indication in the logs of a problem (no error messages, etc).

We're currently using PostgreSQL version 10.10. Our service is JVM-based 
and we're using the PostgreSQL JDBC driver version 42.2.5.

Have we stumbled upon a potential bug here, or do we need to tweak some 
autovacuum settings?  What should we look at next or what should we try 
next to further troubleshoot this?

Regards,

Jim Hurne

Re: Logical replication - ERROR: could not send data to WAL stream: cannot allocate memory for input buffer

[Aleš Zelený]

Thanks for the comment.

from what I was able to monitor memory usage was almost stable and there
were about 20GB allocated as cached memory. Memory overcommit is disabled
on the database server. Might it be a memory issue, since wit was
synchronizing newly added tables with a sum of 380 GB of data containing
JSONB columns (60 bytes to 100kBytes). The problem is, that I was not able
to reproduce it since in dev environment it wors like a charm an as usual
on PROD we were facing this issue.

It is clear that for memory allocation issues testcase would be
appropriate, but I was not able to build reproducible testcase.

Thanks Ales

po 8. 6. 2020 v 8:41 odesílatel Michael Paquier 
napsal:

> On Fri, Jun 05, 2020 at 10:57:46PM +0200, Aleš Zelený wrote:
> > we are using logical replication for more than 2 years and today I've
> found
> > new not yet know error message from wal receiver. The replication was in
> > catchup mode (on publisher side some new tables were created and added to
> > publication, on subscriber side they were missing).
>
> This comes from pqCheckInBufferSpace() in libpq when realloc() fails,
> most probably because this host ran out of memory.
>
> > Repeated several times, finally it proceeded and switch into streaming
> > state. The OS has 64GB RAM, OS + database instance are using usually 20GB
> > rest is used as OS buffers. I've checked monitoring (sampled every 10
> > seconds) and no memory usage peak was visible, so unless it was a very
> > short memory usage peak, I'd not expect the system running out of memory.
> >
> > Is there something I can do to diagnose and avoid this issue?
>
> Does the memory usage increase slowly over time?  Perhaps it was not a
> peak and the memory usage was not steady?  One thing that could always
> be tried if you are able to get a rather reproducible case would be to
> use valgrind and check if it is able to detect any leaks.  And I am
> afraid that it is hard to act on this report without more information.
> --
> Michael
>

Re: autovacuum failing on pg_largeobject and disk usage of the pg_largeobject growing unchecked

[Michael Lewis]

On Tue, Jun 16, 2020 at 10:01 AM Jim Hurne  wrote:

> Other than the increasing elapsed times for the autovacuum, we don't see
> any other indication in the logs of a problem (no error messages, etc).
>
> We're currently using PostgreSQL version 10.10. Our service is JVM-based
> and we're using the PostgreSQL JDBC driver version 42.2.5.
>
> Have we stumbled upon a potential bug here, or do we need to tweak some
> autovacuum settings?  What should we look at next or what should we try
> next to further troubleshoot this?
>


What are your current autovacuum settings? Do you have long
running transactions with any frequency? Decreasing
autovacuum_vacuum_cost_delay to 1 or 2ms may be prudent (default changes
from 20ms down to 2ms with PG 12). Is this a destructive queue of sorts
with no rows permanently stored? If so, I would expect that a daily
scheduled vacuum analyze may be the best course of action.

RE: autovacuum failing on pg_largeobject and disk usage of the pg_largeobject growing unchecked

[Jim Hurne]

Thanks Michael,

Here are our current autovacuum settings:

 autovacuum  | on
 autovacuum_analyze_scale_factor | 0.1
 autovacuum_analyze_threshold| 50
 autovacuum_freeze_max_age   | 2
 autovacuum_max_workers  | 3
 autovacuum_multixact_freeze_max_age | 4
 autovacuum_naptime  | 1min
 autovacuum_vacuum_cost_delay| 20ms
 autovacuum_vacuum_cost_limit| -1
 autovacuum_vacuum_scale_factor  | 0.2
 autovacuum_vacuum_threshold | 50
 autovacuum_work_mem | -1
 log_autovacuum_min_duration | 1s

>  Is this a destructive queue of sorts with no rows permanently stored?

Essentially, yes. The system can create and delete objects at a high rate. 
Here are some recent stats on the pg_largeobject table:

# select * from pg_stat_all_tables where relname = 'pg_largeobject';
-[ RECORD 1 ]---+---
relid   | 2613
schemaname  | pg_catalog
relname | pg_largeobject
seq_scan| 0
seq_tup_read| 0
idx_scan| 66619475
idx_tup_fetch   | 126816721
n_tup_ins   | 57580140
n_tup_upd   | 188474
n_tup_del   | 57640395
n_tup_hot_upd   | 108845
n_live_tup  | 47771
n_dead_tup  | 57787135
n_mod_since_analyze | 115409009
last_vacuum |
last_autovacuum |
last_analyze|
last_autoanalyze|
vacuum_count| 0
autovacuum_count| 0
analyze_count   | 0
autoanalyze_count   | 0

# SELECT pg_size_pretty( pg_total_relation_size('pg_largeobject') );
-[ RECORD 1 ]--+---
pg_size_pretty | 350 GB 

The total size of the actual undeleted large objects is about 60 MB.

Regards,

Jim Hurne




From:   Michael Lewis 
To: Jim Hurne 
Cc: PostgreSQL General 
Date:   06/16/2020 01:06 PM
Subject:[EXTERNAL] Re: autovacuum failing on pg_largeobject and 
disk usage of the pg_largeobject growing unchecked



On Tue, Jun 16, 2020 at 10:01 AM Jim Hurne  wrote:
Other than the increasing elapsed times for the autovacuum, we don't see 
any other indication in the logs of a problem (no error messages, etc).

We're currently using PostgreSQL version 10.10. Our service is JVM-based 
and we're using the PostgreSQL JDBC driver version 42.2.5.

Have we stumbled upon a potential bug here, or do we need to tweak some 
autovacuum settings?  What should we look at next or what should we try 
next to further troubleshoot this?


What are your current autovacuum settings? Do you have long 
running transactions with any frequency? Decreasing 
autovacuum_vacuum_cost_delay to 1 or 2ms may be prudent (default changes 
from 20ms down to 2ms with PG 12). Is this a destructive queue of sorts 
with no rows permanently stored? If so, I would expect that a daily 
scheduled vacuum analyze may be the best course of action.

Re: autovacuum failing on pg_largeobject and disk usage of the pg_largeobject growing unchecked

[Michael Lewis]

On Tue, Jun 16, 2020 at 1:45 PM Jim Hurne  wrote:

> Thanks Michael,
>
> Here are our current autovacuum settings:
>
>  autovacuum  | on
>  autovacuum_analyze_scale_factor | 0.1
>  autovacuum_analyze_threshold| 50
>  autovacuum_freeze_max_age   | 2
>  autovacuum_max_workers  | 3
>  autovacuum_multixact_freeze_max_age | 4
>  autovacuum_naptime  | 1min
>  autovacuum_vacuum_cost_delay| 20ms
>  autovacuum_vacuum_cost_limit| -1
>  autovacuum_vacuum_scale_factor  | 0.2
>  autovacuum_vacuum_threshold | 50
>  autovacuum_work_mem | -1
>  log_autovacuum_min_duration | 1s
>

With no autovacuum_vacuum_cost_limit and autovacuum_work_mem set the same
as maintenance_work_mem, I wouldn't expect any difference between the
performance of manual vs auto vacuum. Still, if you run a manual vacuum
analyze verbose, what sort of output do you get? What
is maintenance_work_mem set to? Are there indexes on this table that
perhaps are very large and needing to be rescanned many times because
maintenance_work_mem isn't high enough to handle in a single pass? You
might try "create index concurrently, drop index concurrently, & rename
index" (reindex concurrently if you were on PG 12) as a sort of online
'vacuum full' on the index(es).

By the way, the best practices for these mailing list suggest partial
quoting and responding in-line or below, not "top posting" with the entire
conversation below.

Sv: autovacuum failing on pg_largeobject and disk usage of the pg_largeobject growing unchecked

[Andreas Joseph Krogh]

På tirsdag 16. juni 2020 kl. 17:59:37, skrev Jim Hurne mailto:jhu...@us.ibm.com>>: 
We have a cloud service that uses PostgreSQL to temporarily store binary
 content. We're using PostgreSQL's Large Objects to store the binary
 content. Each large object lives anywhere from a few hundred milliseconds
 to 5-10 minutes, after which it is deleted.
 [...] 

In my experience vacuumlo, https://www.postgresql.org/docs/12/vacuumlo.html 
, is needed to remove large 
objects, before vacuum can remove them from pg_largeobject. 


--
 Andreas Joseph Krogh 

Re: pg_repack: WARNING: relation must have a primary key or not-null unique keys

[Eugene Pazhitnov]

Ok, thanks a lot! Got it.

вт, 16 июн. 2020 г. в 17:12, Tom Lane :

> Eugene Pazhitnov  writes:
> > xbox=> \d herostat
> > ...
> > "herostat_pkey" PRIMARY KEY, btree (xuid, titleid, heroid) INCLUDE
> (valfloat)
>
> > eugene@dignus:/var/www/html/health$ sudo -u postgres pg_repack -t
> herostat
> > -N -d xbox
> > INFO: Dry run enabled, not executing repack
> > WARNING: relation "public.herostat" must have a primary key or not-null
> > unique keys
>
> Apparently pg_repack can't work with a primary key index that has INCLUDE
> columns.  I have no idea whether that's a fundamental limitation or it's
> just that pg_repack hasn't been taught about the INCLUDE feature.  In
> either case, you need to go consult pg_repack's author(s), who may or
> may not keep up on this list.
>
> regards, tom lane
>


-- 
Evgeny Pazhitnov

Clarification on Expression indexes

[Koen De Groote]

Greetings all.

The following page:
https://www.postgresql.org/docs/11/indexes-expressional.html

States the following:

Index expressions are relatively expensive to maintain, because the derived
> expression(s) must be computed for each row upon insertion and whenever it
> is updated
>

I'd like to get an idea on "relatively expensive". For instance, compared
to a partial index, which is split on one or more boolean values. As
opposed to making a function for this that serves the same identical
calculation.

Let's say that over the lifetime of a row, it rarely gets updated more than
2000 times, and the majority of this is right after creation?

Is this a concern?

Regards,
Koen De Groote

Re: Clarification on Expression indexes

[Tom Lane]

Koen De Groote  writes:
>> Index expressions are relatively expensive to maintain, because the derived
>> expression(s) must be computed for each row upon insertion and whenever it
>> is updated

> I'd like to get an idea on "relatively expensive".

It's basically whatever the cost of evaluating that expression is,
plus the normal costs of index insertion.  If the expression is
something built-in like sin(x), probably the evaluation cost is
negligible ... but with a user-defined function in SQL or some PL,
maybe not so much.

In any case, the real question is "how many expression evaluations am I
going to save over the life of the row, versus how many I pay up-front?"
You didn't address how many queries would benefit from having the index,
so the question is unanswerable with just these facts.

regards, tom lane

Conflict with recovery on PG version 11.6

[Toomas Kristin]

Hi!

Basically after upgrade to version 11.5 from 10.6 I experience error messages 
on streaming replica host “FATAL:  terminating connection due to conflict with 
recovery” and “ERROR: canceling statement due to conflict with recovery”. There 
is no changes for vacuuming on master nor max_standby_streaming_delay for 
replica. I tried to correlate errors with vacuuming process on master but 
according to logs there is no link between them. Somehow I have feeling that 
when query runs longer than value for parameter max_standby_streaming_delay the 
query will be terminated regardless vacuuming process on master.

Is there any changes on version 11.5 what may cause it?

Is there any good solution without setting max_standby_streaming_delay=-1 or 
enabling hot_standby_feedback?

BR,
Toomas