Skip to content

postgresql pgpool-II autofailover and failback

postgresql pgpool-II autofailover and failback published on Комментариев к записи postgresql pgpool-II autofailover and failback нет

Hello, try to find a good material about how-to configure pgpool and found nothing, so I’ve decide to write my own article =))

I’m recommend to read previous articles:
How to install postgres
How to configure repmgr to manage standby databases
because I use those config in this scenario

So, lets start =)

First of all lest configure OS to match our needs ( I had to use RH 6 distributive )
1. configure passwordless ssh to all nodes from root and from postgres user
2. configure passwordless sudo to allow this commands:
add to /etc/sudoers

postgres ALL=(root) NOPASSWD: /sbin/ip
postgres ALL=(root) NOPASSWD: /sbin/arping
postgres ALL=(root) NOPASSWD: /sbin/ifconfig
postgres ALL=(root) NOPASSWD: /etc/init.d/postgresql-9.5

To allow passwordless pcp commands create this file in postgres and root users:

cat $HOME/.pcppass
*:*:pgpooladmin:q1
chmod 600 .pcppass

now lets configure pgpool

start with easiest part, configure pcp.conf
to do this run pg_md5 command with password as first argument:

[postgres@pg1 ~]$ pg_md5 q1
ff33f1b12213e021c2c4a888141953ba

result add to pcp.conf with format of username:pg_md5_pass

echo "pgpooladmin:ff33f1b12213e021c2c4a888141953ba" >> pcp.conf

I’m allow connect to pgpool from whole network, to archive this I’ve add this line to pool_hba.conf

host	all		all	10.10.10.0/24	md5

next session is to configure pgpool.conf
I’ve made it from pgpool.conf.sample-stream

diff pgpool.conf.sample-stream pgpool.conf

27c27,28
< listen_addresses = 'localhost' --- > listen_addresses = ‘*’
>
65c66
< backend_hostname0 = 'host1' --- > backend_hostname0 = ‘pg1’
71c72
< backend_data_directory0 = '/data' --- > backend_data_directory0 = ‘/u01/postgres/9.5/main/pgdata’
76,80c77,89
< #backend_hostname1 = 'host2' < #backend_port1 = 5433 < #backend_weight1 = 1 < #backend_data_directory1 = '/data1' < #backend_flag1 = 'ALLOW_TO_FAILOVER' --- > backend_hostname1 = ‘pg2’
> backend_port1 = 5432
> backend_weight1 = 1
> backend_data_directory1 = ‘/u01/postgres/9.5/main/pgdata’
> backend_flag1 = ‘ALLOW_TO_FAILOVER’
>
> backend_hostname2 = ‘pg3’
> backend_port2 = 5432
> backend_weight2 = 1
> backend_data_directory2 = ‘/u01/postgres/9.5/main/pgdata’
> backend_flag2 = ‘ALLOW_TO_FAILOVER’
>
>
84c93
< enable_pool_hba = off --- > enable_pool_hba = on
120c129
< num_init_children = 32 --- > num_init_children = 300
123c132
< max_pool = 4 --- > max_pool = 1
218c227
< pid_file_name = '/var/run/pgpool/pgpool.pid' --- > pid_file_name = ‘/var/run/pgpool-II-95/pgpool.pid’
333c342
< sr_check_user = 'nobody' --- > sr_check_user = ‘repmgr’
337c346
< sr_check_password = '' --- > sr_check_password = ‘q1’
348c357
< follow_master_command = '' --- > follow_master_command = ‘/u01/postgres/9.5/main/conf/follow_master.sh %d %H %m %P %h’
367c376
< health_check_period = 0 --- > health_check_period = 5
373c382
< health_check_user = 'nobody' --- > health_check_user = ‘repmgr’
375c384
< health_check_password = '' --- > health_check_password = ‘q1’
377c386
< health_check_database = '' --- > health_check_database = ‘postgres’
379c388
< health_check_max_retries = 0 --- > health_check_max_retries = 5
381c390
< health_check_retry_delay = 1 --- > health_check_retry_delay = 5
383c392
< connect_timeout = 10000 --- > connect_timeout = 30000
394c403
< failover_command = '' --- > failover_command = ‘/u01/postgres/9.5/main/conf/failover.sh %d %H %m %P %h’
408c417,418
< failback_command = '' --- > failback_command = ‘/u01/postgres/9.5/main/conf/stb_recreate.sh %d %H %m %P %h’
> #failback_command = »
439c449
< recovery_user = 'nobody' --- > recovery_user = ‘repmgr’
441c451
< recovery_password = '' --- > recovery_password = ‘q1’
465c475
< use_watchdog = off --- > use_watchdog = on
471c481
< trusted_servers = '' --- > trusted_servers = ‘app01,app02’
482c492
< wd_hostname = '' --- > wd_hostname = ‘pg1’
505c515
< delegate_IP = '' --- > delegate_IP = ‘10.10.10.20’
509c519
< if_cmd_path = '/sbin' --- > if_cmd_path = ‘/usr/bin’
512c522
< if_up_cmd = 'ip addr add $_IP_$/24 dev eth0 label eth0:0' --- > if_up_cmd = ‘sudo ip addr add $_IP_$/24 dev eth1 label eth1:1’
515c525
< if_down_cmd = 'ip addr del $_IP_$/24 dev eth0' --- > if_down_cmd = ‘sudo ip addr del $_IP_$/24 dev eth1:1’
518c528
< arping_path = '/usr/sbin' --- > arping_path = ‘/usr/bin’
521c531
< arping_cmd = 'arping -U $_IP_$ -w 1' --- > arping_cmd = ‘sudo arping -U $_IP_$ -w 1’
569c579
< heartbeat_destination0 = 'host0_ip1' --- > heartbeat_destination0 = ‘pg2’
586,588c596,598
< #heartbeat_destination1 = 'host0_ip2' < #heartbeat_destination_port1 = 9694 < #heartbeat_device1 = '' --- > heartbeat_destination1 = ‘pg3’
> heartbeat_destination_port1 = 9694
> heartbeat_device1 = »
601c611
< wd_lifecheck_user = 'nobody' --- > wd_lifecheck_user = ‘pgpool’
604c614
< wd_lifecheck_password = '' --- > wd_lifecheck_password = ‘w7DeMth2oid3oYyE’
610c620
< #other_pgpool_hostname0 = 'host0' --- > other_pgpool_hostname0 = ‘pg2’
613c623
< #other_pgpool_port0 = 5432 --- > other_pgpool_port0 = 9999
616c626
< #other_wd_port0 = 9000 --- > other_wd_port0 = 9000
619,621c629,631
< #other_pgpool_hostname1 = 'host1' < #other_pgpool_port1 = 5432 < #other_wd_port1 = 9000 --- > other_pgpool_hostname1 = ‘p01db03’
> other_pgpool_port1 = 9999
> other_wd_port1 = 9000

[свернуть]

in my configuration I use trusted servers ( app servers ) you may leave it blank, also there was 3 node config ( for quorum) but in my test stand I use only two stands for one datacenter.

Key thig here is to keep first part of this file the same on all host ( where backend_hostname defenition is described )

copy config to second node:

scp  /etc/pgpool-II-95/* pg2:/etc/pgpool-II-95/

modify:
wd_hostname to second node
heartbeat_destination0 to first node
other_pgpool_hostname0 to first node

if you have any problems start pgpool in foreground mode ( also can be usefull to enable debug )
pgpool -f /etc/pgpool-II-95/pgpool.conf -n -d

usefull commands:
check node status
I use connect to VIP ip and port 9999

psql -U repmgr -d postgres -p 9999 -h 10.10.10.20 -c "show pool_nodes"
 node_id | hostname | port | status | lb_weight |  role   | select_cnt
---------+----------+------+--------+-----------+---------+------------
 0       | pg1      | 5432 | 2      | 0.500000  | primary | 0
 1       | pg2      | 5432 | 2      | 0.500000  | standby | 0 

scripts ( check that they are executable or add chmod +x /u01/postgres/9.5/main/conf/*.sh )

follow_master.sh

#!/bin/sh
failed_node=$1
new_master=$2
new_master_id=$3
old_primary=$4
cur_hostname=$5    #%h = host name
(
date
echo "Failed node: $failed_node"
echo "New_master: $new_master"
echo "New_master_id: $new_master_id"
echo "Old_primary: $old_primary"
echo "cur_hostname: $cur_hostname"




set -x
if [ $new_master_id -eq -1 ]; then  echo "smth wrong" ; exit 1; fi


if [ $UID -eq 0 ]
	then  su postgres -c "/usr/pgsql-9.5/bin/repmgr -f /u01/postgres/9.5/main/conf/repmgr.conf -D $PGDATA -h $new_master -U repmgr -d postgres standby follow"
 	else  /usr/pgsql-9.5/bin/repmgr -f /u01/postgres/9.5/main/conf/repmgr.conf -D $PGDATA -h $new_master -U repmgr -d postgres standby follow
fi
exit 0;
) 2>&1 | tee -a /tmp/pgpool_failover.log

[свернуть]

failover.sh

#!/bin/sh
failed_node=$1
new_master=$2
new_master_id=$3
old_primary=$4
cur_hostname=$5    #%h = host name
(
date
echo "Failed node: $failed_node"
echo "New_master: $new_master"
echo "New_master_id: $new_master_id"
echo "Old_primary: $old_primary"
echo "cur_hostname: $cur_hostname"




set -x
if [ $new_master_id -eq -1 ]; then  echo "smth wrong" ; exit 1; fi


if [ $UID -eq 0 ]
	then  su postgres -c "/usr/bin/ssh -T -l postgres $new_master \"/usr/pgsql-9.5/bin/repmgr -f /u01/postgres/9.5/main/conf/repmgr.conf standby promote 2>/dev/null 1>/dev/null <&-\" "
 	else /usr/bin/ssh $new_master "/usr/pgsql-9.5/bin/repmgr -f /u01/postgres/9.5/main/conf/repmgr.conf standby promote 2>/dev/null 1>/dev/null <&-"
fi
sleep 5
if [ $UID -eq 0 ]
 	then su postgres -c  "/usr/bin/ssh -T -l postgres $new_master \"pcp_attach_node -p 9898 -U pgpooladmin -w -n $new_master_id\""
	else /usr/bin/ssh -T -l postgres $new_master "/usr/bin/ssh -T -l postgres $new_master \"pcp_attach_node -p 9898 -U pgpooladmin -w -n $new_master_id\""
fi
exit 0;
) 2>&1 | tee -a /tmp/pgpool_failover.log

[свернуть]
stb_recreate.sh

#!/bin/sh
failed_node=$1
new_master=$2
new_master_id=$3
old_primary=$4
cur_hostname=$5    #%h = host name
repmgr_conf=/u01/postgres/9.5/main/conf/repmgr.conf
(
date
echo "Failed node: $failed_node"
echo "New_master: $new_master"
echo "New_master_id: $new_master_id"
echo "Old_primary: $old_primary"
echo "cur_hostname: $cur_hostname"




set -x
if [ $new_master_id -eq -1 ]; then  echo "smth wrong" ; exit 1; fi


if [ $UID -eq 0 ]
	then  su postgres -c "
/usr/pgsql-9.5/bin/repmgr -c -h $master_node -d postgres -U repmgr -D $PGDATA -f $repmgr_conf standby clone
sudo /etc/init.d/postgresql-9.5 start
/usr/pgsql-9.5/bin/repmgr -f $repmgr_conf standby register -F
sleep 15
pcp_attach_node -h localhost -U pgpooladmin -w -n 1
"
 	else
	/usr/pgsql-9.5/bin/pg_ctl -m fast stop
	mv $PGDATA "$(echo $PGDATA)_$(echo `date +%H:%M_%d%m%Y`)"
	/usr/pgsql-9.5/bin/repmgr -c -h $new_master -d postgres -U repmgr -D $PGDATA -f $repmgr_conf standby clone
	sudo /etc/init.d/postgresql-9.5 start
	/usr/pgsql-9.5/bin/repmgr -f $repmgr_conf standby register -F
	sleep 15
	pcp_attach_node -h localhost -U pgpooladmin -w -n 1

fi
exit 0;
) 2>&1 | tee -a /tmp/pgpool_recreate.log

[свернуть]

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Primary Sidebar

Яндекс.Метрика