On Tue, Jan 21, 2025 at 9:24 PM Adrian Klaver <adrian.kla...@aklaver.com> 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.kla...@aklaver.com > > 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. 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 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. Both are under same vpc security groups Regards Durga Mahesh
