From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: depesz@depesz.com, Amit Kapila <amit.kapila16@gmail.com>
Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Cc: kuroda.hayato@fujitsu.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Logical replication is missing block of rows when sending initial sync?
Date: Fri, 3 Nov 2023 13:20:22 +0100
Message-ID: <a266b461-e340-10c6-d511-40f9edb37d28@enterprisedb.com> (raw)
In-Reply-To: <ZUTh6bnNFHo35/Go@depesz.com>
References: <ZTu8GTDajCkZVjMs@depesz.com>
<TYAPR01MB5866C2BE11AD4FBDC0AB46F0F5A1A@TYAPR01MB5866.jpnprd01.prod.outlook.com>
<20231102.101713.170587046871054404.horikyota.ntt@gmail.com>
<ZUOGohG9DArmXNvV@depesz.com>
<CAA4eK1KDMp_hCFZo=KsbRUOn+JX70Te5BJcTKwfRrt2x++aXXA@mail.gmail.com>
<ZUTh6bnNFHo35/Go@depesz.com>
On 11/3/23 13:04, hubert depesz lubaczewski wrote:
> On Fri, Nov 03, 2023 at 09:09:12AM +0530, Amit Kapila wrote:
>> On Thu, Nov 2, 2023 at 4:53 PM hubert depesz lubaczewski
>> <depesz@depesz.com> wrote:
>>>
>>> On Thu, Nov 02, 2023 at 10:17:13AM +0900, Kyotaro Horiguchi wrote:
>>>> At Mon, 30 Oct 2023 07:10:35 +0000, "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com> wrote in
>>>>> I've tried, but I could not reproduce the failure. PSA the script what I did.
>>>>
>>>> I'm not well-versed in the details of logical replication, but does
>>>> logical replication inherently operate in such a way that it fully
>>>> maintains relationships between tables? If not, isn't it possible that
>>>> the issue in question is not about missing referenced data, but merely
>>>> a temporary delay?
>>>
>>> The problem is that date that appeared *later* was visible on the
>>> subscriber. Data that came earlier was visible too. Just some block of
>>> data got, for some reason, skipped.
>>>
>>
>> Quite strange. I think to narrow down such a problem, the first thing
>> to figure out is whether the data is skipped by initial sync or later
>> replication. To find that out, you can check remote_lsn value in
>> pg_replication_origin_status for the origin used in the initial sync
>> once the relation reaches the 'ready' state. Then, you can try to see
>> on the publisher side using pg_waldump whether the missing rows exist
>> before the value of remote_lsn or after it. That can help us to narrow
>> down the problem and could give us some clues for the next steps.
>
> I will be prepping another set of clusters to upgrade soon, will try to
> get some more data. The window to work on the bad data isn't long,
> though.
>
I think it'd be interesting to know:
1) Commit LSN for the missing rows (for the xmin).
2) Are the other changes for these transactions that *got* replicated
correctly?
3) LSNs used for the tablesync slot, catchup etc. I believe those are in
the server log.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tomas.vondra@enterprisedb.com, amit.kapila16@gmail.com, horikyota.ntt@gmail.com, kuroda.hayato@fujitsu.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Logical replication is missing block of rows when sending initial sync?
In-Reply-To: <a266b461-e340-10c6-d511-40f9edb37d28@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox