当前访客身份：游客 [
吾乃PG一铁匠。
：明白了~~~~~~~
：引用来自“francs”的评论恭喜你又上首页了！太不...
：恭喜你又上首页了！
：恭喜你又上首页了！
：引用来自“即行”的评论java ？？？
：可以用py写插件啊
今日访问：23
昨日访问：17
本周访问：40
本月访问：296
所有访问：13903
利用pgpool实现PostgreSQL的高可用
发表于3个月前( 13:40)&&
阅读（2408）&|&评论（）
0人收藏此文章,
这里利用pgpool-ii实现PG的高可用。
基于流复制的方式，两节点自动切换：
&&&&1、单pgpool
&&&&& & a.环境：
pgpool：192.168.238.129
data1：192.168.238.130
data2：192.168.238.131
&&&&& & b.图例
&&&&& & c.配置互信
ssh-copy-id ha@node1
ssh-copy-id ha@node2
& & & & d.数据库节点配置，请参照《
&&&&& & e.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
# Create the trigger file.
/usr/bin/ssh -T $new_master $trigger_command
&&&&& & g.建立pool_passwd
pg_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 &
[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 *
(6 rows) &&&&& & &node1节点：
[ha@localhost pgdb]$ ps -ef | grep post
00:00:00 /usr/libexec/postfix/master
00:00:00 qmgr -l -t fifo -u
00:00:00 pickup -l -t fifo -u
0 06:06 pts/3
00:00:00 /home/ha/pgdb/bin/postgres
00:00:00 postgres: checkpointer process
00:00:00 postgres: writer process
00:00:00 postgres: wal writer process
00:00:00 postgres: autovacuum launcher process
00:00:00 postgres: stats collector process
00:00:00 postgres: wal sender process rep 192.168.238.131(59415) streaming 0/
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 *
& & 2.两个pgpool节点
&&&&& & a.环境
pgpool:192.168.238.129
pgpool:192.168.238.131
node1:192.168.238.130
node2:192.168.238.131
&&&&& & b.图例
& & & & c.配置互信，同上。 &&&&& & d.数据库节点配置，同上。 &&&&& & e.pgpool配置 &&&&&&&&& & node1
&&&&& & & f.配置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 = 9000
&&&&&&&&g.配置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 = 9000
&&&&& & h.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
# Create the trigger file.
/usr/bin/ssh -T $new_master $trigger_command
& & & &i.建立pool_passwd
pg_md5 -m -p -u postgres pool_passwd
&&&&& & j.测试
#数据库、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=#
更多开发者职位上
1)">1)">1" ng-class="{current:{{currentPage==page}}}" ng-repeat="page in pages"><li class='page' ng-if="(endIndex<li class='page next' ng-if="(currentPage
相关文章阅读利用pgpool实现PostgreSQL的高可用_数据库技术_Linux公社-Linux系统门户网站
你好，游客
利用pgpool实现PostgreSQL的高可用
来源：oschina.net&
作者：Suregogo
这里利用pgpool-ii实现PG的高可用。基于流复制的方式，两节点自动切换：
1、单pgpool
pgpool：192.168.238.129data1：192.168.238.130data2：192.168.238.131
c.配置互信
ssh-copy-id ha@node1ssh-copy-id ha@node2&d.数据库节点配置，请参照《
e.pgpool配置：
listen_addresses = '*'backend_hostname0 = 'node1'backend_port0 = 5432backend_weight0 = 1backend_data_directory0 = '/home/ha/pgdb/data'backend_flag0 = 'ALLOW_TO_FAILOVER'&backend_hostname1 = 'node2'backend_port1 = 5432backend_weight1 = 1backend_data_directory1 = '/home/ha/pgdb/data'backend_flag1 = 'ALLOW_TO_FAILOVER'&enable_pool_hba = onpool_passwd = 'pool_passwd'&pid_file_name = '/home/ha/pgpool/pgpool.pid'logdir = '/home/ha/pgpool/log'&health_check_period = 1health_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=$1trigger_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_passwd
pg_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 haPassword for user ha: psql (9.4.5)Type "help" for help.&postgres=# insert into test values (8);INSERT 0 1postgres=# select *&id ----& 1& 2& 3& 4& 6& 8(6 rows)&node1节点：
[ha@localhost pgdb]$ ps -ef | grep postroot& & &
2124& & & 1& 0 Dec26 ?& & & & 00:00:00 /usr/libexec/postfix/masterpostfix& & 2147&
2124& 0 Dec26 ?& & & & 00:00:00 qmgr -l -t fifo -upostfix&
:01 ?& & & & 00:00:00 pickup -l -t fifo -uha& & & & 13395& & & 1& 0 06:06 pts/3& & 00:00:00 /home/ha/pgdb/bin/postgresha& & & & 1& 0 06:06 ?& & & & 00:00:00 postgres: checkpointer process&
ha& & & & 1& 0 06:06 ?& & & & 00:00:00 postgres: writer process&
ha& & & & 1& 0 06:06 ?& & & & 00:00:00 postgres: wal writer process&
ha& & & & 1& 0 06:06 ?& & & & 00:00:00 postgres: autovacuum launcher process&
ha& & & & 1& 0 06:06 ?& & & & 00:00:00 postgres: stats collector process&
ha& & & & 1& 0 06:07 ?& & & & 00:00:00 postgres: wal sender process rep 192.168.238.131(59415) streaming 0/ha& & & & 13418&
: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 1postgres=# insert into test values (8);INSERT 0 1postgres=# select *&id ----& 1& 2& 3& 4& 6& 8& 8& 8(8 rows)
2.两个pgpool节点
pgpool:192.168.238.129 &span&&/span& pgpool:192.168.238.131node1:192.168.238.130node2:192.168.238.131
c.配置互信，同上。d.数据库节点配置，同上。e.pgpool配置node1
f.配置pgpool（主）
listen_addresses = '*'backend_hostname0 = 'node1'backend_port0 = 5432backend_weight0 = 1backend_data_directory0 = '/home/ha/pgdb/data/'backend_flag0 = 'ALLOW_TO_FAILOVER'backend_hostname1 = 'node2'backend_port1 = 5432backend_weight1 = 1backend_data_directory1 = '/home/ha/pgdb/data/'backend_flag1 = 'ALLOW_TO_FAILOVER'enable_pool_hba = onauthenticationpool_passwd = 'pool_passwd'pid_file_name = '/home/ha/pgpool/pgpool.pid'logdir = '/tmp/log'master_slave_mode = onmaster_slave_sub_mode = 'stream'sr_check_period =2sr_check_user = 'ha'sr_check_password = 'ha'health_check_period = 1health_check_timeout = 20health_check_user = 'ha'health_check_password = 'ha'failover_command = '/home/ha/pgpool/fail.sh %H'recovery_user = 'ha'recovery_password = 'ha'use_watchdog = onwd_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 =9999other_wd_port0 = 9000
g.配置pgpool（从）&listen_addresses = '*'backend_hostname0 = 'node1'backend_port0 = 5432backend_weight0 = 1backend_data_directory0 = '/home/ha/pgdb/data/'backend_flag0 = 'ALLOW_TO_FAILOVER'backend_hostname1 = 'node2'backend_port1 = 5432backend_weight1 = 1backend_data_directory1 = '/home/ha/pgdb/data/'backend_flag1 = 'ALLOW_TO_FAILOVER'enable_pool_hba = onauthenticationpool_passwd = 'pool_passwd'pid_file_name = '/home/ha/pgpool/pgpool.pid'logdir = '/tmp/log'master_slave_mode = onmaster_slave_sub_mode = 'stream'sr_check_period =2sr_check_user = 'ha'sr_check_password = 'ha'health_check_period = 1health_check_timeout = 20health_check_user = 'ha'health_check_password = 'ha'failover_command = '/home/ha/pgpool/fail.sh %H'recovery_user = 'ha'recovery_password = 'ha'use_watchdog = onwd_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 =9999other_wd_port0 = 9000
h.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=$1trigger_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_passwd
pg_md5 -m -p -u postgres pool_passwd
j.测试&#数据库、pgpool启动[ha@node0 pgdb]$ psql -h 192.168.238.151 -p 9999 -d postgres -U haPassword for user ha: psql (9.4.5)Type "help" for help.&postgres=# insert into test values (9);INSERT 0 1postgres=# insert into test values (9);INSERT 0 1postgres=# --杀掉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 1postgres=# insert into test values (9);INSERT 0 1postgres=# insert into test values (9);INSERT 0 1postgres=# insert into test values (9);INSERT 0 1postgres=# insert into test values (9);INSERT 0 1postgres=# insert into test values (9);INSERT 0 1postgres=# insert into test values (9);INSERT 0 1postgres=# 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 1postgres=# insert into test values (9);INSERT 0 1postgres=#
------------------------------------华丽丽的分割线------------------------------------
Server 14.04 下安装 PostgreSQL 9.3.5 数据库&
6.3环境下yum安装PostgreSQL 9.3
PostgreSQL缓存详述
Windows平台编译 PostgreSQL
Ubuntu下LAPP（Linux+Apache+PostgreSQL+PHP)环境的配置与安装
Ubuntu上的phppgAdmin安装及配置
CentOS平台下安装PostgreSQL9.3
PostgreSQL配置Streaming Replication集群
如何在CentOS 7/6.5/6.4 下安装PostgreSQL 9.3 与 phpPgAdmin&
------------------------------------华丽丽的分割线------------------------------------
PostgreSQL 的详细介绍：PostgreSQL 的下载地址：
本文永久更新链接地址：
相关资讯 & & &
& (01月19日)
& (12/21/:21)
& (02月12日)
& (12/30/:29)
& (12/20/:11)
图片资讯 & & &
　　　同意评论声明
　　　发表
尊重网上道德，遵守中华人民共和国的各项有关法律法规
承担一切因您的行为而直接或间接导致的民事或刑事法律责任
本站管理人员有权保留或删除其管辖留言中的任意内容
本站有权在网站内转载或引用您的评论
参与本评论即表明您已经阅读并接受上述条款使用pgpool搭建高可用PostgreSQL HA的步骤 - 推酷
使用pgpool搭建高可用PostgreSQL HA的步骤
pgpool，功能不较多，具有连接池，复制，负载均衡，并行查询，和HA等。今天博主测试下HA的配置。
环境信息：
两台虚拟机，ip分别由192.168.56.101和192.168.56.102 。
PostgreSQL版本：9.3.5
PGHOME：/opt/PostgreSQL/93
PGDATA：/data/pgsql
PGPORT：5432
实验架构：
只有一个pgpool，只测试PostgreSQL热备自动切换功能。
1、准备PostgreSQL环境
在操作之前，需要安装好PostgreSQL，然后配置流复制环境。
这个步骤不明白的朋友可以参考之前的文章。
编译安装PostgreSQL：http://www.sijitao.net/1535.html
PostgreSQL流复制搭建：http://www.sijitao.net/1764.html
2、安装pgpool
pgpool的下载地址：http://www.pgpool.net/download.php?f=pgpool-II-3.3.4.tar.gz 。
tar zxvf pgpool-II-3.3.4.tar.gz
cd pgpool-II-3.3.4
mkdir -p /opt/pgpool
./configure –prefix=/opt/pgpool -with-pgsql=path -with-pgsql=/opt/PostgreSQL/93
make install
3、安装pgpool相关函数
pgpool 函数不是必需安装，但建议安装 pgpool_regclass, pgpool_recovery 函数。
cd /root/pgpool-II-3.3.4/sql
make install
安装完成后可以在/opt/PostgreSQL/93/share/postgresql/extension/看到pgpool相关文件。
root@ubuntu:~/pgpool-II-3.3.4/sql# ls -l /opt/PostgreSQL/93/share/postgresql/extension/
-rw-r–r– 1 root root 791 Nov 11 11:44 pgpool_recovery–1.0.sql
-rw-r–r– 1 root root 160 Nov 11 11:44 pgpool_recovery.control
-rw-r–r– 1 root root 551 Nov 11 11:44 pgpool-recovery.sql
-rw-r–r– 1 root root 283 Nov 11 11:44 pgpool_regclass–1.0.sql
-rw-r–r– 1 root root 152 Nov 11 11:44 pgpool_regclass.control
-rw-r–r– 1 root root 142 Nov 11 11:44 pgpool-regclass.sql
-rw-r–r– 1 root root 332 Aug 22 11:52 plpgsql–1.0.sql
-rw-r–r– 1 root root 179 Aug 22 11:52 plpgsql.control
-rw-r–r– 1 root root 381 Aug 22 11:52 plpgsql–unpackaged–1.0.sql
登陆需要安装的库中，和安装插件一样执行以下两条命令。
create extension pgpool_create extension pgpool_
4、配置两台数据库之间的信任关系
下面这些在两台机器上分别执行，互相登陆不需要密码。
postgres@ubuntu:~$ ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/home/postgres/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/postgres/.ssh/id_rsa.
Your public key has been saved in /home/postgres/.ssh/id_rsa.pub.
The key fingerprint is:
3d:47:a9:3e:9e:36:5b:ef:2e:0e:21:66:0b:12:76:cd postgres@ubuntu
The key’s randomart image is:
+–[ RSA 2048]—-+
|&&&&&&&&&&&&&&&& |
|&&&&&& o&&&& .&& |
|&&& o . E&& o&&& |
|&& . o&& . o&&&& |
|&&& . . S = .&&& |
|&&&& . + + +&&&& |
|&&&&&&& . + .&&& |
|&&&&&&&& .o=..&& |
|&&&&&&&& .++.++& |
+—————–+
postgres@ubuntu:~$ ssh-copy-id postgres@192.168.56.102
The authenticity of host ’192.168.56.102 (192.168.56.102)’ can’t be established.
ECDSA key fingerprint is 8c:f2:97:55:b4:1f:e4:81:1d:55:fd:42:77:3e:c7:c2.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ’192.168.56.102′ (ECDSA) to the list of known hosts.
postgres@192.168.56.102′s password:
Now try logging into the machine, with “ssh ‘postgres@192.168.56.102′”, and check in:
~/.ssh/authorized_keys
to make sure we haven’t added extra keys that you weren’t expecting.
5、配置pgpool
root@ubuntu:/opt/pgpool# chown -R postgres:postgres etc/
root@ubuntu:/opt/pgpool# su – postgres
postgres@ubuntu:~$ echo “export PATH=\”\$PATH:/opt/pgpool/bin\”& &&/home/postgres/.bashrc
postgres@ubuntu:~$ source /home/postgres/.bashrc
postgres@ubuntu:~$ cd /opt/pgpool/etc/
a.配置& pcp.conf
pgpool 提供 pcp 接口，可以查看、管理 pgpool 的状态，并且可以远程操作 pgpool 。pcp.conf是用来对 pcp 相关命令认证的文件，格式为& USERID:MD5PASSWD。
postgres@ubuntu:/opt/pgpool/etc$ cp pcp.conf.sample pcp.conf
postgres@ubuntu:/opt/pgpool/etc$ pg_md5 -u pgpool -p
e6a52c828d56b46129fbf85c4cd164b3
在pcp.conf文件中末尾添加pgpool:e6a52c828d56b46129fbf85c4cd164b3 。
b.配置 pgpool.conf
listen_addresses = ‘*’
port = 9999
socket_dir = ‘/tmp’
pcp_port = 9898
pcp_socket_dir = ‘/tmp’
backend_hostname0 = ’192.168.56.101′&&&&&&& //配置数据节点192.168.56.101
backend_port0 = 5432
backend_weight0 = 1&&&&&&& //负载均衡中的权重值
backend_flag0 = ‘ALLOW_TO_FAILOVER’&&&&&&& //允许自动切换
backend_hostname1 = ’192.168.56.102′&&&&&&& //配置数据节点192.168.56.102
backend_port1 = 5432
backend_weight1 = 1
backend_flag1 = ‘ALLOW_TO_FAILOVER’
enable_pool_hba = on&&&&&&& //打开pool_passwd
pool_passwd = ‘pool_passwd’
authentication_timeout = 60
num_init_children = 32
max_pool = 4
child_life_time = 300
child_max_connections = 0
connection_life_time = 0
client_idle_limit = 0
log_destination = ‘stderr’
print_timestamp = on
log_connections = on
log_hostname = on
log_statement = on
log_per_node_statement = off
log_standby_delay = ‘none’
syslog_facility = ‘LOCAL0′
syslog_ident = ‘pgpool’
debug_level = 0
pid_file_name = ‘/var/run/pgpool/pgpool.pid’
logdir = ‘/tmp’
connection_cache = on
reset_query_list = ‘ABORT; DISCARD ALL’
replication_mode = off
replicate_select = off
insert_lock = on
lobj_lock_table = ”
replication_stop_on_mismatch = off
failover_if_affected_tuples_mismatch = off
load_balance_mode = on
//负载均衡开关
ignore_leading_white_space = on
white_function_list = ”
black_function_list = ‘nextval,setval’
master_slave_mode = o
n&&&&&&& //主备模式开关
master_slave_sub_mode = ‘stream’
//设置流复制模式
sr_check_period = 5
sr_check_user = ‘repuser’
sr_check_password = ’123456′
delay_threshold = 0
follow_master_command = ”
parallel_mode = off
pgpool2_hostname = ”
system_db_hostname& = ‘localhost’
system_db_port = 5432
system_db_dbname = ‘pgpool’
system_db_schema = ‘pgpool_catalog’
system_db_user = ‘pgpool’
system_db_password = ”
health_check_period = 0
health_check_timeout = 20
health_check_user = ‘nobody’
health_check_password = ”
health_check_max_retries = 0
health_check_retry_delay = 1
failover_command = ‘/home/postgres/scripts/failover_stream.sh %d %H /data/pgsql/postgresql.trigger.5432′
failback_command = ”
fail_over_on_backend_error = on
search_primary_node_timeout = 10
recovery_user = ‘nobody’
recovery_password = ”
recovery_1st_stage_command = ”
recovery_2nd_stage_command = ”
recovery_timeout = 90
client_idle_limit_in_recovery = 0
use_watchdog = off
trusted_servers = ”
ping_path = ‘/bin’
wd_hostname = ”
wd_port = 9000
wd_authkey = ”
delegate_IP = ”
ifconfig_path = ‘/sbin’
if_up_cmd = ‘ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0′
if_down_cmd = ‘ifconfig eth0:0 down’
arping_path = ‘/usr/sbin’&&&&&&&&&& # arping command path
arping_cmd = ‘arping -U $_IP_$ -w 1′
clear_memqcache_on_escalation = on
wd_escalation_command = ”
wd_lifecheck_method = ‘heartbeat’
wd_interval = 10
wd_heartbeat_port = 9694
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
heartbeat_destination0 = ‘host0_ip1′
heartbeat_destination_port0 = 9694
heartbeat_device0 = ”
wd_life_point = 3
wd_lifecheck_query = ‘SELECT 1′
wd_lifecheck_dbname = ‘template1′
wd_lifecheck_user = ‘nobody’
wd_lifecheck_password = ”
relcache_expire = 0
relcache_size = 256
check_temp_table = on
memory_cache_enabled = off
memqcache_method = ‘shmem’
memqcache_memcached_host = ‘localhost’
memqcache_memcached_port = 11211
memqcache_total_size =
memqcache_max_num_cache = 1000000
memqcache_expire = 0
memqcache_auto_cache_invalidation = on
memqcache_maxcache = 409600
memqcache_cache_block_size = 1048576
memqcache_oiddir = ‘/var/log/pgpool/oiddir’
white_memqcache_table_list = ”
black_memqcache_table_list = ”
c.failover脚本
/home/postgres/scripts/failover_stream.sh
#! /bin/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.
failed_node=$1
new_master=$2
trigger_file=$3
# Do nothing if standby goes down.
if [ $failed_node = 1 ]; then
# Create the trigger file./usr/bin/ssh -T $new_master /bin/touch $trigger_file
d.配置pool_hba.conf
postgres@ubuntu:/opt/pgpool/etc$ cp pgpool.conf.sample pgpool.conf
这里我把认证权限都设置成md5，和PostgreSQL中的pg_hba.conf一样。
e.配置pool_passwd
配置哪些用户可以通过pgpool登陆数据库，格式user:md5 。
例如：postgres:md5cdf7c8e298f988acbc8d7b6b16cdd5f8 。
6、启动pgpool
启动之前创建两个目录。
mkdir /var/log/pgpool
chown postgres.postgres /var/log/pgpool
mkdir /var/run/pgpool
chown postgres.postgres /var/run/pgpool
postgres@ubuntu:/opt/pgpool/etc$ pgpool -n &/var/log/pgpool/pgpool.log 2&&1 &
7、登陆pgpool、测试HA
postgres@ubuntu:/opt/pgpool/etc$ psql -p 9999 -U postgres -d postgres
psql (9.3.5)
Type “help” for help.
postgres=# show pool_
node_id |&&& hostname&&& | port | status | lb_weight |& role
———+—————-+——+——–+———–+———
0&&&&&& | 192.168.56.101 | 5432 | 2&&&&& | 0.500000& | primary
1&&&&&& | 192.168.56.102 | 5432 | 2&&&&& | 0.500000& | standby
热备和负载均衡这样就配置好了，接下来手动关掉主库。
再次登陆pgpool，查看node状态，可以发现192.168.56.102已经自动切换成primary。
#192.168.56.101
postgres@ubuntu:/opt/pgpool/etc$ psql -p 9999 -U postgres -d postgres
psql (9.3.5)
Type “help” for help.
postgres=# show pool_
node_id |&&& hostname&&& | port | status | lb_weight |& role
———+—————-+——+——–+———–+———
0&&&&&& | 192.168.56.101 | 5432 | 3&&&&& | 0.500000& | standby
1&&&&&& | 192.168.56.102 | 5432 | 2&&&&& | 0.500000& | primary
#192.168.56.102
postgres@ubuntu:/data/pgsql$ ls -l recovery.done-rw-r–r– 1 postgres postgres 4938 Nov 11 11:07 recovery.done
热备自动切换成功。
8、其他问题
如果192.168.56.101重新做了备库后再启动，输入以下命令，status状态变成2 。
/opt/pgpool/bin/pcp_attach_node -d 5 localhost 9898 postgres
postgres和密码123456由之前pcp.conf中设置所得。
常见连接异常问题
pg_hba.conf
pool_hba.conf
pool_passwd
“MD5″ authentication with pgpool failed for user “XX”
MD5 authentication is unsupported in replication, master-slave and parallel mode
“MD5″ authentication with pgpool failed for user “XX”
备注： 这个表格很好地描述了各种连接异常问题，对照表格很容易找到原因。
使用pgpool可以实现主备之间的自动切换，而且也可以有效实现负载均衡功能，个人觉得可以在生产中进行试用。
参考链接：
http://francs3./blog/static//
http://www.pgpool.net/docs/latest/pgpool-en.html
已发表评论数()
已收藏到推刊！
请填写推刊名
描述不能大于100个字符!
权限设置： 公开
仅自己可见
正文不准确
排版有问题
没有分页内容
视频无法显示
图片无法显示