<div dir="ltr">Hi,<div>what if I want to get the <b>primary node hostname</b>, is there any "easy way" to get it ?</div><div>how can I get the hostname using the node_id (without sql query & string manipulation)??</div><div><br></div><div>Thanks,</div><div>cohavisi</div></div><div class="gmail_extra"><br><div class="gmail_quote">On Tue, Nov 10, 2015 at 1:43 AM, 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">> Hi,<br>
><br>
> *using postgresql 9.3.1 and pgpool 3.42*<br>
<span class="">><br>
> I try to write failover & failback mechanism using pgpool attach/detach pcp<br>
> command.<br>
> but sometimes I had noticed that the argument send to the failback script<br>
> are wrong (new primary hostname is not right):<br>
<br>
</span>There's no parameter for new primary hostname. If you care about<br>
primary node, you should use %P.<br>
<br>
# %P = old primary node id<br>
<br>
Note that "master node" != "primary node" in pgpool-II terms. The<br>
master node is a live node which has the youngest node id.<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>
> # *psql -U postgres -h 192.168.15.59 -p 9999 template1 -c "show<br>
> pool_nodes;"*<br>
<span class="">> Password for user postgres:<br>
> node_id | hostname | port | status | lb_weight | role<br>
> ---------+---------------+------+--------+-----------+---------<br>
> 0 | 192.168.15.55 | 5432 | 2 | 0.500000 | standby<br>
> 1 | 192.168.15.56 | 5432 | 2 | 0.500000 | primary<br>
> (2 rows)<br>
><br>
><br>
</span>> #* pcp_detach_node -d 10 192.168.15.59 9898 user password 0*<br>
<span class="">> DEBUG: send: tos="R", len=44<br>
> DEBUG: recv: tos="r", len=21, data=AuthenticationOK<br>
> DEBUG: send: tos="D", len=6<br>
> DEBUG: recv: tos="d", len=20, data=CommandComplete<br>
> DEBUG: send: tos="X", len=4<br>
><br>
><br>
</span>> [root@mgrdb-56 etc]# *psql -U postgres -h 192.168.15.59 -p 9999 template1<br>
> -c "show pool_nodes;"*<br>
<span class="">> Password for user postgres:<br>
> node_id | hostname | port | status | lb_weight | role<br>
> ---------+---------------+------+--------+-----------+---------<br>
> 0 | 192.168.15.55 | 5432 | 3 | 0.500000 | standby<br>
> 1 | 192.168.15.56 | 5432 | 2 | 0.500000 | primary<br>
> (2 rows)<br>
><br>
><br>
><br>
><br>
><br>
</span>> # *pcp_attach_node -d 10 192.168.15.59 9898 user password 0*<br>
<span class="">> DEBUG: send: tos="R", len=44<br>
> DEBUG: recv: tos="r", len=21, data=AuthenticationOK<br>
> DEBUG: send: tos="D", len=6<br>
> DEBUG: recv: tos="c", len=20, data=CommandComplete<br>
> DEBUG: send: tos="X", len=4<br>
><br>
><br>
><br>
</span>> *failback.sh script is being executed:*<br>
> *from pgpool.conf:*<br>
> *failback_command='/etc/pgpool-II/recovery/failback.sh %d %h %H %m %M %D %R<br>
> %r'*<br>
><br>
> *from failback.sh:*<br>
> *NODE_ID=$1*<br>
> *HOST_NAME=$2*<br>
> *NEW_MASTER_HOSTNAME=$3*<br>
> *NEW_MASTER_NODE_ID=$4*<br>
> *OLD_MASTER_NODE_ID=$5*<br>
> *DATABASE_CLUSTER_PATH=$6*<br>
> *MASTER_CLUSTER_PATH=$7*<br>
> *NEW_MASTER_PORT=$8*<br>
><br>
> *function log()*<br>
> *{*<br>
> *###log error messages###*<br>
> *[ ! "$1" ] && return 0*<br>
> */bin/echo "[$(/bin/date +%m/%d/%y\ %H:%M:%S.%2N)] - $1" >><br>
> $PGHOME/failback.log*<br>
> *}*<br>
><br>
> *log ""*<br>
> *log "--- failback of $HOST_NAME ---"*<br>
> *log ""*<br>
> *log "node_id: $NODE_ID"*<br>
> *log "hostname: $HOST_NAME"*<br>
> *log "hostname of new master: $NEW_MASTER_HOSTNAME"*<br>
> *log "new master node id: $NEW_MASTER_NODE_ID"*<br>
> *log "old master node id: $OLD_MASTER_NODE_ID"*<br>
> *log "database cluster path: $DATABASE_CLUSTER_PATH"*<br>
> *log "master database cluster path: $MASTER_CLUSTER_PATH"*<br>
> *log "master port: $NEW_MASTER_PORT"*<br>
> *for i in $*; do*<br>
> * log "$i"*<br>
> *done*<br>
> *exit 0*<br>
><br>
><br>
><br>
> *and the log outputs:*<br>
><br>
> *[11/09/15 19:02:00.66] - --- failback of 192.168.15.55 ---*<br>
> *[11/09/15 19:02:00.66] - node_id: 0*<br>
> *[11/09/15 19:02:00.67] - hostname: 192.168.15.55*<br>
> *[11/09/15 19:02:00.67] - hostname of new master: 192.168.15.55*<br>
> *[11/09/15 19:02:00.67] - new master node id: 0*<br>
> *[11/09/15 19:02:00.67] - old master node id: 1*<br>
> *[11/09/15 19:02:00.67] - database cluster path:<br>
> /home/postgres/databases/fabrix*<br>
> *[11/09/15 19:02:00.68] - master database cluster path:<br>
> /home/postgres/databases/fabrix*<br>
> *[11/09/15 19:02:00.68] - master port: 5432*<br>
> *[11/09/15 19:02:00.68] - 0*<br>
> *[11/09/15 19:02:00.68] - 192.168.15.55*<br>
> *[11/09/15 19:02:00.68] - 192.168.15.55*<br>
> *[11/09/15 19:02:00.69] - 0*<br>
> *[11/09/15 19:02:00.69] - 1*<br>
> *[11/09/15 19:02:00.69] - /home/postgres/databases/fabrix*<br>
> *[11/09/15 19:02:00.69] - /home/postgres/databases/fabrix*<br>
> *[11/09/15 19:02:00.69] - 5432*<br>
<div class="HOEnZb"><div class="h5">><br>
><br>
><br>
> as you can tell the new master us not 192.168.15.55 , should be<br>
> 192.168.15.56.<br>
><br>
><br>
> Please advice,<br>
><br>
> cohavisi<br>
</div></div></blockquote></div><br></div>