使用pgpool-ii 搭建postgresql 高可用、负载均衡架构

标签: pgpool ii postgresql | 发表时间:2013-12-26 11:37 | 作者:xmarker
出处:http://xmarker.blog.163.com
pgpool有很多功能,其中最重要的我觉得是如下几个:提供连接池(负载均衡模式),复制模式(能通过pgpool分发sql,因此是基于sql语句的分发复制),主备模式(依赖其他的复制,如snoly和流复制,但pgpool能把客户端的sql请求根据sql是查询还是修改发送到备库或主库),并行模式(其实就是把表水平拆分到各个数据节点,一条sql查询时需要从多个数据节点查询数据),本文是用主备模式来搭建pg的高可用和负载均衡集群。
要搭建基于postgresql的高可用、负载均衡的数据库集群架构,还有很多技术,如postgres-xc,但本文以最常用的postgresql的主备模式(主库加流复制为例来搭建,1主库+多备库,实现高可用和负载均衡)。高可用即一个节点宕机不影响整体业务运行,负载均衡是指客户端发过来的链接请求能均匀的分布到各个数据节点,负载均衡的时候需要考虑到主库和备库是不同的,主库可读可写而备库只能读,因此select语句可以发往主库和备库,而update、insert、delete等要在主库执行,别的负载均衡软件如lvs是做不到的,但pgpool可以检测sql语句,自动发往不同的节点。
本文用pgpool-ii来实现高可用和读写分离的负载均衡。
1.按照postgresql9.3 ,步骤略
主机名 ip 功能
pgtest5 10.1.1.14 主库
pgtest6 10.1.1.15 备库和pgpool-ii

2.配置流复制:
略,流复制用户为repl用户

3.下载pgpool-ii,目前最新的版本为3.3.2,下载地址为: http://www.pgpool.net/mediawiki/index.php/Downloads

4.安装pgpool-ii
我下载的是rpm包,上传到虚拟机里直接rpm -ivh安装即可:
[root@pgtest6 pgpool]# pwd
/opt/soft/pgpool
[root@pgtest6 pgpool]# ls
pgpool-II-pg93-3.3.2-1.pgdg.x86_64.rpm
[root@pgtest6 pgpool]#rpm -ivh pgpool-II-pg93-3.3.2-1.pgdg.x86_64.rpm

安装完成后查看安装路径:
[root@pgtest6 pgpool]# rpm -qa|grep pgpool
pgpool-II-pg93-3.3.2-1.pgdg.x86_64
[root@pgtest6 pgpool]# rpm -ql pgpool-II-pg93-3.3.2-1.pgdg.x86_64
/etc/pgpool-II-pg93/pcp.conf
/etc/pgpool-II-pg93/pgpool.conf
/etc/pgpool-II-pg93/pgpool.conf.sample-master-slave
/etc/pgpool-II-pg93/pgpool.conf.sample-replication
/etc/pgpool-II-pg93/pgpool.conf.sample-stream
/etc/pgpool-II-pg93/pool_hba.conf 
...
默认配置文件都在/etc/pgpool-II-pg93目录下了,其中我们要配置pgpool.conf和pcp.conf
公共部分的配置:
listen_addresses = '*'
port = 9999
socket_dir = '/tmp'
pcp_port = 9898
pcp_socket_dir = '/tmp'
backend_hostname0 = '10.1.1.14'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/postgres/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = '10.1.1.15'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/postgres/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
enable_pool_hba = on
pool_passwd = '123456'
authentication_timeout = 60
ssl = on
log_destination = 'stderr'                             
print_timestamp = on
log_connections = on
log_hostname = on
log_statement = on
log_per_node_statement = on
log_standby_delay = 'if_over_threshold'
syslog_facility = 'LOCAL0'
syslog_ident = 'pgpool'
debug_level = 1
pid_file_name = '/var/run/pgpool/pgpool.pid'                              
logdir = '/tmp'
#要开启负载均衡,需要设置:
load_balance_mode = on
#要设置主备模式,需要设置:
master_slave_mode = on
master_slave_sub_mode = 'stream'
#要设置主库宕机好备库能自动接管主库,需要设置:
sr_check_period = 10
sr_check_user = 'repl'
sr_check_password = '123456'
delay_threshold = 10000000
health_check_period = 1
health_check_timeout = 20
health_check_user = 'postgres'
health_check_password = '123456'
health_check_max_retries = 0
health_check_retry_delay = 1
failover_command = '/etc/pgpool-II/failover_stream.sh %d %H /postgres/data/trigger.file'  #其中这个文件failover_stream.sh需要定义
#另外并行模式需要关闭:
parallel_mode = off

主库故障后,备库切换成主库的触发文件如下:
[root@pgtest6 pgpool-II-pg93]# more 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
exit 0;
fi
# Create the trigger file.
/usr/bin/ssh -T $new_master /bin/touch $trigger_file
exit 0;
因此,在pg的postgresql.conf中要贺pgpool参数文件的定义( /postgres/data/trigger.file)一致

配置pcp.conf,添加如下用户及密码:
postgres:e8a48653851e28c69d0506508fb27fc5 (密码可以用pg_md5 xxx来生成)

5.配置互信(root用户,pgpool需要在主库故障后登录到备库主机上创建triger文件)
过程略

6.启动pg及pgpool-ii
[root@pgtest6 pgpool-II-pg93]# pgpool -n -d > /tmp/pgpool.log 2>&1 &
[1] 1651
 
/home/postgres@pgtest5$psql -h pgtest6 -U postgres -p 9999
psql (9.3.1)
Type "help" for help.

postgres=# show pool_status;
....
postgres=# show pool_nodes;
node_id | hostname  | port | status | lb_weight |  role  
---------+-----------+------+--------+-----------+---------
0       | 10.1.1.14 | 5432 | 2      | 0.500000  | standby
1       | 10.1.1.15 | 5432 | 2      | 0.500000  | primary
其中status的状态意义如下:
0:从未使用,直接忽略
1:server已经启动,但是连接池中没有连接
2:server已经启动,并且在连接池中存在连接
3:server没有启动或者联系不上

我们在pgtest6上启动pgpool后,发现有30个空闲链接:
[root@pgtest6 pgpool-II-pg93]# ps -ef|grep pgpool
root      1651  1510  0 11:20 pts/0    00:00:00 pgpool -n -d
root      1652  1651  0 11:20 pts/0    00:00:00 pgpool: wait for connection request
root      1653  1651  0 11:20 pts/0    00:00:00 pgpool: wait for connection request
root      1654  1651  0 11:20 pts/0    00:00:00 pgpool: wait for connection request
...

而在pgtest5上,我们没有连接,但通过ps命令我们可以看到已经有客户端链接了(应该是pgpool连过来的)
[root@pgtest5 ~]# ps -ef|grep post
root      1898  1816  0 11:20 pts/0    00:00:00 su - postgres
postgres  1899  1898  0 11:20 pts/0    00:00:00 -bash
postgres  1927     1  0 11:20 pts/0    00:00:00 /usr/local/pgsql/bin/postgres
postgres  1928  1927  0 11:20 ?        00:00:00 postgres: startup process   recovering 00000003000000000000001C
postgres  1929  1927  0 11:20 ?        00:00:00 postgres: checkpointer process   
postgres  1930  1927  0 11:20 ?        00:00:00 postgres: writer process     
postgres  1931  1927  0 11:20 ?        00:00:00 postgres: stats collector process   
postgres  1932  1927  0 11:20 ?        00:00:00 postgres: wal receiver process   streaming 0/1C005000
postgres  2072  1899  0 11:22 pts/0    00:00:00 psql
postgres  2076  1927  0 11:22 ?        00:00:00 postgres: postgres db_test [local] idle
postgres  2193  1927  0 11:24 ?        00:00:00 postgres: postgres postgres 10.1.1.15(33372) idle
postgres  2306  1927  0 11:26 ?        00:00:00 postgres: postgres postgres 10.1.1.15(33547) idle
root      2313  2231  0 11:26 pts/1    00:00:00 grep post

测试:
先在pgtest6(主库)上插入数据,看流复制是否正常:
db_test=# select * from t1;
 id | name 
----+------
(0 rows)
db_test=# insert into t1 values (1000,'aaa');
INSERT 0 1
db_test=# select * from t1;
  id  | name 
------+------
 1000 | aaa
(1 row)

pgtest5(备库)查询:
db_test=# select * from t1;
  id  | name 
------+------
 1000 | aaa
(1 row)
用pgbench连接查看负载均衡是否起效:
/tmp@pgtest5$pgbench -i -F 100 -s 10 -h pgtest6 -U postgres db_test
creating tables...
100000 of 1000000 tuples (10%) done (elapsed 0.16 s, remaining 1.44 s).
200000 of 1000000 tuples (20%) done (elapsed 0.63 s, remaining 2.52 s).
300000 of 1000000 tuples (30%) done (elapsed 1.24 s, remaining 2.90 s).
400000 of 1000000 tuples (40%) done (elapsed 2.06 s, remaining 3.09 s).
500000 of 1000000 tuples (50%) done (elapsed 2.79 s, remaining 2.79 s).
600000 of 1000000 tuples (60%) done (elapsed 4.16 s, remaining 2.77 s).
700000 of 1000000 tuples (70%) done (elapsed 7.50 s, remaining 3.21 s).
800000 of 1000000 tuples (80%) done (elapsed 8.23 s, remaining 2.06 s).
900000 of 1000000 tuples (90%) done (elapsed 10.79 s, remaining 1.20 s).
1000000 of 1000000 tuples (100%) done (elapsed 12.00 s, remaining 0.00 s).
vacuum...
set primary keys...
done.
/tmp@pgtest5$pgbench -c 25 -j 25 -M prepared -n -s 500 -T 60 -f select.sql -h pgtest6 -p 9999 -U postgres db_test
/tmp@pgtest5$pgbench -c 25 -j 25 -M prepared -n -s 500 -T 60 -f select.sql -h pgtest6 -p 9999 -U postgres db_test
transaction type: Custom query
scaling factor: 500
query mode: prepared
number of clients: 25
number of threads: 25
duration: 60 s
number of transactions actually processed: 70523
tps = 1174.801298 (including connections establishing)
tps = 1176.777098 (excluding connections establishing)
通过ps命令查看,pgtest5和pgtest6上分别由30个客户端连接

测试故障切换:
现在我关闭主库(pgtest6),看是否能正常切换:
目前pgtest6为主库,关闭后pgtest5会自动切换为备库。

相关 [pgpool ii postgresql] 推荐:

使用pgpool-ii 搭建postgresql 高可用、负载均衡架构

- - x-marker的博客
要搭建基于postgresql的高可用、负载均衡的数据库集群架构,还有很多技术,如postgres-xc,但本文以最常用的postgresql的主备模式(主库加流复制为例来搭建,1主库+多备库,实现高可用和负载均衡). 高可用即一个节点宕机不影响整体业务运行,负载均衡是指客户端发过来的链接请求能均匀的分布到各个数据节点,负载均衡的时候需要考虑到主库和备库是不同的,主库可读可写而备库只能读,因此select语句可以发往主库和备库,而update、insert、delete等要在主库执行,别的负载均衡软件如lvs是做不到的,但pgpool可以检测sql语句,自动发往不同的节点.

[原]Pgpool使用心得体会

- - 杰克的运维之旅
pgpool作为postgresql的集群应用兼有代理功能,实在是强大,在无数次的实验失败之后,总结一下我的使用心得. 首先提供下载页: 点击这里. 我下载的是installer-pg93-3.3.3.tar.gz,解压之后安装postgresql92-libs-9.2.4-1PGDG.didt.ep.x86_64.rpm和pgpool-II-pg93-3.3.3-1.pgdg.x86_64.rpm,这时pgpool的安装已经结束.

PostgreSQL 9.1发布

- Kai Chen - Solidot
开源数据库项目PostgreSQL发布了v9.1版. 新版本主要的特性包括:同步复制,序列化快照隔离,支持基于列的排序,近邻邻近索引,外来数据封装,支持SELinux许可控制,等等.

PostgreSQL 9.2发布

- - Solidot
时隔一年之后,开源数据库PostgreSQL发布了v9.2版. 主要新特性包括:原生JSON支持,覆盖索引(covering indexes),改进复制和性能等. PostgreSQL显著改善了可伸缩性:线性可扩展性支持最高64核,仅扫描索引,减少CPU消耗;改进垂直可伸缩性:有效利用大服务器硬件资源,锁管理,仅访问索引等底层操作允许数据库引擎处理更大的工作负荷——每秒最高35万只读查询,每秒写入1.4万条数据.

Mysql 和 Postgresql 对比

- - 膘叔
vampire告诉我服务器上安装好了postgreSQL,他也一直在推荐这个玩意,所以了解了一下. Mysql 使用太广泛了,以至于我不得不将一些应用从mysql 迁移到postgresql, 很多开源软件都是以Mysql 作为数据库标准,并且以Mysql 作为抽象基础的,但是具体使用过程中,发现Mysql 有很多问题,所以都迁移到postgresql上了,转一个Mysql 和Postgresql 对比的文章:.

PostgreSQL与MySQL比较

- - 数据库 - ITeye博客
通过执行 MySQL 命令(mysqld)启动实例. 一个实例可以管理一个或多个数据库. 一台服务器可以运行多个 mysqld 实例. 一个实例管理器可以监视 mysqld 的各个实例. 通过执行 Postmaster 进程(pg_ctl)启动实例. 一个实例可以管理一个或多个数据库,这些数据库组成一个集群.

PostgreSQL新手入门

- - 阮一峰的网络日志
自从MySQL被Oracle收购以后, PostgreSQL逐渐成为开源关系型数据库的首选. 本文介绍PostgreSQL的安装和基本用法,供初次使用者上手. 以下内容基于Debian操作系统,其他操作系统实在没有精力兼顾,但是大部分内容应该普遍适用. 首先,安装PostgreSQL客户端. 然后,安装PostgreSQL服务器.

PostgreSQL配置优化

- - CSDN博客推荐文章
转载请注明原文出处: http://blog.csdn.net/roddick621. PostgreSQL配置优化. 200W(整个数据库大小约为300M). 准备命令:pgbench -i -s 20 pgbenchdb. 测试命令:pgbench -r -j4 -c4 -T60 testdb.

PostgreSQL Maestro 14.5 发布

- - 开源中国社区最新新闻
SQL Maestro Group 发布了 PostgreSQL Maestro 14.5 ,这是一个强大的 Windows 下图形化的 PostgreSQL 数据库服务器管理和开发的解决方案. 新版本可通过这里 下载..

PostgreSQL 9.1正式版发布

- Tairan Wang - ITeye资讯频道
PostgreSQL 9.1发布. PostgreSQL(发音为Post-gress-cue-ell)是一个自由的对象-关系数据库服务器(数据库管理系统),基于灵活的 BSD风格许可证. PostgreSQL支持大部分 SQL 标准并且提供了许多其他现代特性:复杂查询、外键、触发器、视图、事务完整性、多版本并发控制等.