Re: dirty_ratio & dirty_background_ratio settings with huge memory

[Laurenz Albe]

pinker wrote:
> Laurenz Albe wrote
> > Yes, you should set vm.dirty_background_bytes and vm.dirty_bytes
> > and not use the *_ratio settings.
> > 
> > 2 GB for vm.dirty_background_bytes and 1 GB for vm.dirty_bytes sounds
> > fine.

To repeat (for the archive): it should be the other way round.

> Thank you Laurenz. I was always wondering why not to set bigger window
> between those 2. Because setting dirty_background_bytes seems quite natural
> for me, i.e. start to write asynchronously faster, but why to provoke stalls
> faster? is it not better to stretch the window much wider, like 1GB for
> dirty_background_bytes and for instance 20 GB dirty_bytes? it's the Approach
> 3: Both Ways from
> https://lonesysadmin.net/2013/12/22/better-linux-disk-caching-performance-vm-dirty_ratio/

I'd say that setting vm.dirty_background_bytes low and vm.dirty_bytes
very high is not a good idea, at least for databases:

- If flushing data to disk in the background can keep up with the write
  activity, you're fine.  But then you'd also be fine with a lower setting
  for vm.dirty_bytes, right?

- If flushing to disk cannot keep up with the write activity, you'll get
  an I/O spike when everything is flushed to disk at checkpoint time and
  the database may become unresponsive for a while.

The idea behind a low vm.dirty_bytes setting is to avoid one big I/O spike
and have several smaller ones instead.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

save query as sql file

[Łukasz Jarych]

Hello,

I ma trying to save query as sql file in pg_admin4 but file --> save as not
exists like here:

https://www.youtube.com/watch?v=L4KJ_Kpymh4

where can i do it ?

Best,
Jacek

RE: help to query json column

[Charles Clavadetscher]

Hello

> -Original Message-
> From: Arup Rakshit [mailto:aruprakshit1...@outlook.com]
> Sent: Mittwoch, 7. März 2018 05:41
> To: pgsql-general@lists.postgresql.org
> Subject: help to query json column
> 
> Hi,
> 
> I would like to select only rows where signature has a non null value. My 
> json looks like :
> 
> {
>   "carInspection": {
> "damages": [
>   {
> "x": 68.670309653916,
> "y": 44.08014571949,
> "errors": [
>   {
> "code": "BR",
> "description": "Gebrochen"
>   },
>   {
> "code": "F",
> "description": "Reifen platt"
>   }
> ]
>   },
>   {
> "x": 40.8014571949,
> "y": 50.273224043716,
> "errors": [
>   {
> "code": "BR",
> "description": "Gebrochen"
>   },
>   {
> "code": "F",
> "description": "Reifen platt"
>   }
> ]
>   },
>   {
> "x": 48.269581056466,
> "y": 37.340619307832,
> "errors": [
>   {
> "code": "F",
> "description": "Reifen platt"
>   }
> ]
>   },
>   {
> "x": 49.180327868852,
> "y": 15.482695810565,
> "errors": [
>   {
> "code": "F",
> "description": "Reifen platt"
>   }
> ]
>   }
> ],
> "layoutURL": "default",
> "signature1": " 0 1000 

Re: Help troubleshooting SubtransControlLock problems

[Rene Romero Benavides]

For such issues, I find this view very useful (the first one):
https://wiki.postgresql.org/wiki/Lock_Monitoring

Examine blocking_pid's ,  and tell us what kind of operation is blocking
the other processes . Also, are there many long running transactions in
your server?


2018-03-06 21:24 GMT-06:00 Scott Frazer :

> Hi, we have a Postgres 9.6 setup using replication that has recently
> started seeing a lot of processes stuck in "SubtransControlLock" as a
> wait_event on the read-replicas. Like this, only usually about 300-800 of
> them:
>
>
>  179706 | LWLockNamed | SubtransControlLock
>
>  186602 | LWLockNamed | SubtransControlLock
>
>  186606 | LWLockNamed | SubtransControlLock
>
>  180947 | LWLockNamed | SubtransControlLock
>
>  186621 | LWLockNamed | SubtransControlLock
>
> The server then begins to crawl, with some queries just never finishing
> until I finally shut the server down.
>
> Searching for that particular combo of wait_event_type and wait_event only
> seems to turn up the page about statistics collection, but no helpful
> information on troubleshooting this lock.
>
> Restarting the replica server clears the locks and allows us to start
> working again, but it's happened twice now in 12 hours and I'm worried it
> will happen again.
>
> Does anyone have any advice on where to start looking?
>
> Thanks,
> Scott
>
>


-- 
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/

help to query json column

[Arup Rakshit]

Hi,

I would like to select only rows where signature has a non null value. My json 
looks like :

{
  "carInspection": {
"damages": [
  {
"x": 68.670309653916,
"y": 44.08014571949,
"errors": [
  {
"code": "BR",
"description": "Gebrochen"
  },
  {
"code": "F",
"description": "Reifen platt"
  }
]
  },
  {
"x": 40.8014571949,
"y": 50.273224043716,
"errors": [
  {
"code": "BR",
"description": "Gebrochen"
  },
  {
"code": "F",
"description": "Reifen platt"
  }
]
  },
  {
"x": 48.269581056466,
"y": 37.340619307832,
"errors": [
  {
"code": "F",
"description": "Reifen platt"
  }
]
  },
  {
"x": 49.180327868852,
"y": 15.482695810565,
"errors": [
  {
"code": "F",
"description": "Reifen platt"
  }
]
  }
],
"layoutURL": "default",
"signature1": 

Re: dirty_ratio & dirty_background_ratio settings with huge memory

[Andres Freund]

On 2018-03-06 18:59:01 -0700, pinker wrote:
> Andres Freund wrote
> > With a halfway modern PG I'd suggest to rather tune postgres settings
> > that control flushing. That leaves files like temp sorting in memory for
> > longer, while flushing things controlledly for other sources of
> > writes. See *_flush_after settings.
> > 
> > Greetings,
> > 
> > Andres Freund
> 
> Thank you Andres, I missed those parameters appearing. It's not the case for
> this customer, because they have an ancient version 9.0, but it's
> interesting for others :)
> The maximal value - 2MB - is it not to small? Regarding the fact that for
> instance the same buffer will be updated 10 times between checkpoints and
> bgwriter well be flushing it directly to disk 10 times instead to allow the
> buffer to stay in the cache and do the update faster?

In most cases where the same buffer is updated frequently it should be
stored in postgres' buffer cache.  Note you can tune those.

Greetings,

Andres Freund

Help troubleshooting SubtransControlLock problems

[Scott Frazer]

Hi, we have a Postgres 9.6 setup using replication that has recently
started seeing a lot of processes stuck in "SubtransControlLock" as a
wait_event on the read-replicas. Like this, only usually about 300-800 of
them:


 179706 | LWLockNamed | SubtransControlLock

 186602 | LWLockNamed | SubtransControlLock

 186606 | LWLockNamed | SubtransControlLock

 180947 | LWLockNamed | SubtransControlLock

 186621 | LWLockNamed | SubtransControlLock

The server then begins to crawl, with some queries just never finishing
until I finally shut the server down.

Searching for that particular combo of wait_event_type and wait_event only
seems to turn up the page about statistics collection, but no helpful
information on troubleshooting this lock.

Restarting the replica server clears the locks and allows us to start
working again, but it's happened twice now in 12 hours and I'm worried it
will happen again.

Does anyone have any advice on where to start looking?

Thanks,
Scott

Re: dirty_ratio & dirty_background_ratio settings with huge memory

[pinker]

Laurenz Albe wrote
> Yes, you should set vm.dirty_background_bytes and vm.dirty_bytes
> and not use the *_ratio settings.
> 
> 2 GB for vm.dirty_background_bytes and 1 GB for vm.dirty_bytes sounds
> fine.
> 
> Yours,
> Laurenz Albe
> -- 
> Cybertec | https://www.cybertec-postgresql.com

Thank you Laurenz. I was always wondering why not to set bigger window
between those 2. Because setting dirty_background_bytes seems quite natural
for me, i.e. start to write asynchronously faster, but why to provoke stalls
faster? is it not better to stretch the window much wider, like 1GB for
dirty_background_bytes and for instance 20 GB dirty_bytes? it's the Approach
3: Both Ways from
https://lonesysadmin.net/2013/12/22/better-linux-disk-caching-performance-vm-dirty_ratio/



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: dirty_ratio & dirty_background_ratio settings with huge memory

[pinker]

Andres Freund wrote
> With a halfway modern PG I'd suggest to rather tune postgres settings
> that control flushing. That leaves files like temp sorting in memory for
> longer, while flushing things controlledly for other sources of
> writes. See *_flush_after settings.
> 
> Greetings,
> 
> Andres Freund

Thank you Andres, I missed those parameters appearing. It's not the case for
this customer, because they have an ancient version 9.0, but it's
interesting for others :)
The maximal value - 2MB - is it not to small? Regarding the fact that for
instance the same buffer will be updated 10 times between checkpoints and
bgwriter well be flushing it directly to disk 10 times instead to allow the
buffer to stay in the cache and do the update faster?




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Can I grant permissions to specific set of uids (linux) ?

[David G. Johnston]

On Tue, Mar 6, 2018 at 1:26 PM, David Gauthier 
wrote:

> Hi:
>
> I'd like to grant select, insert, update, delete to a table for a specific
> set of uids (linux).  All others get select only.  Can the DB authenticate
> the current linux user and grant access based on the fact that they are
> logged in ()IOW, no passwords ?Is this possible ? If so, how ?
>
>
​see

https://www.postgresql.org/docs/10/static/auth-methods.html#AUTH-PEER

Note it authenticates by the name, not the numeric value.

​If that doesn't cover your need the answer to your question is probably no.

David J.

Re: Can I grant permissions to specific set of uids (linux) ?

[Melvin Davidson]

On Tue, Mar 6, 2018 at 3:26 PM, David Gauthier 
wrote:

> Hi:
>
> I'd like to grant select, insert, update, delete to a table for a specific
> set of uids (linux).  All others get select only.  Can the DB authenticate
> the current linux user and grant access based on the fact that they are
> logged in ()IOW, no passwords ?Is this possible ? If so, how ?
>
> Thanks for any help
>


*> I'd like to grant select, insert, update, delete to a table for a
specific set of uids (linux). *


*PostgreSQL does not allow permissions based on uid's.*

*The accepted/implemented way of doing that is to:*

*1. CREATE the appropriate _user_ ROLEs.*

*2: Create a GROUP (role that cannot login)  with the permissions needed. *
*3. GRANT that GROUP/ROLE to the user ROLEs that need it.  -> *

*GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]*






*https://www.postgresql.org/docs/current/static/sql-creategroup.html
https://www.postgresql.org/docs/current/static/sql-createrole.html
https://www.postgresql.org/docs/current/static/sql-grant.html
*--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

Can I grant permissions to specific set of uids (linux) ?

[David Gauthier]

Hi:

I'd like to grant select, insert, update, delete to a table for a specific
set of uids (linux).  All others get select only.  Can the DB authenticate
the current linux user and grant access based on the fact that they are
logged in ()IOW, no passwords ?Is this possible ? If so, how ?

Thanks for any help

Barman versus pgBackRest

[Thomas Poty]

Hello Community,

I hesitate to use barman or pgBackRest.  I have found a lot of common
points between them and a few differences:

About pgBarman, I like :
- be able restore on a remote server from the backup server
- use replication slots for backingup wal on the backup server.

About pgBackRest, I like :

- real differential backup.
- lots of options
- option for backingup if PostgreSQL is already in backup mode


I would like to have :
- advices or feedbach about using pgBackrest or barman.
- pros and cons of these solutions
- differences that I would not have seen.

Thank you

Thomas

Re: plpgsql function-parsing

[Pavel Stehule]

Hi

2018-03-06 16:51 GMT+01:00 chris :

> Hi,
>
> I am trying to create a function that gets passed a statement as a string
> and then I need to change the table_name within the string by adding a
> "_cdc" to it, then execute the statement
>
> ex:
>
> string passed could be GRANT all ON authors TO bob.
>
> then
>
> EXECUTE GRANT all ON authors_cdc TO bob
>
>
> I'm looking into parsing the string but i can't find a way to parse in a
> way that'll work. Any ideas?
>

The extension Orafce  https://github.com/orafce/orafce has some basic
library for parsing SQL PLVlex.

Another solution can be using regular expressions
https://www.postgresql.org/docs/current/static/functions-matching.html

Regards

Pavel



>
> Thanks,
>
> Chris
>
>
>

Re: dirty_ratio & dirty_background_ratio settings with huge memory

[Andres Freund]

On 2018-03-06 03:23:29 -0700, pinker wrote:
> Hi,
> I've got cutomer with really huge RAM, now it's:
>  total   used   free sharedbuffers cached
> Mem:   31021113052596  49515 2088019922961185
> -/+ buffers/cache:  904183011693
> Swap: 8191  1   8190
> 
> (free -m)
> 
> and before it was twice more (6TB).
> 
> and trying to figure out how to set dirty_ratio & dirty_background_ratio
> parameters. Even for normal sized server those default sizes are to high,
> but now would be ridiculously huge, respectively 1,24TB and 300GB. 
> I'm thinking about 1 percent for dirty_ratio and not using
> dirty_background_ratio because it's not possible to set it below 1% but to
> set dirty_background_bytes instead to about 1-2GB.
> Maybe someone has got other experience with RAM of this size and those
> settings?

With a halfway modern PG I'd suggest to rather tune postgres settings
that control flushing. That leaves files like temp sorting in memory for
longer, while flushing things controlledly for other sources of
writes. See *_flush_after settings.

Greetings,

Andres Freund

Re: normalizing a float array?

[Achilleas Mantzios]

On 06/03/2018 18:12, Celia McInnis wrote:

Hi - I can write a stored procedure to normalize float arrays, but is there a 
nicer way of doing this in postgresql?


Last time I checked (1-2 yrs ago) no.

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

normalizing a float array?

[Celia McInnis]

Hi - I can write a stored procedure to normalize float arrays, but is there
a nicer way of doing this in postgresql?

plpgsql function-parsing

[chris]

Hi,

I am trying to create a function that gets passed a statement as a 
string and then I need to change the table_name within the string by 
adding a "_cdc" to it, then execute the statement


ex:

string passed could be GRANT all ON authors TO bob.

then

EXECUTE GRANT all ON authors_cdc TO bob


I'm looking into parsing the string but i can't find a way to parse in a 
way that'll work. Any ideas?



Thanks,

Chris

Re: What is wrong with my pgadmin?

[David G. Johnston]

On Tue, Mar 6, 2018 at 3:15 AM, Laurenz Albe 
wrote:

> David G. Johnston wrote:
> > On Monday, March 5, 2018, Łukasz Jarych  wrote:
> > > Anyone pleasE?
> >
> > Don't know but you might get better results by emailing the support list
> for the pgAdmin application.  This list is for general questions about the
> server.
>
> I agree about asking the specialized list, but -general is certainly
> not only for the server.
>

Yes, I over-generalized, though not by much IMO.  I was too tired to
qualify what kind of client questions were on-topic since "psql" would
still be imperfect.

David J.

Re: dirty_ratio & dirty_background_ratio settings with huge memory

[Laurenz Albe]

Tomas Vondra wrote:
> > 
> > 2 GB for vm.dirty_background_bytes and 1 GB for vm.dirty_bytes sounds fine.
> > 
> 
> It should be the other way around: dirty_background_bytes < dirty_bytes

Of course, thanks for the correction.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: dirty_ratio & dirty_background_ratio settings with huge memory

[Tomas Vondra]

On 03/06/2018 01:16 PM, Laurenz Albe wrote:
> pinker wrote:
>> I've got cutomer with really huge RAM, now it's:
>>  total   used   free sharedbuffers cached
>> Mem:   31021113052596  49515 2088019922961185
>> -/+ buffers/cache:  904183011693
>> Swap: 8191  1   8190
>>
>> (free -m)
>>
>> and before it was twice more (6TB).
>>
>> and trying to figure out how to set dirty_ratio & dirty_background_ratio
>> parameters. Even for normal sized server those default sizes are to high,
>> but now would be ridiculously huge, respectively 1,24TB and 300GB. 
>> I'm thinking about 1 percent for dirty_ratio and not using
>> dirty_background_ratio because it's not possible to set it below 1% but to
>> set dirty_background_bytes instead to about 1-2GB.
>> Maybe someone has got other experience with RAM of this size and those
>> settings?
> 
> Yes, you should set vm.dirty_background_bytes and vm.dirty_bytes
> and not use the *_ratio settings.
> 
> 2 GB for vm.dirty_background_bytes and 1 GB for vm.dirty_bytes sounds fine.
> 

It should be the other way around: dirty_background_bytes < dirty_bytes

regards
-- 
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: dirty_ratio & dirty_background_ratio settings with huge memory

[Laurenz Albe]

pinker wrote:
> I've got cutomer with really huge RAM, now it's:
>  total   used   free sharedbuffers cached
> Mem:   31021113052596  49515 2088019922961185
> -/+ buffers/cache:  904183011693
> Swap: 8191  1   8190
> 
> (free -m)
> 
> and before it was twice more (6TB).
> 
> and trying to figure out how to set dirty_ratio & dirty_background_ratio
> parameters. Even for normal sized server those default sizes are to high,
> but now would be ridiculously huge, respectively 1,24TB and 300GB. 
> I'm thinking about 1 percent for dirty_ratio and not using
> dirty_background_ratio because it's not possible to set it below 1% but to
> set dirty_background_bytes instead to about 1-2GB.
> Maybe someone has got other experience with RAM of this size and those
> settings?

Yes, you should set vm.dirty_background_bytes and vm.dirty_bytes
and not use the *_ratio settings.

2 GB for vm.dirty_background_bytes and 1 GB for vm.dirty_bytes sounds fine.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

dirty_ratio & dirty_background_ratio settings with huge memory

[pinker]

Hi,
I've got cutomer with really huge RAM, now it's:
 total   used   free sharedbuffers cached
Mem:   31021113052596  49515 2088019922961185
-/+ buffers/cache:  904183011693
Swap: 8191  1   8190

(free -m)

and before it was twice more (6TB).

and trying to figure out how to set dirty_ratio & dirty_background_ratio
parameters. Even for normal sized server those default sizes are to high,
but now would be ridiculously huge, respectively 1,24TB and 300GB. 
I'm thinking about 1 percent for dirty_ratio and not using
dirty_background_ratio because it's not possible to set it below 1% but to
set dirty_background_bytes instead to about 1-2GB.
Maybe someone has got other experience with RAM of this size and those
settings?





--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: What is wrong with my pgadmin?

[Laurenz Albe]

David G. Johnston wrote:
> On Monday, March 5, 2018, Łukasz Jarych  wrote:
> > Anyone pleasE?
> 
> Don't know but you might get better results by emailing the support list for 
> the pgAdmin application.  This list is for general questions about the server.

I agree about asking the specialized list, but -general is certainly
not only for the server.

Yours,
Laurenz Albe

Re: psql variables in the DO command

[Pavel Stehule]

2018-03-06 10:17 GMT+01:00 Pavel Luzanov :

> On 05.03.2018 18:35, Pavel Stehule wrote:
>
> I am slowly working on prototype. The work is simple, when variables are
> just scalars. But it is much harder, when we allow composite variables.
> When prototype will be done, I invite any cooperation - there are lot of
> question - and one very hard - where and how the variable should be stored
> (values) - now I have workaround, but it is pretty ugly code.
>
> I'm not a big expert on postgres internals, but ready to participate.
>

big thanks. I'll start new thread to stop do offtopic in this place.

Thank you

Pavel

>
> -
> Pavel Luzanov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>

Re: psql variables in the DO command

[Pavel Luzanov]

On 05.03.2018 18:35, Pavel Stehule wrote:
I am slowly working on prototype. The work is simple, when variables 
are just scalars. But it is much harder, when we allow composite 
variables. When prototype will be done, I invite any cooperation - 
there are lot of question - and one very hard - where and how the 
variable should be stored (values) - now I have workaround, but it is 
pretty ugly code.

I'm not a big expert on postgres internals, but ready to participate.

-
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company