• pacemaker+corosync 搭建一主两从PG集群

    一、OS配置
    1.1环境规划
    环境：centos 7
    数据库：pg11.5
    ip地址：
    node1  10.122.166.120 
    node2  10.122.166.127 
    node3  10.122.166.128 

    vip：
    10.122.166.123
    10.122.166.133

    1.2系统配置
    关闭selinux ‐‐‐‐> /etc/selinux/config
    关闭防火墙 
    systemctl stop firewalld.service 
    systemctl disable firewalld.service
    修改主机名 
    hostnamectl set‐hostname nodeX 
    编辑 /etc/hosts 文件 
    10.122.166.120 node1
    10.122.166.127 node2
    10.122.166.128 node3

    二、数据库安装
    2.1 所有节点安装PG软件（以node1为例）
    1)安装依赖包

    [root@node1 ~]# yum  install gcc gcc-c++ flex bison readline-devel zlib-devel -y

    2)创建用户

    1. [root@node1 ~]# groupadd postgres 
    2. [root@node1 ~]# useradd postgres -g postgres 
    3. [root@node1 ~]# echo "pg123"|passwd postgres --stdin

    3)编译安装PG软件

    1. [root@node1 ~]# tar -xvf postgresql-11.5.tar.gz
    2. [root@node1 ~]# cd postgresql-11.5/
    3. [root@node1 ~]# ./configure --with-segsize=20   --prefix=/data/postgres/
    4. [root@node1 ~]# cd contrib/
    5. [root@node1 ~]# gmake install all

    4)创建数据目录、归档目录

    1. [root@node1 ~]# mkdir -p /data/postgres/data
    2. [root@node1 ~]# mkdir -p /data/postgres/archive_log
    3. [root@node1 ~]# chown -R postgres:postgres /data/postgres
    4. [root@node1 ~]# chmod 0700 /data/postgres/data


    2.2 node1 初始化数据库
    1)初始化数据库

    1. [postgres@node1 ~]$ cd /data/postgres/bin/  
    2. [postgres@node1 bin]$ initdb ‐D /data/postgres/data

    2)修改配置文件

    1. [postgres@node1 data]$ vim postgresql.conf
    2.  
    3. listen_addresses = '*'
    4. port = '5432'
    5. max_connections = '1000'
    6. log_connections = 'yes'
    7. shared_buffers = '4000MB'
    8. effective_cache_size = '1000MB'
    9. temp_buffers = '64MB'
    10. work_mem = '40MB'
    11. max_worker_processes = '32'
    12. max_parallel_workers = '16'
    13. max_parallel_workers_per_gather = '4'
    14. wal_compression = 'on'
    15. log_destination = 'csvlog'
    16. logging_collector = 'on'
    17. log_directory = 'log'
    18. log_truncate_on_rotation = 'on'
    19. log_rotation_size = '30MB'
    20. log_lock_waits = 'on'
    21. lock_timeout = '300000'
    22. wal_level = replica
    23. synchronous_commit = on
    24. archive_mode = on
    25. archive_command = 'cp %p /data/postgres/archive_log/%f'
    26. max_wal_senders=5
    27. wal_keep_segments = 32
    28. hot_standby = on
    29. wal_log_hints = 'on'
    30. log_statement = 'ddl'
    1. [postgres@node1 data]$ vim pg_hba.conf
    2.  
    3. host all all 10.122.166.0/24 trust
    4. host replication all 10.122.166.0/24 trust

    3)添加环境变量

    1. [postgres@node1 ~]$ vi ~/.bash_profile
    2.  
    3. export PATH=/data/postgres/bin/:$PATH
    4. export PGDATA=/data/postgres/data
    5. export LD_LIBRARY_PATH=/data/postgres/lib:${LD_LIBRARY_PATH}
    6.  
    7. [postgres@node1 ~]$ source ~/.bash_profile

    4)启动数据库

    [postgres@node1 ~]$ pg_ctl start

    2.3 node2/node3 创建备库
    1)以 node2 为例

    [postgres@node2 data]$ pg_basebackup -h 10.122.166.120 -p 5432 -U postgres  -D /data/postgres/data

    2)配置recovery.conf 文件

    1. [postgres@node2 data]$ vim /data/postgres/data/recovery.conf
    2. standby_mode = on  
    3. primary_conninfo = 'host=10.122.166.120 port=5432 user=postgres password=postgres'  
    4. recovery_target_timeline = 'latest'

    3)启动备库

    [postgres@node2 ~]$ pg_ctl start

    2.4 node1 查看集群状态

    1. postgres=# select application_name,client_addr,sync_state from pg_stat_replication;
    2.  application_name |  client_addr   | sync_state
    3. ------------------+----------------+------------
    4.  node2            | 10.122.166.127 | async
    5.  node3            | 10.122.166.128 | async
    6. (2 rows)

    2.5 所有节点停止 PG服务
    先备库 后主库

    pg_ctl -D  stop -m f

    三、配置corosync

    1)所有节点安装软件

    yum install -y pacemaker corosync pcs

    2)配置 corosync.conf文件

    1. [root@node1 ~]# cd /etc/corosync
    2. [root@node1 corosync]# vim corosync.conf
    3. totem {
    4.         max_messages:   20
    5.         vsftype:            none
    6.         crypto_cipher:      none
    7.         secauth:            on
    8.         token_retransmits_before_loss_const:    10
    9.         interface {
    10.                 bindnetaddr:    10.122.166.0
    11.                 mcastaddr:      226.94.61.22
    12.                 ringnumber:     0
    13.                 mcastport:      5405
    14.                 ttl:    1
    15.         }
    16.         consensus:              6000
    17.         clear_node_high_bit:    yes
    18.         crypto_hash:            none
    19.         rrp_mode:           none
    20.         join:                   60
    21.         cluster_name:           cluster
    22.         token:                  5000
    23.         version:                2
    24.         transport:              udp
    25.         ip_version:             ipv4
    26.         heartbeat_failures_allowed : 3
    27. }
    28. logging {
    29.         to_logfile:         yes
    30.         timestamp:      on
    31.         syslog_facility:     daemon
    32.         logger_subsys {
    33.                 debug:    off
    34.                 subsys:     QUORUM
    35.         }
    36.         to_syslog:          yes
    37.         debug:              off
    38.         logfile:             /var/log/corosync.log      
    39.           to_stderr:          no
    40.         fileline:           off
    41. }
    42. quorum {
    43.         expected_votes:     3
    44.         two_node:        0
    45.         provider:            corosync_votequorum
    46. }
    47.  
    48. nodelist {
    49.         node {
    50.                 ring0_addr: node1
    51.                 nodeid: 1
    52.         }
    53.         node {
    54.                 ring0_addr: node2
    55.                 nodeid: 2
    56.         }
    57.         node {
    58.                 ring0_addr: node3
    59.                 nodeid: 3
    60.         }
    61. }


    3)复制到另外两个节点

    1. [root@node1 corosync]# scp corosync.conf 10.122.166.127:/etc/corosync 
    2. [root@node1 corosync]# scp corosync.conf 10.122.166.128:/etc/corosync

    4)所有节点启动corosync服务

    service corosync start

    四、配置 pacemaker

    1)所有节点启动 pacemaker服务 

    service pacemaker start

    2)任意节点使用root用户检查状态

    1. [root@node3 cib]# crm_mon -Afr -1
    2. Stack: corosync
    3. Current DC: node2 (version 1.1.23-1.el7_9.1-9acf116022) - partition with quorum
    4. Last updated: Thu Sep 29 13:59:07 2022
    5. Last change: Thu Sep 29 13:58:59 2022 by hacluster via crmd on node2
    6.  
    7. 3 nodes configured
    8. 0 resource instances configured
    9.  
    10. Online: [ node1 node2 node3 ]
    11.  
    12. No resources
    13.  
    14.  
    15. Node Attributes:
    16. * Node node1:
    17. * Node node2:
    18. * Node node3:
    19.  
    20. Migration Summary:
    21. * Node node2:
    22. * Node node1:
    23. * Node node3:

    3)node1 编辑config.pcs文件

    1. [root@node1 ~]# cd /var/lib/pacemaker/
    2. [root@node1 pacemaker]# vim config.pcs
    3.  
    4. pcs cluster cib pgsql_cfg
    5.  
    6. pcs -f pgsql_cfg property set no-quorum-policy="ignore"
    7. pcs -f pgsql_cfg property set stonith-enabled="false"
    8. pcs -f pgsql_cfg resource defaults resource-stickiness="INFINITY"
    9. pcs -f pgsql_cfg resource defaults migration-threshold="1"
    10.  
    11. pcs -f pgsql_cfg resource create vip-master IPaddr2 \
    12.    ip="10.122.166.123" \
    13.    nic="eth0" \
    14.    cidr_netmask="24" \
    15.    op start   timeout="60s" interval="0s"  on-fail="restart" \
    16.    op monitor timeout="60s" interval="10s" on-fail="restart" \
    17.    op stop    timeout="60s" interval="0s"  on-fail="block"
    18.  
    19. pcs -f pgsql_cfg resource    create vip-slave IPaddr2 \
    20.    ip="10.122.166.133" \
    21.    nic="eth0" \
    22.    cidr_netmask="24" \
    23.    meta migration-threshold="0" \
    24.    op start   timeout="60s" interval="0s"  on-fail="stop" \
    25.    op monitor timeout="60s" interval="10s" on-fail="restart" \
    26.    op stop    timeout="60s" interval="0s"  on-fail="ignore"
    27.  
    28. pcs -f pgsql_cfg resource create pgsql pgsql \
    29.    pgctl="/data/postgres/bin/pg_ctl" \
    30.    psql="/data/postgres/bin/psql" \
    31.    pgdata="/data/postgres/data/" \
    32.    rep_mode="sync" \
    33.    node_list="node1 node2 node3" \
    34.    restore_command="cp %p /data/postgres/archive_log/%f" \
    35.    primary_conninfo_opt="keepalives_idle=60 keepalives_interval=5 keepalives_count=5" \
    36.    master_ip="10.122.166.123" \
    37.    restart_on_promote='true' \
    38.    op start   timeout="60s" interval="0s"  on-fail="restart" \
    39.    op monitor timeout="60s" interval="4s" on-fail="restart" \
    40.    op monitor timeout="60s" interval="3s"  on-fail="restart" role="Master" \
    41.    op promote timeout="60s" interval="0s"  on-fail="restart" \
    42.    op demote  timeout="60s" interval="0s"  on-fail="stop" \
    43.    op stop    timeout="60s" interval="0s"  on-fail="block" \
    44.    op notify  timeout="60s" interval="0s"
    45.  
    46. pcs -f pgsql_cfg resource master pgsql-cluster pgsql \
    47.    master-max=1 master-node-max=1 clone-max=3 clone-node-max=1 notify=true
    48.    
    49. pcs -f pgsql_cfg resource group add master-group vip-master           
    50. pcs -f pgsql_cfg resource group add slave-group vip-slave
    51.                  
    52. pcs -f pgsql_cfg constraint colocation add master-group with master pgsql-cluster INFINITY    
    53. pcs -f pgsql_cfg constraint order promote pgsql-cluster then start master-group symmetrical=false score=INFINITY                                                                                        
    54. pcs -f pgsql_cfg constraint order demote  pgsql-cluster then stop  master-group symmetrical=false score=0   
    55.                                                                                                              
    56. pcs -f pgsql_cfg constraint colocation add slave-group with slave pgsql-cluster INFINITY         
    57. pcs -f pgsql_cfg constraint order promote pgsql-cluster then start slave-group symmetrical=false score=INFINITY                                                                                                    
    58. pcs -f pgsql_cfg constraint order demote  pgsql-cluster then stop  slave-group symmetrical=false score=0 
    59.  
    60. pcs cluster cib-push  pgsql_cfg


    4)所有节点增加 root用户的 PATH路径

    1. [root@node1 ~]# vim .bash_profile  
    2. export PATH=/data/postgres/bin/:$PATH 
    3. [root@node1 ~]# source .bash_profile

    5)node1 加载配置

    1. [root@node1 pacemaker]# sh config.pcs
    2. Warning: Defaults do not apply to resources which override them with their own defined values
    3. Warning: Defaults do not apply to resources which override them with their own defined values
    4. Assumed agent name 'ocf:heartbeat:IPaddr2' (deduced from 'IPaddr2')
    5. Assumed agent name 'ocf:heartbeat:IPaddr2' (deduced from 'IPaddr2')
    6. Assumed agent name 'ocf:heartbeat:pgsql' (deduced from 'pgsql')
    7. Adding pgsql-cluster master-group (score: INFINITY) (Options: first-action=promote then-action=start symmetrical=false)
    8. Adding pgsql-cluster master-group (score: 0) (Options: first-action=demote then-action=stop symmetrical=false)
    9. Adding pgsql-cluster slave-group (score: INFINITY) (Options: first-action=promote then-action=start symmetrical=false)
    10. Adding pgsql-cluster slave-group (score: 0) (Options: first-action=demote then-action=stop symmetrical=false)
    11. CIB updated

    6)等待一会，再次检查状态

    1. [root@node1 ~]#  crm_mon -Afr -1
    2. Stack: corosync
    3. Current DC: node2 (version 1.1.23-1.el7_9.1-9acf116022) - partition with quorum
    4. Last updated: Thu Sep 29 15:14:27 2022
    5. Last change: Thu Sep 29 14:15:00 2022 by root via crm_attribute on node1
    6.  
    7. 3 nodes configured
    8. 5 resource instances configured
    9.  
    10. Online: [ node1 node2 node3 ]
    11.  
    12. Full list of resources:
    13.  
    14.  Master/Slave Set: pgsql-cluster [pgsql]
    15.      Masters: [ node1 ]
    16.      Slaves: [ node2 node3 ]
    17.  Resource Group: master-group
    18.      vip-master (ocf::heartbeat:IPaddr2):       Started node1
    19.  Resource Group: slave-group
    20.      vip-slave  (ocf::heartbeat:IPaddr2):       Started node2
    21.  
    22. Node Attributes:
    23. * Node node1:
    24.     + master-pgsql                      : 1000
    25.     + pgsql-data-status                 : LATEST
    26.     + pgsql-master-baseline             : 000000002D000098
    27.     + pgsql-status                      : PRI
    28. * Node node2:
    29.     + master-pgsql                      : 100
    30.     + pgsql-data-status                 : STREAMING|SYNC
    31.     + pgsql-status                      : HS:sync
    32. * Node node3:
    33.     + master-pgsql                      : -INFINITY
    34.     + pgsql-data-status                 : STREAMING|ASYNC
    35.     + pgsql-status                      : HS:async
    36.  
    37. Migration Summary:
    38. * Node node2:
    39. * Node node1:
    40. * Node node3:

    7)任意节点使用 pcs命令检查状态

    1. [root@node1 ~]# pcs status
    2. Cluster name: cluster
    3. Stack: corosync
    4. Current DC: node2 (version 1.1.23-1.el7_9.1-9acf116022) - partition with quorum
    5. Last updated: Thu Sep 29 15:15:08 2022
    6. Last change: Thu Sep 29 14:15:00 2022 by root via crm_attribute on node1
    7.  
    8. 3 nodes configured
    9. 5 resource instances configured
    10.  
    11. Online: [ node1 node2 node3 ]
    12.  
    13. Full list of resources:
    14.  
    15.  Master/Slave Set: pgsql-cluster [pgsql]
    16.      Masters: [ node1 ]
    17.      Slaves: [ node2 node3 ]
    18.  Resource Group: master-group
    19.      vip-master (ocf::heartbeat:IPaddr2):       Started node1
    20.  Resource Group: slave-group
    21.      vip-slave  (ocf::heartbeat:IPaddr2):       Started node2
    22.  
    23. Daemon Status:
    24.   corosync: active/disabled
    25.   pacemaker: active/disabled
    26.   pcsd: inactive/disabled

    8)在 node1(主库)查看状态

    1. postgres=# select application_name,client_addr,sync_state from pg_stat_replication;
    2.  application_name |  client_addr   | sync_state
    3. ------------------+----------------+------------
    4.  node2            | 10.122.166.127 | sync
    5.  node3            | 10.122.166.128 | async
    6. (2 rows)


    安装 crmsh

    1. [root@ceph1 ~]# wget -P /etc/yum.repos.d/ http://download.opensuse.org/repositories/network:/ha-clustering:/Stable/CentOS_CentOS-7/network:ha-clustering:Stable.repo
    2. [root@ceph1 ~]# yum install -y crmsh
    3. [root@ceph1 ~]# crm configure property stonith-enabled=false

    使用 crm 命令

    1. [root@node1 ~]# crm status
    2. Stack: corosync
    3. Current DC: node2 (version 1.1.23-1.el7_9.1-9acf116022) - partition with quorum
    4. Last updated: Thu Sep 29 15:18:04 2022
    5. Last change: Thu Sep 29 14:15:00 2022 by root via crm_attribute on node1
    6.  
    7. 3 nodes configured
    8. 5 resource instances configured
    9.  
    10. Online: [ node1 node2 node3 ]
    11.  
    12. Full list of resources:
    13.  
    14.  Master/Slave Set: pgsql-cluster [pgsql]
    15.      Masters: [ node1 ]
    16.      Slaves: [ node2 node3 ]
    17.  Resource Group: master-group
    18.      vip-master (ocf::heartbeat:IPaddr2):       Started node1
    19.  Resource Group: slave-group
    20.      vip-slave  (ocf::heartbeat:IPaddr2):       Started node2


    常用命令

    1. 停止集群服务：            service pacemaker stop (会自动停止PG)
    2. 启动集群服务：            service pacemaker start(会自动启动PG)
    3. 查看集群状态：            crm_mon -Afr -1
    4. 查看集群状态且启动刷新:    crm_mon -Afr
    5. 刷新集群状态：            crm resource cleanup pgsql-cluster
    6. 刷新某一个节点的状态：     crm resource cleanup pgsql nodename
    7. 清理历史告警:             crm resource cleanup

  • 相关阅读:
    Spark_SQL-DataFrame数据写出以及读写数据库（以MySQl为例）
    Electron 打包exe方法
    gRPC集成protoc-gen-validate
    Java中List不同实现类的对比
    WebFlux 详解
    数据通信网络基础
    关于支付宝授权用户信息
    docker&kubernets中级篇（十）
    vulnhub Loly: 1
    面对6G时代 适合通信专业的 毕业设计题目
  • 原文地址：https://blog.csdn.net/silenceray/article/details/127111305