Re: Stream consistent snapshot via a logical decoding plugin as a series of INSERTs

On Fri, Jan 15, 2016 at 12:09 PM, Shulgin, Oleksandr <
oleksandr(dot)shulgin(at)zalando(dot)de> wrote:

> On Fri, Jan 15, 2016 at 11:08 AM, Simon Riggs <simon(at)2ndquadrant(dot)com>
> wrote:
>
>> On 15 January 2016 at 08:30, Shulgin, Oleksandr <
>> oleksandr(dot)shulgin(at)zalando(dot)de> wrote:
>>
>>
>>> I'd like to propose generic functions (probably in an extension, or in
>>> core if not possible otherwise) to facilitate streaming existing data from
>>> the database *in the same format* that one would get if these would be the
>>> changes decoded by a logical decoding plugin.
>>>
>>> The idea is to use a snapshot returned from CREATE_REPLICATION_SLOT
>>> command of the replication protocol to get a consistent snapshot of the
>>> database, then start listening to new changes on the slot.
>>>
>>
>> It sounds like this is already possible.
>>
>
> Totally, that's how it was supposed to be used anyway. What is missing
> IMO is retrieving the initial snapshot in the same format as that the later
> changes will arrive.
>

POC patch attached. Findings:

1) Needs an actual slot for all the decode machinery to work (code depends
on MyReplicationSlot being set).
2) Requires a core patch.
3) Currently only supports textual output, adding binary is trivial.

Acquiring a slot means this cannot be run in parallel from multiple
backends. Any ideas on how to overcome this (except for opening multiple
slots with the same LSN)?
To obtain a consistent snapshot, the client still needs to take care of
preserving and setting transaction snapshot properly.

--
Alex