Date: Thu, 2 Nov 2023 12:24:32 +0100
From: hubert depesz lubaczewski <depesz@depesz.com>
Sender: depesz@depesz.com
To: "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>
Cc: 'Kyotaro Horiguchi' <horikyota.ntt@gmail.com>,
	"pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: Logical replication is missing block of rows when sending
 initial sync?
Message-ID: <ZUOG8N93Tl+lEkQn@depesz.com>
Reply-To: depesz@depesz.com
References: <ZTu8GTDajCkZVjMs@depesz.com>
 <TYAPR01MB5866C2BE11AD4FBDC0AB46F0F5A1A@TYAPR01MB5866.jpnprd01.prod.outlook.com>
 <20231102.101713.170587046871054404.horikyota.ntt@gmail.com>
 <TYCPR01MB5870BFB795F529F4619D8BB9F5A6A@TYCPR01MB5870.jpnprd01.prod.outlook.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: 
 <TYCPR01MB5870BFB795F529F4619D8BB9F5A6A@TYCPR01MB5870.jpnprd01.prod.outlook.com>
Archived-At: 
 <https://www.postgresql.org/message-id/ZUOG8N93Tl%2BlEkQn%40depesz.com>
Precedence: bulk

On Thu, Nov 02, 2023 at 03:17:03AM +0000, Hayato Kuroda (Fujitsu) wrote:
> Dear Horiguchi-san, 
> 
> > 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?
> 
> I assumed that tuples referred by FK existed before synchronization or during that.
> In this case, IIUC the status is never 'r' before they are replicated. Thought?
> 
> Dear Depesz,
> 
> Is above assumption right? 
> 
> Also, I suggest you that pg_stat_replicaiton can be checked on publisher when you
> meet the failure again. If pg_stat_replication.replay_lsn is much behind
> pg_stat_replication.sent_lsn, the replication may be delayed so that the tuples
> are not appeared. pg_stat_subscription on subscriber has similar infos with replay_lsn.
> 
> Note that pg_stat_replication may be not updated if feedbacks from a subscriber
> is disabled.
> 
> https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-SUBSCRIPTION

Whenever the problem happens lag is virtually nonexistent.
We test it by adding new rows to couple of tables, and verify that these
rows show up on subscriber. They did.

Again - replication was *working* with the exception of block of rows
from one (so far it never happened on more than one) table that was
missing.

Best regards,

depesz