Re: [HACKERS] pg_stat_replication when standby is unreachable
Abhishek Rai abhishek...@gmail.com writes: 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? Not that I know of. We don't register standbies at all, so the master only knows about those which are successfully connected now. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_stat_replication when standby is unreachable
On Wed, May 29, 2013 at 9:16 AM, Peter Eisentraut pete...@gmx.net wrote: 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? By a live standby, I mean that the standby is currently connected to the primary and being used for synchronous replication. It may be lagging (e.g. when in catchup mode), but at least it's connected. This way, when the standby is fully caught up, I can be sure that if I suffer a permanent loss of the master or the replica, I would not lose any transaction which was successfully acknowledged to a client. 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. I see. Thanks for the response. Actually, pg_stat_replication seemed to be doing almost the exact thing I needed - providing a list of currently successfully connected standbys. And as per my observation, it was getting updated as standbys joined and left, precisely what I wanted. The request for a config parameter was simply to tune how quickly a down standby is reported via pg_stat_replication. But I can live without a configuration param to tune this behavior, as long as there is something in the code based on which I can infer how long it will take for the result of pg_stat_replication to be updated when a standby joins or leaves. I can use these timeouts to configure timeouts in the clustering software. Thanks, Abhishek
Re: [HACKERS] pg_stat_replication when standby is unreachable
On Wed, May 29, 2013 at 9:14 AM, Dimitri Fontaine dimi...@2ndquadrant.frwrote: Abhishek Rai abhishek...@gmail.com writes: 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? Not that I know of. We don't register standbies at all, so the master only knows about those which are successfully connected now. Actually that is precisely what I need. If the master reports via pg_stat_replication the set of standbies that it is connected to, then the clustering software can just rely on it to know if standby is active or not. However, this information is not very useful until there are some guarantees on how long it would take for the response of pg_stat_replication to be updated in response to an event. Without that, the clustering software would not know how long to wait before declaring the standby unhealthy. It's not a requirement that the timeout be configurable, as long as it's deterministic. Thanks for your help! Abhishek Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Re: [HACKERS] pg_stat_replication when standby is unreachable
I looked a bit more into the code and it appears to me that the following are true: - A separate wal sender process is created on the primary side for each connected standby. - The wal sender process terminates (walsender.c / WalSndLoop) when there is an error to write to the standby's socket. - If the standby machine is reachable but postgres is not running there any more, then the wal sender terminates almost immediately, probably because the standby machine sends a TCP RST to the wal sender. - If the standby machine is unreachable, then the wal sender will keep trying to send wal data. However, since the wal sender uses a non-blocking socket to talk to the standby, it will timeout and exit after replication_timeout (configured in postgresql.conf). So it seems like the wal sender should exit within replication_timeout or sooner, and this will be reflected using an update to pg_stat_replication. Therefore, I could just wait for up to replication_timeout before declaring the standby as dead. Thanks, Abhishek
