[pgpool-general: 4182] Re: pcp_detach_node / pcp_attach_node - wrong primary on failback.sh

[Tatsuo Ishii]

> Hi,
> what if I want to get the *primary node hostname*, is there any "easy way"
> to get it  ?
> how can I get the hostname using the node_id (without sql query & string
> manipulation)??

There is no way to get old primary hostname in the script for
now. Probably it's not terriby hard to modify pgpool-II though.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

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