Re: Records count mismatch with logical replication

On Thu, Jan 23, 2025 at 11:24 PM Durgamahesh Manne <
maheshpostgres9(at)gmail(dot)com> wrote:

>
>
> On Thu, Jan 23, 2025 at 10:08 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)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