Re: Retrieve the snapshot's LSN

On Fri, Jul 17, 2015 at 6:53 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Fri, Jul 17, 2015 at 8:31 AM, Florent Guiliani <florent(at)guiliani(dot)fr> wrote:
>> A pg_export_snapshot_for_slot(...) would work very well.
>>
>> Let me explain the use case. You have many downstream systems that are
>> replicated with logical decoding. Using a dedicated replication slot
>> for each target is not practical. A single logical replication slot is
>> configured. It generates a stream of LSN-stamped transactions in
>> commit order. Those transactions are published to all downstream
>> nodes.
>>
>> The snapshot exported during the slot creation can be used to generate
>> a complete dump that the replicated systems will load before applying
>> the transaction stream.
>>
>> How do you individually reload/recover one of the downstream node? You
>> can use the initial dump and reapply all transactions emitted since
>> the slot's inception. It will quickly become impractical. What you
>> need is to generate a newer dump and only apply the transactions from
>> that point.
>
> I'd like to point out that I told Andres repeatedly during the
> development of logical decoding that this exact thing was going to be
> a problem. He told me fixing it was way too complicated, but I hope
> he'll relent, because I still think this is important.
>
> (Not trying to be a jerk here.)

As a quick solution, I added a replication command named
LOGICAL_DECODING_SNAPSHOT that does everything CREATE_REPLICATION_SLOT
does for logical slots except that the slot is automatically dropped (
see attached patch or
https://github.com/flyerman/postgres/compare/9.5...flyerman:export-logical-snapshot
).

Feel free to review and comment my patch. I'd like to provide a better
solution but I'd need some guidance on how to get the locking right
between the snapshot creation and the LSN retrieval.

--
Florent