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.

Transactions that commit in between the last SYNC before the backup and 
the time the backup is created might not write to sl_log but if they 
don't add rows to sl_log then we don't care about them because they 
didn't alter any replicated tables. If they did write to a replicated 
table then we know about since the xid will show up in the sl_log in the 
backup.