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 1tb1ob-007M9c-1C for pgsql-general@arkaria.postgresql.org; Thu, 23 Jan 2025 18:20:29 +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 1tb1oa-001mSD-2l for pgsql-general@arkaria.postgresql.org; Thu, 23 Jan 2025 18:20:28 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tb1oZ-001mS4-M6 for pgsql-general@lists.postgresql.org; Thu, 23 Jan 2025 18:20:27 +0000 Received: from mail-ej1-x62b.google.com ([2a00:1450:4864:20::62b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tb1oW-0017z2-2E for pgsql-general@lists.postgresql.org; Thu, 23 Jan 2025 18:20:27 +0000 Received: by mail-ej1-x62b.google.com with SMTP id a640c23a62f3a-aaeef97ff02so239509566b.1 for ; Thu, 23 Jan 2025 10:20:25 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1737656424; x=1738261224; 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=us+phBf0I8pNWaBKGmvGxNBT1Wvo+8xueZYMCnjspps=; b=jSLrehUB7JpptL+tjBo2CcqocL/loWjeJzSHOFU1qVd3vOqy43bzxvoR4TAdq3iEgU F8y8ej8wVhgbGVKY+QckzQyuFUhy62jLvaOMvz4qqKurtT7G+1k2ZvekvkHHFtJ/PNZR Eerguyz4tN3zEmwJf2XqxzxPKSh1jqNo+Oc19b62EfgKf+vEOQ8SysdxbPizH9z8jgoC xhgsg/nWjkiHZfPIKjijUyxG+zIc6Vxq0M4WAdr5fDeXV2+f8/tPA/O+IhgiaV07m5SM Q1vzDXqMsDDMps423872ZEbxiZDJgWXguhCzGzkLrb8zSzscr96J+hSCXO7sG2Tg6UBG E31g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1737656424; x=1738261224; 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=us+phBf0I8pNWaBKGmvGxNBT1Wvo+8xueZYMCnjspps=; b=EtxGxb9e1vkdeKPwTHNFP3NpaT9ObOz3k5rXRXbdc6P2S4ZynQoCF3Rpm31uZBYTu8 uRKTUnrgM++3wDK9TwVozYfbJC8a9VmExIzmKx9TSKZtG++nKRINVhlKpPH6qCfIKaBm DN+HlxqdujGhipulVQdQeJckGP4H6rdFNReT8zwGSF6StxgyQDEEDKU06AvSBu2JMB2t 6GjaKJNUpSi9b7OQwwMhF1UXnoR5mSISU3DgHw5mcpD1CVVAuB9k0cS27ifmm74EoLdg w2AG4Yf7utJnB1Zat9YzJNDNYTrBwcOtm3M1x3x0VzZmPJWYpr3m+tjZJRUQ2RYEzB3y mH8g== X-Gm-Message-State: AOJu0YxLIstfBDtLEUaWcYPKc+cy9PXZTi5t8sR0rbRPKX6vdGdUiVR/ xoI7S5/DRn3qkI1dDUVc5e1+0M6fLnncp9+ZVCPZvjZ1J75gLSxC6lF3IbNFV9ARfMlcdiqt6EP 35dFaFs76Fml7bVrqwHA3KnQGsXqVxsD5pblaKw== X-Gm-Gg: ASbGncuYIU/EcqqnxqAJ+/BMzxeRwe+AfD51nDzF079JUYbqBFdYn6MTbRNvSaXyILD kAGZ5pvD8UWqdP1tOxF2+uTdeqhwZZ+qMrMW6dj5uoCD/95gMBZIDt7l7yzIAZQ== X-Google-Smtp-Source: AGHT+IHUIqkDZ/bA0mtZZx0Tq5MRJkFuRxaE5I/0nE3McGIQFiL0Jb1H5YnIoNS+D/4ANVjzAa2Pzv9gwMaSv+ZYwAY= X-Received: by 2002:a17:907:969f:b0:aae:8495:e064 with SMTP id a640c23a62f3a-ab38b44e119mr2609183266b.40.1737656423939; Thu, 23 Jan 2025 10:20:23 -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: From: Durgamahesh Manne Date: Thu, 23 Jan 2025 23:51:01 +0530 X-Gm-Features: AWEUYZnndKolnfnyu1qmueF_dtZ6S0oy5Swm45teKZQ8-r1-rtnEgkKTrVwmK6k Message-ID: Subject: Re: Records count mismatch with logical replication To: Adrian Klaver Cc: pgsql-general Content-Type: multipart/alternative; boundary="000000000000e404c6062c63a976" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000e404c6062c63a976 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Jan 23, 2025 at 11:24=E2=80=AFPM Durgamahesh Manne < maheshpostgres9@gmail.com> wrote: > > > On Thu, Jan 23, 2025 at 10:08=E2=80=AFPM Adrian Klaver > wrote: > >> On 1/22/25 18:53, Durgamahesh Manne wrote: >> > >> > >> > >> >> > > 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 >> >> Except the subscriber is lagging behind the publisher. >> >> '... everything looks good' is an opinion not actual data. >> >> > >> > 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 ? >> >> Are you referring to this?: >> >> >> https://www.postgresql.org/docs/current/warm-standby.html#SYNCHRONOUS-RE= PLICATION >> >> Though I am not sure you want to do this as from above: >> >> "When requesting synchronous replication, each commit of a write >> transaction will wait until confirmation is received that the commit has >> been written to the write-ahead log on disk of both the primary and >> standby server. The only possibility that data can be lost is if both >> the primary and the standby suffer crashes at the same time. This can >> provide a much higher level of durability, though only if the sysadmin >> is cautious about the placement and management of the two servers. >> Waiting for confirmation increases the user's confidence that the >> changes will not be lost in the event of server crashes but it also >> necessarily increases the response time for the requesting transaction. >> The minimum wait time is the round-trip time between primary and standby= ." >> >> If you are not referring to above then you will need to explain further. >> >> > >> > Regards, >> > Durgamahesh >> > >> >> -- >> Adrian Klaver >> adrian.klaver@aklaver.com >> >> > Hi > > Source Publication Side: > archiving=3D> select * from pg_replication_slots ; > -[ RECORD 1 ]-------+-------------- > slot_name | cls_eva_msa > plugin | pgoutput > slot_type | logical > datoid | 16601 > database | archiving > temporary | f > active | t > active_pid | 3237 > xmin | > catalog_xmin | 2935229621 > restart_lsn | 16C8/40CEC600 > confirmed_flush_lsn | 16C8/440FFF50 > wal_status | reserved > safe_wal_size | > two_phase | f > conflicting | f > -[ RECORD 2 ]-------+-------------- > slot_name | cle_clm_mka > plugin | pgoutput > slot_type | logical > datoid | 16601 > database | archiving > temporary | f > active | t > active_pid | 3501 > xmin | > catalog_xmin | 2935229621 > restart_lsn | 16C8/40CEC600 > confirmed_flush_lsn | 16C8/440FFF50 > wal_status | reserved > safe_wal_size | > two_phase | f > conflicting | f > archiving=3D> select * from pg_stat_replication; > client_hostname | > client_port | 52506 > backend_start | 2025-01-23 16:58:04.697304+00 > backend_xmin | > state | streaming > sent_lsn | 16C7/BDE4BB48 > write_lsn | 16C7/BDE4BB48 > flush_lsn | 16C7/BDE4BB48 > replay_lsn | 16C7/BDE4BB48 > write_lag | 00:00:00.002271 > flush_lag | 00:00:00.002271 > replay_lag | 00:00:00.002271 > sync_priority | 0 > sync_state | async > reply_time | 2025-01-23 17:34:39.901979+00 > -[ RECORD 2 ]----+------------------------------ > pid | 3501 > usesysid | 14604130 > usename | archiving > application_name | cle_clm_mka > client_addr | 10.80.0.168 > client_hostname | > client_port | 55412 > backend_start | 2025-01-22 09:31:11.83963+00 > backend_xmin | > state | streaming > sent_lsn | 16C7/BDE4BB48 > write_lsn | 16C7/BDE4BB48 > flush_lsn | 16C7/BDE4BB48 > replay_lsn | 16C7/BDE4BB48 > write_lag | 00:00:00.001642 > flush_lag | 00:00:00.023143 > replay_lag | 00:00:00.001642 > sync_priority | 0 > sync_state | async > reply_time | 2025-01-23 17:34:39.903052+00 > > Subscription Side : archiving=3D> select * from pg_stat_subscription wher= e > subname =3D 'cls_eva_msa'; > -[ RECORD 1 ]---------+------------------------------ > subid | 1936652827 > subname | cls_eva_msa > pid | 18746 > relid | > received_lsn | 16C7/FB48DFE0 > last_msg_send_time | 2025-01-23 17:41:11.924562+00 > last_msg_receipt_time | 2025-01-23 17:41:11.933344+00 > latest_end_lsn | 16C7/FB48DFE0 > latest_end_time | 2025-01-23 17:41:11.924562+00 > > archiving=3D> select * from pg_stat_subscription where subname =3D > 'cle_clm_mka'; > -[ RECORD 1 ]---------+------------------------------ > subid | 1892055116 > subname | cle_clm_mka > pid | 507 > relid | > received_lsn | 16C7/FB8CDF68 > last_msg_send_time | 2025-01-23 17:41:17.375879+00 > last_msg_receipt_time | 2025-01-23 17:41:17.378932+00 > latest_end_lsn | 16C7/FB8CDF68 > latest_end_time | 2025-01-23 17:41:17.375879+00 > > If you need to see more stats based on your info will give you > Your response in this regard is valuable > > Are you referring to this?: > > > https://www.postgresql.org/docs/current/warm-standby.html#SYNCHRONOUS-REP= LICATION > > > No.I am using logical replication in asynchronous mode > > Except the subscriber is lagging behind the publisher. > > '... everything looks good' is an opinion not actual data. > Correct > > Regards > Durga Mahesh > Hi Adrian Klaver At subscription side getting this error repeatedly So Do we have any solution without resync full data again from scratch - 2025-01-23 18:11:46 UTC::@:[507]:DEBUG: logical replication did not find row to be updated in replication target relation's partition "markets_archive_p20250118" Regards, Durga Mahesh --000000000000e404c6062c63a976 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Thu, Jan 23,= 2025 at 11:24=E2=80=AFPM Durgamahesh Manne <maheshpostgres9@gmail.com> wrote:


On Thu, Jan 23, 2025 at 10:08=E2=80=AFPM Adrian Klaver <adrian.klaver@aklaver.= com> wrote:
On 1/22/25 18:53, Durgamahesh Manne wrote:
>
>
>

>=C2=A0 > But records count varies with difference of more than 10 th= ousand
>
> Have you looked at the I/0 statistics between the Postgres instances?<= br> >
> Seems everything looks good with pg replication slots

Except the subscriber is lagging behind the publisher.

'... everything looks good' is an opinion not actual data.

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

Are you referring to this?:

https://www.postgre= sql.org/docs/current/warm-standby.html#SYNCHRONOUS-REPLICATION

Though I am not sure you want to do this as from above:

"When requesting synchronous replication, each commit of a write
transaction will wait until confirmation is received that the commit has been written to the write-ahead log on disk of both the primary and
standby server. The only possibility that data can be lost is if both
the primary and the standby suffer crashes at the same time. This can
provide a much higher level of durability, though only if the sysadmin
is cautious about the placement and management of the two servers.
Waiting for confirmation increases the user's confidence that the
changes will not be lost in the event of server crashes but it also
necessarily increases the response time for the requesting transaction. The minimum wait time is the round-trip time between primary and standby.&q= uot;

If you are not referring to above then you will need to explain further.
>
> Regards,
> Durgamahesh
>

--
Adrian Klaver
adrian.klave= r@aklaver.com


Hi=C2=A0

Sour= ce Publication Side:
archiving=3D> select * from pg_replicatio= n_slots ;
-[ RECORD 1 ]-------+--------------
slot_name =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 | cls_eva_msa
plugin =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0| pgoutput
slot_type =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 | logical
datoid =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 1= 6601
database =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| archiving
te= mporary =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | f
active =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| t
active_pid =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0| 3237
xmin =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0|
catalog_xmin =C2=A0 =C2=A0 =C2=A0 =C2=A0| 2935229621
restart_= lsn =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 16C8/40CEC600
confirmed_flush_lsn | 16= C8/440FFF50
wal_status =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| reserved
s= afe_wal_size =C2=A0 =C2=A0 =C2=A0 |
two_phase =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 | f
conflicting =C2=A0 =C2=A0 =C2=A0 =C2=A0 | f
-[ RECORD = 2 ]-------+--------------
slot_name =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |= cle_clm_mka
plugin =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| pg= output
slot_type =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | logical
datoid = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 16601
database =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| archiving
temporary =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 | f
active =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0| t
active_pid =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 3501
xmi= n =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
catalog_xmin = =C2=A0 =C2=A0 =C2=A0 =C2=A0| 2935229621
restart_lsn =C2=A0 =C2=A0 =C2=A0= =C2=A0 | 16C8/40CEC600
confirmed_flush_lsn | 16C8/440FFF50
wal_statu= s =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| reserved
safe_wal_size =C2=A0 =C2= =A0 =C2=A0 |
two_phase =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | f
conflic= ting =C2=A0 =C2=A0 =C2=A0 =C2=A0 | f
archiving=3D> select * fro= m pg_stat_replication;
client_hostname =C2=A0|
client_port =C2=A0 =C2= =A0 =C2=A0| 52506
backend_start =C2=A0 =C2=A0| 2025-01-23 16:58:04.69730= 4+00
backend_xmin =C2=A0 =C2=A0 |
state =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0| streaming
sent_lsn =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 16C7/BDE= 4BB48
write_lsn =C2=A0 =C2=A0 =C2=A0 =C2=A0| 16C7/BDE4BB48
flush_lsn = =C2=A0 =C2=A0 =C2=A0 =C2=A0| 16C7/BDE4BB48
replay_lsn =C2=A0 =C2=A0 =C2= =A0 | 16C7/BDE4BB48
write_lag =C2=A0 =C2=A0 =C2=A0 =C2=A0| 00:00:00.0022= 71
flush_lag =C2=A0 =C2=A0 =C2=A0 =C2=A0| 00:00:00.002271
replay_lag = =C2=A0 =C2=A0 =C2=A0 | 00:00:00.002271
sync_priority =C2=A0 =C2=A0| 0sync_state =C2=A0 =C2=A0 =C2=A0 | async
reply_time =C2=A0 =C2=A0 =C2=A0= | 2025-01-23 17:34:39.901979+00
-[ RECORD 2 ]----+---------------------= ---------
pid =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 3501
= usesysid =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 14604130
usename =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0| archiving
application_name | cle_clm_mka
client= _addr =C2=A0 =C2=A0 =C2=A0| 10.80.0.168
client_hostname =C2=A0|
clien= t_port =C2=A0 =C2=A0 =C2=A0| 55412
backend_start =C2=A0 =C2=A0| 2025-01-= 22 09:31:11.83963+00
backend_xmin =C2=A0 =C2=A0 |
state =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0| streaming
sent_lsn =C2=A0 =C2=A0 =C2=A0 = =C2=A0 | 16C7/BDE4BB48
write_lsn =C2=A0 =C2=A0 =C2=A0 =C2=A0| 16C7/BDE4B= B48
flush_lsn =C2=A0 =C2=A0 =C2=A0 =C2=A0| 16C7/BDE4BB48
replay_lsn = =C2=A0 =C2=A0 =C2=A0 | 16C7/BDE4BB48
write_lag =C2=A0 =C2=A0 =C2=A0 =C2= =A0| 00:00:00.001642
flush_lag =C2=A0 =C2=A0 =C2=A0 =C2=A0| 00:00:00.023= 143
replay_lag =C2=A0 =C2=A0 =C2=A0 | 00:00:00.001642
sync_priority = =C2=A0 =C2=A0| 0
sync_state =C2=A0 =C2=A0 =C2=A0 | async
reply_time = =C2=A0 =C2=A0 =C2=A0 | 2025-01-23 17:34:39.903052+00
=C2=A0
Subscription Side : archiving=3D> select * from pg_stat_subscription = where subname =3D 'cls_eva_msa';
-[ RECORD 1 ]---------+------= ------------------------
subid =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 | 1936652827
subname =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 | cls_eva_msa
pid =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 | 18746
relid =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 |
received_lsn =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0| 16C7/FB48DFE0
last_msg_send_time =C2=A0 =C2=A0| 2025-01-23 17:41:11= .924562+00
last_msg_receipt_time | 2025-01-23 17:41:11.933344+00
late= st_end_lsn =C2=A0 =C2=A0 =C2=A0 =C2=A0| 16C7/FB48DFE0
latest_end_time = =C2=A0 =C2=A0 =C2=A0 | 2025-01-23 17:41:11.924562+00

archiving=3D>= ; select * from pg_stat_subscription where subname =3D 'cle_clm_mka'= ;;
-[ RECORD 1 ]---------+------------------------------
subid =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 1892055116
subname = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | cle_clm_mka
pid =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 507
relid = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |
received_lsn = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 16C7/FB8CDF68
last_msg_send_time =C2= =A0 =C2=A0| 2025-01-23 17:41:17.375879+00
last_msg_receipt_time | 2025-0= 1-23 17:41:17.378932+00
latest_end_lsn =C2=A0 =C2=A0 =C2=A0 =C2=A0| 16C7= /FB8CDF68
latest_end_time =C2=A0 =C2=A0 =C2=A0 | 2025-01-23 17:41:17.375= 879+00

If you need to see more stats based on your info will give you=C2=A0
Your response in this regard is valuable=C2= =A0

Are you referring to this?:

= https://www.postgre= sql.org/docs/current/warm-standby.html#SYNCHRONOUS-REPLICATION


No.I am using logical replication in asynchronous mo= de=C2=A0

Except the subscriber is lagging behind the publisher.

'... e= verything looks good' is an opinion not actual data.
Correct=C2=A0

Regards
Durga Mahesh<= /div>

Hi Adrian Klaver

=
At subscription side=C2=A0
getting this error repeated= ly=C2=A0 So Do we have any solution without resync full data again from scr= atch=C2=A0
  • 2025-01-23 18:11:46 UTC::@:[507]:DE= BUG: logical replication did not find row to be updated in replication targ= et relation's partition "markets_archive_p20250118"
=C2=A0Regards,
Durga Mahesh
--000000000000e404c6062c63a976--