Re: pg_stat_replication when standby is unreachable

On 5/28/13 9:42 PM, Abhishek Rai wrote:
> Detecting primary health is easy.  But what is the best way to know if
> the standby is live?  Since this is not a hot-standby, I cannot send
> queries to it.

Then how do you define "live" for your use case?

> Currently, I'm sending the following query to the primary:
>
>   SELECT * from pg_stat_replication();
>
> I've noticed that when I terminate the standby (cleanly or through kill
> -9), the result of above function goes from 1 row to zero rows.  The
> result comes back to 1 row when the standby restarts and reconnects.  I
> was wondering if there is any kind of guarantee about the results of
> pg_stat_replication as the standby suffers a network partition, and/or
> restarts and reconnects with the primary.  Are there any parameters that
> control this behavior?

No, pg_stat_replication is not an appropriate tool for tracking
standbys, for the reasons you point out.  You need to track the list of
actual and potential standbys that you are interested in somewhere else.