On Thu, Jan 23, 2025 at 11:24 PM Durgamahesh Manne < maheshpostgr...@gmail.com> wrote:
> > > On Thu, Jan 23, 2025 at 10:08 PM Adrian Klaver <adrian.kla...@aklaver.com> > 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-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." >> >> If you are not referring to above then you will need to explain further. >> >> > >> > Regards, >> > Durgamahesh >> > >> >> -- >> Adrian Klaver >> adrian.kla...@aklaver.com >> >> > Hi > > Source Publication Side: > archiving=> 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=> 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=> select * from pg_stat_subscription where > subname = '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=> select * from pg_stat_subscription where subname = > '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-REPLICATION > > > 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
