How to create logical replication slot with NOEXPORT_SNAPSHOT in jdbc

From: Igor Polishchuk <ora4dba(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org, Igor Polishchuk <ora4dba(at)gmail(dot)com>
Subject: How to create logical replication slot with NOEXPORT_SNAPSHOT in jdbc
Date: 2018-07-17 01:41:19
Message-ID: CAEcs1Y6EWC7AAULU5nHYsfkHUsXppMDrTszp1ho60f9WDnopcQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jonathan S. Katz 2018-07-17 03:54:29 Re: User documentation vs Official Docs
Previous Message Christopher Browne 2018-07-17 01:32:03 Re: User documentation vs Official Docs