How to create logical replication slot with NOEXPORT_SNAPSHOT in jdbc
Hello Everybody.
We are trying to use logical decoding for detecting database changes.
However, when we create a replication slot, the data processing pauses if
there are still transactions running from before the slot creation. If I
understand correctly, the slot is waiting for creating a consistent
snapshot and is blocked by the long transactions.
In our application, we don't need it, as we only want to see if some tables
were modified. Is it possible to create a logical replication slot with
NOEXPORT_SNAPSHOT option using jdbc?
This is a feature of feature of the Streaming Replication Protocol
described in
https://www.postgresql.org/docs/current/static/logicaldecoding-explanation.html#id-1.8.14.8.5
It is just not clear how to pass this option. We are using Postgres 10.4,
jbc
changelog for 42.2.2
<https://jdbc.postgresql.org/documentation/changelog.html#version_42.2.2>
This is a groovy method for creating the replication slot:
@CompileStatic(TypeCheckingMode.SKIP)
private void createReplicationSlot( PGConnection replConnection) {
def slotName = getSlotName()
Sql sql = new Sql(dataSource)
def exists = sql.firstRow("select * from pg_replication_slots where
slot_name = '${slotName}'".toString())
if (exists)
log.info "replication slot ${slotName} detected and
${exists?.active ? 'active' : 'not active'}"
else
log.info "replication slot ${slotName} not detected"
if (!exists) {
replConnection.getReplicationAPI()
.createReplicationSlot()
.logical()
.withSlotName(slotName)
.withOutputPlugin("wal2json")
.make();
log.info "created replication slot ${slotName}"
}
--
Thanks
Igor Polishchuk
On 17.07.18 03:41, Igor Polishchuk wrote:
We are trying to use logical decoding for detecting database changes.
However, when we create a replication slot, the data processing pauses
if there are still transactions running from before the slot creation.
If I understand correctly, the slot is waiting for creating a consistent
snapshot and is blocked by the long transactions.
In our application, we don't need it, as we only want to see if some
tables were modified. Is it possible to create a logical replication
slot with NOEXPORT_SNAPSHOT option using jdbc?
That doesn't do what you want. You still need to wait for the snapshot
to be created; there is no way around that. The NOEXPORT_SNAPSHOT
option just means that the snapshot, once created, won't be exported for
use by other sessions.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services