Feedback-ID: i76984098:Fastmail
Message-ID: <7544dd46-c11a-43b4-bb70-85ccbbed382f@aklaver.com>
Date: Thu, 23 Jan 2025 10:28:22 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Records count mismatch with logical replication
To: Durgamahesh Manne <maheshpostgres9@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
References: <CAJCZko+gYNGiixGcqKAhjhLhUZ3QQg2rGs6nH7ZUNwTtjYLfGw@mail.gmail.com>
 <e1bd37c2-9e2c-4914-b8be-bd75b633dfc1@aklaver.com>
 <CAJCZko+HhLFkiDLDPg3fscUEO4uWyEFT4_e6GBMeywnnwRC-rg@mail.gmail.com>
 <79b7d5dc-3a1d-4ef0-9a3d-055268dc7d09@aklaver.com>
 <CAJCZkoLZb5ON_T-YecS3-1sQjiGUVLdgKjS93thQyzZKiaWuFQ@mail.gmail.com>
 <3691ad0c-189c-4290-a434-e03cb6301cac@aklaver.com>
 <CAJCZkoJgAh_T_NJU5Jn3mzJJQE4LE-tDEjEyP37gKNR1X0cDWQ@mail.gmail.com>
 <44e3ca5d-fcbd-4f96-a6a6-09becff15cd7@aklaver.com>
 <CAJCZkoLatfwsQ-puesPfjrYtUmaAwokWQvqShoTGfJ56QzLfQw@mail.gmail.com>
 <aa3168eb-f529-4e4a-bcb7-e01140ef5b3f@aklaver.com>
 <CAJCZkoKvzBxjAZxfOsUpEmwmrtV33ZZaHC-d-yvF6RPAxoATkQ@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAJCZkoKvzBxjAZxfOsUpEmwmrtV33ZZaHC-d-yvF6RPAxoATkQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/7544dd46-c11a-43b4-bb70-85ccbbed382f%40aklaver.com>
Precedence: bulk

On 1/23/25 09:54, Durgamahesh Manne wrote:


See comments in line below.

> 
> Source Publication Side:

> archiving=> select * from pg_stat_replication;

There is missing information here.

Am I right in assuming this is for slot cls_eva_msa?

And that it going to same client_addr 10.80.0.168?

> 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

The lag times are minimal.

Where the queries done below done at later time then those above?

> 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
> 

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

So what does the AWS dashboard show for the I/0 between the servers?

> 
> Regards
> Durga Mahesh

-- 
Adrian Klaver
adrian.klaver@aklaver.com