On 10/16/2014 11:48 AM, Glyn Astill wrote:
>> From: Glyn Astill <glynastill at yahoo.co.uk>
>
>> To: Dave Cramer <davecramer at gmail.com>
>> Cc: slony <slony1-general at lists.slony.info>
>> Sent: Thursday, 16 October 2014, 16:26
>> Subject: Re: [Slony1-general] Lag time increasing but there are no events
>>
>>
>>>  From: Dave Cramer <davecramer at gmail.com>
>>> To: Glyn Astill <glynastill at yahoo.co.uk>
>>> Cc: slony <slony1-general at lists.slony.info>
>>> Sent: Thursday, 16 October 2014, 15:41
>>> Subject: Re: [Slony1-general] Lag time increasing but there are no events
>>>
>>>
>>>
>>> Actually I think it is because a pg_dump of the db is going on.
>>>
>>>
>>> Can you dump a slave ?
>>>
>>>
>>> Do you have to exclude the slony clusters (which would make sense)?
>>
>>
>> Yeah from past experience the locks taken by pg_dump cause replication to lag. I
>> normally use the -N switch in pg_dump to exclude the schema.  For pg_restore I
>> usually create a TOC with the -l option and pipe it to grep to filter out the
>> references to the slony schema that get dumped for the triggers, and then -L
>> option to use it with my restore.
>
> Also I assumed you were using 2.0+ there, with 1.2 there's some slight fiddling done in pg_catalog to disable triggers, from memory pg_trigger.tgrelid is pointed to an index rather than the table or something like that.  I used to dump out with the slony schema, and run uninstallnode() and drop schema cascade against the restored database.

Dumping a replica won't work <2.0 because the dump won't contain all the 
information. With recent PG versions it will actually error out. With 
2.0+ it does work, so with 2.0+ it is possible to take the backup from a 
replica.

Dave: A pg_dump can very well stop the creation of new SYNC events since 
slon needs a brief exclusive lock on the sl_event table. If that is the 
case, the st_last_event in sl_status will not be advancing as if no slon 
would be running, which would explain the increase of st_lag_time with 
zero st_lag_num_events.

As suggested, dumping the database with -N to exclude the Slony-I schema 
should do the trick and dumping it has very little value anyway. If you 
ever have to restore from that dump, you'd either have to restore all 
replicas from it as well and rebuild the cluster with OMIT COPY, or 
rebuild the cluster with Slony copying the data. You would need to do 
the latter if replicas aren't full copies of the entire master.


Regards,
Jan

-- 
Jan Wieck
Senior Software Engineer
http://slony.info