投稿
  1. 首页
  2. sql

PostgreSQL+pgpool-II复制方案

光伏双反 • 2022-6-2 • sql • 阅读 20

PostgreSQL+pgpool-II复制方案,第1张

概述1. Pgpool-II介绍 pgpool-II是PostgreSQL服务器之间一种有效的中间件和PostgreSQL数据库客户端。它提供了以下功能。 连接池  pgpool-II保存到PostgreSQL服务器的连接，当一个相同新连接(如用户名、数据库、协议版本)进来时，重用他们。它减少了连接开销，提高了系统的整体吞吐量。  复制  pgpool-II可以管理多个PostgreSQL服务器。使用 1. Pgpool-II介绍

pgpool-II是Postgresql服务器之间一种有效的中间件和Postgresql数据库客户端。它提供了以下功能。

连接池
pgpool-II保存到Postgresql服务器的连接，当一个相同新连接(如用户名、数据库、协议版本)进来时，重用他们。它减少了连接开销，提高了系统的整体吞吐量。
复制
pgpool-II可以管理多个Postgresql服务器。使用复制功能可以使2个或更多的物理磁盘上创建一个实时备份，这样服务不会因服务器的磁盘故障而中断。
负载平衡
如果数据库是复制的，在任何服务器上执行一个SELECT查询会返回相同的结果。pgpool-II复制特性的优势在于减少每个Postgresql服务器上的负载，因为它可以使用分布在多个服务器之间进行SELECT查询，从而提高系统的整体吞吐量。最好是查询和Postgresql服务器数量成一定比例，多用户同时执行多查询达到负载均衡最好的效果。
限制连接数
Postgresql的最大并发连接数有一定限制的，当超过限制的连接数后，连接会被拒绝。然而，设置增加最大连接数又会增加资源消耗，影响系统性能。pgpool-II也有最大连接数限制，但超过的连接进来时是进行立即排队，而不是返回一个错误。

pgpool-II交互Postgresql的后端和前端协议时，起着继电器的作用。因此，数据库应用程序(前端)认为pgpool-II是真实的Postgresql服务器，服务器(后端)认为pgpool-II是它的客户端之一。因为pgpool-II在服务器和客户端是透明的，所以pgpool-II可以使用现有的数据库应用程序而做到几乎不修改它们。

环境：
CentOS6.8
Postgresql9.5
pgpool-II-pg95-3.6.1

2. pgpool-II安装 2.1 安装pgpool-II yum源，并安装pgpool-II

从官网找到相关yum源rpm包，使用rpm安装后，并使用yum安装pgpool-II

rpm -ivh http://www.pgpool.net/yum/rpms/3.6/redhat/rhel-6-x86_64/pgpool-II-release-3.6-1.noarch.rpm
yum -y install pgpool-II-pg95
yum -y install pgpool-II-pg95-deBUGinfo
yum -y install pgpool-II-pg95-devel
yum -y install pgpool-II-pg95-extensions
chkconfig pgpool on  # 添加开机启动
service start/stop pgpool  # 服务启/停
2.2 添加Pgpool-II运行用户

添加Pgpool-II运行用户，并给相关目录的读写权限

[root@im110 pgpool-II]# useradd pgpool                           
[root@im110 pgpool-II]# passwd pgpool
Changing password for user pgpool.
New password: 
Retype new password: 
passwd: all authentication tokens updated successfully.
[root@im110 pgpool-II]# chown -R pgpool.pgpool /etc/pgpool-II
[root@im110 pgpool-II]# mkdir -p /var/run/pgpool/
[root@im110 pgpool-II]# chown pgpool.pgpool /var/run/pgpool/

修改启动脚本的pgpool运行用户：

650) this.width=650;" src="http://img.jb51.cc/vcimg/static/loading.png" title="-1488635145904.png" alt="wKiom1i68SvQiVltAAFbuXiC4wY969.png" src="https://s1.51cto.com/wyfs02/M01/8E/45/wKiom1i68SvQiVltAAFbuXiC4wY969.png">

2.3 设置pcp.conf 
cp /etc/pcp.conf.sample /etc/pcp.conf

内容格式为如下，一行一个，#号为注释标识

username:[md5 encrypted password]

[md5 encrypted password] 可以使用如下命令生成

$ pg_md5 pgpool
ba777e4c2f15c11ea8ac3be7e0440aa0

使用pg_md5 -p会隐藏输入的密码

$ pg_md5 -p
password: your_password

配置文件pcp.conf必须允许pgpool执行用户可读。

2.4 设置Pgpool-II配置文件

pgpool.conf是Pgpool-II的主配置文件。启动Pgpool-II时可以使用 -f 参数指定 pgpool.conf路径， 默认是使用/etc/pgpool.conf.

Pgpool-II每个模式对应的配置文件模板

650) this.width=650;" src="http://img.jb51.cc/vcimg/static/loading.png" title="2.png" alt="wKioL1i68UjTJHo6AAAqreaSMZU450.png" src="https://s4.51cto.com/wyfs02/M00/8E/43/wKioL1i68UjTJHo6AAAqreaSMZU450.png">

复制一份作为你的配置文件：

# cd /etc
# cp pgpool.sample-replication pgpool.conf
2.5 配置backend信息

在pgpool.conf中加入如下格式的配置，其中0为backend主机号，不能重复。

backend_socket_dir = '/tmp'
backend_hostname0 = @H_502_188@'10.1.0.110'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = @H_502_188@'/var/lib/pgsql/9.5/data/'
backend_flag0 = @H_502_188@'ALLOW_TO_FAIlovER'
2.6 修改认证方式

为了更安全，修改认证方式为md5

2.6.1 修改pgpool-II的认证方式为md5

vim /etc/pgpool-II/pool_hba.conf

# TYPE DATABASE  USER    CIDR-ADDRESS     METHOD
host  all       all       10.1.0.0/24       md5
# "local" is for Unix domain socket connections only
local  all       all                  md5
# IPv4 local connections:
host  all     all     127.0.1/32     md5
host  all     all     ::1/128        md5
2.6.2 修改Postgresql的认证方式为md5

vim pg_hba.conf

# TYPE DATABASE    USER      ADDRESS         METHOD

# "local" is for Unix domain socket connections only
# IPv4 local connections:
host  all       all       32      trust
host  all       all       24       md5
# IPv6 local connections:
host  all       all       ::128         trust
# Allow replication connections from localhost,by a user with the
# replication privilege.
local  all   postgres                md5
#local  replication   postgres                peer
#host  replication   postgres    32      IDent
#host  replication   postgres    ::128         IDent
[root@im110 pgpool-II]#
2.7 测试pgpool-II同步

这里作简单测试，在pgpool-II入口创建数据库，看各节点是否自动创建，删除后，看各节点是否自动删除。

[root@im109 ~]# psql -U postgres -h 10.1.0.115 -p 9999
Password for user postgres: 
psql (9.5.6)
Type "help" for help.

postgres=# show pool_nodes;
 node_ID | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay 
---------+------------+-------+--------+-----------+--------+------------+-------------------+-------------------
 0    | 10.1.0.110 | 54321 | up   | 0.500000 | master | 0     | true       | 0
 1    | 10.1.0.109 | slave | false       | 0
(2 rows)

postgres=# 
postgres=# \l
                 List of databases
  name  | Owner  | EnCoding |  Collate  |  Ctype  |  Access privileges  
-----------+----------+----------+-------------+-------------+-----------------------
 postgres | postgres | UTF8   | en_US.UTF-8 | 
 template0 | =c/postgres     +
      |     |       | postgres=CTc/postgres
 template1 | postgres=CTc/postgres
 wiseuCMSg | 
(4 rows)

postgres=# create database test01;
CREATE DATABASE
postgres=# \q
[root@im109 ~]# psql -U postgres -h 10.1.0.110 -p 54321
Password for user postgres: 
psql (9.5.6)
Type "help" for help.

postgres=# \l
                 List of databases
  name  | postgres=CTc/postgres
 test01  | 
 wiseuCMSg | 
(5 rows)

postgres=# \q
[root@im109 ~]# psql -U postgres -h 10.1.0.109 -p 54321 
Password for user postgres: 
psql (9.5.6)
Type "help" for help.

postgres=# \l
                 List of databases
  name  | 
(5 rows)

postgres=# \q
[root@im109 ~]# psql -U postgres -h 10.1.0.115 -p 9999 
Password for user postgres: 
psql (9.5.6)
Type "help" for help.

postgres=# \l
                 List of databases
  name  | 
(5 rows)

postgres=# drop database test01;
DROP DATABASE
postgres=# \l
                 List of databases
  name  | 
(4 rows)

postgres=# \q
[root@im109 ~]# psql -U postgres -h 10.1.0.110 -p 54321
Password for user postgres: 
psql (9.5.6)
Type "help" for help.

postgres=# \l
                 List of databases
  name  | 
(4 rows)

postgres=# \q
[root@im109 ~]# psql -U postgres -h 10.1.0.109 -p 54321
Password for user postgres: 
psql (9.5.6)
Type "help" for help.

postgres=# \l
                 List of databases
  name  | 
(4 rows)

postgres=# \q
[root@im109 ~]#

测试成功，说明从pgpool-II入口 *** 作，各节点会同步数据。

3. 安装pgpooladmin（不推荐）

虽然官网提供此工具，用于页面管理pgpool，但是我觉得不安全，且可能BUG较多。以下文中会有一些BUG列出来。
安装方法：

3.1 解压pgpooladmin至web目录，使用户能访问其PHP 
wget http://www.pgpool.net/download.PHP?f=pgpooladmin-3.5.3.tar.gz -O pgpooladmin-3.5.3.tar.gz
cd /var/www
tar -zxvf pgpooladmin-3.5.3.tar.gz
ln -s pgpooladmin-3 pgpooladmin

mkdir templates_c
chmod 777 templates_c
chown www conf/pgmgt.conf.PHP
chmod 644 conf/pgmgt.conf.PHP
3.2 PHP-fpm运行用户和pgpool用户统一

pgpooladmin使用pcp命令控制pgpool启停，因此需要统一PHP和pgpool运行用户，以便pgpooladmin从页面控制pgpool启停。可参考上文中2.2步骤 *** 作。

3.3 根据向导完成安装

访问安装页面，完成检测，完成安装。
http://yourweb/pgpooladmin/install/

650) this.width=650;" src="http://img.jb51.cc/vcimg/static/loading.png" title="3.png" alt="wKioL1i68WfAulIaAACOkiYmI1s037.png" src="https://s3.51cto.com/wyfs02/M01/8E/43/wKioL1i68WfAulIaAACOkiYmI1s037.png">

从上图看到，3.5下面的勾处未有文字提示，我此处为通过。因为我装了PHP-psql扩展。从源码中也可以看出，是检测pgsql扩展。

650) this.width=650;" src="http://img.jb51.cc/vcimg/static/loading.png" title="4.png" alt="wKiom1i68XuiAPUmAADR9csV1BU119.png" src="https://s4.51cto.com/wyfs02/M02/8E/45/wKiom1i68XuiAPUmAADR9csV1BU119.png">

.pcppass文件格式如下（pgpool3.5以下用到）：
它的作用是用于指定连接postgresql的连接串，包括主机、端口、用户、密码。

host:port:user:password
3.4 一些细节说明

完全设置好的完整功能如下：

650) this.width=650;" src="http://img.jb51.cc/vcimg/static/loading.png" title="5.png" alt="wKiom1i68YnTQsDSAAM9Cxnl9gw780.png" src="https://s2.51cto.com/wyfs02/M01/8E/45/wKiom1i68YnTQsDSAAM9Cxnl9gw780.png">

显示pgpooladmin运行机器的hostname
若PHP报warning，可在hosts中添加IP对应主机名

[root@im110 pgpooladmin-3.5.3]# vim /etc/hosts
.110 im110

此处为pgpooladmin的登录用户，superuser: yes表示在pgpool数据库中，用户为管理员用户。此数据库可以pgpool下，也可分离到其它postgresql中。若此处非yes，则节点相关 *** 作为不可 *** 作的灰色。

650) this.width=650;" src="http://img.jb51.cc/vcimg/static/loading.png" title="6.png" alt="wKioL1i68Z_z1v5AAAGK7hdHQ30078.png" src="https://s5.51cto.com/wyfs02/M02/8E/43/wKioL1i68Z_z1v5AAAGK7hdHQ30078.png">

页面中显示节点是否连接，是在pgpool中show pool_nodes命令下的节点status.

[root@im109 ~]# psql -U postgres -h .115 -p 9999
Password for user postgres: 
psql (9.5.6)
Type "help" for help.

postgres=# show pool_nodes;
 node_ID | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay 
---------+------------+-------+--------+-----------+--------+------------+-------------------+-------------------
0    | .110 | 54321 | up   | 0.500000 | master | 0     | true       | 0
1    | .109 | 0.500000 | slave | false       | 0
(2 rows)

其它命令参考官方文档：
http://www.pgpool.net/docs/latest/en/html/reference.html

pgpool.conf 设置中的健康检查，检查postgressql是否启动
此处是通过连接各节点是否成功来判断的，因此需要在各节点创建用于连接的角色。

650) this.width=650;" src="http://img.jb51.cc/vcimg/static/loading.png" title="7.png" alt="wKiom1i68bvQRZrjAAa0GSSHbjI049.png" src="https://s1.51cto.com/wyfs02/M00/8E/45/wKiom1i68bvQRZrjAAa0GSSHbjI049.png">

[root@im110 pgpool-II]# psql -U pgpool -p 54321 -h .110 template1
Password for user pgpool: 
psql (for help.

template1=>
3.5 PHP.ini的disable_functions设置

源代码中PHP调用的exec执行pcp相关命令，

650) this.width=650;" src="http://img.jb51.cc/vcimg/static/loading.png" title="8.png" alt="wKioL1i68fTjEIktAAGJrX6aAh0463.png" src="https://s5.51cto.com/wyfs02/M01/8E/43/wKioL1i68fTjEIktAAGJrX6aAh0463.png">

因此需要将PHP.ini配置文件中的disable_functions中的exec去掉，以允许PHP使用该函数。

650) this.width=650;" src="http://img.jb51.cc/vcimg/static/loading.png" title="9.png" alt="wKiom1i68gKidRD8AAFsGTre76Q015.png" src="https://s2.51cto.com/wyfs02/M02/8E/45/wKiom1i68gKidRD8AAFsGTre76Q015.png">

3.6 pgpooladmin中pcp_stop_pgpool参数

在pgpool-II3.6中，pcp_stop_pgpool用法如下：

root@im110 pgpooladmin]# pcp_stop_pgpool --help
pcp_stop_pgpool - terminate pgpool-II
Usage:
pcp_stop_pgpool [OPTION...] 
Options:
 -U,--username=name  username for PCP authentication
 -h,--host=HOSTname  pgpool-II host
 -p,--port=PORT    PCP port number
 -w,--no-password   never prompt for password
 -W,--password     force password prompt (should happen automatically)
 -m,--mode=MODE    MODE can be "smart","fast",or "immediate"
 -d,--deBUG      enable deBUG message (optional)
 -v,--verbose     output verbose messages
 -?,--help       print this help

pgpooladmin中需要做相应修改。

650) this.width=650;" src="http://img.jb51.cc/vcimg/static/loading.png" title="10.png" alt="wKioL1i68hOjcekaAAFEKYQ1iBQ799.png" src="https://s3.51cto.com/wyfs02/M02/8E/43/wKioL1i68hOjcekaAAFEKYQ1iBQ799.png">

4. 总结

此Postgresql高可用方案搭建简单方便，且可以在不影响原来环境的基础上搭建；

生产环境中的pgpoool-II是怎样的我不好评判。网上此博客写的不错，可以看看，PG的两种集群技术：Pgpool-II与Postgres-XL

总结

以上是内存溢出为你收集整理的PostgreSQL+pgpool-II复制方案全部内容，希望文章能够帮你解决PostgreSQL+pgpool-II复制方案所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。

欢迎分享，转载请注明来源：内存溢出

原文地址: http://outofmemory.cn/sjk/1173361.html

赞 (0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
光伏双反 光伏双反 一级用户组
0 0
上一篇 2022-06-02
下一篇 2022-06-02

发表评论

登录后才能评论

评论列表（0条）

Copyright © 2022 内存溢出 版权所有 湘ICP备2022025235号 Powered by outofmemory.cn

保存