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.