public inbox for [email protected]
help / color / mirror / Atom feedFrom: Durgamahesh Manne <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Records count mismatch with logical replication
Date: Thu, 23 Jan 2025 23:51:01 +0530
Message-ID: <CAJCZkoJrrezxCHxh0tmy-LQ+6CeizkDt88kM_yTDzVADhLdsJQ@mail.gmail.com> (raw)
In-Reply-To: <CAJCZkoKvzBxjAZxfOsUpEmwmrtV33ZZaHC-d-yvF6RPAxoATkQ@mail.gmail.com>
References: <CAJCZko+gYNGiixGcqKAhjhLhUZ3QQg2rGs6nH7ZUNwTtjYLfGw@mail.gmail.com>
<[email protected]>
<CAJCZko+HhLFkiDLDPg3fscUEO4uWyEFT4_e6GBMeywnnwRC-rg@mail.gmail.com>
<[email protected]>
<CAJCZkoLZb5ON_T-YecS3-1sQjiGUVLdgKjS93thQyzZKiaWuFQ@mail.gmail.com>
<[email protected]>
<CAJCZkoJgAh_T_NJU5Jn3mzJJQE4LE-tDEjEyP37gKNR1X0cDWQ@mail.gmail.com>
<[email protected]>
<CAJCZkoLatfwsQ-puesPfjrYtUmaAwokWQvqShoTGfJ56QzLfQw@mail.gmail.com>
<[email protected]>
<CAJCZkoKvzBxjAZxfOsUpEmwmrtV33ZZaHC-d-yvF6RPAxoATkQ@mail.gmail.com>
On Thu, Jan 23, 2025 at 11:24 PM Durgamahesh Manne <
[email protected]> wrote:
>
>
> On Thu, Jan 23, 2025 at 10:08 PM Adrian Klaver <[email protected]>
> 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
>> [email protected]
>>
>>
> 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
view thread (13+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Records count mismatch with logical replication
In-Reply-To: <CAJCZkoJrrezxCHxh0tmy-LQ+6CeizkDt88kM_yTDzVADhLdsJQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox