文档章节

利用pgpool实现PostgreSQL的高可用

PGSmith
 PGSmith
发布于 2015/12/29 13:40
字数 1248
阅读 5183
收藏 97

基于流复制的方式,两节点自动切换:

    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.数据库节点配置,请参照《 使用pg_basebackup搭建PostgreSQL流复制环境 》。


        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
        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 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  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
        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 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=#




© 著作权归作者所有

共有 人打赏支持
PGSmith

PGSmith

粉丝 90
博文 55
码字总数 59804
作品 0
济南
后端工程师
加载中

评论(8)

Dishwashe
Dishwashe
mark
PGSmith
PGSmith

引用来自“francs”的评论

恭喜你又上首页了!
太不好意思了,需要多想您学习,我最近好多东西都参考了您的blog。
陶月和
陶月和
图图
francs
francs
恭喜你又上首页了!
YuanyuanL
YuanyuanL
恭喜你又上首页了!
PGSmith
PGSmith

引用来自“即行”的评论

java
???
即行
即行
java
即行
即行
java
【入门教程】PostgreSQL+SequoiaDB HA 实践

前言 SequoiaDB作为分布式数据库,从设计之初就已经支持SQL访问。目前,SequoiaDB自研的SQL访问组件SequoiaSQL作为企业版的功能之一已经提供给上百家企业用户使用,并且已经实现分布式架构下...

巨杉数据库
2017/10/26
0
0
用Pgpool-II实现Postgresql高可用集群

其实整个安装和配置过程比较简单,官方网站有比较好的文档,在此只是根据前几天的实际部署整理一下。(实际执行的命令都用红色标出) 服务器: 10.18.27.181 pgpool服务器 --------此服务器上...

javasql
2015/02/27
0
0
java postgresql csv文件数据导入

1.使用jar驱动自带的CopyManager(我这里使用的是postgresql-9.4.1212.jre7.jar) public class PGPool { PGPoolingDataSource source = new PGPoolingDataSource(); public PGPool(){ //PG d......

无知的小孩
07/25
0
0
PostgreSQL 数据库初体验

高强,“DBA+济南群”联合发起人。现就职于山东华鲁科技发展股份有限公司。擅长Oracle、AIX、Linux、PostgreSQL和DB2等产品的实施、运维和故障处理。曾是一名存储工程师,负责实施存储、双机...

高强
2015/10/15
0
0
PostgreSQL 负载均衡中间件 Pgpool-II 5 版齐发

Pgpool-II 是一个给 PostgreSQL 补充实用功能的工具,包括:连接池、负载均衡、自动故障切换等等。 Pgpool全球开发集团宣布推出以下版本的Pgpool-II: 3.7.3 3.6.10 3.5.14 3.4.17 3.3.21 这...

周其
04/18
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

49.Nginx防盗链 访问控制 解析php相关 代理服务器

12.13 Nginx防盗链 12.14 Nginx访问控制 12.15 Nginx解析php相关配置(502的问题) 12.16 Nginx代理 扩展 502问题汇总 http://ask.apelearn.com/question/9109 location优先级 http://blog....

王鑫linux
今天
1
0
Nginx防盗链、访问控制、解析php相关配置、Nginx代理

一、Nginx防盗链 1. 编辑虚拟主机配置文件 vim /usr/local/nginx/conf/vhost/test.com.conf 2. 在配置文件中添加如下的内容 { expires 7d; valid_referers none blocked server_names *.tes......

芬野de博客
今天
0
0
spring EL 和资源调用

资源调用 import org.springframework.beans.factory.annotation.Value;import org.springframework.context.annotation.PropertySource;import org.springframework.core.io.Resource;......

Canaan_
今天
1
0
memcached命令行、memcached数据导出和导入

一、memcached命令行 yum装telnet yum install telent 进入memcached telnet 127.0.0.1 11211 命令最后的2表示,两位字节,30表示过期时间(秒) 查看key1 get key1 删除:ctrl+删除键 二、m...

Zhouliang6
今天
1
0
Linux定时备份MySQL数据库

做项目有时候要备份数据库,手动备份太麻烦,所以找了一下定时备份数据库的方法 Linux里有一个 crontab 命令被用来提交和管理用户的需要周期性执行的任务,就像Windows里的定时任务一样,用这...

月夜中徘徊
今天
1
1

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部