环境介绍
操作系统版本
CentOS Linux release 7.8.2003 (Core)
数据库版本
psql (13.10)
PCS集群版本
[root@hd-clw-select-proxysql01 ~]# rpm -qa|grep pacemaker
pacemaker-libs-1.1.23-1.el7_9.1.x86_64
pacemaker-cli-1.1.23-1.el7_9.1.x86_64
pacemaker-1.1.23-1.el7_9.1.x86_64
pacemaker-cluster-libs-1.1.23-1.el7_9.1.x86_64
[root@hd-clw-select-proxysql01 ~]# rpm -qa|grep pcs
pcsc-lite-libs-1.8.8-8.el7.x86_64
pcs-0.9.169-3.el7.centos.3.x86_64
[root@hd-clw-select-proxysql01 ~]# rpm -qa|grep corosync
corosynclib-2.4.5-7.el7_9.2.x86_64
corosync-2.4.5-7.el7_9.2.x86_64
172.26.181.25 虚拟机物理ip
172.26.181.26 虚拟机物理ip
172.26.181.136 读写 vip
172.26.181.137 只读 vip
PG 安装包
[root@hd-clw-select-proxysql01 opt]# ll postgresql13*
-rw------- 1 root root 1485584 Mar 15 16:38 postgresql13-13.10-1PGDG.rhel7.x86_64.rpm
-rw------- 1 root root 629284 Mar 15 16:39 postgresql13-contrib-13.10-1PGDG.rhel7.x86_64.rpm
-rw------- 1 root root 393964 Mar 15 16:40 postgresql13-libs-13.10-1PGDG.rhel7.x86_64.rpm
-rw------- 1 root root 5615388 Mar 15 16:52 postgresql13-server-13.10-1PGDG.rhel7.x86_64.rpm
[root@hd-clw-select-proxysql01 opt]# ll resource-agents-4.12.0.zip
-rw------- 1 root root 1149827 Mar 15 14:37 resource-agents-4.12.0.zip
PG软件包下载地址：https://yum.postgresql.org/13/redhat/rhel-7-x86_64/repoview/
resource-agents-4.12.0 下载地址： https://github.com/ClusterLabs/resource-agents/releases/tag/v4.12.0
一、安装基础软件
1. 配置 hostname [all servers]
[root@hd-clw-select-proxysql01 ~]# more /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
172.26.181.25 hd-clw-select-proxysql01
172.26.181.26 hd-clw-select-proxysql02
2. 关闭 firewalld and selinux[all servers]
# systemctl disable firewalld
# systemctl stop firewalld
# systemctl status firewalld
# sestatus
SELinux status: disabled
3. 安装 pcs[all servers]
# yum -y install libsmb*
# yum install -y pacemaker pcs
# yum install -y autoconf automake libtool
# yum install -y docbook-style-xsl
# yum install -y gcc-c++ glib2-devel
4.pacemaker resource-agents 更新 [all servers]
# unzip resource-agents-4.8.0.zip
# cd resource-agents-4.8.0
# ./autogen.sh
# ./configure
# make && make install
确认支持PG12以上版本
/usr/lib/ocf/resource.d/heartbeat/pgsql文件，1918 行，包含ocf_version_cmp "$version" "12"
5.pcsd[all servers]
# echo "hacluster"| passwd --stdin hacluster
6.安装postgresql数据库软件 [all servers]
rpm -ivh postgresql13-libs-13.10-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql13-13.10-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql13-server-13.10-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql13-contrib-13.10-1PGDG.rhel7.x86_64.rpm
7.service setup [all servers]
systemctl disable postgresql-13.service
systemctl disable corosync
systemctl disable pacemaker
systemctl enable pcsd.service
systemctl start pcsd.service
8.cluster auth[any one host]
# pcs cluster auth hd-clw-select-proxysql01 hd-clw-select-proxysql02 -u hacluster -p "hacluster"
9.设置数据库集群[any one host]
#### 配置集群节点 ####
# pcs cluster setup --name cluster_pg01 hd-clw-select-proxysql01 hd-clw-select-proxysql02
#### 启动所有集群节点 ####
# pcs cluster start --all
#### 检查集群状态 ####
# pcs status --full
10.数据库目录配置[all servers]
# mkdir /pgdata
# chown postgres.postgres /pgdata/
# chmod 700 /pgdata/
11.数据库用户环境变量配置[all servers]
# su - postgres
$ cat .bash_profile
[ -f /etc/profile ] && source /etc/profile
PGDATA=/pgdata
export PGDATA
export PATH=/usr/pgsql-13/bin:$PATH
# If you want to customize your settings,
# Use the file below. This is not overridden
# by the RPMS.
[ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile
12.primary数据库配置 [hd-clw-select-proxysql01]
#### hd-clw-select-proxysql01 ####
1).初始化数据库
# su - postgres
$ initdb -D /pgdata/
2).配置主机访问
$ vi pg_hba.conf
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication replic_user 172.26.181.0/24 md5
host all all 0.0.0.0/0 md5
3).配置数据库参数 （这里仅测试环境pg参数，生产环境需要详细配置）
$ vi postgresql.conf
listen_addresses = '*'
wal_keep_size = 10240 # wal keep files size
4).创建复制用户
$ pg_ctl start
waiting for server to start....2021-06-06 00:28:55.991 CST [13488] LOG: redirecting log output to logging collector process
2021-06-06 00:28:55.991 CST [13488] HINT: Future log output will appear in directory "log".
done
server started
-bash-4.2$ psql
psql (13.10)
Type "help" for help.
postgres=# create user replic_user with replication password 'replic_user';
CREATE ROLE
postgres=# \du
13.创建secondary数据库 [hd-clw-select-proxysql02]
#### create secondary server ####
$ cd /pgdata/
$ pg_basebackup -h hd-clw-select-proxysql01 -U replic_user -D /pgdata/ -P -v
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/A000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_13.102"
24987/24987 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/A000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
14.停止primary数据库 [hd-clw-select-proxysql01]
$ pg_ctl stop
waiting for server to shut down.... done
server stopped
二、配置pacemaker数据库集群 [any one host] （一个节点配置即可，会自动同步到另外一个节点）
1.检查集群状态
# pcs status
Cluster name: cluster_pg01
WARNINGS:
No stonith devices and stonith-enabled is not false
Stack: corosync
Current DC: hd-clw-select-proxysql01 (version 1.1.23-1.el7_9.1-9acf116022) - partition with quorum
Last updated: Sun Jun 6 00:53:57 2021
Last change: Sun Jun 6 00:11:48 2021 by hacluster via crmd on hd-clw-select-proxysql01
3 nodes configured
resource instances configured
Online: [ hd-clw-select-proxysql01 hd-clw-select-proxysql02 ]
No resources
Daemon Status:
corosync: active/disabled
pacemaker: active/disabled
pcsd: active/enabled
#
2.创建集群文件
# pcs cluster cib cib.xml
# ll
total 4
-rw-r--r-- 1 root root 1672 Jun 6 00:52 cluster_pg13.xml
#
3.配置数据库资源
# property: cluster-name
pcs -f cib.xml property set cluster-name="pg_cluster_01"
# property: disable stonith, quorum
pcs -f cib.xml property set no-quorum-policy="ignore"
pcs -f cib.xml property set stonith-enabled="false"
# resource: master-vip
pcs -f cib.xml resource create master-vip ocf:heartbeat:IPaddr2 \ ip=172.26.181.136 cidr_netmask=24 nic=ens160 iflabel=master op monitor interval=5s
# resource: replica-vip
pcs -f cib.xml resource create replica-vip ocf:heartbeat:IPaddr2 \ ip=172.26.181.137 cidr_netmask=24 nic=ens160 iflabel=replica op monitor interval=5s
# resource: pgsql
pcs -f cib.xml resource create pgsql ocf:heartbeat:pgsql \
pgctl="/usr/pgsql-13/bin/pg_ctl" \
psql="/usr/pgsql-13/bin/psql" \
pgdata="/pgdata" \
node_list="hd-clw-select-proxysql01 hd-clw-select-proxysql02" \
restore_command="" \
master_ip="172.26.181.136" \
repuser="replic_user" \
rep_mode="sync" \
primary_conninfo_opt="password=replic_user keepalives_idle=60 keepalives_interval=5 keepalives_count=5" \
op monitor interval="11s" \
op monitor interval="10s" role="Master" \
master master-max=1 master-node-max=1 clone-max=3 clone-node-max=1 notify=true target-role='Started'
# constraint: master-vip, pgsql on master node
pcs -f cib.xml constraint colocation add master-vip with master pgsql-master INFINITY
# constraint: pgsql promote node MasterGroup
pcs -f cib.xml constraint order promote pgsql-master then start master-vip symmetrical=false score=INFINITY
# constraint: pgsql demote node MasterGroup
pcs -f cib.xml constraint order demote pgsql-master then stop master-vip symmetrical=false score=0
# constraint: replica-vip sync standby 、sync standby on master
pcs -f cib.xml constraint location replica-vip rule score=200 pgsql-status eq HS:sync
pcs -f cib.xml constraint location replica-vip rule score=100 pgsql-status eq PRI
pcs -f cib.xml constraint location replica-vip rule score=-INFINITY not_defined pgsql-status
pcs -f cib.xml constraint location replica-vip rule score=-INFINITY pgsql-status ne HS:sync and pgsql-status ne PRI
# cluster: push cib file into cib
pcs cluster cib-push cib.xml
4.刷新集群状态
# pcs resource refresh --full
Waiting for 1 reply from the CRMd. OK
#
5.查询集群状态
[root@hd-clw-select-proxysql01 ~]# pcs status --full
Cluster name: cluster_pg01
Stack: corosync
Current DC: hd-clw-select-proxysql01 (1) (version 1.1.23-1.el7_9.1-9acf116022) - partition with quorum
Last updated: Sat Apr 1 15:43:55 2023
Last change: Fri Mar 17 15:09:56 2023 by root via crm_attribute on hd-clw-select-proxysql01
2 nodes configured
5 resource instances configured
Online: [ hd-clw-select-proxysql01 (1) hd-clw-select-proxysql02 (2) ]
Full list of resources:
master-vip (ocf::heartbeat:IPaddr2): Started hd-clw-select-proxysql01
replica-vip (ocf::heartbeat:IPaddr2): Started hd-clw-select-proxysql02
Master/Slave Set: pgsql-master [pgsql]
pgsql (ocf::heartbeat:pgsql): Slave hd-clw-select-proxysql02
pgsql (ocf::heartbeat:pgsql): Master hd-clw-select-proxysql01
pgsql (ocf::heartbeat:pgsql): Stopped
Masters: [ hd-clw-select-proxysql01 ]
Slaves: [ hd-clw-select-proxysql02 ]
Node Attributes:
* Node hd-clw-select-proxysql01 (1):
+ master-pgsql : 1000
+ pgsql-data-status : LATEST
+ pgsql-master-baseline : 000000000F000148
+ pgsql-status : PRI
* Node hd-clw-select-proxysql02 (2):
+ master-pgsql : 100
+ pgsql-data-status : STREAMING|SYNC
+ pgsql-status : HS:sync
Migration Summary:
* Node hd-clw-select-proxysql02 (2):
* Node hd-clw-select-proxysql01 (1):
Fencing History:
PCSD Status:
hd-clw-select-proxysql02: Online
hd-clw-select-proxysql01: Online
Daemon Status:
corosync: active/disabled
pacemaker: active/disabled
pcsd: active/enabled
参考：
PgSQL Replicated Cluster：
http://clusterlabs.org/wiki/PgSQL_Replicated_Cluster
就是玩，想进群聊天交流的+++微信哈！
标签：13,PostgreSQL,pcs,proxysql01,pgsql,select,pacemaker,clw,hd From： https://www.cnblogs.com/andy6/p/17278951.html