On Tue, Jan 21, 2025 at 9:24 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 1/21/25 04:08, Durgamahesh Manne wrote:
> Hi Team,
>
> I have publication and subscription servers .So seems data replication
> running with minimal lag but records count mismatch with more than 10
> thousand records between source and destination tables
>
> Could you please help in resolving this issue?
Not without a good deal more information:
1) Postgres version on either side of the replication.
2) The replication configuration.
3) Define how lag is being calculated and what 'minimal' is.
4) Define how the record counts are being derived.
5) The network distance between the servers.
>
> Regards,
> Durga Mahesh
>
>
>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
Hi Adrian Klaver
Really Thanks for your quick response
This happened during repack lag went to more than 350Gb then gradually decreased to minimal lag after running pg_repack
1) Postgres version on either side of the replication.
Source(publication) :16.4
Destination(subscription) : 14.11
2) The replication configuration.
2) The replication configuration.
OLAP workload archiving (32GB 8 Vcpus)
Source : wal_level = logical
wal_sender_timeout = 30s
max_wal_senders = 40
max_replication_slots = 20
max_logical_replication_workers = 4
wal_buffers = 64MB
commit_delay = 2000
commi_siblings = 12
wal_writer_delay = 300
wal_writer_flush_after = 1MB
bgwriter_delay = 20
min_wal_size = 8GB
max_wal_size = 32Gb
Destination : 128GB 32 vcpus
wal_level = logical
wal_receiver_timeout = 30s
max_wal_senders = 40
max_replication_slots = 60
max_logical_replication_workers = 23
wal_buffers = 64MB
commit_delay = default
commi_siblings = default
wal_writer_delay = default
wal_writer_flush_after = default
bgwriter_delay = 20
min_wal_size = 8GB
max_wal_size = 32Gb
3) Define how lag is being calculated and what 'minimal' is.
postgres=> select slot_name,pg_wal_lsn_diff(pg_current_wal_lsn(),restart_lsn) as bytes_behind,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as behind_size, active from pg_replication_slots;
slot_name | bytes_behind | behind_size | active
-------------+--------------+-------------+--------
cls_eva_msa | 22906216 | 22 MB | t
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as behind_size, active from pg_replication_slots;
slot_name | bytes_behind | behind_size | active
-------------+--------------+-------------+--------
cls_eva_msa | 22906216 | 22 MB | t
4) Define how the record counts are being derived.
Source : archiving=> select count(*) from archiving.events_archive ;
count
---------
1262908
(1 row)
Destination : archiving=> select count(*) from archiving.events_archive ;
count
---------
1252062
(1 row)
5) The network distance between the servers.
count
---------
1262908
(1 row)
Destination : archiving=> select count(*) from archiving.events_archive ;
count
---------
1252062
(1 row)
5) The network distance between the servers.
Both are under same vpc security groups
Regards
Durga Mahesh
