<html>
<head>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">Hello,<br>
<br>
</div>
<blockquote
cite="mid:20150204.211808.420871969969360195.t-ishii@sraoss.co.jp"
type="cite">
<pre wrap="">This is the functionality that I'm looking for: Two nodes, with one
master and one slave, that will cover for each other if and when there
is a problem.
My goal in using pgpool is to provide our application with a single
address and port for accessing the database. By connecting to pgpool,
it doesn't matter which is the primary (master) and which is the
secondary (slave); pgpool will route the query to the right place.
</pre>
</blockquote>
<br>
My company is looking for the same kind of function, and we have
indeed started using pgpool-II for query partitionning and failover
detection, but using streaming replication.<br>
<br>
Although, whenever a node fails and comes back online, pgpool never
attaches it again automatically, even if it's started replicating
and catched up with the master.<br>
<br>
In order to automate all, I've been looking into scripting this,
using a combination of :<br>
* pcp_node_info ;<br>
* pg_last_xlog_receive_location(), pg_last_xlog_replay_location(),
pg_last_xact_replay_timestamp(), pg_is_in_recovery() on the slave ;<br>
* pg_stat_replication table on the master ;<br>
in order to determine if node is ready to be reattached, and
pcp_attach_node to make it happen.<br>
<br>
But I also have read in your todo list
(<a class="moz-txt-link-freetext" href="http://www.pgpool.net/mediawiki/index.php/TODO">http://www.pgpool.net/mediawiki/index.php/TODO</a>) :<br>
<blockquote>Automatically reattach a node in streaming master/slave
configuration<br>
<br>
In streaming master/slave configuration there could be an option
to automatically reattach a node if it's up-to-date with the
master (0 bytes behind). It often happens that due to minor
network outage a slave node is dropped off from pgpool and stays
down even if the the node has resumed replication with master and
is up-to-date.pgpool already knows how much slave is behind master
so i guess this wouldn't be too difficult to implement? (from
bugtrack #17)<br>
<br>
</blockquote>
Would it be complicated to implement directly in pgpool-II such an
automatic reattach system ? If I were to use scripting to detect a
node is up again and catched up, where should I place the different
steps, between failover_command, recovery_1st_stage_command or
recovery_2nd_stage_command ?<br>
<br>
これからお世話になります<br>
どうもありがとうございます!<br>
<br>
Loïc Gomez<br>
</body>
</html>