PostgreSQL+pgpool-II复制方案

亦凉 2022-09-26 13:58 379阅读 0赞

# PostgreSQL+pgpool-II复制方案 #

@(学习)\[PostgreSQL, pgpool-II, 高可用\]

*  PostgreSQLpgpool-II复制方案
    
     *  Pgpool-II介绍
     *  pgpool-II安装
        
         *  1 安装pgpool-II yum源并安装pgpool-II
         *  2 添加Pgpool-II运行用户
         *  3 设置pcpconf
         *  4 设置Pgpool-II配置文件
         *  5 配置backend信息
         *  6 修改认证方式
            
             *  61 修改pgpool-II的认证方式为md5
             *  62 修改PostgreSQL的认证方式为md5
         *  7 测试pgpool-II同步
     *  安装pgpoolAdmin不推荐
        
         *  1 解压pgpoolAdmin至web目录使用户能访问其php
         *  2 php-fpm运行用户和pgpool用户统一
         *  3 根据向导完成安装
         *  4 一些细节说明
         *  5 phpini的disable\_functions设置
         *  6 pgpoolAdmin中pcp\_stop\_pgpool参数
     *  总结

## 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运行用户：  
![图1][1]

### 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每个模式对应的配置文件模板

![图2][2]

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

# cd /etc
    # cp pgpool.conf.sample-replication pgpool.conf

### 2.5 配置backend信息 ###

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

backend_socket_dir = '/tmp'
    backend_hostname0 = '10.1.0.110'
    backend_port0 = 5432
    backend_weight0 = 1
    backend_data_directory0 = '/var/lib/pgsql/9.5/data/'
    backend_flag0 = '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.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             127.0.0.1/32            trust
    host    all             all             10.1.0.0/24             md5
    # IPv6 local connections:
    host    all             all             ::1/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 127.0.0.1/32 ident
    #host replication postgres ::1/128 ident
    [root@im110 pgpool-II]#

### 2.7 测试pgpool-II同步 ###

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

测试成功，说明从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.5.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/][http_yourweb_pgpooladmin_install]  
![图3][3]

1.  从上图看到，3.5下面的勾处未有文字提示，我此处为通过。因为我装了php-psql扩展。从源码中也可以看出，是检测pgsql扩展。  
    ![图4][4]
2.  `.pcppass`文件格式如下（pgpool3.5以下用到）：  
    它的作用是用于指定连接postgresql的连接串，包括主机、端口、用户、密码。

host:port:user:password

### 3.4 一些细节说明 ###

完全设置好的完整功能如下：  
![图5][5]  
3.4.1） 显示pgpoolAdmin运行机器的hostname  
若php报warning，可在hosts中添加IP对应主机名

[root@im110 pgpoolAdmin-3.5.3]# vim /etc/hosts
    10.1.0.110 im110

3.4.2） 此处为pgpoolAdmin的登录用户，superuser: yes表示在pgpool数据库中，用户为管理员用户。此数据库可以pgpool下，也可分离到其它postgresql中。若此处非yes，则节点相关操作为不可操作的灰色。代码中并未指定host，所以只能是使用socket。 ![图6][6]

3.4.3） 页面中显示节点是否连接，是在pgpool中show pool\_nodes命令下的节点status.

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

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

[root@im110 pgpool-II]# psql -U pgpool -p 54321 -h 10.1.0.110 template1
    Password for user pgpool: 
    psql (9.5.6)
    Type "help" for help.
    
    template1=>

### 3.5 php.ini的disable\_functions设置 ###

源代码中php调用的exec执行pcp相关命令，  
![图8][8]  
因此需要将php.ini配置文件中的disable\_functions中的exec去掉，以允许php使用该函数。  
![图9][9]

### 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中需要做相应修改。  
![图10][10]

## 4. 总结 ##

1.  此PostgreSQL高可用方案搭建简单方便，且可以在不影响原来环境的基础上搭建；
2.  生产环境中的pgpoool-II是怎样的我不好评判。网上此博客写的不错，可以看看，[PG的两种集群技术：Pgpool-II与Postgres-XL][PG_Pgpool-II_Postgres-XL]

[1]: /images/20220709/104e217f11c64874b6e43f08edaa0cc4.png
[2]: /images/20220709/525b66e3ef634203b20e9d12bc1765ec.png
[http_yourweb_pgpooladmin_install]: http://yourweb/pgpooladmin/install/
[3]: /images/20220709/4f273739e4d24ab2b0ac3060c91354b9.png
[4]: /images/20220709/77ab19d3c8ca43638c09a4fa6297ae1e.png
[5]: /images/20220709/8eb55d4101494e5fad89c518a21efc1e.png
[6]: /images/20220709/6c95cdaabc794bc8ab0e6d7a97357d7b.png
[http_www.pgpool.net_docs_latest_en_html_reference.html]: http://www.pgpool.net/docs/latest/en/html/reference.html
[7]: /images/20220709/c4674542ae7f46029bcdf8a2e37cf5a6.png
[8]: /images/20220709/04cc651b1be14dc1b8c881f6d1906122.png
[9]: /images/20220709/16da5850f77b4ea7ac155c8829c1a5b8.png
[10]: /images/20220709/33744ad1fd0c4dc6a2b3ca0c07492aa9.png
[PG_Pgpool-II_Postgres-XL]: https://segmentfault.com/a/1190000007012082