On 10-10-18 03:48 PM, Aleksey Tsalolikhin wrote:
> Hi.  Replication lags over 5 minutes behind (sometimes over 10 minutes
> behind) while pg_dump is running on the slave.
>
> We monitor our replication lag using Bucardo's check_postgres.pl and
> it keeps showing up on the monitoring/alerting radar 4 times a day,
> when we do the pg_dump.
>
> I've increased the alert threshold to 30 minutes as an immediate
> measure, but is there any way to reduce pg_dump's impact on
> replication?  or will we pretty much have to live with it?

Can you exclude the slony schema from your dump?

What might be happening is that the pg_dump obtains a shared read lock 
on the rows in sl_confirm (or sl_config_lock).  pg_dump will hold these 
locks until your backup is complete.

When Slony tries to generate sync events (or any other event) it needs 
an exclusive lock on sl_event to get the ordering right.  This means 
that no new sync events can be generated until after the pg_dump finishes.

This probably needs to be better documented, I was surprised when I 
recently encountered it.  Even the old page on locking issues was silent 
to this http://www.slony.info/documentation/1.2/locking.html

>
> Best,
> -at
> _______________________________________________
> Slony1-general mailing list
> Slony1-general at lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general