1) Run the below Query on Primary:-
SELECT
pid,
usename,
application_name,
client_addr,
state,
sync_state,
sent_lsn,
write_lsn,
flush_lsn,
replay_lsn,
(sent_lsn - replay_lsn) AS replication_lag
FROM
pg_stat_replication;
postgres=# SELECT
postgres-# pid,
postgres-# usename,
postgres-# application_name,
postgres-# client_addr,
postgres-# state,
postgres-# sync_state,
postgres-# sent_lsn,
postgres-# write_lsn,
postgres-# flush_lsn,
postgres-# replay_lsn,
postgres-# (sent_lsn - replay_lsn) AS replication_lag
postgres-# FROM
postgres-# pg_stat_replication;
pid | usename | application_name | client_addr | state | sync_state | sent_lsn | write_lsn | flush_lsn | replay_lsn | replication_lag
------+---------+------------------+---------------+-----------+------------+------------+------------+------------+------------+-----------------
5225 | repuser | walreceiver | 192.168.0.202 | streaming | async | 0/3FCE4B98 | 0/3FCE4B98 | 0/3FCE4B98 | 0/3FCE4B98 | 0
(1 row)
2. Check pg_stat_wal_receiver on the Standby Server
On the standby server, you can check the pg_stat_wal_receiver view, which provides details about the WAL receiver process:
run on standby :-
SELECT * FROM pg_stat_wal_receiver;
postgres=# SELECT * FROM pg_stat_wal_receiver;
pid | status | receive_start_lsn | receive_start_tli | written_lsn | flushed_lsn | received_tli | last_msg_send_time | la
st_msg_receipt_time | latest_end_lsn | latest_end_time | slot_name | sender_host | sender_port |
conninfo
-------+-----------+-------------------+-------------------+-------------+-------------+--------------+----------------------------------+--------
--------------------------+----------------+----------------------------------+-----------+---------------+-------------+-------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------
11743 | streaming | 0/8000000 | 1 | 0/3FCE4B98 | 0/3FCE4B98 | 1 | 2024-10-27 21:57:51.021617+05:30 | 2024-10
-27 21:57:51.014668+05:30 | 0/3FCE4B98 | 2024-10-27 21:51:50.271248+05:30 | | 192.168.0.201 | 5433 | user=repuser passfile=/v
ar/lib/pgsql/.pgpass channel_binding=prefer dbname=replication host=192.168.0.201 port=5433 fallback_application_name=walreceiver sslmode=prefer s
slcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any
(1 row)
3. Using pg_current_wal_lsn()
While the pg_current_wal_lsn() function is not available during recovery, it can be used on the primary server to get the current WAL location:
postgres=#
postgres=# SELECT pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
0/3FCE4B98
(1 row)
4. Check for Replication Lag
If the function is not available, you can calculate the replication lag using simple arithmetic with LSN values,
since pg_stat_replication provides both the current WAL LSN and the replay LSN.
Here's how to do it:
SELECT
application_name,
pg_current_wal_lsn() AS current_lsn,
replay_lsn,
pg_current_wal_lsn() - replay_lsn AS lag_bytes
FROM
pg_stat_replication;
postgres=# SELECT
postgres-# application_name,
postgres-# pg_current_wal_lsn() AS current_lsn,
postgres-# replay_lsn,
postgres-# pg_current_wal_lsn() - replay_lsn AS lag_bytes
postgres-# FROM
postgres-# pg_stat_replication;
application_name | current_lsn | replay_lsn | lag_bytes
------------------+-------------+------------+-----------
walreceiver | 0/3FCE4B98 | 0/3FCE4B98 | 0
No comments:
Post a Comment