Re: Monitoring streaming replication from standby on Windows

[Yamen LA <iyamen@xxxxxxxx>]

Thank you Karl.

I am trying to determine on the slave itself whether streaming replication (i.e. WAL receiver process) is active or not, similar to checking pg_stat_replication on the master. In fact, this is part of a larger module I am building to control the databases and automate failovers.

As for monitoring the offset between the two, what is a reasonable value for the differences between last xlog sent, received and replayed?

-Yamen

---

Date: Thu, 13 Dec 2012 19:43:53 -0600
From: karl@xxxxxxxxxxxxx
To: iyamen@xxxxxxxx
CC: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: Monitoring streaming replication from standby on Windows

On 12/13/2012 7:36 PM, Yamen LA wrote:

Hello,

I would like to know how to check the status of the streaming replication from standby server on Windows. Apparently from the master I can use the pg table "pg_stat_replication". This table is, however, empty on the standby since it contains information about WAL sender processes and not WAL receiver. pg_last_xlog_replay_location and pg_last_xlog_receive_location also continue to be valid even when the streaming replication is down, so they don't help in this case.
From online tutorials and PostgreSQL wiki the only way I found is by checking the running processes for wal sender and wal receiver using ps command on Unix systems. The problem is that on Windows, all those processes carry the same name, postgresql.exe.

I suppose there should be some parameter to get the db engine as it realizes when the streaming replication is down and it logs that in pg_log files, but I can't seem to find such a parameter.

Thank you for your help.

-Yamen

What are you trying to determine?

If it's whether the replication is caught up, I have a small "C" program that will do that and have posted it before (I can do that again if you'd like.)

If it's whether it's "up", that's a bit more complex, since you have to define "up." 

For most purposes determining that the offset between the two is less than some value at which you alarm is sufficient, and if you then alarm if you can't reach the master and slave hosts, you then know if the machines are "up" from a standpoint of reachability on the network as well.

--
-- Karl Denninger
The Market Ticker ®
Cuda Systems LLC