Re: Issue with logical replication

On Wed, Aug 11, 2021 at 3:28 AM Kacey Holston
<kacey(dot)holston(at)pgexperts(dot)com> wrote:
>
>
>
>
>
> > On Aug 9, 2021, at 9:21 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> >
> > On Tue, Aug 10, 2021 at 12:25 AM Kacey Holston
> > <kacey(dot)holston(at)pgexperts(dot)com> wrote:
> >>
> >> My apologies.
> >>
> >> The data was missing on PG13 from the initial copy.
> >>
> >
> > Okay, so all the steps after you say "We then reversed the direction
> > of replication by running this on the PostgreSQL 13 server:" in your
> > email are not required to see the problem, right?
>
> The reversed direction is only included for fullness of steps. The data is missing before that happens, we just did not discover the missing data the first time until after the we reversed direction.
>
> When made the second attempt we confirmed the data was missing after the initial sync completed.
>
> So, the steps can be ignored after the CREATE SUBSCRIPTION command.
>
> >
> > Can you please tell whether the data you see missing is the new data
> > generated on the publisher when the copy is in progress or it was the
> > original data of the table?
>
> It was the original data of the table that is missing.

This seems to mean that COPY by table sync workers missed rows, which
isn't likely to happen, though. On the other hand, reading the
following description in the first your message, I thought the data
you see missing on the subscriber (pg13) were created on the publisher
*after* starting logical replication:

---
We were able to determine that all the rows missing were created on
the publisher starting seven minutes after replication began and
lasted for three hours.
---

Am I missing something?

The queries executed on the publisher and the subscriber don't always
necessarily match since the data are replicated asynchronously. I
suppose the publisher (pg11) was receiving queries during table sync.
Could you elaborate on how you checked if the rows were missing?

Also, could you share the server versions including the minor version?

Regards,

--
Masahiko Sawada
EDB: https://www.enterprisedb.com/