基于Pgpool搭建PostgreSQL11的集群

2020 年 5 月 15 日 by Leave a Comment



    • 基于Pgpool-II4.1.0搭建PostgreSQL11集群

    Pgpool介绍

    pgpool相关介绍不在该文章中，需要者可以参考我的另一篇文章。传送门：pgpool介绍

    环境准备

    本次使用三台CentOS7.3的服务器来搭建集群，首先在三台服务器上部署PostgreSQL11.6版本的数据库，部署教程大家可以网上自行查找。具体规划如下：

    架构图参考官网：

    搭建配置

    01服务器配置

    故障转移、在线恢复时需要ssh到其它服务器执行命令，故需要配置服务器之间无密码ssh登录（如服务器之间已经配置ssh可以跳过该节，但是需要修改failover.sh等脚本）

         [all servers]# cd ~/.ssh
    [all servers]# ssh-keygen -t rsa -f id_rsa_pgpool
    [all servers]# ssh-copy-id -i id_rsa_pgpool.pub postgres@server1
    [all servers]# ssh-copy-id -i id_rsa_pgpool.pub postgres@server2
    [all servers]# ssh-copy-id -i id_rsa_pgpool.pub postgres@server3

    [all servers]# su postgres
    [all servers]$ cd ~/.ssh
    [all servers]$ ssh-keygen -t rsa -f id_rsa_pgpool
    [all servers]$ ssh-copy-id -i id_rsa_pgpool.pub postgres@server1
    [all servers]$ ssh-copy-id -i id_rsa_pgpool.pub postgres@server2
    [all servers]$ ssh-copy-id -i id_rsa_pgpool.pub postgres@server3

    02pgpool安装

    pgpool安装不在该文章中介绍，需要者可以参考我另一篇文章。传送门：pgpool安装。

    03PostgreSQL数据库配置

    WAL归档
    需要WAL归档的可自行配置，该示例暂时未使用WAL归档

        [all servers]# su - postgres
    [all servers]$ mkdir /var/lib/pgsql/archivedir
    ## postgres.conf 配置
    archive_mode = on
    archive_command = 'cp "%p" "/var/lib/pgsql/archivedir/%f"'

    这儿有一个归档的脚本，使用该脚本可以自行修改保留多少天归档 pg_archive.sh

    postgres.conf配置
    该配置仅在主节点配置，从节点使用pgpool的在线恢复功能配置

       listen_addresses = '*'
    port = 5432
    max_wal_senders = 10
    max_replication_slots = 10
    wal_level = replica
    hot_standby = on
    wal_log_hints = on

    数据库用户以及密码配置

        [server1]# psql -U postgres -p 5432
    postgres=# CREATE ROLE pgpool WITH LOGIN PASSWORD 'pgpool';
    postgres=# CREATE ROLE repl WITH REPLICATION LOGIN PASSWORD 'repl';
    ## 用于SHOW POOL_NODES 展示 "replication_state" and "replication_sync_state"
    postgres=# GRANT pg_monitor TO pgpool;

    pg_hba.con配置
    添加用户验证配置，将repl复制用户添加到pg_hba中。此文档中所有密码使用md5验证。

      host    all             all             0.0.0.0/0               md5
    host    replication     repl            0.0.0.0/0               md5

    配置.pgpass文件用于无密码操作

    由于在故障转移、在线恢复时使用脚本进行操作，脚本中使用pg_basebakup、pg_rewind等命令，所以需要配置无密码操作

    在postgres用户的home目录下创建.pgpass文件，并且文件权限为600。

       [all servers]# su - postgres
    [all servers]$ vi ~/.pgpass
    ## 格式为：hostname:port:database:username:password
    server1:5432:replication:repl:
    server2:5432:replication:repl:
    server3:5432:replication:repl:
    server1:5432:postgres:postgres:
         server2:5432:postgres:postgres:
         server3:5432:postgres:postgres:
         [all servers]$ chmod 600  ~/.pgpass

    04pgpool配置

    1、# cp -p /usr/local/pgpool/etc/pgpool.conf.sample-stream /usr/local/pgpool/etc/pgpool.conf

    2、配置PostgreSQL数据库信息

        # - Backend Connection Settings -

    # 有几台PostgreSQL数据库，配置几个后端信息，使用后缀名0、1、2……

    backend_hostname0 = 'server1' # Host name or IP address to connect to for backend 0
    backend_port0 = 5432 # Port number for backend 0
    backend_weight0 = 1 # Weight for backend 0 (only in load balancing mode)
    backend_data_directory0 = '/data/pgsql/sport/' # Data directory for backend 0
    # Controls various backend behavior
    # ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER
    backend_flag0 = 'ALLOW_TO_FAILOVER'

    backend_hostname1 = 'server2'
    backend_port1 = 5432
    backend_weight1 = 1
    backend_data_directory1 = '/var/lib/pgsql/11/data'
    backend_flag1 = 'ALLOW_TO_FAILOVER'

    backend_hostname2 = 'server3'
    backend_port2 = 5432
    backend_weight2 = 1
    backend_data_directory2 = '/var/lib/pgsql/11/data'
    backend_flag2 = 'ALLOW_TO_FAILOVER'

    3、基础配置

      listen_addresses = '*'
    pid_file_name = '/var/run/pgpool/pgpool.pid'
    # PID file name
    # Can be specified as relative to the"
    # location of pgpool.conf file or
    # as an absolute path
    # (change requires restart)
    logdir = '/var/run/pgpool'
    # Directory of pgPool status file
    # (change requires restart)

    创建pgpool运行需要目录mkdir -p /var/run/pgpool

    4、配置复制延迟检查

     sr_check_user = 'pgpool'
    ## 自从4.0版本后，如果密码设置为的话，pgpool会首先从`pool_passwd`文件中获取密码，然后在使用空密码
    sr_check_password = 'pgpool'

    5、配置健康检查

       # Health check period
    # Disabled (0) by default
    health_check_period = 5

    # Health check timeout
    # 0 means no timeout
    health_check_timeout = 30

    health_check_user = 'pgpool'
    health_check_password = 'pgpool'

    health_check_max_retries = 3

    6、配置故障转移

       failover_command = '/usr/locla/pgpool/etc/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
    follow_master_command = '/usr/locla/pgpool/etc/follow_master.sh %d %h %p %D %m %H %M %P %r %R'

    关于脚本会在后面给出下载地址以及脚本使用说明

    # 给脚本设置执行权限，注意：该脚本需要在三台pgpool服务器中都需要创建
    chmod +x /usr/local/pgpoll/etc/{failover.sh,follow_master.sh}

    7、配置在线恢复

    为了使用Pgpool-II执行在线恢复，我们需要配置PostgreSQL用户名和在线恢复命令recovery_1st_stage。由于执行在线恢复需要PostgreSQL中的超级用户特权，因此我们在recovery_user中指定postgres用户。然后，我们在PostgreSQL主服务器（server1）的数据库目录中创建recovery_1st_stage和pgpool_remote_start，并添加执行权限。

       recovery_user = 'postgres'
    # Online recovery user
    recovery_password = 'postgres'
    # Online recovery password

    recovery_1st_stage_command = 'recovery_1st_stage'

    ## 该脚本只需要在数据库主节点创建，后续使用在线恢复时会复制过去
    [server1]# su - postgres
    [server1]$ vi /data/pgsql/sport/recovery_1st_stage
    [server1]$ vi /data/pgsql/sport/pgpool_remote_start
    [server1]$ chmod +x /data/pgsql/sport/{recovery_1st_stage,pgpool_remote_start}

    数据库创建扩展，该扩展是为了能够执行在线恢复（如果在安装时已经创建该扩展则可以跳过该步骤）

        [server1]# su - postgres
    [server1]$ psql template1 -c "CREATE EXTENSION pgpool_recovery"

    8、配置客户端身份验证

    ## pgpool.conf中
    enable_pool_hba = on

    身份验证文件为/usr/local/pgpool/etc/pool_hba.conf，配置方式与PostgreSQL基本一样。(scram-sha-256方式可参考pgpool官网)

      host    all         pgpool           0.0.0.0/0          md5
    host    all         postgres         0.0.0.0/0          md5
    cd /usr/local/pgpool/etc
    ../bin/pg_md5 -p -m -u postgres pool_passwd
    ../bin/pg_md5 -p -m -u pgpool pool_passwd
    cat /etc/pgpool-II/pool_passwd
    # pgpool:AESheq2ZMZjynddMWk5sKP/Rw==
    # postgres:AESHs/pWL5rtXy2IwuzroHfqg==

    9、看门狗配置

    use_watchdog = on
    delegate_IP = '192.168.111.6' ##vip配置
    ## 网卡名字需要正确配置
    if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev enp0s8 label enp0s8:0'
    if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev enp0s8'
    arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I enp0s8'

    配置其它pgpool信息，注意：该配置在三台服务器不一样，只需要配置另外几台即可
    [server1配置如下]
    # - Other pgpool Connection Settings -

    other_pgpool_hostname0 = 'server2'
    other_pgpool_port0 = 9999
    other_wd_port0 = 9000

    other_pgpool_hostname1 = 'server3'
    other_pgpool_port1 = 9999
    other_wd_port1 = 9000

    heartbeat_destination0 = 'server2'
    heartbeat_destination_port0 = 9694
    heartbeat_device0 = ''

    heartbeat_destination1 = 'server3'
    heartbeat_destination_port1 = 9694
    heartbeat_device1 = ''
    [server2配置如下]
    # - Other pgpool Connection Settings -

    other_pgpool_hostname0 = 'server1'
    other_pgpool_port0 = 9999
    other_wd_port0 = 9000

    other_pgpool_hostname1 = 'server3'
    other_pgpool_port1 = 9999
    other_wd_port1 = 9000

    heartbeat_destination0 = 'server1'
    heartbeat_destination_port0 = 9694
    heartbeat_device0 = ''

    heartbeat_destination1 = 'server3'
    heartbeat_destination_port1 = 9694
    heartbeat_device1 = ''
    [server3配置如下]
    # - Other pgpool Connection Settings -

    other_pgpool_hostname0 = 'server1'
    other_pgpool_port0 = 9999
    other_wd_port0 = 9000

    other_pgpool_hostname1 = 'server2'
    other_pgpool_port1 = 9999
    other_wd_port1 = 9000

    heartbeat_destination0 = 'server1'
    heartbeat_destination_port0 = 9694
    heartbeat_device0 = ''

    heartbeat_destination1 = 'server2'
    heartbeat_destination_port1 = 9694
    heartbeat_device1 = ''

    10、pcp命令配置

    cd /usr/local/pgpool/bin
    echo 'pgpool:'`pg_md5 pgpool` >> /usr/local/pgpool/etc/pcp.conf

    以上pgpool相关配置都配置完成，最终会生成pgpool.conf、pool_hba.conf、pool_passwd、pcp.conf，可以配置完一台后，在其它服务器copy配置文件即可。注意：pgpool.conf中看门狗配置需要在其它服务器修改一下。

    05相关脚本

    此处一共需要四个脚本分别为:

    failover.sh 目录为: /usr/local/pgpool/etc/下

    follow_master.sh 目录为: /usr/local/pgpool/etc/下

    recovery_1st_stage 目录为：PostgreSQL数据库数据目录下

    pgpool_remote_start目录为：PostgreSQL数据库数据目录下

    脚本地址：https://github.com/MrSmallLiu/pgpool

    脚本参考于官网，但是稍有改动：

    创建与删除复制槽时，如果使用ip地址，则会报错，因为名字不允许有.，修改：${FAILED_NODE_HOST//./_}

    follow_master.sh脚本中一处bug

    # drop replication slot
    ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${NEW_MASTER_NODE_HOST} -i ~/.ssh/id_rsa_pgpool "
    ${PGHOME}/bin/psql -p ${NEW_MASTER_NODE_PORT} -c \"SELECT pg_drop_replication_slot('${FAILED_NODE_HOST//./_}')\"
    "

    注意：

    注意所有脚本中 PGHOME变量为PostgreSQL安装路径

    注意所有脚本中ARCHIVEDIR，如果未使用，需要将其注释，并且修改相应使用地方

    注意所有脚本中PGPOOL_PATH变量为pgpool的bin路径

    注意recovery_1st_stage脚本中PRIMARY_NODE_HOST变量，该变量取hostname值，可以先在自己服务器测试一下该命令是否符合预期。不符合者使用hostnamectl set-hostname server1

    启动

    后台运行
    /usr/local/pgpool/bin/pgpool

    debug运行
    /usr/local/pgpool/bin/pgpool -n -d
    /usr/local/pgpool/bin/pgpool -n

    standby 数据库启动

    -n 表示在pgpool.conf配置文件中PostgreSQL配置的后面的序号
    /usr/local/pgpool/bin/pcp_recovery_node -h 192.168.111.6 -p 9898 -U pgpool -n 1
    Password:
    pcp_recovery_node -- Command Successful

    /usr/local/pgpool/bin/pcp_recovery_node -h 192.168.111.6 -p 9898 -U pgpool -n 2
    Password:
    pcp_recovery_node -- Command Successful

    停止

    /usr/local/pgpool/bin/pgpool -m fast stop

    测试

    使用客户端连接（navicat、pgAdmin等）
    psql -h 192.168.111.6 -p 9999 -U passwd：postgres

    执行show pool_nodes

    负载均衡测试

    可以配置PostgreSQL日志打印sql语句，使用vip地址连接上后测试输出即可看到查询会负载到三台服务器（默认根据连接使用负载均衡，需要几次新建查询来测试）

    自动故障转移测试

    可以使用PostgreSQL命令停掉主节点数据库来测试故障转移

    写在最后

    以上配置经过我们部署测试是可以正常运行的，但是是后续整理才写的该篇文章，可能存在遗漏不足的地方。小伙伴在配置过程中有疑惑、配置后未能正常启动、有文档意见都可以与我联系。随时欢迎提给我，然后我进行修改文档，以帮助更多人。

    后面抽时间整理一个pgpool的部署流程图，方便大家理解部署流程。

    关于配置文件的具体讲解以及优化会在后面再写相关文档，欢迎持续关注

    关于该文章，欢迎各位批评指正

    关于作者

    GISer
    小刘先森
    QQ: 1016817543
    邮箱：1016817543@qq.com
    github：https://github.com/MrSmallLiu (欢迎star)

    刘航，国信司南（北京）地理信息技术有限公司，技术经理。负责过多个基于 PostgreSQL 数据库以及 PostGIS 的大中型项目。公司矢量切片产品（基于 PostgreSQL 数据库）研发核心人员。

    Filed Under: PostgreSQL

    Speak Your Mind

    *

    京ICP备14059771号-2