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
Postgresql 9.6 -> AWS RDS Postgresql 12.2 with pg_logical
Hello, I need to replicate Postgresql 9.6 to AWS RDS Postgresql 12.2 with pg_logical. AWS RDS Pg 12.2 (target) only supports pg_logical 2.3.0. Can I use v2.3.1 on the source and v2.3.0 on the target? Thank you in advance Igor
Pglogical 2.3.0 in AWS RDS 12.2
Hello everybody,
Has anybody succeeded with using pglogical in AWS Postgresql 12.2?
They have pglogical v2.3.0, and it has this issue
https://github.com/2ndQuadrant/pglogical/issues/240
<https://github.com/2ndQuadrant/pglogical/issues/240>, which was fixed in the
2.3.1.
However, one cannot upgrade pglogical to 2.3.1 in RDS. Just curious if somebody
found a work around. Otherwise, it seems unusable with RDS v12.2 right now.
The specific issue is:
The replication just does not start after creating a subscription. If I try
synchronize, I see:
select pglogical.alter_subscription_synchronize('subscription1');
ERROR: could not get table list: ERROR: syntax error at or near "."
LINE 1: ...i.att_list, i.has_row_filter, i.nspname as i.nsptarget…
In the db log:
2020-06-01 05:05:59
UTC;user=replication;db=demandbase_production;app=subscription1_sync;client=3.217.57.139(10005);[52691-3]
ERROR: syntax error at or near "." at character 89
2020-06-01 05:05:59
UTC;user=replication;db=demandbase_production;app=subscription1_sync;client=3.217.57.139(10005);[52691-4]
STATEMENT: SELECT i.relid, i.nspname, i.relname, i.att_list, i.has_row_filter,
i.nspname as i.nsptarget, i.relname as i.reltarget FROM (SELECT DISTINCT relid
FROM pglogical.tables WHERE set_name =
ANY(ARRAY['default','default_insert_only','ddl_sql'])) t, LATERAL
pglogical.show_repset_table_info(t.relid,
ARRAY['default','default_insert_only','ddl_sql']) i
In GIT:
user@MacBook-Pro pglogical (HEAD detached at REL2_3_1) $ git checkout REL2_3_0
Previous HEAD position was 19d6f4f Merge branch 'pglogical2-fix-revert' into
REL2_x_STABLE
HEAD is now at 4d53ca4 pglogical_create_subscriber: Fix for PostgreSQL 12
user@MacBook-Pro pglogical (HEAD detached at REL2_3_0) $ grep -R "i.nspname as
i.nsptarge" *
pglogical_rpc.c:" i.has_row_filter, i.nspname as i.nsptarget, i.relname
as i.reltarget"
ipolishchuk@MacBook-Pro pglogical (HEAD detached at REL2_3_0) $
Source Postgresql 9.6 pglogical 2.3.1 or 2.3.2
Target AWS RDS Postgresql 12.2 pglogical 2.3.0
Thank you
Igor Polishchuk
Logical decoding plus streaming replication fail-over
Hello, Did anybody solve a problem of logical replication slot fail-over on a streaming replication cluster? I have a logical decoding on a logical replication slot set up on a streaming replication master. Streaming replication does not propagate the replication slot state. In case of fail-over, I need to create a new replication slot on a new muster and loose some database changes. Is there any work-around that would allow avoid loosing database changes on a logical replication slot after a fail-over? Postgres 10.8 on CentOS 7. -- Thanks Igor Polishchuk
