Обсуждение: How to create logical replication slot with NOEXPORT_SNAPSHOT in jdbc
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
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}"
}
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 PolishchukOn 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