Re: Retrieve the snapshot's LSN

On Thu, Jul 16, 2015 at 7:13 PM, Andres Freund <andres@anarazel.de> wrote:
> On 2015-07-16 13:08:48 -0400, Robert Haas wrote:
>> On Thu, Jul 16, 2015 at 12:54 PM, Andres Freund <andres@anarazel.de> wrote:
>> > Well, in combination with logical decoding it kinda has one: It should
>> > allow you to take a dump of the database with a certain snapshot and
>> > replay all transactions with a commit lsn bigger than the "snapshot's
>> > lsn" and end up with a continually consistent database.
>> >
>> > Visibility for HS actually works precisely in commit LSN order, even if
>> > that is possibly different than on the primary...
>>
>> That makes sense, and hopefully answers Florent's question about why
>> this is only exposed through the slot mechanism.
>
> Trying to swap-in the pub conversion, I think Florent wanted to be able to
> re-sync a standby from an existing slot. Which kinda makes sense to
> me. We could do something like
> SELECT * FROM pg_export_snapshot_for_slot(...);
>
> which would return the snapshot name and the LSN.
>
> There'd need to be some finicky locking to get that, but it should b epossible.

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.

Problem: How do you synchronize this newer dump with the LSN-stamped
stream of transactions? Being able to tell what LSN correspond to the
consistent dump would solve it.

I've started a quick&dirty solution:
https://github.com/flyerman/postgres/commit/a13432d5e596a8b13ff911637afd764f53af2ab3

where I copied CreateReplicationSlot():

https://github.com/flyerman/postgres/blob/a13432d5e596a8b13ff911637afd764f53af2ab3/src/backend/replication/walsender.c#L764

into ExportLogicalDecodingSnapshot() and removed everything that isn't
needed for the snapshot creation. I still need to plug it into the
replication protocol grammar to test it.

It's not very good solution. Among others bad things, it will exercise
the output plugin for nothing.

--
Florent