[pgpool-general: 3560] Re: Using follow_master_command and pcp password

[Mark Kirkwood]

On 20/03/15 15:30, Tatsuo Ishii wrote:
>> Hi,
>>
>> I'm experimenting with setting follow_master_command to make the
>> remaining standbys follow the new primary post failover. I'm on Ubuntu
>> 14.04 using postgres 9.3 and pgpool2 3.3.2.
>>
>> I am using a pretty standard failover script:
>>
>> $ cat failover.sh
>> #!/bin/bash -x
>> FALLING_NODE=$1         # %d
>> OLDPRIMARY_NODE=$2      # %P
>> NEW_PRIMARY=$3          # %H
>> PGDATA=$4               # %R
>>
>> echo "DEBUG: failover.sh $1 $2 $3 $4"
>> if [ $FALLING_NODE = $OLDPRIMARY_NODE ]; then
>>     ssh -T postgres@$NEW_PRIMARY touch $PGDATA/trigger
>> fi
>> exit 0
>>
>>
>> ...and the follow rewrites the recovery.conf to use the new primary,
>> plus restarts and (as a consequence) reattaches it:
>>
>> $ cat follow.sh
>> #!/bin/bash -x
>> NODE=$1                 # %d
>> HOST=$2                 # %h
>> OLDPRIMARY_NODE=$3      # %P
>> NEW_PRIMARY=$4          # %H
>> PGDATA=$5               # %R
>> PORT=$6                 # %r
>>
>> echo "DEBUG: follow.sh $1 $2 $3 $4 $5 $6"
>> if [ $FALLING_NODE = $NEW_PRIMARY ]; then
>>     echo "Primary - no action needed"
>> else
>>     ssh -T postgres@$HOST "
>> cd $PGDATA;
>> cat > recovery.conf << EOT
>> standby_mode = 'on'
>> primary_conninfo = 'host=$NEW_PRIMARY port=$PORT user=replica'
>> trigger_file = '$PGDATA/trigger'
>> recovery_target_timeline = 'latest'
>> EOT
>> service postgresql restart
>> "
>> sleep 90
>> /usr/sbin/pcp_attach_node 0 localhost 9898 postgres thepassword $NODE
>> fi
>> exit 0
>>
>>
>> This seems to work ok - however having to have the pcp admin password in
>> the script is undesirable, is there a smarter way to achieve this?
> 
> Unfortunately there's no way to fix this. The only workaround I can
> think of is changing the follow_master_command file permission to be
> only readable by pgpool process.
> 
> Next major version of pgpool-II (3.5) will overcome the problem by
> using similar mechanism of psql's .pgpass.

Cool,

Having a solution on the way is great!

Cheers

Mark