Brad Nicholson wrote:

>Dirk Jagdmann wrote:
>  
>
>>Hello Experts,
>>
>>I'd like to (programmatically) determine when a subscription of a new
>>node into a slony cluster has finished, or better said, when the bulk
>>data transfer from the origin node did finish and the node is more or
>>less synchronised to the origin?
>>    
>>
>
>These are two separate things.  When you do an initial subscription
>finishes, the new node will be behind by the amount of time that the
>subscription took.  It then has to catch up on all the events that have
>since come in.
>
>If you are wanting to watch for when the initial subscription is done,
>watch the slon logs for a message like:
>
>006-03-21 16:44:36 GMT DEBUG1 copy_set 2 done in 17.443 seconds
>
>If you want to know when the node is actually caught up - query
>sl_status on the master.
>
>  
>
In principle, you should be able to do something a bit more specific
from the database side.

I'll let the details be an "exercise for the reader" (laziness, yes ;-))...

- You could look on the origin to find the event number of the subscribe
set event.

- Then watch sl_status on the origin, looking at the row for the
subscriber, waiting until the event number there gets past the event
number you got earlier.

That doesn't provide "completely caught up"; that provides "when the
subscription event completes."  That still seems like it would be a
useful piece of information for Dirk...

There's a feature I want to add to Slony-I at some point that would
provide a pretty exact way of doing this...

http://gborg.postgresql.org/project/slony1/bugs/bugupdate.php?1412

Immediately after the SUBSCRIBE SET, you'd submit a SUBMIT NOTIFY
request, let's say, SUBMIT NOTIFY (event='DONE_SUBSCRIPTION'); and set
up a script that listens on the subscriber for 'DONE_SUBSCRIPTION'.  It
would wake up a few moments after the initial subscription is completed,
though before subsequent SYNCs could be processed...

If the desire is to be pretty sure that the subscriber is caught up to
within some time interval, then the simple answer is always to check
sl_status on the origin to see how far behind the subscriber is...