How do I get pcp to automatically attach nodes to

Question

I'm using postgres 9.4.9, pgpool 3.5.4 on centos 6.8.

I'm having a major hard time getting pgpool to automatically detect when nodes are up (it often detects the first node but rarely detects the secondary) but if I use pcp_attach_node to tell it what nodes are up, then everything is hunky dory.

So I figured until I could properly sort the issue out, I would write a little script to check the status of the nodes and attach them as appropriate, but I'm having trouble with the password prompt. According to the documentation, I should be able to issue commands like

pcp_attach_node 10 localhost 9898 pgpool mypass 1

but that just complains

pcp_attach_node: Warning: extra command-line argument "localhost" ignored pcp_attach_node: Warning: extra command-line argument "9898" ignored pcp_attach_node: Warning: extra command-line argument "pgpool" ignored pcp_attach_node: Warning: extra command-line argument "mypass" ignored pcp_attach_node: Warning: extra command-line argument "1" ignored

it'll only work when I use parameters like

pcp_attach_node -U pgpool -h localhost -p 9898 -n 1 

and there's no parameter for the password, I have to manually enter it at the prompt.

Any suggestions for sorting this other than using Expect?

Answer 1

You have to create PCPPASSFILE. Search pgpool documentation for more info.

Example 1:

create PCPPASSFILE for logged user (vi ~/.pcppass), file content is 127.0.0.1:9897:user:pass (hostname:port:username:password), set file permissions 0600 (chmod 0600 ~/.pcppass)

command should run without asking for password

pcp_attach_node -h 127.0.0.1 -U user -p 9897 -w -n 1

Example 2:

create PCPPASSFILE (vi /usr/local/etc/.pcppass), file content is 127.0.0.1:9897:user:pass (hostname:port:username:password), set file permissions 0600 (chmod 0600 /usr/local/etc/.pcppass), set variable PCPPASSFILE (export PCPPASSFILE=/usr/local/etc/.pcppass)

command should run without asking for password

pcp_attach_node -h 127.0.0.1 -U user -p 9897 -w -n 1

Script for auto attach the node

You can schedule this script with for example crontab.

#!/bin/bash
#pgpool status
#0 - This state is only used during the initialization. PCP will never display it.
#1 - Node is up. No connections yet.
#2 - Node is up. Connections are pooled.
#3 - Node is down.

source $HOME/.bash_profile
export PCPPASSFILE=/appl/scripts/.pcppass
STATUS_0=$(/usr/local/bin/pcp_node_info -h 127.0.0.1 -U postgres -p 9897 -n 0 -w | cut -d " " -f 3)
echo $(date +%Y.%m.%d-%H:%M:%S.%3N)" [INFO] NODE 0 status "$STATUS_0;

if (( $STATUS_0 == 3 ))
then
    echo $(date +%Y.%m.%d-%H:%M:%S.%3N)" [WARN] NODE 0 is down - attaching node"
    TMP=$(/usr/local/bin/pcp_attach_node -h 127.0.0.1 -U postgres -p 9897 -n 0 -w -v)
    echo $(date +%Y.%m.%d-%H:%M:%S.%3N)" [INFO] "$TMP 
fi


STATUS_1=$(/usr/local/bin/pcp_node_info -h 127.0.0.1 -U postgres -p 9897 -n 1 -w | cut -d " " -f 3)
echo $(date +%Y.%m.%d-%H:%M:%S.%3N)" [INFO] NODE 1 status "$STATUS_1;

if (( $STATUS_1 == 3 ))
then
    echo $(date +%Y.%m.%d-%H:%M:%S.%3N)" [WARN] NODE 1 is down - attaching node"
    TMP=$(/usr/local/bin/pcp_attach_node -h 127.0.0.1 -U postgres -p 9897 -n 1 -w -v)
    echo $(date +%Y.%m.%d-%H:%M:%S.%3N)" [INFO] "$TMP 
fi

exit 0