On 07/26/2015 10:48 PM, Steve Singer wrote:
> On 07/26/2015 08:01 PM, Jan Wieck wrote:
>> On 07/26/2015 07:35 PM, Jan Wieck wrote:
>>> That said, pg_dump isn't that much faster than Slony's copy_set() so
>>> we'd need to find a way to reconstruct an sl_setsync entry from
>>> something like a binary base backup. That is not trivial.
>>
>> Here is a wild idea that I am going to test, but someone may throw a
>> wrench into it while I'm doing that:
>>
>> Let us assume that the application can be stopped and the database
>> brought into so called "single user mode" for a short period of time.
>> During that maintenance window we do the following:
>>
>>     * Shutdown the application
>>     * Create the cluster with two nodes and paths via slonik script.
>>     * Shutdown the slon for the slave.
>>     * Create a SYNC event on the master.
>>     * Execute a SUBSCRIBE SET ... OMIT COPY.
>>     * Create an LVM snapshot of $PGDATA (+ pg_xlog ...)
>>     * Resume the application
>>
>> That should take no longer than a few minutes. Properly scripted it
>> could be in the second range. We now could
>>
>>     * Use LVM snapshot(s) to plow over the slave's $PGDATA and so on.
>>     * Modify the slave's DB to correct the local node ID and fake an
>>       sl_setsync entry reflecting the SYNC created on the master.
>>     * Fire up the slave's slon.
>>
>>
>> Comments?
>>
>
> I think that would work.
>
> Is there  a way we could do this without an application outage.
>
> It seems to me that if you take a backup of the master wih pg_basebackup
> or through any other consistent method of a binary backup you could look
> at the restored instance and say
>
> * Any transactions that show as committed in the last SYNC that shows up
> in the restored sl_event table are committed
> * Any transactions that were not included in the above, but have actions
> in sl_log_1 or sl_log_2 on the restored instance were committed at the
> time of the backup (otherwise the rows inserted by those transactions
> couldn't be in sl_log)
> * Any transactions that were committed by the next SYNC on the origin
> but don't show up in the above list can be pulled as part of the first
> real SYNC to be processed, ie you would show those as 'in progress' (or
> not yet started) for fake sl_setsync value you create.

I think this is insufficient. Transactions that were in progress when 
the backup started are not guaranteed to be committed by the next SYNC. 
In fact they could still be in progress many SYNCs later.

What probably could work to produce that snapshot is to wait for the 
sl_log, that was active (inserted into) at backup time to be closed - 
IOW a log switch to complete. All xids found in that log on the master 
EXCEPT all xids in that log on the restored slave are to be considered 
in progress at backup time.

This should build an artificial snapshot that tells us where to start. 
Any SYNC after the log switch completed would make for a suitable next 
SYNC to leap to. So we would record this artificial snapshot as a SYNC 
with ev_seqno - 1 of the next SYNC.


Regards, Jan

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