暂无图片
暂无图片
1
暂无图片
暂无图片
暂无图片
首页 / PostgreSQL pgpool-II集群配置

PostgreSQL pgpool-II集群配置

三杯酒coO 2020-03-02
3506

集群提供了一种实现水平可伸缩性的方法，并提供了通过添加节点来处理更多工作的能力，它可以防止单节点的故障以保障业务持续运行。PostgreSQL本身不支持任何集群解决方案，不过有许多商业和社区产品提供此实现。


集群有两种模型（图片来源于网络）：


目前比较主流的PG集群软件解决方案有 Postgres-XC，Pgpool II、Patroni、Citus等，这些都是比较流行的解决方案。我们今天主要介绍的是 Pgpool II，虽然国内使用的比较少，但是在国外有着广泛的应用，而且是PG社区中比较好的原生态软件。


一、pgpool-II简介


pgpool-II 是一个位于 PostgreSQL 服务器和 PostgreSQL 数据库客户端之间的中间件，它提供以下功能：


  • 连接池
    pgpool-II 保持已经连接到 PostgreSQL 服务器的连接，并在使用相同参数（例如：用户名，数据库协议版本）连接进来时重用它们。它减少了连接开销，并增加了系统的总体吞吐量。

  • 复制
    pgpool-II 可以管理多个 PostgreSQL 服务器。激活复制功能并使在2台或者更多 PostgreSQL 节点中建立一个实时备份成为可能，这样，如果其中一台节点失效，服务可以不被中断继续运行。

  • 负载均衡
    如果数据库进行了复制，则在任何一台服务器中执行一个 SELECT 查询将返回相同的结果。pgpool-II          利用了复制的功能以降低每台 PostgreSQL 服务器的负载。它通过分发 SELECT 查询到所有可用的服务器中，增强了系统的整体吞吐量。在理想的情况下，读性能应该和 PostgreSQL 服务器的数量成正比。负载均很功能在有大量用户同时执行很多只读查询的场景中工作的效果最好。

  • 限制超过限度的连接
    PostgreSQL 会限制当前的最大连接数，当到达这个数量时，新的连接将被拒绝。增加这个最大连接数        会增加资源消耗并且对系统的全局性能有一定的负面影响。pgpoo-II 也支持限制最大连接数，但它的          做法是将连接放入队列，而不是立即返回一个错误。

  • 并行查询
    使用并行查询时，数据可以被分割到多台服务器上，所以一个查询可以在多台服务器上同时执行，以减        少总体执行时间。并行查询在查询大规模数据的时候非常有效。


pgpool-II 使用 PostgreSQL 的前后台程序之间的协议，并且在前后台之间传递消息。因此，一个（前端的）数据库应用程序认为 pgpool-II 就是实际的 PostgreSQL 数据库，而后端的服务进程则认为 pgpool-II 是它的一个客户端。因为 pgpool-II 对于服务器和客户端来说是透明的，现有的数据库应用程序基本上可以不需要修改就可以使用 pgpool-II 了。


目前pgpool-II最新版本Pgpool II 4.1，该版本主要是为性能发布，主要关注增加Pgpool II性能，同时缩小直接使用PG与结合使用中间件Pgpool ll之间的差距。


部分特性：


  • 共享关系缓存替换关系缓存（relcache）并提供在所有Pgpool II子节点之间共享的共享缓存。Pgpool II在执行简单查询时首次需要访问一堆系统目录表，使用共享关系缓存，信息将在所有子节点之间共享。因此，当添加新节点时，不需要再次查询系统表

  • 增强扩展查询性能

  • 语句级负载平衡:语句级负载平衡实质上是查询级的负载平衡，即每当pgpool将新查询在会话中响应处理时，发出新查询，将确定负载平衡节点。新的参数是“statement_level_load_balance””。如果将此设置为on，则启用该功能（可以通过重新加载pgpool.conf来更改参数）。官方解释


1.1 pgpool-II架构



二、安装


前提已经安装并配置好Postgresql,配置好pg的流复制 ,配置好环境变量,特别是 PGHOME、PGDATA等。


关于PG的安装请参考
PostgreSQL安装（编译方式）
PostGreSQL安装配置(yum方式)


关于流复制可参考文章
PG流复制配置


关于PGPool-II的文档
pgpool-II 4.1.1 Documentation
Pgpool Wiki


2.1 环境说明


版本说明：


  • CentOS 7.6

  • Postgresql 12.2

  • PGPool-II 4.1


节点说明


本文是以两节点pg为案例进行试验测试的。但是对于watchdog来说 ，4.1的版本要求都是三节点，为的是防止vip选举时出现脑裂问题。如果有条件，建议生产还是按照三节点来进行配置。


三节点注意需要在 pg库中安装 pgpool_recovery扩展进行管理。Installing pgpool_recovery

IP地址角色数据目录
192.168.20.155主库/data/pgsql/12/data
192.168.20.156备库/data/pgsql/12/data
192.168.20.157虚拟IP


2.2 安装方式1：yum


pgpool-II安装在主备两台上面都安装


相对简单一些的还可以使用yum 来进行安装。


    #install
    yum install https://www.pgpool.net/yum/rpms/4.1/redhat/rhel-7Server-x86_64/pgpool-II-release-4.1-1.noarch.rpm
    yum install pgpool-II-pg12
    yum install pgpool-II-pg12-debuginfo
    yum install pgpool-II-pg12-devel
    yum install pgpool-II-pg12-extensions
    # configure service
    systemctl enable pgpool.service
    systemctl start pgpool.service
    systemctl stop pgpool.service


    2.3 安装方式2：编译安装

    pgpool-II安装在主备两台上面都安装

      cd usr/local/src/
      wget https://www.pgpool.net/mediawiki/images/pgpool-II-4.1.1.tar.gz
      tar xvf pgpool-II-4.1.1.tar.gz
      cd pgpool-II-4.1.0
      ./configure --with-pgsql=/usr/local/pgsql-12/ --prefix=/usr/local/pgpool2
      make && make install
      # 用来存放进程id、切换脚本等
      mkdir usr/local/pgsql-12/run


      三、配置

      pgpool-II的几种模式官方介绍

      我们推荐使pgpool-II使用PG的流复制配置
      关于流复制可参考文章 PG流复制配置

      如果是采用复制模式(native replication mode)，则需要配置 Creating insert_lock table

      3.1 环境变量

        cat >>/etc/profile <<EOF
        export PATH=\$PATH:/usr/local/pgpool2/bin
        export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/pgpool2/lib
        EOF


        3.2 pcp.conf 配置

        主备库配置相同，可在主库配置好后scp到备库

        Configuring pcp.conf
        Pgpool-II  提供一个管理接口用来进行一些管理操作，例如 查看pgpool的状态和远程终止pgpool进程等。pcp.conf 是该接口认证需要的一个密码文件。
        该文件内容格式username:[md5 encrypted password]

          # 根据模板生成配置文件
          cd usr/local/pgpool2/etc/
          cp pcp.conf.sample pcp.conf
          pg_md5  123456

            # 将生成的密码配置到 pcp.conf
            cat >>pcp.conf <<EOF
            postgres:1060b7b46a3bd36b3a0d66e0127d0517
            EOF


            3.3 主库pgpool.conf配置

            查询缓存可以将查询的语句和结果保存到pgpool-II的缓存中，以供后续相同的查询（变量亦相同）直接返回结果而不用去查询数据库，极大的提高了查询性能。然后，在一些情况下反而会有点慢，因为它需要额外的去存储缓存这样的动作。

            如果表被update了，它会自动删除pgpool-II的缓存（关于这个机制，官方没有细说，因此不清楚如果是不经过pgpool-II的 update表 ，pgpool-II会不会捕获到的表已经被update 从而去删除其缓存，猜测是做不到的）。如果频繁的update ，将会降低 cache hit ratio，性能因此下降。

            我们已经在pg端进行了缓存，并且有大量的update ，因此本例没有配置查询缓存。
            关于查询缓存的配置参考官方文档

            • pgpool.conf默认模板
            Operation modeConfiguration file name
            Streaming replication modepgpool.conf.sample-stream
            Replication modepgpool.conf.sample-replication
            Master slave mode
            Raw modepgpool.conf.sample
            Logical replication modepgpool.conf.sample-logical

            • 通过模板生成pgpool.conf

              cd usr/local/pgpool2/etc
              cp pgpool.conf.sample-stream pgpool.conf


              • 主库 编辑pgpool.conf，内容如下

                # - pgpool Connection Settings -
                listen_addresses = '*'
                port = 9999
                # - pgpool Communication Manager Connection Settings -
                pcp_listen_addresses = '*'
                pcp_port = 9898
                # - Backend Connection Settings -
                backend_hostname0 = '192.168.20.155'
                backend_port0 = 5432
                backend_weight0 = 1
                backend_data_directory0 = '/data/pgsql/12/data'
                backend_flag0 = 'ALLOW_TO_FAILOVER'
                backend_hostname1 = '192.168.20.156'
                backend_port1 = 5432
                backend_weight1 = 1
                backend_data_directory1 = '/data/pgsql/12/data'
                backend_flag1 = 'ALLOW_TO_FAILOVER'
                # - Authentication -
                enable_pool_hba = on
                pool_passwd = 'pool_passwd'
                authentication_timeout = 60
                # FILE LOCATIONS
                pid_file_name = '/usr/local/pgpool2/run/pgpool.pid'
                # CONNECTION POOLING
                connection_cache = on
                max_pool=16
                num_init_children=1000
                # REPLICATION MODE
                replication_mode = off
                # LOAD BALANCING MODE
                load_balance_mode = on
                # MASTER/SLAVE MODE
                master_slave_mode = on
                master_slave_sub_mode = 'stream'
                # - Streaming -
                sr_check_period = 5
                sr_check_user = 'repl'
                sr_check_password = 'Rep9102'
                sr_check_database = 'postgres'
                # HEALTH CHECK
                health_check_period = 10
                health_check_timeout = 10
                health_check_user = 'pgread'
                health_check_password = 'pgread_password'
                health_check_database = 'postgres'
                # FAILOVER AND FAILBACK
                failover_command = '/usr/local/pgpool2/run/failover_stream.sh %H %N'
                # WATCHDOG
                use_watchdog = on
                enable_consensus_with_half_votes=on  # 该参数让让watchdog 运行在两个节点成为可能，否则必须要三个节点才能起到 watchdog的 自动切换作用
                wd_hostname = '192.168.20.155'
                wd_port = 9000
                delegate_IP = '192.168.20.157'
                wd_lifecheck_method='heartbeat'
                wd_monitoring_interfaces_list='bond0:0'
                wd_heartbeat_port = 9694
                wd_priority=100
                if_cmd_path = '/'
                if_up_cmd = '/usr/sbin/ifconfig bond0:0 inet $_IP_$ netmask 255.255.255.0'
                if_down_cmd = '/usr/sbin/ifconfig bond0:0 down'
                arping_cmd = '/usr/sbin/arping -U $_IP_$ -w 1 -I bond0'
                # -- heartbeat mode --
                heartbeat_destination0 = '192.168.20.156'  # 主库上设置备库的ip地址
                heartbeat_device0 = 'bond0'
                heartbeat_destination_port0=9694
                # - Other pgpool Connection Settings -
                other_pgpool_hostname0 = '192.168.20.156' # 主库上设置备库的ip地址
                other_pgpool_port0 = 9999
                other_wd_port0 = 9000
                # -- for log
                log_statement = on
                log_client_messages = on
                log_line_prefix = '%t-%l : %d %a %u'


                pgpool-II集群要点在watchdog上，watchdog 会检测集群节点的有效性，并在主节点失败后进行主节点的选举，将数据库和vip 切换到可用的一个备节点。关于watchdog的一些参数配置参考Watchdog
                特别是对于本例中特别参数enable_consensus_with_half_votes 一定要注意，如果是奇数个节点则不必在意。

                • pgpool.conf中参数 failover_command 用到的脚本 failover_stream.sh 配置

                主库备库该脚本都需要配置，内容保持一致

                使用该脚本的前提是 运行pgpool的 进程的用户（本例是root） 要可以使用postgres用户 免密登录 主机和备机

                即 ssh $NEW_MASTER_NODE_HOST
                和 ssh $OLD_MASTER_NODE_HOST
                 需要免密登录

                  cat >>/usr/local/pgpool2/run/failover_stream.sh <<EOF
                  #!/bin/bash
                  # This script is run by failover_command.
                  # Failover command for streaming replication.
                  # Special values:
                  #   %d = failed node id
                  #   %h = failed node hostname
                  #   %p = failed node port number
                  #   %D = failed node database cluster path
                  #   %m = new master node id
                  #   %H = new master node hostname
                  #   %M = old master node id
                  #   %P = old primary node id
                  #   %r = new master port number
                  #   %R = new master database cluster path
                  #   %N = old primary node hostname
                  #   %S = old primary node port number
                  #   %% = '%' character
                  NEW_MASTER_NODE_HOST=$1
                  OLD_MASTER_NODE_HOST=$2
                  PGDATA=/data/pgsql/12/data
                  PGHOME=/usr/local/pgsql-12
                  PGUSER=postgres
                  STANDBY_FILE=$PGDATA/standby.signal
                  promote_command="$PGHOME/bin/pg_ctl promote -D $PGDATA"
                  touch_command="test ! -f $STANDBY_FILE && touch $STANDBY_FILE"
                  # Prompte standby database.
                  /usr/bin/ssh -T $PGUSER@$NEW_MASTER_NODE_HOST $promote_command
                  # touch standby.signal
                  /usr/bin/ssh -T $PGUSER@$OLD_MASTER_NODE_HOST $touch_command
                  exit 0;
                  EOF


                  • postgres.conf中 pool_passwd 参数用到的 'pool_passwd'配置

                  主库备库该脚本都需要配置，内容保持一致

                    #在pgpool中添加pg数据库的用户名和密码
                    pg_md5 -p -m -u postgres
                    #数据库登录用户是postgres,这里输入登录密码，不能出错
                    #输入密码后，该密码是登录pg库的密码 ，在pgpool/etc目录下会生成一个pool_passwd文件
                    #如果程序用户apper 需要通过pgpool-II登录pg数据库，那么需要将apper配置到pool_passw。则进行如下操作
                    pg_md5 -p -m -u apper
                    # 输入apper密码后，密码一定要正确， ，apper 将会配置到 pool_passwd文件中


                    *注意 ，pgpool 需要通过socket 及ip 访问pg
                    在本次测试中 ，pg库的 postgresql.conf中设置了socket 文件的安全性#unix_socket_permissions = 0700
                    ，即只允许pg用户postgres访问该socket文件，即时root也不能通过 该socket 访问pg库，然后pgpool 报错连不到数据库。因此又去掉了该安全设置，才可以访问。后面有时间的话可以深入研究下pgpool访问pg的原理。*

                    3.4 备库 pgpool.conf配置

                      # - pgpool Connection Settings -
                      listen_addresses = '*'
                      port = 9999
                      # - pgpool Communication Manager Connection Settings -
                      pcp_listen_addresses = '*'
                      pcp_port = 9898
                      # - Backend Connection Settings -
                      backend_hostname0 = '192.168.20.155'
                      backend_port0 = 5432
                      backend_weight0 = 1
                      backend_data_directory0 = '/data/pgsql/12/data'
                      backend_flag0 = 'ALLOW_TO_FAILOVER'
                      backend_hostname1 = '192.168.20.156'
                      backend_port1 = 5432
                      backend_weight1 = 1
                      backend_data_directory1 = '/data/pgsql/12/data'
                      backend_flag1 = 'ALLOW_TO_FAILOVER'
                      # - Authentication -
                      enable_pool_hba = on
                      pool_passwd = 'pool_passwd'
                      authentication_timeout = 60
                      # FILE LOCATIONS
                      pid_file_name = '/usr/local/pgpool2/run/pgpool.pid'
                      # CONNECTION POOLING
                      connection_cache = on
                      max_pool=16
                      num_init_children=100
                      # REPLICATION MODE
                      replication_mode = off
                      # LOAD BALANCING MODE
                      load_balance_mode = on
                      # MASTER/SLAVE MODE
                      master_slave_mode = on
                      master_slave_sub_mode = 'stream'
                      # - Streaming -
                      sr_check_period = 5
                      sr_check_user = 'repl'
                      sr_check_password = 'Rep9102'
                      sr_check_database = 'postgres'
                      # HEALTH CHECK
                      health_check_period = 10
                      health_check_timeout = 10
                      health_check_user = 'pgread'
                      health_check_password = 'pgread_password'
                      health_check_database = 'postgres'
                      # FAILOVER AND FAILBACK
                      failover_command = '/usr/local/pgpool2/run/failover_stream.sh %H %N'
                      # WATCHDOG
                      use_watchdog = on
                      enable_consensus_with_half_votes=on
                      wd_hostname = '192.168.20.155'
                      wd_port = 9000
                      delegate_IP = '192.168.20.157'
                      wd_lifecheck_method='heartbeat'
                      wd_monitoring_interfaces_list='bond0:0'
                      wd_heartbeat_port = 9694
                      wd_priority=100
                      if_cmd_path = '/'
                      if_up_cmd = '/usr/sbin/ifconfig bond0:0 inet $_IP_$ netmask 255.255.255.0'
                      if_down_cmd = '/usr/sbin/ifconfig bond0:0 down'
                      arping_cmd = '/usr/sbin/arping -U $_IP_$ -w 1 -I bond0'
                      # -- heartbeat mode --
                      heartbeat_destination0 = '192.168.20.155'  # 备库上设置主库的ip地址
                      heartbeat_device0 = 'bond0'
                      heartbeat_destination_port0=9694
                      # - Other pgpool Connection Settings -
                      other_pgpool_hostname0 = '192.168.20.155' # 备库上设置主库的ip地址
                      other_pgpool_port0 = 9999
                      other_wd_port0 = 9000
                      # -- for log
                      log_statement = on
                      log_client_messages = on
                      log_line_prefix = '%t-%l : %d %a %u'


                      四、pgpool-II启动关闭

                        #启动
                        mkdir -p var/log/pgpool/
                        pgpool -n -d -D > var/log/pgpool/pgpool.log 2>&1 &
                        #关闭
                        pgpool -m fast stop


                        四、模拟测试

                        模拟几种情况下的pgpool 功能。

                        • 主节点数据库关闭，pgpool 会自动将备库升级为主库，但是vip 并不会切换到备库，并将连接转向备库前端几乎感觉不要连接中断，因为pgpool 会缓存连接。
                        • 主节点上的 pgpool 关闭，由于pgpool 配置了watchdog的，从而具有pgpool的容灾功能，vip 会切换到 备库上 。但是数据库还在 主节点，数据库并不会切换，前端可能会有短暂的连接中断
                        • 关闭主节点服务器，备库会升级为主库，vip也会切换大备节点，前端会有短暂连接中断
                        • 备库、备节点pgpool、备机关闭 ，主节点的pgpool 会将备节点从集群中移除，对前端没有影响
                          备库启动后，需要手动将备库加入到集群
                          一般主库节点id为0 ，备库节点id为1.集群配置好后 通过 psql -h vip -p9999 -Upostgres
                          输入数据postgres的 密码进入，然后通过show pool_nodes
                           可看到集群情况
                          [root@0source_db]# psql -h vip -p9999 -Upostgres
                          Password for user postgres:
                          psql (12.2)
                          Type "help" for help.
                          postgres=# show pool_nodes;
                          node_id |    hostname     | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
                          ---------+-----------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
                          0       | 192.168.20.155 | 5432 | up     | 0.500000  | primary | 19319691   | true              | 0                 |                   |                        | 2020-02-29 04:12:30
                          1       | 192.168.20.156 | 5432 | up   | 0.500000  | standby | 23532      | false             | 0                 |                   |                        | 2020-02-29 08:00:26
                          (2 rows)
                          postgres=#


                           
                           手动将备库加入到pgpool-II集群
                           
                            pcp_attach_node -d -U postgres -h vip -p 9898 -n 1
                            #输入密码即可


                            测试请自行验证，此处就补贴测试过程了，作者很懒。。。

                            五、pool_hba.conf的配置

                            该配置是客户端连接pgpool-II控制的文件，作用和postgresql的 pg_hba.conf作用一样。为了简便，可将pg_hba.conf的内容拷贝到 pool_hba.conf中 。主备节点内容相同

                            六、附录

                            6.1 备库修复

                            有的时候，主备切换时，由于主库数据产生了变化，启动备库时会报时间线不同步。
                            这个时候需要进行修复

                              pg_rewind  --target-pgdata=/data/pgsql/12/data --source-server='host=primary port=5432 user=repl dbname=postgres password=Rep9102'


                              6.2 pgpool常用命令

                              pgpool的一些常用命令

                              6.2.1 Server commands

                              pgpool --   Pgpool-II main server

                              6.2.2 PCP commands

                              pcp_common_options --  common options used in PCP commands
                              pcp_node_count --  displays the total number of database nodes
                              pcp_node_info --  displays the information on the given node ID
                              pcp_watchdog_info --  displays the watchdog status of the Pgpool-II
                              pcp_proc_count --  displays the list of Pgpool-II children process IDs
                              pcp_proc_info --  displays the information on the given Pgpool-II child process ID
                              pcp_pool_status --  displays the parameter values as defined in pgpool.conf
                              pcp_detach_node --  detaches the given node from Pgpool-II. Exisiting connections to Pgpool-II are forced to be disconnected.
                              pcp_attach_node --  attaches the given node to Pgpool-II.
                              pcp_promote_node --  promotes the given node as new master to Pgpool-II
                              pcp_stop_pgpool --  terminates the Pgpool-II process
                              pcp_recovery_node --  attaches the given backend node with recovery

                              6.2.3 Other commands

                              pg_md5 --  produces encrypted password in md5
                              pg_enc --  AES256 password encryption utility
                              pgproto --  tests PostgreSQL or any other servers that understand the frontend/backend protocol.
                              pgpool_setup --  Create a temporary installation of Pgpool-II cluster
                              watchdog_setup --  Create a temporary installation of Pgpool-II clusters with watchdog
                              #### 6.2.4 SQL type commands
                              PGPOOL SHOW -- show the value of a configuration parameter
                              PGPOOL SET -- change a configuration parameter
                              PGPOOL RESET -- restore the value of a configuration parameter to the default value
                              SHOW POOL STATUS --  sends back the list of configuration parameters with their name, value, and description
                              SHOW POOL NODES --  sends back a list of all configured nodes
                              SHOW POOL_PROCESSES --  sends back a list of all Pgpool-II processes waiting for connections and dealing with a connection
                              SHOW POOL_POOLS --  sends back a list of pools handled by Pgpool-II.
                              SHOW POOL_VERSION --  displays a string containing the Pgpool-II release number.
                              SHOW POOL_CACHE --  displays cache storage statistics

                              6.2.5 pgpool_adm extension

                              pgpool_adm_pcp_node_info --  a function to display the information on the given node ID
                              pgpool_adm_pcp_pool_status --  a function to retrieves parameters in pgpool.conf.
                              pgpool_adm_pcp_node_count --  a function to retrieves number of backend nodes.
                              pgpool_adm_pcp_attach_node --  a function to attach given node ID
                              pgpool_adm_pcp_detach_node --  a function to detach given node ID


                              
                
                               
                               postgresqlpostgresql
                                 
                              文章转载自三杯酒coO，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
                                 
                              评论