<div dir="ltr">One idea to make Pgpool not depend on any status file....<div><br></div><div>Could pgpool2 check/compare the last query's timestamp saved in the postgresql log file of both nodes?</div><div><br></div><div><br></div><div><br></div></div><div class="gmail_extra"><br><div class="gmail_quote">On 6 August 2015 at 08:24, Tatsuo Ishii <span dir="ltr"><<a href="mailto:ishii@postgresql.org" target="_blank">ishii@postgresql.org</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span class="im HOEnZb">No. It measn both replication_mode and master_slave_mode = off.<br>
<br>
</span><div class="HOEnZb"><div class="h5">Best regards,<br>
--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
<br>
> "we need to remove this part except in "raw mode", in which database<br>
> incosistency problem will not happen."<br>
><br>
> Raw mode means 'connection_cache=off' ?<br>
> El 06/08/2015 02:30, "Tatsuo Ishii" <<a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>> escribió:<br>
><br>
>> It appeared that the behavior (if all backend are down, pgpool_status<br>
>> is ignored) is intentional.<br>
>><br>
>> From src/main/pgpool_main.c:<br>
>><br>
>> /*<br>
>> * If no one woke up, we regard the status file bogus<br>
>> */<br>
>> if (someone_wakeup == false)<br>
>> {<br>
>> for (i=0;i< pool_config->backend_desc->num_backends;i++)<br>
>> {<br>
>> BACKEND_INFO(i).backend_status = CON_CONNECT_WAIT;<br>
>> }<br>
>> (void)write_status_file();<br>
>> }<br>
>><br>
>> Here is the commit log:<br>
>> -------------------------------------------------------------<br>
>> commit a97eed16ef8c3a481c0cd0282b9950fb4ee28a89<br>
>> Author: Tatsuo Ishii <ishii at <a href="http://sraoss.co.jp" rel="noreferrer" target="_blank">sraoss.co.jp</a>><br>
>> Date: Sat Feb 13 11:23:55 2010 +0000<br>
>><br>
>> Fix read_status_file so that if all nodes were marked down status,<br>
>> it is regarded that this file is bogus. This will prevent "all<br>
>> node down" syndrome.<br>
>> -------------------------------------------------------------<br>
>><br>
>> The decision was made long time ago by me, but now I think this was<br>
>> not correct decision as you pointed out. I think we need to remove<br>
>> this part except in "raw mode", in which database incosistency problem<br>
>> will not happen.<br>
>><br>
>> Best regards,<br>
>> --<br>
>> Tatsuo Ishii<br>
>> SRA OSS, Inc. Japan<br>
>> English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>> Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
>><br>
>> > Thank you. I've confirmed that if only *one* of the two servers is<br>
>> > unreachable, pgpool behaves as expected (waits for the server to be<br>
>> > manually reattached).<br>
>> ><br>
>> > Although I wonder also, even if pgpool *did* correctly refuse to send<br>
>> > traffic if both servers were "down" in pgpool_status on restart, how<br>
>> > should we know in which direction to recover data (from A to B or B to<br>
>> > A)? Pgpool does not record in pgpool_status which "down" server was<br>
>> > the last to go down (and is thus authoritative). As a workaround I<br>
>> > think it would work to write a failover/failback_command which records<br>
>> > this information.<br>
>> ><br>
>> > On Wed, Aug 5, 2015 at 6:59 PM, Tatsuo Ishii <<a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>><br>
>> wrote:<br>
>> >> Pgpool should recognize that both A and B are in down status, but<br>
>> >> actually not. Let me investigate...<br>
>> >><br>
>> >> Best regards,<br>
>> >> --<br>
>> >> Tatsuo Ishii<br>
>> >> SRA OSS, Inc. Japan<br>
>> >> English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>> >> Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
>> >><br>
>> >>> Consider the following sequence, starting from a healthy system of two<br>
>> >>> PG servers (A and B) joined in "replication" mode:<br>
>> >>><br>
>> >>> 1) Server A loses connectivity.<br>
>> >>> 2) A write comes in, which pgpool commits to server B.<br>
>> >>> 3) Server B loses connectivity.<br>
>> >>> 4) Server A regains connectivity.<br>
>> >>> 5) pgpool restarts (due to either sysadmin action or failure).<br>
>> >>><br>
>> >>> At this point, pgpool happily directs all traffic to server A, which<br>
>> >>> does *not* have the most recent commit to server B. This is very bad<br>
>> >>> since I have now lost data consistency.<br>
>> >>><br>
>> >>> Rather, I would expect that pgpool remembers that it has written data<br>
>> >>> to B but not to A, and would refuse incoming connections until A has<br>
>> >>> been recovered from B.<br>
>> >>><br>
>> >>> Even to workaround, if before restarting pgpool, I had some tool which<br>
>> >>> checked the state in which pgpool left the two servers and then<br>
>> >>> rectified them, that would suffice. However since pgpool doesn't seem<br>
>> >>> to track at all the fact that it had written some data only to B but<br>
>> >>> not to A, that information is not available (e.g. from pgpool_status).<br>
>> >>><br>
>> >>> What am I missing? How is it that others use pgpool in "replication"<br>
>> >>> mode without encountering data inconsistencies when nodes fail?<br>
>> >>> _______________________________________________<br>
>> >>> pgpool-general mailing list<br>
>> >>> <a href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a><br>
>> >>> <a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" rel="noreferrer" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br>
>> _______________________________________________<br>
>> pgpool-general mailing list<br>
>> <a href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a><br>
>> <a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" rel="noreferrer" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br>
>><br>
</div></div></blockquote></div><br></div>