[pgpool-general: 3555] Using follow_master_command and pcp password

[Mark Kirkwood]

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?

Cheers

Mark