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
