利用pgpool实现PostgreSQL的高可用

这里利用pgpool-ii实现PG的高可用。基于流复制的方式，两节点自动切换：1、单pgpoola.环境：pgpool：192.168.238.129
data1：192.168.238.130
data2：192.168.238.131b.图例c.配置互信ssh-copy-id ha@node1
ssh-copy-id ha@node2

d.数据库节点配置，请参照《使用pg_basebackup搭建PostgreSQL流复制环境》。http://www.linuxidc.com/Linux/2015-12/126895.htme.pgpool配置：listen_addresses = "*"
backend_hostname0 = "node1"
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = "/home/ha/pgdb/data"
backend_flag0 = "ALLOW_TO_FAILOVER"

backend_hostname1 = "node2"
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = "/home/ha/pgdb/data"
backend_flag1 = "ALLOW_TO_FAILOVER"

enable_pool_hba = on
pool_passwd = "pool_passwd"

pid_file_name = "/home/ha/pgpool/pgpool.pid"
logdir = "/home/ha/pgpool/log"

health_check_period = 1
health_check_user = "ha"
health_check_password = "ha"

failover_command = "/home/ha/pgdb/fail.sh %H"

recovery_user = "ha"
recovery_password = "ha"

f.fail.sh# Failover command for streaming replication.
# This script assumes that DB node 0 is primary, and 1 is standby.
#
# If standby goes down, do nothing. If primary goes down, create a
# trigger file so that standby takes over primary node.
#
# Arguments: $1: failed node id. $2: new master hostname. $3: path to
# trigger file.

new_master=$1
trigger_command="/home/ha/pgdb/bin/pg_ctl -D /home/ha/pgdb/data promote -m fast"

# Do nothing if standby goes down.
if [ $failed_node = 1 ]; then
exit 0;
fi

# Create the trigger file.
/usr/bin/ssh -T $new_master $trigger_command

exit 0;
g.建立pool_passwdpg_md5 -m -p -u postgres pool_passwd

PS：在9.1之前一直用的是trigger_file，这里建议用promote -m fast的方式，因为
“ pg_ctl promote -m fast will skip the checkpoint at end of recovery so that we can achieve very fast failover when the apply delay is low. Write new WAL record XLOG_END_OF_RECOVERY to allow us to switch timeline correctly for downstream log readers. If we skip synchronous end of recovery checkpoint we request a normal spread checkpoint so that the window of re-recovery is low. Simon Riggs and Kyotaro Horiguchi, with input from Fujii Masao. Review by Heikki Linnakangas ”h.测试
pgpool节点[ha@node0 pgdb]$ pgpool -n -d > /tmp/pgpool.log 2>&1 &
[1] 22928
[ha@node0 pgdb]$ psql -h 192.168.238.129 -p 9999 -d postgres -U ha
Password for user ha:
psql （9.4.5）
Type "help" for help.

postgres=# insert into test values （8）;
INSERT 0 1
postgres=# select * from test ;
id
----
1
2
3
4
6
8
（6 rows）

node1节点：[ha@localhost pgdb]$ ps -ef | grep post
root 2124 1 0 Dec26 ？ 00:00:00 /usr/libexec/postfix/master
postfix 2147 2124 0 Dec26 ？ 00:00:00 qmgr -l -t fifo -u
postfix 13295 2124 0 06:01 ？ 00:00:00 pickup -l -t fifo -u
ha 13395 1 0 06:06 pts/3 00:00:00 /home/ha/pgdb/bin/postgres
ha 13397 13395 0 06:06 ？ 00:00:00 postgres: checkpointer process
ha 13398 13395 0 06:06 ？ 00:00:00 postgres: writer process
ha 13399 13395 0 06:06 ？ 00:00:00 postgres: wal writer process
ha 13400 13395 0 06:06 ？ 00:00:00 postgres: autovacuum launcher process
ha 13401 13395 0 06:06 ？ 00:00:00 postgres: stats collector process
ha 13404 13395 0 06:07 ？ 00:00:00 postgres: wal sender process rep 192.168.238.131（59415） streaming 0/21000060
ha 13418 4087 0 06:07 pts/3 00:00:00 grep post
[ha@localhost pgdb]$ kill -9 13395

pgpool节点：postgres=# insert into test values （8）;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
postgres=# insert into test values （8）;
INSERT 0 1
postgres=# insert into test values （8）;
INSERT 0 1
postgres=# select * from test ;
id
----
1
2
3
4
6
8
8
8
（8 rows）2.两个pgpool节点a.环境pgpool:192.168.238.129 <span></span> pgpool:192.168.238.131
node1:192.168.238.130
node2:192.168.238.131b.图例c.配置互信，同上。
d.数据库节点配置，同上。
e.pgpool配置
node1f.配置pgpool（主）listen_addresses = "*"
backend_hostname0 = "node1"
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = "/home/ha/pgdb/data/"
backend_flag0 = "ALLOW_TO_FAILOVER"
backend_hostname1 = "node2"
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = "/home/ha/pgdb/data/"
backend_flag1 = "ALLOW_TO_FAILOVER"
enable_pool_hba = on
authentication
pool_passwd = "pool_passwd"
pid_file_name = "/home/ha/pgpool/pgpool.pid"
logdir = "/tmp/log"
master_slave_mode = on
master_slave_sub_mode = "stream"
sr_check_period =2
sr_check_user = "ha"
sr_check_password = "ha"
health_check_period = 1
health_check_timeout = 20
health_check_user = "ha"
health_check_password = "ha"
failover_command = "/home/ha/pgpool/fail.sh %H"
recovery_user = "ha"
recovery_password = "ha"
use_watchdog = on
wd_hostname = "node1" #本端
delegate_IP = "192.168.238.151"
#利用ifconfig，查看网卡
if_up_cmd = "ifconfig eth1:0 inet $_IP_$ netmask 255.255.255.0"
if_down_cmd = "ifconfig eth1:0 down"
heartbeat_destination0 = "node2" #对端
heartbeat_device0 = "eth0"
other_pgpool_hostname0 = "node2" #对端
other_pgpool_port0 =9999
other_wd_port0 = 9000g.配置pgpool（从）

listen_addresses = "*"
backend_hostname0 = "node1"
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = "/home/ha/pgdb/data/"
backend_flag0 = "ALLOW_TO_FAILOVER"
backend_hostname1 = "node2"
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = "/home/ha/pgdb/data/"
backend_flag1 = "ALLOW_TO_FAILOVER"
enable_pool_hba = on
authentication
pool_passwd = "pool_passwd"
pid_file_name = "/home/ha/pgpool/pgpool.pid"
logdir = "/tmp/log"
master_slave_mode = on
master_slave_sub_mode = "stream"
sr_check_period =2
sr_check_user = "ha"
sr_check_password = "ha"
health_check_period = 1
health_check_timeout = 20
health_check_user = "ha"
health_check_password = "ha"
failover_command = "/home/ha/pgpool/fail.sh %H"
recovery_user = "ha"
recovery_password = "ha"
use_watchdog = on
wd_hostname = "node2" #本端
delegate_IP = "192.168.238.151"
#利用ifconfig，查看网卡
if_up_cmd = "ifconfig eth1:0 inet $_IP_$ netmask 255.255.255.0"
if_down_cmd = "ifconfig eth1:0 down"
heartbeat_destination0 = "node1" #对端
heartbeat_device0 = "eth1"
other_pgpool_hostname0 = "node1" #对端
other_pgpool_port0 =9999
other_wd_port0 = 9000h.fail.sh

# Failover command for streaming replication.
# This script assumes that DB node 0 is primary, and 1 is standby.
#
# If standby goes down, do nothing. If primary goes down, create a
# trigger file so that standby takes over primary node.
#
# Arguments: $1: failed node id. $2: new master hostname. $3: path to
# trigger file.

new_master=$1
trigger_command="/home/ha/pgdb/bin/pg_ctl -D /home/ha/data start"

# Do nothing if standby goes down.
if [ $failed_node = 1 ]; then
exit 0;
fi

# Create the trigger file.
/usr/bin/ssh -T $new_master $trigger_command

exit 0;

i.建立pool_passwdpg_md5 -m -p -u postgres pool_passwdj.测试

#数据库、pgpool启动
[ha@node0 pgdb]$ psql -h 192.168.238.151 -p 9999 -d postgres -U ha
Password for user ha:
psql （9.4.5）
Type "help" for help.

postgres=# insert into test values （9）;
INSERT 0 1
postgres=# insert into test values （9）;
INSERT 0 1
postgres=#
--杀掉node1的数据库进程
postgres=# insert into test values （9）;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
postgres=# insert into test values （9）;
INSERT 0 1
postgres=# insert into test values （9）;
INSERT 0 1
postgres=# insert into test values （9）;
INSERT 0 1
postgres=# insert into test values （9）;
INSERT 0 1
postgres=# insert into test values （9）;
INSERT 0 1
postgres=# insert into test values （9）;
INSERT 0 1
postgres=# insert into test values （9）;
INSERT 0 1
postgres=# insert into test values （9）;
INSERT 0 1
--杀掉node1的pgpool进程
postgres=# insert into test values （9）;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
postgres=# insert into test values （9）;
INSERT 0 1
postgres=# insert into test values （9）;
INSERT 0 1
postgres=#------------------------------------华丽丽的分割线------------------------------------Ubuntu Server 14.04 下安装 PostgreSQL 9.3.5 数据库 http://www.linuxidc.com/Linux/2015-12/126175.htmCentOS 6.3环境下yum安装PostgreSQL 9.3 http://www.linuxidc.com/Linux/2014-05/101787.htmPostgreSQL缓存详述 http://www.linuxidc.com/Linux/2013-07/87778.htmWindows平台编译 PostgreSQL http://www.linuxidc.com/Linux/2013-05/85114.htmUbuntu下LAPP（Linux+Apache+PostgreSQL+PHP）环境的配置与安装 http://www.linuxidc.com/Linux/2013-04/83564.htmUbuntu上的phppgAdmin安装及配置 http://www.linuxidc.com/Linux/2011-08/40520.htmCentOS平台下安装PostgreSQL9.3 http://www.linuxidc.com/Linux/2014-05/101723.htmPostgreSQL配置Streaming Replication集群 http://www.linuxidc.com/Linux/2014-05/101724.htm如何在CentOS 7/6.5/6.4 下安装PostgreSQL 9.3 与 phpPgAdmin http://www.linuxidc.com/Linux/2014-12/110108.htm------------------------------------华丽丽的分割线------------------------------------PostgreSQL 的详细介绍：请点这里
PostgreSQL 的下载地址：请点这里本文永久更新链接地址