how to sort out the replication_lag of 1D 4H in two pgedge nodes

[Rammurthy5]

pgegde running on t2.micro for 23 tables. total size of data inside all 23 tables are less than 100MB. Biggest table we got is 302K rows (58MB).
Vaccum done no dead tuple found.
wal_level setting is logical, max_wal_senders setting is 16.
Is there anything else that needs to be checked, changed? How do we sort out this replication lag?

Postgres v15.3
we are using pgBouncer v1.20 in front of these two pgEdge nodes. they live in two different AZ but in same region.
CPU usage is less than 5%

Can someone please help ?

{
  "pg_isready": true,
  "readonly": "off",
  "cpu_pct": 0.0,
  "load_avg": [
    0.08740234375,
    0.05615234375,
    0.01513671875
  ],
  "disk": {
    "read_mb": 95372.2,
    "write_mb": 21685.1,
    "size": "16G",
    "used": "5.1G",
    "available": "11G",
    "used_pct": 33.0,
    "mount_point": "/"
  },
  "resolutions": 1,
  "slots": [
    {
      "slot_name": "spk_test_pg_ie1_sub_ie2ie1",
      "commit_lsn": "0/1A7ABD78",
      "commit_timestamp": "2023-09-13 09:09:11.484028+00:00",
      "replication_lag": "1 day, 4:50:41.419267",
      "replication_lag_bytes": "None"
    }
  ]
}

[luss]

Hi RAM,

Seems like your replication is stuck. Let me get someone to take a look with you.

--Luss

[hari4osdb]

Hi Ram - Thanks for posting the issue here.

To understand the issue, could you help us with some basic info using the following command output from both nodes:

• nodectl info (the latest and greatest is NodeCtl: v23.129)
• nodectl list (this command lists all the components installed)
• nodectl info pg15

While we're on the replication lag topic, let me introduce you a couple of spock extension metadata objects.

• spock.lag_tracker
• spock.local_sync_status

lag_tracker is the view which computes (easily) from pg_stat_replication, using the core function pg_wal_lsn_diff() and spock function - lag_tracker

On my local demo cluster (from n1)

demo=# SELECT * FROM spock.lag_tracker ;
      slot_name       | commit_lsn |       commit_timestamp        | replication_lag | replication_lag_bytes
----------------------+------------+-------------------------------+-----------------+-----------------------
 spk_demo_n1_sub_n2n1 | 0/1C5E900  | 2023-10-12 10:46:34.445058+00 | 00:12:02.274576 |                   112
(1 row)

You don't need to execute those lengthy SQL queries joining pg_stat_replication/slots and functions.

spock.local_sync_status is more of a sanity checker to know the status of the local node from a replication standpoint.

In your reply, include the output of above query too.