Delaying apply log files in replica node - Mailing list pgsql-admin
Hi Team,
We have 3 node postgres patroni HA setup in our environment,
2 node running in same datacentre (As a Primary & Replica) , another one node running in different datacentre (As a DR) .
We have observed there is lag between replica nodes whenever full load running in primary due to high WAL generation , (1 WAL file size is 16 MB) ( Max file generation in 1 sec ~ 200 Files)
By using the below query we are monitoring the lag
patronictl -c /usr/pgsql-14/patroni/patroni.yml list
But while running this query we are getting output Lag is 0 , then we have validated at db level it’s showing there was a lag.
In node lxpgseinvprdw03 (Running in same data centre ) Lag showing as 0 , but when we checked at db level startup recovering with 1.5 hr behind WAL file
Got the below output at db level .
Query: SELECT pg_is_in_recovery() AS is_slave,pg_last_wal_receive_lsn() AS receive,pg_last_wal_replay_lsn() AS replay,pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() AS synced,(EXTRACT(EPOCH FROM now()) - EXTRACT(EPOCH FROM pg_last_xact_replay_timestamp()))::int AS lag;
Output
is_slave | receive | replay | synced | lag
----------+---------------+---------------+--------+------
t | CD41/9BF51698 | CCD7/6F6A3188 | f | 9448
Please advise us.
- Why we get difference lag value while querying and db level ?
- How we can speed up the wal apply process in replica side ?
Please let us know if need any further details from our end….Thanks !
Regards,
Kamaldeen
NOTICE: All information in and attached to the e-mails below may be proprietary, confidential, privileged and otherwise protected from improper or erroneous disclosure. If you are not the sender's intended recipient, you are not authorized to intercept, read, print, retain, copy, forward, or disseminate this message. If you have erroneously received this communication, please notify the sender immediately by phone (704-758-1000) or by e-mail and destroy all copies of this message electronic, paper, or otherwise. By transmitting documents via this email: Users, Customers, Suppliers and Vendors collectively acknowledge and agree the transmittal of information via email is voluntary, is offered as a convenience, and is not a secured method of communication; Not to transmit any payment information E.G. credit card, debit card, checking account, wire transfer information, passwords, or sensitive and personal information E.G. Driver's license, DOB, social security, or any other information the user wishes to remain confidential; To transmit only non-confidential information such as plans, pictures and drawings and to assume all risk and liability for and indemnify Lowe's from any claims, losses or damages that may arise from the transmittal of documents or including non-confidential information in the body of an email transmittal. Thank you.
Attachment
pgsql-admin by date: