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

[Tatsuo Ishii]

> 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.

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