Search Postgresql Archives

Re: Metric to calculate WAL size left to transfer to Standby

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello Laurenz,

The above metric works fine for my primary server. However, We have a cascading setup in our production system. This particular query doesn't work for my intermediate server which is standby to the primary server but also a master to one more standby server. We get the following error:
prod=# SELECT round(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) / (1024 :: NUMERIC ^ 2), 2 ) AS replay_lag FROM pg_stat_replication where application_name like 'rtv%';
ERROR:  recovery is in progress
HINT:  WAL control functions cannot be executed during recovery.


I wanted to also track the transfer/replay lag between the intermediate server and the final standby. I narrowed down the problem with pg_current_wal_lsn() procedure. Any idea on how to mitigate this? or any alternative to the pg_current_wal_lsn() procedure that I can use to get transfer lag?

Thanks,
Viral Shah


On Fri, Apr 16, 2021 at 7:52 PM Viral Shah <vshah@xxxxxxxxxxxxxxxxx> wrote:
Hello Laurenz,

Thank you so much for sending the query. It was exactly what I needed. I just made 1 modification to beautify the transfer and replay lag and I can see the size in bytes.

SELECT application_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn)) AS transfer_lag,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_lag
FROM pg_stat_replication;
I am now using zabbix to constantly monitor them and notify myself if it breaches a certain threshold.

Thanks again!

Best,
Viral Shah
Nodal Exchange LLC


On Thu, Apr 15, 2021 at 8:10 AM Laurenz Albe <laurenz.albe@xxxxxxxxxxx> wrote:
On Wed, 2021-04-14 at 17:50 -0400, Viral Shah wrote:
> We have a PostgreSQL 10.12 cluster of servers in two different data centers.
>  Off lately, in the case of a large WAL generation, we are seeing replication
>  delay between the master and the standby server. These delays have off lately
>  been there for an unusually long time. I was wondering if we have any metric
>  that can calculate the amount (size) of WAL transfer left between master and
>  standby?
>
> PS: We have ensured we have upgraded our firewalls for better speed transfer.
>
> Any help on how to figure out the slowness in the WAL transfer would be much appreciated.

SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn) AS transfer_lag,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag
FROM pg_stat_replication;

If both are delayed, it might be that the network cannot cope.

If only the second number is delayed, you have replication conflicts
with queries on the standby.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux