Logical replication/publication question

From: Mark Fletcher <markf(at)corp(dot)groups(dot)io>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Logical replication/publication question
Date: 2022-10-09 16:56:59
Message-ID: CAEO096rs3QQqyjz-AXPfYk82BBVGHDetPFRUM33OXTpAZAN9Bw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

We're migrating from 9.6 to 14, using pglogical. We have several logical
slots on the 9.6 instance implementing a change data capture pattern. For
the migration, we plan on recreating the slots on the 14 instance, without
taking a snapshot of the data. When the migration happens, we will simply
start using the slots on the 14 instance (with the understanding that the
LSNs won't match between the 9.6 and 14 instances).

In testing, we have this working, but there was a wrinkle and I'd like to
know if my understanding is correct. On the 9.6 instance, when creating the
replication slots, we would use the START_REPLICATION SLOT command, and
then immediately take a snapshot, and it has worked great for years.

On a 14 instance, receiving changes from the 9.6 instance via pglogical, if
we recreate the logical slot, but not take a snapshot, no changes are
propagated, and there are no errors. However, if we first issue a 'CREATE
PUBLICATION' command, then changes are propagated to the slot as expected.

We never issued a 'CREATE PUBLICATION' command on the 9.6 instance. My
guess is that the act of taking a snapshot twittled whatever bits were
necessary to propagate changes through the slot. By not taking a snapshot
on the 14 instance, that doesn't happen, hence the need for the 'CREATE
PUBLICATION' command. Alternatively, something was changed between 9.6 and
14 that now requires the new command (I looked through the docs and could
find no mention, however).

Is my understanding correct? Am I missing something? I just want to make
sure I'm not screwing something up.

Thanks,
Mark

Browse pgsql-general by date

  From Date Subject
Next Message gzh 2022-10-10 02:32:56 Re:Re: Different execution plan between PostgreSQL 8.4 and 12.11
Previous Message Mladen Gogala 2022-10-09 16:05:17 Re: Different execution plan between PostgreSQL 8.4 and 12.11