postgresqllogical-replicationlogical-decoding

How to use exported snapshot in Postgres replication slot


I'm using the EXPORT_SNAPSHOT option documented here when creating a postgres logical replication slot. The documentation states that 'export, which is the default, will export the snapshot for use in other sessions. This option can't be used inside a transaction...'

However, when I try to use the snapshot exported here from another transaction with the SET TRANSACTION SNAPSHOT command I keep getting the invalid snapshot identifier error.

Isn't it contradictory that there's an export snapshot option which explicitly states that it shouldn't be used inside a transaction(thus we cannot control when it commits) but we cannot use the exported snapshot in another transaction(precisely because we cannot control when the create_replication_slot commits and that snapshot is probably already purged by the time we try to use it)?

ps: What I want to achieve is to use the exported snapshot to copy table data prior to creating a replication slot before actually starting the logical replication.


Solution

  • So here is what I had made working, just FYI: