Machine learning top of db logs

[Toomas Kristin]

Hi,

Basically seems that data science and machine learning are going to be more and 
more popular at every field of life. I have considered to use machine learning 
top of logs generated by PostgreSQL servers. However before I start maybe 
someone already has done that and can share some experience?

Toomas

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

Re: Conflict with recovery on PG version 11.6

[Toomas Kristin]

Hi Laurenz,

Thank you!

What are reasons for conflicts? Based on documentation seems that the only 
reason can be that vacuum removed unused tuples that are in use at standby host 
and due to that standby host cannot apply modifications while blocking query 
either finishes or will be terminated. isnt it? Or there can be some other 
reasons?

I just wondering what would be impact when I increase value for 
autovacuum_vacuum_scale_factor in order force vacuuming process postpone the 
clean up process.

BR,
Toomas

> On 17. Jun 2020, at 12:42, Laurenz Albe  wrote:
> 
> On Wed, 2020-06-17 at 08:42 +0300, Toomas Kristin wrote:
>> 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?
> 
> The basic behavior shouldn't have changed since v10.
> 
> Check "pg_stat_database_conflicts" to see what kinds of conflicts that are.
> 
> The only solutions to avoid queries being canceled due to replication 
> conflicts are:
> 
> 1. avoid that such conflicts happen:
>   - set "hot_standby_feedback = on" on the standby and/or
> "vacuum_defer_cleanup_age" on the primary to avoid VACUUM conflicts
>   - Don't lock tables in access exclusive mode
> 
> 2. set "max_standby_streaming_delay" to -1
> 
> Note that it can be quite hard to completely avoid replication conflicts.
> Trying to have both no delay in applying changes and no cancelled queries
> is often not possible without seriously crippling autovacuum.
> 
> Yours,
> Laurenz Albe
> -- 
> Cybertec | https://www.cybertec-postgresql.com
> 

Re: Conflict with recovery on PG version 11.6

[Toomas Kristin]

Hi!

What is difference between following error messages?

ERROR:  canceling statement due to conflict with recovery
FATAL:  terminating connection due to conflict with recovery

I tried to correlate process termination on standby and vacuuming on master.

Here is a sample timeline
1) 2020-06-17 22:45:42 - Last vacuuming before session
2) 2020-06-18 01:01:12 - Session was started at standby
3) 2020-06-18 01:27:53 - Session was terminated at standby
4) 2020-06-18 02:59:32 - Next vacuuming

Based on that I would say there is no correlation between vacuuming and process 
termination.

How can I identify where is the conflict that stops replication and forces to 
terminate a query on standby host?

BR,
Toomas

> On 17. Jun 2020, at 16:38, Toomas Kristin  wrote:
> 
> Hi Laurenz,
> 
> Thank you!
> 
> What are reasons for conflicts? Based on documentation seems that the only 
> reason can be that vacuum removed unused tuples that are in use at standby 
> host and due to that standby host cannot apply modifications while blocking 
> query either finishes or will be terminated. isnt it? Or there can be some 
> other reasons?
> 
> I just wondering what would be impact when I increase value for 
> autovacuum_vacuum_scale_factor in order force vacuuming process postpone the 
> clean up process.
> 
> BR,
> Toomas
> 
>> On 17. Jun 2020, at 12:42, Laurenz Albe  wrote:
>> 
>> On Wed, 2020-06-17 at 08:42 +0300, Toomas Kristin wrote:
>>> 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?
>> 
>> The basic behavior shouldn't have changed since v10.
>> 
>> Check "pg_stat_database_conflicts" to see what kinds of conflicts that are.
>> 
>> The only solutions to avoid queries being canceled due to replication 
>> conflicts are:
>> 
>> 1. avoid that such conflicts happen:
>>  - set "hot_standby_feedback = on" on the standby and/or
>>"vacuum_defer_cleanup_age" on the primary to avoid VACUUM conflicts
>>  - Don't lock tables in access exclusive mode
>> 
>> 2. set "max_standby_streaming_delay" to -1
>> 
>> Note that it can be quite hard to completely avoid replication conflicts.
>> Trying to have both no delay in applying changes and no cancelled queries
>> is often not possible without seriously crippling autovacuum.
>> 
>> Yours,
>> Laurenz Albe
>> -- 
>> Cybertec | https://www.cybertec-postgresql.com
>> 
> 

Re: Conflict with recovery on PG version 11.6

[Toomas Kristin]

Hi,

> There can be other reasons:
> 
> - replicated ACCESS EXCLUSIVE locks that conflict with queries
> - replicated ACCESS EXCLUSIVE locks that cause deadlocks
> - buffer pins that are needed for replication but held by a query
> - dropped tablespaces that hold temporary files on the standby

Thank you for ideas what to verify.

> I told you the remedies above, why don't you like them?

Basically I want to achieve situation where replication is not suspended  (lag 
is not more than 3 minutes) and statements on standby are not terminated. Based 
on collected information I don’t see any connection between vacuuming on master 
and termination of statements on standby. I can temporarily disable vacuuming 
in order to be 100% sure this is the case. And when I set 
max_standby_streaming_delay either -1 or as a very big number then it helps 
avoid query termination but doesn’t help me about suspended replication. All 
worked with same configuration on Postgres version 10.6, the issue started 
after version upgrade.

This is the reason why I am very keen to find out real cause for the conflict.

BR,
Toomas

Re: Conflict with recovery on PG version 11.6

[Toomas Kristin]

Hi,

Thank you all for help. 

> FWIW in case you haven't tried yet, if you could find a DETAILS: line
> following to the ERROR: canceling.." message in server log, it would
> narrow the possibility.


I executed a database dump with 4 jobs and here are logs how this ended.

2020-06-19 02:38:28 UTC:[30953]:ERROR:40001:canceling statement due to conflict 
with recovery
2020-06-19 02:38:28 UTC:[30953]:DETAIL:User query might have needed to see row 
versions that must be removed.
2020-06-19 02:38:28 UTC:[30953]:LOCATION:ProcessInterrupts, postgres.c:3057
2020-06-19 02:38:28 UTC:[30953]:STATEMENT:
2020-06-19 02:38:28 UTC:[30926]:FATAL:40001:terminating connection due to 
conflict with recovery
2020-06-19 02:38:28 UTC:[30926]:DETAIL:User query might have needed to see row 
versions that must be removed.
2020-06-19 02:38:28 UTC:[30926]:HINT:In a moment you should be able to 
reconnect to the database and repeat your command.
2020-06-19 02:38:28 UTC:[30926]:LOCATION:ProcessInterrupts, postgres.c:2987
2020-06-19 02:38:28 UTC:[30952]:ERROR:40001:canceling statement due to conflict 
with recovery
2020-06-19 02:38:28 UTC:[30952]:DETAIL:User query might have needed to see row 
versions that must be removed.
2020-06-19 02:38:28 UTC:[30952]:LOCATION:ProcessInterrupts, postgres.c:3057
2020-06-19 02:38:28 UTC:[30952]:STATEMENT:
2020-06-19 02:38:28 UTC:[30953]:LOG:08006:could not receive data from 
client:Connection reset by peer
2020-06-19 02:38:28 UTC:[30953]:LOCATION:pq_recvbuf, pqcomm.c:978

Does it give any hints for anyone? Or how to find right version of source code 
what to analyse?

BR,
Toomas

Re: Conflict with recovery on PG version 11.6

[Toomas Kristin]

Hi Laurenz,

> Yes, that are conflicts with VACUUM.

Thank you for help. hot_standby_feedback did what I expected (no lag on 
replication and no session termination) and case is closed. Only it is foggy 
for me how it can be when no logs about vacuuming from that time window when 
session is active on standby host.

BR,
Toomas

Re: ERROR: canceling statement due to conflict with recovery

[Toomas Kristin]

Hi,

Basically I had the same topic recently and based on observation I would say 
that configuration parameter hot_standby_feedback disables no only some 
vacuuming operations but something else as well. I played thru the same 
scenario where I disabled hot_standby_feedback and and vacuuming, but still had 
conflicts on standby node. I am on opinion that the conflict is not related 
with the table that has disabled vacuuming the conflict may be related with any 
table instead that is used during the same session/connection.

I would suggest to make sure that application/client disconnects from standby 
node as soon as possible in order to release all possible locks/blockers on 
master that may cause bloating.

BR,
Toomas

> On 24. Jun 2020, at 11:31, RAJAMOHAN  wrote:
> 
> Hello all,
> 
> Your expertise is needed on this. I was going through previous mails 
> regarding the same topic and was able to setup the slave with 
> hot_standby_feedback  enabled.
> Queries are running fine with bloat occuring on master.
> 
> I tried the below scenario, where i disabled hot_standby_feedback and on 
> table level disabled autovacuum for 2 big tables on master. Ran the query on 
> the slave machine but still conflict error occurs.
> 
> I checked the pg_stat_database_conflicts view, the counter is increasing for 
> confl_snapshot.  Cross-checked with pg_stat_user_tables view,  last time 
> autovacuum happened for the 2 tables was 1 day before.
> 
> 
> My doubt, even though no autovacuum and no DML activities happening for both 
> the tables in master. Why is conflict error occuring?
> 
> 
> 
> Thanks & Regards,
> Rajamohan.J
> 

Re: Both side privileges

[Toomas Kristin]

And please be sure that default permissions are defined properly. Otherwise it 
may happen that userA creates a new table but userB has access for that. 
Alternative option is that user has to grant proper access for the role group 
after every time when a new db object is created.

Toomas

> On 14. Jul 2020, at 16:36, Jean-Philippe Chenel  wrote:
> 
> Thank for your answer David.
> This is what I'll do.
> De : David G. Johnston  >
> Envoyé : 13 juillet 2020 19:46
> À : Jean-Philippe Chenel mailto:jp.che...@live.ca>>
> Cc : pgsql-general@lists.postgresql.org 
>  
>  >
> Objet : Re: Both side privileges
>  
> On Mon, Jul 13, 2020 at 4:42 PM Jean-Philippe Chenel  > wrote:
> Hi,
> 
> I try to give userA privileges on userB objects and same thing to the userB, 
> giving privileges on userA objects. 
> 
> Grant userB to userA; —ok
> Grant userA to userB; —error: role userB is already member of role userA
> 
> Create a "group role" that retains ownership and then add both users to that 
> group.
> 
> David J.