测试环境信息：
OS version：
# more /etc/redhat-release
CentOS Linux release 7.8.2003 (Core)
# su - postgres
Last login: Sun Jun 6 04:02:39 CST 2021 on pts/0
postgresql version:
$ psql --version
psql (PostgreSQL) 13.3
$ exit
logout
pacemaker version:
# rpm -qa|grep pacemaker
pacemaker-libs-1.1.23-1.el7_9.1.x86_64
pacemaker-1.1.23-1.el7_9.1.x86_64
pacemaker-cli-1.1.23-1.el7_9.1.x86_64
pacemaker-cluster-libs-1.1.23-1.el7_9.1.x86_64
pcs version:
# rpm -qa|grep pcs
pcs-0.9.169-3.el7.centos.1.x86_64
#
一、安装基础软件
1.hostname[all servers]
[all servers]# hostnamectl set-hostname pg13vm01
cat << _EOF_ > /etc/hosts
127.0.0.1 localhost
192.168.43.96 pg13vm01
192.168.43.217 pg13vm02
192.168.43.185 pg13vm03
_EOF_
# more /etc/hosts
127.0.0.1 localhost
192.168.43.96 pg13vm01
192.168.43.217 pg13vm02
192.168.43.185 pg13vm03
#
2.firewalld and selinux[all servers]
# systemctl disable firewalld
# systemctl stop firewalld
# sestatus
SELinux status: disabled
#### or enable ####
systemctl --quiet --now enable firewalld
firewall-cmd --quiet --permanent --add-service=high-availability
firewall-cmd --quiet --permanent --add-service=postgresql
firewall-cmd --quiet --reload
#
3.pcs[all servers]
# yum install -y pacemaker pcs
# yum install autoconf automake libtool
# yum install docbook-style-xsl
# yum install gcc-c++ glib2-devel
4.pacemaker resource-agents 更新
# 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]
#### yum.repo.list ####
# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
#### install postgresql ####
# yum install -y postgresql13-server postgresql13-contrib
# rpm -qa|grep postgresql
postgresql13-server-13.3-1PGDG.rhel7.x86_64
postgresql13-13.3-1PGDG.rhel7.x86_64
postgresql13-contrib-13.3-1PGDG.rhel7.x86_64
postgresql13-libs-13.3-1PGDG.rhel7.x86_64
#
7.service setup
# 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 pg13vm01 pg13vm02 pg13vm03 -u hacluster -p "hacluster"
pg13vm01: Authorized
pg13vm02: Authorized
pg13vm03: Authorized
#
9.设置数据库集群[any one host]
#### 配置集群节点 ####
# pcs cluster setup --name cluster_pg01 pg13vm01 pg13vm02 pg13vm03
Destroying cluster on nodes: pg13vm01, pg13vm02, pg13vm03...
pg13vm01: Stopping Cluster (pacemaker)...
pg13vm02: Stopping Cluster (pacemaker)...
pg13vm03: Stopping Cluster (pacemaker)...
pg13vm01: Successfully destroyed cluster
pg13vm03: Successfully destroyed cluster
pg13vm02: Successfully destroyed cluster
Sending 'pacemaker_remote authkey' to 'pg13vm01', 'pg13vm02', 'pg13vm03'
pg13vm01: successful distribution of the file 'pacemaker_remote authkey'
pg13vm02: successful distribution of the file 'pacemaker_remote authkey'
pg13vm03: successful distribution of the file 'pacemaker_remote authkey'
Sending cluster config files to the nodes...
pg13vm01: Succeeded
pg13vm02: Succeeded
pg13vm03: Succeeded
Synchronizing pcsd certificates on nodes pg13vm01, pg13vm02, pg13vm03...
pg13vm01: Success
pg13vm02: Success
pg13vm03: Success
Restarting pcsd on the nodes in order to reload the certificates...
pg13vm01: Success
pg13vm02: Success
pg13vm03: Success
#
#### 启动所有集群节点 ####
# pcs cluster start --all
pg13vm01: Starting Cluster (corosync)...
pg13vm02: Starting Cluster (corosync)...
pg13vm03: Starting Cluster (corosync)...
pg13vm03: Starting Cluster (pacemaker)...
pg13vm01: Starting Cluster (pacemaker)...
pg13vm02: Starting Cluster (pacemaker)...
#
#### 检查集群状态 ####
# pcs status --full
Cluster name: cluster_pg01
WARNINGS:
No stonith devices and stonith-enabled is not false
Stack: corosync
Current DC: pg13vm01 (1) (version 1.1.23-1.el7_9.1-9acf116022) - partition with quorum
Last updated: Sun Jun 6 00:11:57 2021
Last change: Sun Jun 6 00:11:48 2021 by hacluster via crmd on pg13vm01
3 nodes configured
0 resource instances configured
Online: [ pg13vm01 (1) pg13vm02 (2) pg13vm03 (3) ]
No resources
Node Attributes:
* Node pg13vm01 (1):
* Node pg13vm02 (2):
* Node pg13vm03 (3):
Migration Summary:
* Node pg13vm02 (2):
* Node pg13vm01 (1):
* Node pg13vm03 (3):
Fencing History:
PCSD Status:
pg13vm03: Online
pg13vm01: Online
pg13vm02: Online
Daemon Status:
corosync: active/disabled
pacemaker: active/disabled
pcsd: active/enabled
#
10.数据库目录配置[all servers]
# mkdir /pgdata
# chown postgres.postgres /pgdata/
# chmod 700 /pgdata/
11.数据库用户环境变量配置[all servers]
# su - postgres
$ vi .bash_profile
[ -f /etc/profile ] && source /etc/profile
export PGDATA=/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数据库配置[pg13vm01]
#### pg13vm01 ####
1).初始化数据库
# su - postgres
$ initdb -D /pgdata/
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /pgdata ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D /pgdata/ -l logfile start
$
2).配置主机访问
$ vi pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 md5
# IPv6 local connections:
#host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication replic_user 192.168.43.0/24 md5
3).配置数据库参数
$ 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.3)
Type "help" for help.
postgres=# create user replic_user with replication password 'replic_user';
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-------------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
replic_user | Replication | {}
postgres=#
13.创建secondary数据库[pg13vm02,pg13vm03]
#### create secondary server ####
$ cd /pgdata/
$ pg_basebackup -h pg13vm01 -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_13632"
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数据库[pg13vm01]
$ pg_ctl stop
waiting for server to shut down.... done
server stopped
$
二、配置pacemaker数据库集群[pg13vm01]
1.检查集群状态
# pcs status
Cluster name: cluster_pg01
WARNINGS:
No stonith devices and stonith-enabled is not false
Stack: corosync
Current DC: pg13vm01 (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 pg13vm01
3 nodes configured
0 resource instances configured
Online: [ pg13vm01 pg13vm02 pg13vm03 ]
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=192.168.43.201 cidr_netmask=24 nic=ens33 iflabel=master op monitor interval=5s
# resource: replica-vip
pcs -f cib.xml resource create replica-vip ocf:heartbeat:IPaddr2 \
ip=192.168.43.202 cidr_netmask=24 nic=ens33 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="pg13vm01 pg13vm02 pg13vm03" \
restore_command="" \
master_ip="192.168.43.201" \
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.查询集群状态
# pcs status --full
Cluster name: cluster_pg01
Stack: corosync
Current DC: pg13vm01 (1) (version 1.1.23-1.el7_9.1-9acf116022) - partition with quorum
Last updated: Sun Jun 6 04:02:24 2021
Last change: Sun Jun 6 04:02:08 2021 by root via crm_attribute on pg13vm01
3 nodes configured
5 resource instances configured
Online: [ pg13vm01 (1) pg13vm02 (2) pg13vm03 (3) ]
Full list of resources:
master-vip (ocf::heartbeat:IPaddr2): Started pg13vm01
replica-vip (ocf::heartbeat:IPaddr2): Started pg13vm03
Master/Slave Set: pgsql-master [pgsql]
pgsql (ocf::heartbeat:pgsql): Master pg13vm01
pgsql (ocf::heartbeat:pgsql): Slave pg13vm02
pgsql (ocf::heartbeat:pgsql): Slave pg13vm03
Masters: [ pg13vm01 ]
Slaves: [ pg13vm02 pg13vm03 ]
Node Attributes:
* Node pg13vm01 (1):
+ master-pgsql : 1000
+ pgsql-data-status : LATEST
+ pgsql-master-baseline : 00000000050000D8
+ pgsql-status : PRI
* Node pg13vm02 (2):
+ master-pgsql : -INFINITY
+ pgsql-data-status : STREAMING|ASYNC
+ pgsql-status : HS:async
* Node pg13vm03 (3):
+ master-pgsql : 100
+ pgsql-data-status : STREAMING|SYNC
+ pgsql-status : HS:sync
+ pgsql-xlog-loc : 0000000009000060
Migration Summary:
* Node pg13vm01 (1):
* Node pg13vm02 (2):
* Node pg13vm03 (3):
Fencing History:
PCSD Status:
pg13vm03: Online
pg13vm01: Online
pg13vm02: Online
Daemon Status:
corosync: active/disabled
pacemaker: active/disabled
pcsd: active/enabled
#