postgresql - 如何在运行 Centos 7 的两个节点上设置 Postgresql 高可用性

Question

我正在尝试为 Postgresql 数据库设置高可用性以在两个节点中同时运行，如果主节点发生故障，自动故障转移将重新分配给从节点并成为可能发生读写的新主节点。我正在使用 Pacemaker 和 Corosync，Postgresql 9.6

这适用于在 Centos7 下运行的两台运行 Postgresql 的服务器。一台服务器/节点作为主服务器（mfsdbtest2；ip-10.22.28.202），另一台作为从服务器（mfsdbtest1；ip-10.22.28.200）。在之前的测试中，我在测试故障转移时手动将从属设备提升为主设备（使用 pg_ctl 提升），但我无法恢复到主设备（mfsdbtest2：具有读/写权限）和从属设备（mfsdbtest1：具有读取权限）只有特权）。

我设置了以下资源；1.) 在 10.22.28.201 托管浮动/虚拟 IP 的 v_ip 资源。2.) 运行显示当前活动节点的基本 HTML 页面的 WebServer 资源。- 即使在故障转移之后，上述两个资源也可以正常工作。

3.）主/从设置：msPostgresql [pgsql] - 显示在 cib(pgsql_cfg) 资源中设置为主/从的两个节点

4.) 资源组：master-group - 包含 vip-master 和 vip-rep 的组

注意：现在整个 PostgreSQL HA 设置都失败了。两个数据库之间的复制运行良好。

对于我的设置，我点击了以下链接：

https://wiki.clusterlabs.org/wiki/PgSQL_Replicated_Cluster

下面是为集群配置的 pgsql 资源的配置：

1. Nodes: 10.22.28.202 - Master Node: mfsdbtest2
          10.22.28.200 - Slave Node: mfsdbtest1

2. IP Configs: 10.22.28.201 - Floating IP (v_ip)
            10.22.28.203 - Replication IP
            10.22.28.205 - Floating IP (Assigned to Slave Node)

pcs cluster cib pgsql_cfg

pcs -f pgsql_cfg property set no-quorum-policy="ignore"
pcs -f pgsql_cfg property set stonith-enabled="false"
pcs -f pgsql_cfg resource defaults resource-stickiness="INFINITY"
pcs -f pgsql_cfg resource defaults migration-threshold="1"

-----The vip-master resource controls the pgsql-vip IP address. It is started on the node hosting the PostgreSQL master resource-----
pcs -f pgsql_cfg resource create vip-master IPaddr2 ip="10.22.28.201" nic="ens192" cidr_netmask="24" op start timeout="60s" interval="0s" on-fail="restart" op monitor timeout="60s" interval="10s" on-fail="restart" op stop timeout="60s" interval="0s" on-fail="block" 

-----The vip-rep resource controls the pgsql-vip IP address. It is started on the node hosting the PostgreSQL master resource-----
pcs -f pgsql_cfg resource create vip-rep IPaddr2 ip="10.22.28.203" nic="ens192" cidr_netmask="24" meta migration-threshold="0" op start timeout="60s" interval="0s" on-fail="stop" op monitor timeout="60s" interval="10s" on-fail="restart" op stop timeout="60s" interval="0s" on-fail="ignore" 

pcs -f pgsql_cfg resource create pgsql ocf:heartbeat:pgsql pgctl="/usr/pgsql-9.6/bin/pg_ctl" psql="/usr/pgsql-9.6/bin/psql" pgdata="/var/lib/pgsql/9.6/data" rep_mode="sync" node_list="mfsdbtest1
 mfsdbtest2" restore_command="cp /archive/db_archive/\%f \%p" master_ip="10.22.28.203" primary_conninfo_opt="keepalives_idle=60 keepalives_interval=5 keepalives_count=5" restart_on_promote='true' op start timeout="60s" interval="0s"  on-fail="restart" op monitor timeout="60s" interval="10s" on-fail="restart" op monitor timeout="60s" interval="9s"  on-fail="restart" role="Master" op promote timeout="60s" interval="0s"  on-fail="restart" op demote timeout="60s" interval="0s" on-fail="stop" op stop timeout="60s" interval="0s" on-fail="block" op notify timeout="60s" interval="0s"


pcs -f pgsql_cfg resource master msPostgresql pgsql master-max=1 master-node-max=1 clone-max=2 clone-node-max=1 notify=true


pcs -f pgsql_cfg resource group add master-group vip-master vip-rep

pcs -f pgsql_cfg constraint colocation add master-group with Master msPostgresql INFINITY
pcs -f pgsql_cfg constraint order promote msPostgresql then start master-group symmetrical=false score=INFINITY
pcs -f pgsql_cfg constraint order demote  msPostgresql then stop  master-group symmetrical=false score=0

pcs cluster cib-push pgsql_cfg

以下是我运行 pcs status 时的结果：

Cluster name: mycluster

WARNINGS:
Corosync and pacemaker node names do not match (IPs used in setup?)

Stack: corosync
Current DC: mfsdbtest2 (version 1.1.19-8.el7_6.4-c3c624ea3d) - partition with
Last updated: Thu Jul 11 09:38:29 2019
Last change: Thu Jul 11 09:25:28 2019 by root via cibadmin on mfsdbtest1

2 nodes configured
6 resources configured

Online: [ mfsdbtest1 mfsdbtest2 ]

Full list of resources:

 v_ip   (ocf::heartbeat:IPaddr2):       Started mfsdbtest1
 WebServer      (ocf::heartbeat:apache):        Started mfsdbtest1
 Master/Slave Set: msPostgresql [pgsql]
     Masters: [ mfsdbtest2 ]
     Slaves: [ mfsdbtest1 ]
 Resource Group: master-group
     vip-master (ocf::heartbeat:IPaddr2):       Stopped
     vip-rep    (ocf::heartbeat:IPaddr2):       Stopped

Failed Actions:
* vip-master_start_0 on mfsdbtest2 'not configured' (6): call=31, status=comp
    last-rc-change='Thu Jul 11 09:33:18 2019', queued=1ms, exec=113ms


Daemon Status:
  corosync: active/enabled
  pacemaker: active/enabled
  pcsd: active/enabled

请帮帮我

标签： postgresqlcentos7high-availability