Thread: Monitoring streaming replication from standby on Windows

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

Hello

I was just wondering the same. As for the slave, it seems to me that
the "ps fax" output "postgres: startup process   recovering" is at least
a quick way to check if the replication client has synced.

In a case where e.g. the slave was down for a while and then unsuccessfully
waits for WAL files which were long deleted on the master, this line changed
to "startup process  waiting".

HTH,

-christian-


On Fri, 14 Dec 2012 09:52:49 -0400
Yamen LA <iyamen@live.com> wrote:

>
> 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
controlthe databases and automate failovers. 
>
> As for monitoring the offset between the two, what is a reasonable value for the differences between last xlog sent,
receivedand replayed? 
>
> -Yamen
>
> Date: Thu, 13 Dec 2012 19:43:53 -0600
> From: karl@denninger.net
> To: iyamen@live.com
> CC: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] 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

--
Network Engineering & Design; Content Delivery Platform & IP

NETCOLOGNE Gesellschaft für Telekommunikation mbH
Am Coloneum 9 | 50829 Köln
Tel: 0221 2222-8711 | Fax: 0221 2222-78711
www.netcologne.de

Geschäftsführer:
Dr. Hans Konle (Sprecher)
Dipl.-Ing. Karl-Heinz Zankel
HRB 25580, AG Köln



Diese Nachricht (inklusive aller Anhänge) ist vertraulich. Sollten Sie diese
Nachricht versehentlich erhalten haben, bitten wir, den Absender (durch
Antwort-E-Mail) hiervon unverzüglich zu informieren und die Nachricht zu
löschen. Die E-Mail darf in diesem Fall weder vervielfältigt noch in anderer
Weise verwendet werden.