How do I get pcp to automatically attach nodes to postgres pgpool?

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?

tyfyh · Answer 1 · 2017-07-24T08:15:31.537

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

Is there any way we can make the command execution automated or inform pg pool to execute this command once the down node is up? — Rahul Munjal, Jul 21 '17 at 10:00

score 0 · Answer 2 · answered Dec 22 '21 at 16:55

0

yes you can trigger execution of this command using a customised failover_command (failover.sh in your /etc/pgpool)

answered Dec 22 '21 at 16:55

VME

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Dec 23 '21 at 00:46

score 0 · Answer 3 · answered Apr 03 '22 at 19:36

Automated way to up your pgpool down node:

copy this script into a file with execute permission to your desired location with postgres ownership into all nodes.

run crontab -e comamnd under postgres user

Finally set that script to run every minute at crontab . But to execute it for every second you may create your own service and run it.

#!/bin/bash
# This script will up all pgpool down node

#************************
#******NODE STATUS*******
#************************

# 0 - This state is only used during the initialization.
# 1 - Node is up. No connection yet.
# 2 - Node is up and connection is pooled.
# 3 - Node is down

#************************
#******SCRIPT*******
#************************
server_node_list=(0 1 2)    
for server_node in ${server_node_list[@]}
do
    source $HOME/.bash_profile
    export PCPPASSFILE=/var/lib/pgsql/.pcppass
    node_status=$(pcp_node_info -p 9898 -h localhost -U pgpool  -n $server_node -w | cut -d ' ' -f 3);

    if [[ $node_status == 3 ]]
    then
            pcp_attach_node -n $server_node -U pgpool -p 9898 -w -v
    fi

done

How do I get pcp to automatically attach nodes to postgres pgpool?

3 Answers3

Linked