<html><head></head><body><div style="font-family:Helvetica Neue, Helvetica, Arial, sans-serif;font-size:16px;"><div style="font-family:Helvetica Neue, Helvetica, Arial, sans-serif;font-size:16px;"><div style="font-family:Helvetica Neue, Helvetica, Arial, sans-serif;font-size:16px;"><div><div>Ah ok, thanks, so I have learned something more about pgpool (I did not know it was possible to give multiple flags to a backend) </div><div><br></div><div>I got it working more or less :-)</div><div><br></div><div>So I set</div><div><span>
<div><span class="ydpc13e733cs1">backend_flag0 = 'ALWAYS_MASTER|ALLOW_TO_FAILOVER'</span></div><div><span class="ydpc13e733cs1">and</span></div><div>backend_flag1 = 'ALLOW_TO_FAILOVER'</div><div><span class="ydpc13e733cs1"><br></span></div><div><span class="ydpc13e733cs1">and then</span></div><div><span class="ydpc13e733cs1"><br></span></div><div><span class="ydpc13e733cs1">failover_command=''<br></span><br></div><div>fail_over_on_backend_error = no</div><div>health_check_period = 5</div><div>health_check_max_retries = 0</div><div>health_check_retry_delay = 0</div><div>load_balance_mode = no</div><div><span class="ydpc13e733cs1"><br></span></div><div><span class="ydpc13e733cs1">when the primary is stopped, the failover_command is executed (not because of the health check but because pgpool detects the administrative shutdown and therefore it triggers a failover, this is as documented). The failover does nothing though (empty command).</span></div><div><span class="ydpc13e733cs1"><br></span></div><div><span class="ydpc13e733cs1">The only problem is that the backend 2 got detached from the pool, I see this in the log</span></div><div><span class="ydpc13e733cs1"><span>
<p class="ydp2804747ap1"><span class="ydp2804747as1">2018-06-25 18:13:20: pid 30392: LOG:<span class="ydp2804747aApple-converted-space"> </span>starting follow degeneration. shutdown host pg02(5432)</span></p></span>And so I cannot connect anymore before both hosts are down.</span></div><div><span class="ydpc13e733cs1"><span>
<p class="ydpbb311ebdp1"><span class="ydpbb311ebds1">2018-06-25 18:13:41: pid 30462: FATAL:<span class="ydpbb311ebdApple-converted-space"> </span>pgpool is not accepting any new connections<br></span>2018-06-25 18:13:41: pid 30462: DETAIL:<span class="ydpbb311ebdApple-converted-space"> </span>all backend nodes are down, pgpool requires at least one valid node<br>2018-06-25 18:13:41: pid 30462: HINT:<span class="ydpbb311ebdApple-converted-space"> </span>repair the backend nodes and restart pgpool</p></span></span></div><div><span class="ydpc13e733cs1">I have to perform pcp_attach_node of the standby manually and then it works. Of course I could automate the attach node via the follow_master command, so it is not a big deal</span></div><div><span class="ydpc13e733cs1"><br></span></div><div><span class="ydpc13e733cs1">It is not a set-up that I can use, I need to have automatic failover (and also automatic recovery of failed masters and failed standbys)</span></div></span><br></div><div>Thanks,</div><div><br></div><div class="ydpa3cfadd4signature">Pierre</div></div>
<div><br></div><div><br></div>
</div></div><div id="ydp2f0b6d6fyahoo_quoted_0387506638" class="ydp2f0b6d6fyahoo_quoted">
<div style="font-family:'Helvetica Neue', Helvetica, Arial, sans-serif;font-size:13px;color:#26282a;">
<div>
On Monday, June 25, 2018, 6:32:47 PM GMT+2, Andre Piwoni <apiwoni@webmd.net> wrote:
</div>
<div><br></div>
<div><br></div>
<div><div id="ydp2f0b6d6fyiv5882545514"><div><div dir="ltr">Pierre, please see Tatsuo explanation above. <div><br clear="none"></div><div>If I don't use <span style="font-size:12.8px;text-decoration-color:initial;float:none;display:inline;">DISALLOW_TO_FAILOVER flag and primary is stopped then I'm able to use read replica. It may take 30 seconds for failover to happen and during this time no connectivity is allowed.</span><div><span style="font-size:12.8px;text-decoration-color:initial;float:none;display:inline;">My backend flag for pimary is <span style="background-color:rgb(255,255,255);text-decoration-color:initial;float:none;display:inline;">set to 'ALWAYS_MASTER|ALLOW_TO_</span><span style="background-color:rgb(255,255,255);text-decoration-color:initial;float:none;display:inline;">FAILOVER' since I don't have failover for the primary set at the moment so there's no point in searching for new primary. ALLOW_TO_FAILOVER seems to be needed to allow read-only functionality when primary is down and no other slave is promoted to primary.</span></span></div></div><div><span style="font-size:12.8px;text-decoration-color:initial;float:none;display:inline;"><span style="background-color:rgb(255,255,255);text-decoration-color:initial;float:none;display:inline;">You may need to create sr_check_user too.</span></span></div></div><div class="ydp2f0b6d6fyiv5882545514gmail_extra"><br clear="none"><div class="ydp2f0b6d6fyiv5882545514yqt9433039617" id="ydp2f0b6d6fyiv5882545514yqt90489"><div class="ydp2f0b6d6fyiv5882545514gmail_quote">On Sun, Jun 24, 2018 at 12:54 PM, Pierre Timmermans <span dir="ltr"><<a shape="rect" href="mailto:ptim007@yahoo.com" rel="nofollow" target="_blank">ptim007@yahoo.com</a>></span> wrote:<br clear="none"><blockquote class="ydp2f0b6d6fyiv5882545514gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"><div><div style="font-family:Helvetica Neue, Helvetica, Arial, sans-serif;font-size:16px;"><div style="font-family:Helvetica Neue, Helvetica, Arial, sans-serif;font-size:16px;"><div><div>I cannot get this to work neither, I tried various combination of parameters. Once the primary is stopped pgpool does not accept connections at all. </div><div><br clear="none"></div><div>In particular, if I set the failover_command to '' (empty string) then when the primary is stopped pgpool starts searching for a new primary (<span>find_primary_node: checking backend no x repeated in the log) but in the meantime it does not accept connections.</span></div><div><span><br clear="none"></span></div><div>Another solution to this problem is to not use pgpool but use a connection string of the form</div><div><br clear="none"></div><div>postgresql://user:secret@host1 :5432;host2:5432/somedb? target_session_attrs=any</div><div><br clear="none"></div><div><a shape="rect" href="https://paquier.xyz/postgresql-2/postgres-10-libpq-read-write/" rel="nofollow" target="_blank">https://paquier.xyz/ postgresql-2/postgres-10- libpq-read-write/</a><br clear="none"></div><div><br clear="none"></div><div><br clear="none"></div><div>but I am not sure all clients support this format (the nodejs pg-node does not). </div><span class="ydp2f0b6d6fyiv5882545514HOEnZb"><font color="#888888"></font></span><div><br clear="none"></div><div class="ydp2f0b6d6fyiv5882545514m_7039436672158548814ydp40a86b2asignature">Pierre</div></div>
<div><br clear="none"></div><div><br clear="none"></div>
</div><div class="ydp2f0b6d6fyiv5882545514m_7039436672158548814ydp981e2c0byahoo_quoted" id="ydp2f0b6d6fyiv5882545514m_7039436672158548814ydp981e2c0byahoo_quoted_0540816913">
<div style="font-family:'Helvetica Neue', Helvetica, Arial, sans-serif;font-size:13px;color:#26282a;"><span class="ydp2f0b6d6fyiv5882545514">
</span><div>
On Wednesday, June 20, 2018, 5:50:06 PM GMT+2, Andre Piwoni <<a shape="rect" href="mailto:apiwoni@webmd.net" rel="nofollow" target="_blank">apiwoni@webmd.net</a>> wrote:
</div>
<div><br clear="none"></div>
<div><br clear="none"></div>
<div><div id="ydp2f0b6d6fyiv5882545514m_7039436672158548814ydp981e2c0byiv8782646484"><div><span class="ydp2f0b6d6fyiv5882545514"></span><div dir="ltr"><div>Wolf,</div><div>Do you mind sharing relevant configuration and version of pgpool you are using? Do you have more than one slave?</div><div><br clear="none"></div>I have tried the following combinations:<div><br clear="none"></div><div>backend_flag0 = 'ALWAYS_MASTER|DISALLOW_TO_ FAILOVER'<br clear="none"></div><div>backend_flag1 = 'DISALLOW_TO_FAILOVER'<br clear="none"></div><div><br clear="none"></div><div>
<span style="font-size:small;background-color:rgb(255,255,255);text-decoration-color:initial;float:none;display:inline;">backend_flag0 = 'ALWAYS_MASTER'</span>
<br clear="none"></div><div>
<span style="font-size:small;background-color:rgb(255,255,255);text-decoration-color:initial;float:none;display:inline;">backend_flag1 = 'DISALLOW_TO_FAILOVER'</span>
<br clear="none"></div><div><br clear="none"></div><div>
<div style="font-size:small;text-decoration-color:initial;"><span style="font-size:small;background-color:rgb(255,255,255);text-decoration-color:initial;float:none;display:inline;">backend_flag0 = 'ALWAYS_MASTER'</span><span> </span><br clear="none"></div><div style="font-size:small;text-decoration-color:initial;"><span style="font-size:small;background-color:rgb(255,255,255);text-decoration-color:initial;float:none;display:inline;">backend_flag1 = 'ALLOW_TO_FAILOVER'</span><span> </span></div><div style="font-size:small;text-decoration-color:initial;"><span><br clear="none"></span></div><div style="text-decoration-color:initial;">master_slave_mode = on<br clear="none"></div><div style="text-decoration-color:initial;">master_slave_sub_mode = 'stream'<br clear="none"></div><div style="text-decoration-color:initial;"><br clear="none"></div><div style="text-decoration-color:initial;">sr_check_period = 0 and default sr_check_period = 10<br clear="none"></div><div style="text-decoration-color:initial;"><div>health_check_period = 0<br clear="none"></div></div>
failover_command = ''<br clear="none"></div><div>fail_over_on_backend_error = off and fail_over_on_backend_error = on<br clear="none"></div></div><div class="ydp2f0b6d6fyiv5882545514m_7039436672158548814ydp981e2c0byiv8782646484gmail_extra"><br clear="none"><div class="ydp2f0b6d6fyiv5882545514m_7039436672158548814ydp981e2c0byiv8782646484yqt6508780117" id="ydp2f0b6d6fyiv5882545514m_7039436672158548814ydp981e2c0byiv8782646484yqtfd09636"><div class="ydp2f0b6d6fyiv5882545514m_7039436672158548814ydp981e2c0byiv8782646484gmail_quote"><span class="ydp2f0b6d6fyiv5882545514">On Wed, Jun 20, 2018 at 8:40 AM, Wolf Schwurack <span dir="ltr"><<a shape="rect" href="mailto:wolf@uen.org" rel="nofollow" target="_blank">wolf@uen.org</a>></span> wrote:<br clear="none"></span><blockquote class="ydp2f0b6d6fyiv5882545514m_7039436672158548814ydp981e2c0byiv8782646484gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"><span class="ydp2f0b6d6fyiv5882545514">Yes it is possible I have all my Pgpool hosts setup that way. My setup is a streaming replica and I don't apply the failover.sh script. If the master goes down pgpool fails over to the standby in readonly mode. <br clear="none">
<br clear="none">
<br clear="none">
Wolfgang Schwurack<br clear="none">
Database/System Administrator<br clear="none">
Utah Education Network<br clear="none">
801-587-9444<br clear="none">
<a shape="rect" href="mailto:wolf@uen.org" rel="nofollow" target="_blank">wolf@uen.org</a><br clear="none">
</span><span class="ydp2f0b6d6fyiv5882545514"></span><div><div class="ydp2f0b6d6fyiv5882545514m_7039436672158548814ydp981e2c0byiv8782646484h5"> <br clear="none">
<br clear="none">
<br clear="none">
On 6/19/18, 9:48 PM, "<a shape="rect" href="mailto:pgpool-general-bounces@pgpool.net" rel="nofollow" target="_blank">pgpool-general-bounces@ pgpool.net</a> on behalf of Bo Peng" <<a shape="rect" href="mailto:pgpool-general-bounces@pgpool.net" rel="nofollow" target="_blank">pgpool-general-bounces@ pgpool.net</a> on behalf of <a shape="rect" href="mailto:pengbo@sraoss.co.jp" rel="nofollow" target="_blank">pengbo@sraoss.co.jp</a>> wrote:<br clear="none">
<br clear="none">
Which version of Pgpool-II do you use?<br clear="none">
It also happened by latest version of Pgpool-II 3.7.4?<br clear="none">
<br clear="none">
On Tue, 19 Jun 2018 20:00:58 -0700<br clear="none">
Andre Piwoni <<a shape="rect" href="mailto:apiwoni@webmd.net" rel="nofollow" target="_blank">apiwoni@webmd.net</a>> wrote:<br clear="none">
<br clear="none">
> I have been trying to figure how can I configure PgPool with one primary<br clear="none">
> and one hot-standby slave to route read-requests to hot-standby when<br clear="none">
> primary is down without any failover. Is it currently possible? Please<br clear="none">
> don't ask why.<br clear="none">
> <br clear="none">
> Thank you,<br clear="none">
> Andre Piwoni<br clear="none">
<br clear="none">
<br clear="none">
-- <br clear="none">
Bo Peng <<a shape="rect" href="mailto:pengbo@sraoss.co.jp" rel="nofollow" target="_blank">pengbo@sraoss.co.jp</a>><br clear="none">
SRA OSS, Inc. Japan<br clear="none">
<br clear="none">
</div></div> ______________________________ _________________<br clear="none">
pgpool-general mailing list<br clear="none">
<a shape="rect" href="mailto:pgpool-general@pgpool.net" rel="nofollow" target="_blank">pgpool-general@pgpool.net</a><br clear="none">
<a shape="rect" href="http://www.pgpool.net/mailman/listinfo/pgpool-general" rel="nofollow" target="_blank">http://www.pgpool.net/mailman/ listinfo/pgpool-general</a><br clear="none">
<br clear="none">
<br clear="none">
</blockquote></div><br clear="none"></div></div></div></div><span class="ydp2f0b6d6fyiv5882545514"></span><div class="ydp2f0b6d6fyiv5882545514m_7039436672158548814ydp981e2c0byqt6508780117" id="ydp2f0b6d6fyiv5882545514m_7039436672158548814ydp981e2c0byqtfd74630">______________________________ _________________<br clear="none">pgpool-general mailing list<br clear="none"><a shape="rect" href="mailto:pgpool-general@pgpool.net" rel="nofollow" target="_blank">pgpool-general@pgpool.net</a><br clear="none"><a shape="rect" href="http://www.pgpool.net/mailman/listinfo/pgpool-general" rel="nofollow" target="_blank">http://www.pgpool.net/mailman/ listinfo/pgpool-general</a><br clear="none"></div></div>
</div>
</div></div></div></blockquote></div></div><br clear="none"><br clear="all"><div><br clear="none"></div>-- <br clear="none"><div class="ydp2f0b6d6fyiv5882545514gmail_signature"><div dir="ltr"><p><b><span style="color:#404040;">Andre Piwoni</span></b></p>
<p><span style="color:#404040;">Sr. Software Developer,
BI/Database</span></p>
<p><b><i><span style="color:#1f497d;">Web</span></i></b><span style="color:black;">MD Health Services</span><span></span></p>
<p><span style="color:#404040;">Mobile: 801.541.4722</span></p>
<p><span style="color:#1f497d;"><a shape="rect" href="http://www.webmdhealthservices.com/" rel="nofollow" target="_blank"><span style="color:blue;">www.webmdhealthservices.com</span></a></span><span style="color:#404040;"></span></p></div></div>
</div></div></div></div>
</div>
</div></div></body></html>