Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tanLe-005ChS-KD for pgsql-general@arkaria.postgresql.org; Thu, 23 Jan 2025 02:53:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tanLd-008poC-Fj for pgsql-general@arkaria.postgresql.org; Thu, 23 Jan 2025 02:53:37 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tanLd-008po3-0j for pgsql-general@lists.postgresql.org; Thu, 23 Jan 2025 02:53:37 +0000 Received: from mail-ej1-x62c.google.com ([2a00:1450:4864:20::62c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tanLa-0011NS-14 for pgsql-general@lists.postgresql.org; Thu, 23 Jan 2025 02:53:35 +0000 Received: by mail-ej1-x62c.google.com with SMTP id a640c23a62f3a-aa67ac42819so71340166b.0 for ; Wed, 22 Jan 2025 18:53:34 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1737600812; x=1738205612; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=HDG41Ch15WCqLCGu/WXlg7kVQMqb5WPixD4fDzk5XRk=; b=deLusSE4ybZqD560DRmrGyk+0DiM9cnIN/zZuxItozysDYBA1w5sYh3d2NBCF+maT7 nT4EwhhRpZvWP6hFSkDTvzPN1fQyRtq1sPezuJ/b80mRZWo6sGSiI7waZZfv0Vtxa++e VvOR04KXcHRzBdonUSzJpeCA7QCgmLx4J4c+Y+SYQhB1jGP3oiJ3p9RP6f4Ry5sUCzKA ZAls68Wvghcm02SCZM6mG0WvMnL2nNKGmG1gnY8AWsFmvCQpPLjTJpHmI8wocNEoXE6n kmk5CN+BulORadxQ+wuWdu/BrAn78SasTGTum80tKWoRyl00OoGA9Y/LQ5xHXlhxm3s8 eu1g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1737600812; x=1738205612; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=HDG41Ch15WCqLCGu/WXlg7kVQMqb5WPixD4fDzk5XRk=; b=p4LU3ljO9eiUkQgPDFMhFnTkNvo4OCA9LXYHSNK8Js/Z7NABAuRL0egz0CbSa83k8+ zUrpKPYQBnmi7KrufleEpG78OU12d3mztIasvJC2Nh6ZhYTk1VNHN/x+L0uW+48IbShT anGESPA5+10MrDGByE95eyW1mWa6nJ+pZEupC61tnvwZCMgsumglUE+WFHEk8jl6jtVx Erjly5ZIsM820acYceZJbVj39QADfQGBx33GiZovGoljf8pfCCM3Shui3BFyqyk+0Akn 3pyuHr36627T7OuSAw90lvWTDNIhPOej7sb2VWDajaLoBKQYzda4JTh7RoYUAF3q7P/M tfQQ== X-Gm-Message-State: AOJu0YwRalk0eKHRCN1lanMJCoj4O+eB6DTzLblzDxolfpF+MdxY69sW ve7GCbowoMJjoba/L+3Tc6AR0KQ415tNdZe2KQb4Di9v669g2qoNRRqwG8gsRZvpLXIPaWB+bqG gRRJjfyRZFyxME+Tz42u+36++jrnv5LbO X-Gm-Gg: ASbGncv/pgeQWh9704oJzPYbt/v0Brgleeyn6DYo0jh7X4DVzqutfV90QeXhYTFSZCg yZfi9NrzmKE+03llkQJDlFr2YES1lJiyoaqQ/aE6IzjL4fzYngXGwUQRjSA9lNdU= X-Google-Smtp-Source: AGHT+IFxDwCgHdCqSgOAQBkzUk7UZFUgfu6Nw0rI79pXROyR5QCN5VtQ4lR8K8zNjuSbS0rRhBjC0dFuQ9TLJtqLnPM= X-Received: by 2002:a17:907:7fa8:b0:aaf:4008:5e2c with SMTP id a640c23a62f3a-ab38b0b68bcmr2341423766b.2.1737600811990; Wed, 22 Jan 2025 18:53:31 -0800 (PST) MIME-Version: 1.0 References: <79b7d5dc-3a1d-4ef0-9a3d-055268dc7d09@aklaver.com> <3691ad0c-189c-4290-a434-e03cb6301cac@aklaver.com> <44e3ca5d-fcbd-4f96-a6a6-09becff15cd7@aklaver.com> In-Reply-To: <44e3ca5d-fcbd-4f96-a6a6-09becff15cd7@aklaver.com> From: Durgamahesh Manne Date: Thu, 23 Jan 2025 08:23:18 +0530 X-Gm-Features: AbW1kvaxegGkaPR6zLo1qSpIaCKYyLbd2FZjhyV0jLbW6jr7otRQEQMCsJbfqCw Message-ID: Subject: Re: Records count mismatch with logical replication To: Adrian Klaver Cc: pgsql-general Content-Type: multipart/alternative; boundary="00000000000029055c062c56b7ca" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000029055c062c56b7ca Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, 22 Jan, 2025, 03:11 Adrian Klaver, wrote: > On 1/21/25 11:40, Durgamahesh Manne wrote: > > > > > > On Wed, 22 Jan, 2025, 00:22 Adrian Klaver, > > wrote: > > > > > > > > On 1/21/25 10:06 AM, Durgamahesh Manne wrote: > > > > > > > > Hi Adrian Klaver > > > > > > 22,906,216 bytes/10,846 rows works out to 2112 bytes per row. > > > > > > Is that a reasonable per row estimate? > > > > > > Yes sometimes would be vary > > > > If I am following the lag went from 350GB behind to 22MB. > > > > Is the issue that the lag has stalled at 22MB? > > > > > > > > Regards, > > > Durga Mahesh > > > > > > > -- > > Adrian Klaver > > adrian.klaver@aklaver.com > > > > > > Hi Adrian Klaver > > > > Is the issue that the lag has stalled at 22MB? > > > > Depends on load of source > > The lag would be either decrease or increase in Kb 's and Mb's (not Gb'= s) > > It s not constant as Data being replicated to target > > Previously you stated: > > "Both are under same vpc security groups" > > Does this refer to AWS? > > If so per: > > https://docs.aws.amazon.com/vpc/latest/userguide/what-is-amazon-vpc.html > > "The following diagram shows an example VPC. The VPC has one subnet in > each of the Availability Zones in the Region, EC2 instances in each > subnet, and an internet gateway to allow communication between the > resources in your VPC and the internet." > > So where are the two Postgres instances physically located relative to > each other? > > > > > But records count varies with difference of more than 10 thousand > > Have you looked at the I/0 statistics between the Postgres instances? > > > > > How to mitigate this issue in simplest way ? > > Until it is determined what is causing the lag there is no way to deal > with it. > > > > > Regards > > Durga Mahesh > > -- > Adrian Klaver > adrian.klaver@aklaver.com > > > > Hi Hi =E2=81=B9So where are the two Postgres instances physically located relativ= e to each other? Both in lreland under same vpc security groups > > But records count varies with difference of more than 10 thousand Have you looked at the I/0 statistics between the Postgres instances? Seems everything looks good with pg replication slots Does this pg logical slot get changes function help to pull pending changes to subscription that can be sync with publication server for real time sync ? Regards, Durgamahesh --00000000000029055c062c56b7ca Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable



On Wed, 22 Jan, 2025= , 03:11 Adrian Klaver, <adr= ian.klaver@aklaver.com> wrote:
On 1/21/25 11:40, Durgamahesh Manne wrote:
>
>
> On Wed, 22 Jan, 2025, 00:22 Adrian Klaver, <adrian.klaver@ak= laver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>
>
>=C2=A0 =C2=A0 =C2=A0On 1/21/25 10:06 AM, Durgamahesh Manne wrote:
>
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > Hi Adrian Klaver
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > 22,906,216 bytes/10,846 rows=C2=A0 works out = to 2112 bytes per row.
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > Is that a reasonable per row estimate?
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > Yes=C2=A0 sometimes would be vary
>
>=C2=A0 =C2=A0 =C2=A0If I am following the lag went from 350GB behind to= 22MB.
>
>=C2=A0 =C2=A0 =C2=A0Is the issue that the lag has stalled at 22MB?
>
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > Regards,
>=C2=A0 =C2=A0 =C2=A0 > Durga Mahesh
>=C2=A0 =C2=A0 =C2=A0 >
>
>=C2=A0 =C2=A0 =C2=A0--
>=C2=A0 =C2=A0 =C2=A0Adrian Klaver
>=C2=A0 =C2=A0 =C2=A0adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
> Hi Adrian Klaver
>
> Is the issue that the lag has stalled at 22MB?
>
> Depends on load of source
> The lag would be either decrease or increase in Kb 's and Mb's= (not Gb's)
> It s not constant as Data being replicated to target

Previously you stated:

"Both are under same vpc security groups"

Does this refer to AWS?

If so per:

https://docs.aws.= amazon.com/vpc/latest/userguide/what-is-amazon-vpc.html

"The following diagram shows an example VPC. The VPC has one subnet in=
each of the Availability Zones in the Region, EC2 instances in each
subnet, and an internet gateway to allow communication between the
resources in your VPC and the internet."

So where are the two Postgres instances physically located relative to
each other?

>
> But records count varies with difference of more than 10 thousand

Have you looked at the I/0 statistics between the Postgres instances?

>
> How to mitigate this issue in simplest way ?

Until it is determined what is causing the lag there is no way to deal
with it.

>
> Regards
> Durga Mahesh

--
Adrian Klaver
adrian.klaver@aklaver.com



Hi
Hi
=E2=81=B9So where = are the two Postgres instances physically located relative to
each other?
Both in lreland under same vpc= security groups=C2=A0

<= br>
>
> But records cou= nt varies with difference of more than 10 thousand
<= br>
Have you looked at the I/0 statistics between th= e Postgres instances?

Se= ems everything looks good with pg replication slots=C2=A0

Does this pg logical slot get changes fun= ction help to pull pending changes to subscription that can be sync with = publication server for real time sync ?

Regards,
Durgamahesh=C2=A0

--00000000000029055c062c56b7ca--