PostgreSQL9.4.5 使用 pgpool 实现读写分离和负载均衡 雲端信息中心 研發處 Version Corporation Author Date 1.1 Inventec shu-guang wang 2016/03/10 一、实验原理 1.1、pgpool 的功能 pgpool 是一个位于 PostgreSQL 服务器和 PostgreSQL 数据库客户端之间的中间件，它提供以 下功能： 连接池 pgpool-II 保持已经连接到 PostgreSQL 服务器的连接，并在使用相同参数（例如：用户名， 数据库，协议版本）连接进来时重用它们。它减少了连接开销，并增加了系统的总体吞吐量。 复制 pgpool-II 可以管理多个 PostgreSQL 服务器。激活复制功能并使在 2 台或者更多 PostgreSQL 节点中建立一个实时备份成为可能，这样，如果其中一台节点失效，服务可以不被中断继续 运行。 负载均衡 如果数据库进行了复制，则在任何一台服务器中执行一个 SELECT 查询将返回相同的结果。 pgpool-II 利用了复制的功能以降低每台 PostgreSQL 服务器的负载。它通过分发 SELECT 查 询到所有可用的服务器中，增强了系统的整体吞吐量。在理想的情况下，读性能应该和 PostgreSQL 服务器的数量成正比。负载均很功能在有大量用户同时执行很多只读查询的场 景中工作的效果最好。 限制超过限度的连接 PostgreSQL 会限制当前的最大连接数，当到达这个数量时，新的连接将被拒绝。增加这个 最大连接数会增加资源消耗并且对系统的全局性能有一定的负面影响。pgpoo-II 也支持限制 最大连接数，但它的做法是将连接放入队列，而不是立即返回一个错误。 并行查询 使用并行查询时，数据可以被分割到多台服务器上，所以一个查询可以在多台服务器上同时 执行，以减少总体执行时间。并行查询在查询大规模数据的时候非常有效。 1.2、pgpool 的优点 （1）不用修改程序 （2）PHP, Perl, Java 等等语言都可以操作，没有语言限制 （3）prefork 型体系架构 （4）可以限制访问 PostgreSQL 连接数 （5）故障转移功能 （6）配备的复制功能 （7）配备了负载平衡 1.3、pgpool 的缺点 （1）增加系统开销 （2）不支持所有的.libpq 协议 （3）template1, regression 等名字数据库没有映射到池里面 

（4）没有及时删除临时表， 8.3 以后 reset_query_list 设置为 "DISCARD ALL"的话可以解决 这个问题 。 （5）没有及时删除 PREPARE，8.3 以后 reset_query_list 设置为 "DISCARD ALL"的话可以解决 这个问题 。 二、实验环境 DB1 DB2 PGPOOL1 PGPOOL2 三、实验架构 192.168.200.201 192.168.200.202 192.168.200.211 192.168.200.212 CentOS7.1 CentOS7.1 CentOS7.1 CentOS7.1 四、实验步骤 4.1、准备 PostgreSQL 主从流复制环境 在 DB1 和 DB2 上部署 postgresql94 主从流复制环境 4.2、安装 pgpool 在 PGPOOL1 和 PGPOOL2 上 yum -y install pgpool-II-94 4.3、配置 pgpool 和两台数据库之间的信任关系 在 PGPOOL1 上 ssh-keygen -t rsa ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.200.212 在 PGPOOL2 上 ssh-keygen -t rsa ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.200.211 4.4、配置 pgpool 在 PGPOOL1 和 PGPOOL2 上 (1)配置 pcp.conf cd /etc/pgpool-II-94 cp pcp.conf.sample pcp.conf 

pg_md5 -u pgpool -p 假设输入的密码是 PGpool123com，生成 62ea98b3d8d73d84d28297aa3c3bc5c6，则在 pcp.conf 中添加下面一行 pgpool:62ea98b3d8d73d84d28297aa3c3bc5c6 (2)配置 pgpool.conf cp pgpool.conf.sample-stream pgpool.conf 修改部分内容如下 listen_addresses = '*' backend_hostname0 = '192.168.200.201' backend_port0 = 5432 backend_weight0 = 1 backend_data_directory0 = '/var/lib/pgsql/9.4/data' backend_flag0 = 'ALLOW_TO_FAILOVER' backend_hostname1 = '192.168.200.202' backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 = '/var/lib/pgsql/9.4/data' backend_flag1 = 'ALLOW_TO_FAILOVER' enable_pool_hba = on log_connections = on log_hostname = on log_statement = on pid_file_name = '/var/run/pgpool-II-94/pgpool.pid' insert_lock = on sr_check_user = 'repl' sr_check_password = 'REPL123com' (3)配置 pool_hba.conf cp pool_hba.conf.sample pool_hba.conf 修改成和 DB1 上的 pg_hba.conf 类似，比如 local host host host (4)执行命令"pg_md5 -u postgres -p -m"，密码输入 DB1 上数据库用户 postgres 的密码，这会 生成密码文件 pool_passwd 4.5、启动 pgpool systemctl start pgpool-II-94 4.5、查看 pgpool 状态 ps -ef |grep pgpool netstat -anp |grep pgpool psql -h 192.168.200.221 -p 9999 -U postgres -d postgres -c "show pool_nodes;" 正常的话会显示如下 127.0.0.1/32 0.0.0.0/0 ::1/128 trust trust md5 md5 all all all all all all all all