基于Pgpool的Postgresql12主备异步流复制高可用架构部署

写在前面

本帖子基于配置环境如下：

内容	版本
操作系统	Centos7.4
Postgresql	12.6-1
Pgpool	4.2.2

整体架构：

一、PG数据库安装（主备节点）

下载所需RPM包

		网盘链接：https://pan.baidu.com/s/1-QPf_vEK8lEihtiZ919Nmg
		密码：hx3u

以下操作请在主备节点上分别以root用户执行：

1. 在root下创建路径/root/postgredb，并将文件postgre10_postgis24_pgpool.tar.gz中的所有文件解压到所创建的路径下

   
   mkdir -p /root/postgredb;cd /root/postgredb
   unzip Postgres.zip
   
   

2. 在/etc/yum.repos.d路径下，创建repo文件：postgresql.repo，内容如下

   
   [postgres]
   name=postgres
   baseurl=file:///root/postgredb/Postgres
   enabled=1
   gpgcheck=0
   
   

3. 执行如下命令，使PG数据库的yum生效

   
   yum clean all
   yum repolist
   
   

4. 相关RPM包安装

   
   yum install -y pgpool-II-pg12 pgpool-II-pg12-debuginfo pgpool-II-pg12-devel pgpool-II-pg12-extensions postgis31_12 postgis31_12-client postgresql12 postgresql12-server gcc
   
   

5. 修改postgres用户密码

   
   echo '1qaz)P(O' | passwd postgres --stdin
   
   

二、PG数据库配置（主备节点）

1. 自定义systemd服务，修改PGDATA存储目录位置

   
   vi /usr/lib/systemd/system/postgresql-12.service
   
   将下面路径修改为PG数据库数据文件目录的绝对路径
   Environment=PGDATA=/pgsql/data/
   
   

2. 通过下面命令，让配置生效

   
   systemctl daemon-reload
   
   

3. 创建PG数据库文件存储目录，并修改属主给postgres用户

   tips：postgres用户在pg数据库安装时候会自动创建

   
   mkdir -p /pgsql/data/
   chown -R postgres:postgres /pgsql
   
   

4. 通过下面命令对PG数据库进行初始化

   tips：会将数据库所有初始化数据保存到上面步骤创建的/pgsql/data/下

   
   postgresql-12-setup initdb
   
   

5. 启动数据库

   
   systemctl start postgresql-12
   
   

6. 防火墙放开端口5432

   tips：如果需要开启firewalld的话，IP段需要按照实际情况配置，这里网段按照“192.168.50.0/24”举例

   
   firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.50.0/24" port protocol="tcp" port="5432" accept'
   firewall-cmd --reload
   
   

三、PG异步流复制配置（主节点）

1. 登陆主节点的库，创建具有用于传递数据的具有replication权限的用户replicator，并同时修改postgres用户密码为postgres

   
   su - postgres
   psql
   
   

   
   CREATE ROLE replicator login replication password '1qaz)P(O';
   ALTER USER postgres WITH PASSWORD '1qaz)P(O';
   \q
   
   

2. 修改配置文件：/pgsql/data/pg_hba.conf，清空“# TYPE DATABASE USER ADDRESS METHOD”以下所有内容，并在最后添加如下配置，让所有外部访问，包括replication权限的用户以及后续的应用用户，全部需要密码验证

   
   # "local" is for Unix domain socket connections only
   local all all peer
   local replication all peer
   host replication all 0.0.0.0/0 md5
   host replication all ::1/128 md5
   host all all 0.0.0.0/0 md5
   
   

3. 修改主节点库的配置文件postgresql.conf，在原配置文件postgresql.conf的基础上修改,修改内容如下

   
   listen_addresses = '*' 
   port = 5432 
   max_wal_senders = 10 
   wal_level = replica
   hot_standby = on
   wal_keep_segments = 64
   full_page_writes = on
   wal_log_hints = on
   max_connections = 1000
   log_filename = 'postgresql-%Y-%m-%d.log'
   primary_conninfo = 'host=备节点IP地址 port=5432 user= replicator password=1qaz)P(O'
   recovery_target_timeline = 'latest'
   
   

4. postgresql.auto.conf配置，新增内容如下

   
   primary_conninfo = 'user=replicator password=''1qaz)P(O'' host=备节点IP地址 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
   
   

5. 重启pg数据库使配置生效

   
   systemctl restart postgresql-12
   
   

四、PG异步流复制配置（备节点）

1. 登陆备节点，切换到postgres账户，从主库备份创建备库

   
   su - postgres
   rm -rf /pgsql/data/*
   pg_basebackup -D /pgsql/data -Fp -Xs -Pv -R -h 主节点IP -p 5432 -U replicator
   （输入密码）
   
   

   输出内容如下：

   transaction log start point: 0/2000060 on timeline 1
   pg_basebackup: starting background WAL receiver
   22806/22806 kB (100%), 1/1 tablespace
   transaction log end point: 0/2000130
   pg_basebackup: waiting for background process to finish streaming …
   pg_basebackup: base backup completed

2. 备节点的postgresql.conf配置，编辑如下内容

   
   primary_conninfo = 'host=主节点IP地址 port=5432 user= replicator password=1qaz)P(O'
   
   

3. postgresql.auto.conf配置，去掉主节点IP配置，保留备节点IP配置，内容如下

   
   primary_conninfo = 'user=replicator password=''1qaz)P(O'' host=主节点IP地址 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
   
   

4. 重启pg数据库使配置生效

   
   systemctl restart  postgresql-12
   
   

五、PG异步流复制状态查询（主节点）

1. 登录主节点，切换到postgres用户，执行命令psql登录主节点数据库，执行命令

   
   \x
   select * from pg_stat_replication;
   
   

   

   说明：
   pid: WAL发送进程的进程号。
   usename: WAL发送进程的数据库用户名。
   application_name：连接WAL发送进程的应用别名，此参数显示值为备库
   recovery.conf配置文件中primary_conninfo参数application_name选项的值。
   client_addr：连接到WAL发送进程的客户端IP地址，也就是备库的IP。
   backend start: WAL发送进程的启动时间。
   state：显示WAL发送进程的状态，startup表示WAL进程在启动过程中；catchup表示备库正在追赶主库；streaming表示备库已经追赶上了主库，并且主库向备库发送WAL日志流，这个状态是流复制的常规状态；backup表示通过pg_basebackup正在进行备份；stopping表示WAL发送进程正在关闭。
   sentlsn: WAL发送进程最近发送的WAL日志位置。
   write lsn：备库最近写人的WAL日志位置，这时WAL日志流还在操作系统缓存中，还没写人备库WAL日志文件。
   flush lsn：备库最近写人的WAL日志位置，这时WAL日志流已写入备库WAL日志文件。
   replay lsn：备库最近应用的WAL日志位置。
   write_lag：主库上WAL日志落盘后等待备库接收WAL日志（这时WAL日志流还没写人备库WAL日志文件，还在操作系统缓存中）并返回确认信息的时间。
   flush_lag：主库上WAL日志落盘后等待备库接收WAL日志（这时WAL日志流已写人备库WAL日志文件，但还没有应用WAL日志）井返回确认信息的时间。
   replay_lag：主库上WAL日志落盘后等待备库接收WAL日志（这时WAL日志流已写入备库WAL日志文件，并且己应用WAL日志）并返回确认信息的时间。
   sync _priority：基于优先级的模式中备库被选中成为同步备库的优先级，对于基于quorum的选举模式此字段则无影响。
   sync_state：同步状态，有以下状态值，async表示备库为异步同步模式；potential表示备库当前为异步同步模式，如果当前的同步备库岩机，异步备库可升级成为同步备库；sync表示当前备库为同步模式；quorum表示备库为quorumstandbys的候选

六、pgpool配置（主备节点）

1. 修改配置文件：/etc/pgpool-II/pool_hba.conf，与同节点的postgre数据库的/pgsql/data/pg_hba.conf保持一致

   
   # "local" is for Unix domain socket connections only
   local all all md5
   local replication all md5
   host replication all 0.0.0.0/0 md5
   host replication all ::1/128 md5
   host all all 0.0.0.0/0 md5
   
   

2. 生成PG数据库的postgres用户密码的md5值（这里密码是postgres，如果上面设置postgres用户密码为其他，需要对应修改）

   
   echo 'postgres:'`pg_md5 1qaz)P(O` >> /etc/pgpool-II/pcp.conf
   
   

3. 创建pgpool_node_id

   
   节点1：
   echo 0 > /etc/pgpool-II/pgpool_node_id
   
   节点2：
   echo 1 > /etc/pgpool-II/pgpool_node_id
   
   

4. 在pgpool中添加pg数据库的用户名和密码，数据库登录用户是postgres，这里输入登录密码

   
   cd /etc/pgpool-II
   pg_md5 -p -m -u postgres pool_passwd
   password：（这里输入1qaz)P(O，如果密码不是这个，可以对应修改）
   
   

5. 配置系统命令权限

   
   chmod +s /sbin/ifconfig
   chmod +s /sbin/ip
   chmod +s /sbin/ifup
   chmod +s /bin/ping
   chmod +s /sbin/arping
   
   

6. 创建主备切换脚本

   
   touch /etc/pgpool-II/failover_stream.sh
   chmod u+x /etc/pgpool-II/failover_stream.sh
   vi /etc/pgpool-II/failover_stream.sh
   
   

   #! /bin/sh
   # Failover command for streaming replication.
   # Arguments: $1: new master hostname.
   
   new_master=$1
   trigger_command=" /usr/pgsql-12/bin/pg_ctl promote -D /pgsql/data"
   
   # Prompte standby database.
   /usr/bin/ssh -T $new_master $trigger_command
   
   exit 0;
   
   

7. 修改相关目录属主

   
   chown -R postgres:postgres /etc/pgpool-II
   chown -R postgres:postgres /var/run/pgpool
   
   

8. 设置主机ssh互信（两个节点需要做互访白名单，避免绕行，在postgres用户下操作）

   
   su – postgres
   ssh-keygen -t rsa -b 1024（一路回车）
   cd ~/.ssh
   touch authorized_keys
   chmod 600 authorized_keys
   
   将主节点的公钥/var/lib/pgsql/.ssh/id_rsa.pub内容拷贝到salve认证文件authorized_keys，同理将备节点公钥id_rsa.pub内容拷贝到主节点同样目录下的认证文件authorized_keys
   
   

9. 按照流复制创建pgpool.conf配置文件（/etc/pgpool-II目录下操作）

   
   su - postgres
   cd /etc/pgpool-II
   cp pgpool.conf.sample-stream pgpool.conf
   
   

10. 创建日志目录

    
    mkdir /etc/pgpool-II/pgpool_log
    
    

11. 编辑pgpool.conf文件内容，需要修改的配置如下，其他保持默认即可

    
    # - pgpool Connection Settings -
    listen_addresses = '*'
    
    # - Backend Connection Settings -
    backend_hostname0 = '节点0的IP地址'
    backend_port0 = 5432
    backend_weight0 = 1
    backend_data_directory0 = '/pgsql/data'
    backend_flag0 = 'ALLOW_TO_FAILOVER'
    backend_application_name0 = 'postgre12-1'
    
    backend_hostname1 = '节点1的IP地址'
    backend_port1 = 5432
    backend_weight1 = 1
    backend_data_directory1 = '/pgsql/data'
    backend_flag1 = 'ALLOW_TO_FAILOVER'
    backend_application_name1 = 'postgre12-2'
    
    # - Authentication -
    enable_pool_hba = on
    
    # - Concurrent session and pool size –
    # notice : num_init_children*max_pool < max_connections- superuser_reserved_connections
    num_init_children = 200
    
    # This is used when logging to stderr:
    logging_collector = on
    log_directory = '/etc/pgpool-II/pgpool_log'
    log_filename = 'pgpool_%Y-%m-%d.log'
    log_file_mode = 0600
    log_rotation_age = 1d
    log_rotation_size = 0
    
    # - Streaming -
    sr_check_period = 5
    sr_check_user = 'replicator'
    sr_check_password = '1qaz)P(O'
    
    # HEALTH CHECK
    health_check_period = 10
    health_check_user = 'postgres'
    health_check_password = '1qaz)P(O'
    health_check_database = 'postgres'
    
    # FAILOVER AND FAILBACK
    failover_command = '/etc/pgpool-II/failover_stream.sh %H'
    
    # WATCHDOG
    use_watchdog = on
    
    # - Watchdog communication Settings –
    hostname0 = '节点0的IP地址'
    wd_port0 = 9000
    pgpool_port0 = 9999
    hostname1 = '节点1的IP地址'
    wd_port1 = 9000
    pgpool_port1 = 9999
    
    delegate_IP = 'VIP地址'
    #eth0是网卡名，具体需要按照实际业务网卡名为准
    if_up_cmd = 'ip addr add $_IP_$/24 dev eth0 label eth0:0'
    if_down_cmd = 'ip addr del $_IP_$/24 dev eth0'
    arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I enp0s3'
    
    # - Watchdog consensus settings for failover –
    enable_consensus_with_half_votes = on
    
    # -- heartbeat mode –
    heartbeat_hostname0 = '节点0的IP地址'
    heartbeat_port0 = 9694
    heartbeat_device0 = ''
    heartbeat_hostname1 = '节点1的IP地址'
    heartbeat_port1 = 9694
    heartbeat_device1 = ''
    
    

12. 防火墙放开端口9999、9898、9000、9694如果需要开启firewalld的话，IP段需要按照实际情况配置）

    
    firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.50.0/24" port protocol="tcp" port="9999" accept'
    firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.50.0/24" port protocol="tcp" port="9898" accept'
    firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.50.0/24" port protocol="tcp" port="9000" accept'
    firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.50.0/24" port protocol="udp" port="9694" accept'
    firewall-cmd --reload
    
    

13. 分别启动主备节点的pgpool服务

    
    systemctl start pgpool.service
    
    

14. 查询pgpool的状态

    
    pcp_watchdog_info -U postgres