How to check stream replication latest history status

Started by 徐志宇徐over 3 years ago2 messagesgeneral
Jump to latest
#1徐志宇徐
xuzhiyuster@gmail.com

Hi All

I set up Postgres 11 stream replication env.Use keepalived to control
VIP and a script to check Postgres status.
Usually. I could check the stream replication status by this view
"pg_stat_replcation" on primary server.
Check this view "pg_stat_wal_receiver" on standby server.

Sometime the primary server abnormal or the network interrupt by
unexpected issue.

Both of them no record "pg_stat_replcation", "pg_stat_wal_receiver"
on that time.

I want to know the stream replication gap between primary and standby
before standby promote.
If the primary and standby have long replay_lag. I don't want to
standby server promote.

Is there any view or function which store the latest stream
replication gap(replay_lag) between primary and standby ?
Or it there any workaround to know this stream replication
gap(replay_lag) infomation ? Thanks for your help.

Jack.Xu

#2Kyotaro Horiguchi
horikyota.ntt@gmail.com
In reply to: 徐志宇徐 (#1)
Re: How to check stream replication latest history status

At Mon, 14 Nov 2022 00:09:36 +0800, 徐志宇徐 <xuzhiyuster@gmail.com> wrote in

If the primary and standby have long replay_lag. I don't want to
standby server promote.

Is there any view or function which store the latest stream
replication gap(replay_lag) between primary and standby ?
Or it there any workaround to know this stream replication
gap(replay_lag) infomation ? Thanks for your help.

replay_lag vanishes when the steram is gone. But commit timestamps
might work for you. You can see when the last replayed transaction on
the standby was commited on the primary.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center