一、项目介绍

1.1、pg_auto_failover概述

g_auto_failover 是 PostgreSQL 的一个扩展和服务，用于监控和管理Postgres 集群的自动故障转移。它针对简单性和正确性进行了优化，并支持 Postgres 10 及更高版本。

pg_auto_failover 支持多种 Postgres 架构，并为您的 Postgres 服务实现安全的自动故障转移。可以仅使用两个数据节点开始，监视器将为这两个数据节点指定主节点和辅助节点的角色。

pg_auto_failover 监视器实现了状态机，并依靠核心 PostgreSQL 设施来提供 HA。例如，当检测到辅助节点不可用，或者其滞后过多时，监视器会将其从主synchronous_standby_names节点上的设置中删除。在辅助节点恢复正常监控之前，不允许进行故障转移和切换操作，以防止数据丢失。

1.2、功能列表

pg_auto_failover 由以下部分组成：

一个名为pgautofailover

用于操作 pg_auto_failover 监视器的 PostgreSQL 服务

一个 pg_auto_failover keeper 来操作你的 PostgreSQL 实例，请参阅pg_autoctl run

1.3、多节点架构

Pg_auto_failover 允许您拥有多个备用节点，并提供对生产架构特性的高级控制。
本例具有两个备用节点的架构
当使用默认设置添加第二个备用节点时，您将获得以下架构：

具有两个备用节点的 pg_auto_failover 架构：

在这种情况下，将设置三个具有相同特性的节点，从而实现 Postgres 服务和生产数据集的 HA。此架构的一个重要设置是number_sync_standbys。

复制设置number_sync_standbys主服务器在提交事务时应等待多少个备用节点。为了使系统具有良好的可用性，pg_auto_failover 需要备用节点参与复制仲裁：这允许任何备用节点发生故障而不会影响系统遵守复制仲裁的能力。number_sync_standbys + 1

当监视器上的组中仅注册了两个节点时，我们有一个主节点和一个辅助节点。然后number_sync_standbys只能设置为零。当将第二个备用节点添加到 pg_auto_failover 组时，监视器会自动增加到number_sync_standbys1，如上图所示。

当number_sync_standbys设置为零时，pg_auto_failover 将实现业务连续性设置，如架构基础中所示：然后使用同步复制来保证可以在没有数据丢失的情况下实现故障转移。

详细信息：

1、设置number_sync_standbys为 1 后，该架构始终维护数据集的两个副本：一个在当前主节点上（上图中的节点 A），一个在首先确认事务的备用节点上（图中的节点 B 或节点 C）。

2、当其中一个备用节点不可用时，仍可以通过剩余的备用节点维护数据集的第二个副本。

3、当两个备用节点都不可用时，就无法再保证复制仲裁，因此主节点上的写入将被阻止。Postgres 主节点将等待，直到至少一个备用节点确认本地提交的事务，从而将您的 Postgres 服务降级为只读。

4、number_sync_standbys当向监视器注册两个备用节点时，可以手动设置为零，以覆盖默认行为。

5、在这种情况下，当第二个备用节点与第一个备用节点同时变得不健康时，主节点将被分配状态 Wait_primary。在该状态下，通过设置为空字符串，在主节点上禁用同步复制synchronous_standby_names。即使此时没有可用的生产数据集的额外副本，也可以在主节点上进行写入。

6、设置number_sync_standbys为零允许在两个备用节点都关闭时写入数据。在这种情况下，将保留生产数据集的单个副本，如果主节点发生故障，则会丢失一些数据。丢失多少取决于您的备份和恢复机制。

1.4、官网资料地址

安装包下载地址：
https://github.com/hapostgres/pg_auto_failover/releases/tag/v2.1

官网地址：
https://pg-auto-failover.readthedocs.io/en/main/

二、环境初始化

2.1、环境规划

主机名称	主机IP	用途	操作系统	PostgreSQL版本	pg_auto_failover
Node1	192.168.1.101	Primary	CentOS Linux 7.9	16.2	2.1
Node2	192.168.1.102	witness	CentOS Linux 7.9	16.2	2.1
Node3	192.168.1.103	Monitor	CentOS Linux 7.9	16.2	2.1

2.2、检查操作系统

cat /etc/redhat-release

2.3、统一主机名称

vi /etc/hostname
或 hostnamectl set-hostname Node1

2.4、关闭SELINUX

sed -i  "s/SELINUX=enforcing/SELINUX=disabled/"  /etc/selinux/config

2.5、关闭防火墙

systemctl stop firewalld.service
systemctl disable firewalld.service

说明：
查看开放的端口：
firewall-cmd –list-ports
开启防火墙端口：
firewall-cmd –zone=public –add-port=5432/tcp –permanent

命令含义：

–zone #作用域

–add-port=9200/tcp #添加端口，格式为：端口/通讯协议

–permanent #永久生效，没有此参数重启后失效

重新加载一遍才会生效：

firewall-cmd –reload

2.6、安装插件

yum -y install readline readline-devel zlib zlib-devel gettext gettext-devel openssl openssl-devel pam pam-devel libxml2 libxml2-devel libxslt libxslt-devel perl perl-devel tcl-devel uuid-devel gcc gcc-c++ make flex bison perl-ExtUtils*  libicu  libicu-devel  ntp libcurl-devel wget unzip

2.7、编辑hosts

说明：将主机名和 ip 解析写到/etc/hosts 文件。


cat>>/etc/hosts<<EOF
192.168.1.101  Node1
192.168.1.102  Node2
192.168.1.103  Node3
EOF

2.8、用户limits设置

cat>>/etc/security/limits.conf<<EOF
postgres soft nofile 1048576
postgres hard nofile 1048576
postgres soft nproc 131072
postgres hard nproc 131072
postgres soft stack 10240
postgres hard stack 32768
postgres soft core 6291456
postgres hard core 6291456
EOF

2.9、调整内核

cat>>/etc/sysctl.conf<<EOF
fs.file-max = 76724200
kernel.sem = 10000 10240000 10000 1024
kernel.shmmni = 4096
kernel.shmall = 253702
kernel.shmmax = 1039163392
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_max = 1048576
fs.aio-max-nr = 40960000
vm.dirty_ratio=20
vm.dirty_background_ratio=3
vm.dirty_writeback_centisecs=100
vm.dirty_expire_centisecs=500
vm.swappiness=10
vm.min_free_kbytes=524288
vm.swappiness=0
vm.overcommit_memory=2
vm.overcommit_ratio=75
net.ipv4.ip_local_port_range = 10000 65535
EOF

查看是否生效

sysctl -p

2.10、时间同步

说明：配置时间同步，开启ntp

主节点执行（192.168.1.101）

sed -i '12arestrict 192.168.1.101 nomodify notrap nopeer noquery' /etc/ntp.conf
sed -i '22,25s/^/# /' /etc/ntp.conf
sed -i '25aserver 127.127.1.0' /etc/ntp.conf
sed -i '26aFudge 127.127.1.0 stratum 10' /etc/ntp.conf
service ntpd start

chkconfig ntpd on
systemctl enable ntpd.service
systemctl start ntpd.service

Node2节点（192.168.1.102）

sed -i '12arestrict 192.168.1.102 nomodify notrap nopeer noquery' /etc/ntp.conf
sed -i '22,25s/^/# /' /etc/ntp.conf
sed -i '25aserver 192.168.1.101' /etc/ntp.conf
sed -i '26aFudge 192.168.1.101 stratum 10' /etc/ntp.conf
service ntpd start
chkconfig ntpd on
systemctl enable ntpd.service
systemctl start ntpd.service

Node3节点（192.168.1.103）

cp /etc/ntp.conf /etc/ntp.conf.bak
sed -i '12arestrict 192.168.1.103 nomodify notrap nopeer noquery' /etc/ntp.conf
sed -i '22,25s/^/# /' /etc/ntp.conf
sed -i '25aserver 192.168.1.101' /etc/ntp.conf
sed -i '26aFudge 192.168.1.101 stratum 10' /etc/ntp.conf
service ntpd start
chkconfig ntpd on
systemctl enable ntpd.service
systemctl start ntpd.service

2.11、创建用户

注：在在所有节点上创建DBA用户

groupadd postgres -g 4000
useradd postgres -g 4000 -u 4000
echo "postgres"|passwd --stdin postgres

2.12、所有节点配置sudo

sed -i.bak '100apostgres   ALL=(ALL)       NOPASSWD:ALL' /etc/sudoers

2.13、配置各节点互信

互信脚本如下：

su - postgres
rm -rf /home/postgres/.ssh
mkdir ~/.ssh
chmod 700 ~/.ssh
ssh-keygen -t rsa



ssh-copy-id postgres@192.168.1.101
ssh-copy-id postgres@192.168.1.102
ssh-copy-id postgres@192.168.1.103

Node1：

[postgres@Node1 ~]$ ssh-copy-id postgres@192.168.1.102
/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/home/postgres/.ssh/id_rsa.pub"
The authenticity of host '192.168.1.102 (192.168.1.102)' can't be established.
ECDSA key fingerprint is SHA256:lx7Ykd3KUValFLX4c0Bf7tbXHeJNbcXZ5VgnBxn43LI.
ECDSA key fingerprint is MD5:5d:41:7c:42:00:f7:e3:f9:d8:fb:ba:19:7a:66:67:70.
Are you sure you want to continue connecting (yes/no)? yes
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are alr                                          eady installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to inst                                          all the new keys
postgres@192.168.1.102's password:

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh 'postgres@192.168.1.102'"
and check to make sure that only the key(s) you wanted were added.

[postgres@Node1 ~]$ ssh-copy-id postgres@192.168.1.103
/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/home/postgres/.ssh/id_rsa.pub"
The authenticity of host '192.168.1.103 (192.168.1.103)' can't be established.
ECDSA key fingerprint is SHA256:S40RFtrnLXgbo6ZCJYE0Y50pCCGBYbwYUSWLBAUfQCQ.
ECDSA key fingerprint is MD5:c6:ef:8d:2d:c4:0d:d1:67:21:4d:73:67:fd:69:3d:fb.
Are you sure you want to continue connecting (yes/no)? yes
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are alr                                          eady installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to inst                                          all the new keys
postgres@192.168.1.103's password:

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh 'postgres@192.168.1.103'"
and check to make sure that only the key(s) you wanted were added.

Node2：

[postgres@Node2 ~]$ ssh-copy-id postgres@192.168.1.101
/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/home/postgres/.ssh/id_rsa.pub"
The authenticity of host '192.168.1.101 (192.168.1.101)' can't be established.
ECDSA key fingerprint is SHA256:tsuIJ4dbVxLjxLmqjMcV4xwZ0CkThWo1znacBex8KZM.
ECDSA key fingerprint is MD5:cf:c0:6d:6e:d7:b6:d6:37:41:47:35:50:92:ca:03:df.
Are you sure you want to continue connecting (yes/no)? yes
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are alr                                          eady installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to inst                                          all the new keys
postgres@192.168.1.101's password:

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh 'postgres@192.168.1.101'"
and check to make sure that only the key(s) you wanted were added.

[postgres@Node2 ~]$ ssh-copy-id postgres@192.168.1.103
/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/home/postgres/.ssh/id_rsa.pub"
The authenticity of host '192.168.1.103 (192.168.1.103)' can't be established.
ECDSA key fingerprint is SHA256:S40RFtrnLXgbo6ZCJYE0Y50pCCGBYbwYUSWLBAUfQCQ.
ECDSA key fingerprint is MD5:c6:ef:8d:2d:c4:0d:d1:67:21:4d:73:67:fd:69:3d:fb.
Are you sure you want to continue connecting (yes/no)? yes
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are alr                                          eady installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to inst                                          all the new keys
postgres@192.168.1.103's password:

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh 'postgres@192.168.1.103'"
and check to make sure that only the key(s) you wanted were added.

[postgres@Node2 ~]$

Node3：

[postgres@Node3 ~]$ ssh-copy-id postgres@192.168.1.101
/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/home/postgres/.ssh/id_rsa.pub"
The authenticity of host '192.168.1.101 (192.168.1.101)' can't be established.
ECDSA key fingerprint is SHA256:tsuIJ4dbVxLjxLmqjMcV4xwZ0CkThWo1znacBex8KZM.
ECDSA key fingerprint is MD5:cf:c0:6d:6e:d7:b6:d6:37:41:47:35:50:92:ca:03:df.
Are you sure you want to continue connecting (yes/no)? yes
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are alr                                          eady installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to inst                                          all the new keys
postgres@192.168.1.101's password:

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh 'postgres@192.168.1.101'"
and check to make sure that only the key(s) you wanted were added.

[postgres@Node3 ~]$ ssh-copy-id postgres@192.168.1.102
/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/home/postgres/.ssh/id_rsa.pub"
The authenticity of host '192.168.1.102 (192.168.1.102)' can't be established.
ECDSA key fingerprint is SHA256:lx7Ykd3KUValFLX4c0Bf7tbXHeJNbcXZ5VgnBxn43LI.
ECDSA key fingerprint is MD5:5d:41:7c:42:00:f7:e3:f9:d8:fb:ba:19:7a:66:67:70.
Are you sure you want to continue connecting (yes/no)? yes
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are alr                                          eady installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to inst                                          all the new keys
postgres@192.168.1.102's password:

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh 'postgres@192.168.1.102'"
and check to make sure that only the key(s) you wanted were added.

三、PostgreSQL服务部署

3.1、目录规划

目录编号	目录名称	备注
1	/install	安装包上传目录
2	/postgres	postgres根目录
3	/postgres/server	软件安装目录
4	/postgres/wal	WAL 目录
5	/postgres/archive	归档目录
6	/postgres/backup	备份目录
7	/postgres/pgauto	集群安装目录
8	/postgres/pgauto/data	集群数据目录

创建安装目录脚本如下：

mkdir /install
mkdir -p /postgres
mkdir -p /postgres/server
mkdir -p /postgres/wal
mkdir -p /postgres/archive
mkdir -p /postgres/backup
mkdir -p /postgres/pgauto
mkdir -p /postgres/pgauto/data
chown -R postgres:postgres  /postgres /install
chmod 0775 /postgres
chmod 0700 /postgres/pgauto/data

3.2、部署PostgreSQL（三台主机Node1、Node2、Node3都要部署）

su  postgres
cd /install

上传安装包

3.3、由Node1分发文件到Node2、Node3

scp  ./* 192.168.1.102:/install
scp  ./* 192.168.1.103:/install

3.4、解压PostgreSQL安装包（三台主机Node1、Node2、Node3都要解压）

[postgres@Node1 install]$ tar xzvf postgresql-16.2.tar.gz
[postgres@Node1 install]$ unzip pg_auto_failover-main.zip

[postgres@Node2 install]$ tar xzvf postgresql-16.2.tar.gz
[postgres@Node2 install]$ unzip pg_auto_failover-main.zip

[postgres@Node3 install]$ tar xzvf postgresql-16.2.tar.gz
[postgres@Node3 install]$ unzip pg_auto_failover-main.zip

3.5、部署PostgreSQL（三台主机Node1、Node2、Node3都要部署）

Node1：

./configure --prefix=/postgres/server  --with-pgport=5431  --with-openssl

make && make install

Node2：

./configure --prefix=/postgres/server  --with-pgport=5431  --with-openssl

make && make install

Node3：

./configure --prefix=/postgres/server  --with-pgport=5431  --with-openssl

make && make install

说明：使用 make 或者 make world
$ make world
使用 make install 或者 make install-world 进行安装
$ make install-world #包含扩展包和文档

3.6、设置软链接（三台主机Node1、Node2、Node3都要设置）

su root
mkdir /postgresql
chmod -R 777 /postgresql
chown -R postgres:postgres /postgresql
ln -s /postgres/server /postgresql

3.7、切换到pg源码contrib目录，执行安装（三台主机Node1、Node2、Node3都要编译安装）

make  clean && make -j 4

Node1：

[postgres@Node1 postgresql-16.2]$ cd contrib/
[postgres@Node1 contrib]$ make  clean && make -j 4
make -C adminpack clean
make[1]: Entering directory `/install/postgresql-16.2/contrib/adminpack'
rm -f adminpack.so   libadminpack.a  libadminpack.pc
rm -f  adminpack.o adminpack.bc
rm -rf results/ regression.diffs regression.out tmp_check/ tmp_check_iso/ log/ output_iso/
make[1]: Leaving directory `/install/postgresql-16.2/contrib/adminpack'
make -C amcheck clean
make[1]: Entering directory `/install/postgresql-16.2/contrib/amcheck'
rm -f amcheck.so   libamcheck.a  libamcheck.pc
rm -f  verify_heapam.o verify_nbtree.o verify_heapam.bc verify_nbtree.bc
...........................................................................................................................................................
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -fvisibility=hidden -shared -o pgcrypto.so  crypt-blowfish.o crypt-des.o crypt-gensalt.o crypt-md5.o mbuf.o openssl.o pgcrypto.o pgp-armor.o pgp-cfb.o pgp-compress.o pgp-decrypt.o pgp-encrypt.o pgp-info.o pgp-mpi.o pgp-mpi-openssl.o pgp-pgsql.o pgp-pubdec.o pgp-pubenc.o pgp-pubkey.o pgp-s2k.o pgp.o px-crypt.o px-hmac.o px.o -L../../src/port -L../../src/common    -Wl,--as-needed -Wl,-rpath,'/postgres/server/lib',--enable-new-dtags  -fvisibility=hidden  -lcrypto -lz
make[1]: Leaving directory `/install/postgresql-16.2/contrib/pgcrypto'
[postgres@Node1 contrib]$

Node2：

[postgres@Node2 postgresql-16.2]$ cd contrib/
[postgres@Node2 contrib]$ make  clean && make -j 4
make -C adminpack clean
make[1]: Entering directory `/install/postgresql-16.2/contrib/adminpack'
rm -f adminpack.so   libadminpack.a  libadminpack.pc
rm -f  adminpack.o adminpack.bc
rm -rf results/ regression.diffs regression.out tmp_check/ tmp_check_iso/ log/ output_iso/
make[1]: Leaving directory `/install/postgresql-16.2/contrib/adminpack'
make -C amcheck clean
make[1]: Entering directory `/install/postgresql-16.2/contrib/amcheck'
rm -f amcheck.so   libamcheck.a  libamcheck.pc
rm -f  verify_heapam.o verify_nbtree.o verify_heapam.bc verify_nbtree.bc
...........................................................................................................................................................
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -fvisibility=hidden -shared -o pgcrypto.so  crypt-blowfish.o crypt-des.o crypt-gensalt.o crypt-md5.o mbuf.o openssl.o pgcrypto.o pgp-armor.o pgp-cfb.o pgp-compress.o pgp-decrypt.o pgp-encrypt.o pgp-info.o pgp-mpi.o pgp-mpi-openssl.o pgp-pgsql.o pgp-pubdec.o pgp-pubenc.o pgp-pubkey.o pgp-s2k.o pgp.o px-crypt.o px-hmac.o px.o -L../../src/port -L../../src/common    -Wl,--as-needed -Wl,-rpath,'/postgres/server/lib',--enable-new-dtags  -fvisibility=hidden  -lcrypto -lz
make[1]: Leaving directory `/install/postgresql-16.2/contrib/pgcrypto'
[postgres@Node2 contrib]$

Node3：

[postgres@Node3 postgresql-16.2]$ cd contrib/
[postgres@Node3 contrib]$ make  clean && make -j 4
make -C adminpack clean
make[1]: Entering directory `/install/postgresql-16.2/contrib/adminpack'
rm -f adminpack.so   libadminpack.a  libadminpack.pc
rm -f  adminpack.o adminpack.bc
rm -rf results/ regression.diffs regression.out tmp_check/ tmp_check_iso/ log/ output_iso/
make[1]: Leaving directory `/install/postgresql-16.2/contrib/adminpack'
make -C amcheck clean
make[1]: Entering directory `/install/postgresql-16.2/contrib/amcheck'
rm -f amcheck.so   libamcheck.a  libamcheck.pc
rm -f  verify_heapam.o verify_nbtree.o verify_heapam.bc verify_nbtree.bc
...........................................................................................................................................................
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -fvisibility=hidden -shared -o pgcrypto.so  crypt-blowfish.o crypt-des.o crypt-gensalt.o crypt-md5.o mbuf.o openssl.o pgcrypto.o pgp-armor.o pgp-cfb.o pgp-compress.o pgp-decrypt.o pgp-encrypt.o pgp-info.o pgp-mpi.o pgp-mpi-openssl.o pgp-pgsql.o pgp-pubdec.o pgp-pubenc.o pgp-pubkey.o pgp-s2k.o pgp.o px-crypt.o px-hmac.o px.o -L../../src/port -L../../src/common    -Wl,--as-needed -Wl,-rpath,'/postgres/server/lib',--enable-new-dtags  -fvisibility=hidden  -lcrypto -lz
make[1]: Leaving directory `/install/postgresql-16.2/contrib/pgcrypto'
[postgres@Node3 contrib]$

3.8、切换到pg源码contrib/btree_gist目录，执行安装（三台主机Node1、Node2、Node3都要编译安装）

Node1：

[postgres@Node1 contrib]$ cd btree_gist/
[postgres@Node1 btree_gist]$ make && make install
make -C ../../src/backend generated-headers
make[1]: Entering directory `/install/postgresql-16.2/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/catalog'
make -C nodes distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/nodes'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/nodes'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/utils'
make[1]: Leaving directory `/install/postgresql-16.2/src/backend'
make -C ../../src/backend generated-headers
make[1]: Entering directory `/install/postgresql-16.2/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/catalog'
make -C nodes distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/nodes'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/nodes'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/utils'
make[1]: Leaving directory `/install/postgresql-16.2/src/backend'
/bin/mkdir -p '/postgres/server/lib'
/bin/mkdir -p '/postgres/server/share/extension'
/bin/mkdir -p '/postgres/server/share/extension'
/bin/install -c -m 755  btree_gist.so '/postgres/server/lib/btree_gist.so'
/bin/install -c -m 644 ./btree_gist.control '/postgres/server/share/extension/'
/bin/install -c -m 644 ./btree_gist--1.0--1.1.sql ./btree_gist--1.1--1.2.sql ./btree_gist--1.2.sql ./btree_gist--1.2--1.3.sql ./btree_gist--1.3--1.4.sql ./btree_gist--1.4--1.5.sql ./btree_gist--1.5--1.6.sql ./btree_gist--1.6--1.7.sql  '/postgres/server/share/extension/'

Node2：

[postgres@Node2 contrib]$ cd btree_gist/
[postgres@Node2 btree_gist]$ make && make install
make -C ../../src/backend generated-headers
make[1]: Entering directory `/install/postgresql-16.2/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/catalog'
make -C nodes distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/nodes'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/nodes'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/utils'
make[1]: Leaving directory `/install/postgresql-16.2/src/backend'
make -C ../../src/backend generated-headers
make[1]: Entering directory `/install/postgresql-16.2/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/catalog'
make -C nodes distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/nodes'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/nodes'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/utils'
make[1]: Leaving directory `/install/postgresql-16.2/src/backend'
/bin/mkdir -p '/postgres/server/lib'
/bin/mkdir -p '/postgres/server/share/extension'
/bin/mkdir -p '/postgres/server/share/extension'
/bin/install -c -m 755  btree_gist.so '/postgres/server/lib/btree_gist.so'
/bin/install -c -m 644 ./btree_gist.control '/postgres/server/share/extension/'
/bin/install -c -m 644 ./btree_gist--1.0--1.1.sql ./btree_gist--1.1--1.2.sql ./btree_gist--1.2.sql ./btree_gist--1.2--1.3.sql ./btree_gist--1.3--1.4.sql ./btree_gist--1.4--1.5.sql ./btree_gist--1.5--1.6.sql ./btree_gist--1.6--1.7.sql  '/postgres/server/share/extension/'

Node3：

[postgres@Node3 contrib]$ cd btree_gist/
[postgres@Node3 btree_gist]$ make && make install
make -C ../../src/backend generated-headers
make[1]: Entering directory `/install/postgresql-16.2/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/catalog'
make -C nodes distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/nodes'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/nodes'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/utils'
make[1]: Leaving directory `/install/postgresql-16.2/src/backend'
make -C ../../src/backend generated-headers
make[1]: Entering directory `/install/postgresql-16.2/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/catalog'
make -C nodes distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/nodes'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/nodes'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/utils'
make[1]: Leaving directory `/install/postgresql-16.2/src/backend'
/bin/mkdir -p '/postgres/server/lib'
/bin/mkdir -p '/postgres/server/share/extension'
/bin/mkdir -p '/postgres/server/share/extension'
/bin/install -c -m 755  btree_gist.so '/postgres/server/lib/btree_gist.so'
/bin/install -c -m 644 ./btree_gist.control '/postgres/server/share/extension/'
/bin/install -c -m 644 ./btree_gist--1.0--1.1.sql ./btree_gist--1.1--1.2.sql ./btree_gist--1.2.sql ./btree_gist--1.2--1.3.sql ./btree_gist--1.3--1.4.sql ./btree_gist--1.4--1.5.sql ./btree_gist--1.5--1.6.sql ./btree_gist--1.6--1.7.sql  '/postgres/server/share/extension/'

3.9、切换到pg源码contrib/pg_stat_statements目录，执行安装（三台主机Node1、Node2、Node3都要编译安装）

Node1：

[postgres@Node1 btree_gist]$ cd /install/postgresql-16.2/contrib/pg_stat_statements/
[postgres@Node1 pg_stat_statements]$ make && make install
make -C ../../src/backend generated-headers
make[1]: Entering directory `/install/postgresql-16.2/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/catalog'
make -C nodes distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/nodes'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/nodes'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/utils'
make[1]: Leaving directory `/install/postgresql-16.2/src/backend'
make -C ../../src/backend generated-headers
make[1]: Entering directory `/install/postgresql-16.2/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/catalog'
make -C nodes distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/nodes'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/nodes'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/utils'
make[1]: Leaving directory `/install/postgresql-16.2/src/backend'
/bin/mkdir -p '/postgres/server/lib'
/bin/mkdir -p '/postgres/server/share/extension'
/bin/mkdir -p '/postgres/server/share/extension'
/bin/install -c -m 755  pg_stat_statements.so '/postgres/server/lib/pg_stat_statements.so'
/bin/install -c -m 644 ./pg_stat_statements.control '/postgres/server/share/extension/'
/bin/install -c -m 644 ./pg_stat_statements--1.4.sql ./pg_stat_statements--1.9--1.10.sql ./pg_stat_statements--1.8--1.9.sql ./pg_stat_statements--1.7--1.8.sql ./pg_stat_statements--1.6--1.7.sql ./pg_stat_statements--1.5--1.6.sql ./pg_stat_statements--1.4--1.5.sql ./pg_stat_statements--1.3--1.4.sql ./pg_stat_statements--1.2--1.3.sql ./pg_stat_statements--1.1--1.2.sql ./pg_stat_statements--1.0--1.1.sql  '/postgres/server/share/extension/'
[postgres@Node1 pg_stat_statements]$

Node2：

[postgres@Node2 btree_gist]$ cd /install/postgresql-16.2/contrib/pg_stat_statements/
[postgres@Node2 pg_stat_statements]$ make && make install
make -C ../../src/backend generated-headers
make[1]: Entering directory `/install/postgresql-16.2/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/catalog'
make -C nodes distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/nodes'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/nodes'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/utils'
make[1]: Leaving directory `/install/postgresql-16.2/src/backend'
make -C ../../src/backend generated-headers
make[1]: Entering directory `/install/postgresql-16.2/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/catalog'
make -C nodes distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/nodes'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/nodes'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/utils'
make[1]: Leaving directory `/install/postgresql-16.2/src/backend'
/bin/mkdir -p '/postgres/server/lib'
/bin/mkdir -p '/postgres/server/share/extension'
/bin/mkdir -p '/postgres/server/share/extension'
/bin/install -c -m 755  pg_stat_statements.so '/postgres/server/lib/pg_stat_statements.so'
/bin/install -c -m 644 ./pg_stat_statements.control '/postgres/server/share/extension/'
/bin/install -c -m 644 ./pg_stat_statements--1.4.sql ./pg_stat_statements--1.9--1.10.sql ./pg_stat_statements--1.8--1.9.sql ./pg_stat_statements--1.7--1.8.sql ./pg_stat_statements--1.6--1.7.sql ./pg_stat_statements--1.5--1.6.sql ./pg_stat_statements--1.4--1.5.sql ./pg_stat_statements--1.3--1.4.sql ./pg_stat_statements--1.2--1.3.sql ./pg_stat_statements--1.1--1.2.sql ./pg_stat_statements--1.0--1.1.sql  '/postgres/server/share/extension/'
[postgres@Node2 pg_stat_statements]$

Node3：

[postgres@Node3 btree_gist]$ cd /install/postgresql-16.2/contrib/pg_stat_statements/
[postgres@Node3 pg_stat_statements]$ make && make install
make -C ../../src/backend generated-headers
make[1]: Entering directory `/install/postgresql-16.2/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/catalog'
make -C nodes distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/nodes'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/nodes'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/utils'
make[1]: Leaving directory `/install/postgresql-16.2/src/backend'
make -C ../../src/backend generated-headers
make[1]: Entering directory `/install/postgresql-16.2/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/catalog'
make -C nodes distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/nodes'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/nodes'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/install/postgresql-16.2/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/install/postgresql-16.2/src/backend/utils'
make[1]: Leaving directory `/install/postgresql-16.2/src/backend'
/bin/mkdir -p '/postgres/server/lib'
/bin/mkdir -p '/postgres/server/share/extension'
/bin/mkdir -p '/postgres/server/share/extension'
/bin/install -c -m 755  pg_stat_statements.so '/postgres/server/lib/pg_stat_statements.so'
/bin/install -c -m 644 ./pg_stat_statements.control '/postgres/server/share/extension/'
/bin/install -c -m 644 ./pg_stat_statements--1.4.sql ./pg_stat_statements--1.9--1.10.sql ./pg_stat_statements--1.8--1.9.sql ./pg_stat_statements--1.7--1.8.sql ./pg_stat_statements--1.6--1.7.sql ./pg_stat_statements--1.5--1.6.sql ./pg_stat_statements--1.4--1.5.sql ./pg_stat_statements--1.3--1.4.sql ./pg_stat_statements--1.2--1.3.sql ./pg_stat_statements--1.1--1.2.sql ./pg_stat_statements--1.0--1.1.sql  '/postgres/server/share/extension/'
[postgres@Node3 pg_stat_statements]$

3.10、设置环境变量（三台主机Node1、Node2、Node3都要设置）

vi  ~/.bash_profile

export PGHOME=/postgres/server
export PATH=$PGHOME/bin:$PATH
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH

#此处$PGDATA直接设置为pg_auto的目录，否则会出错
export PGDATA=/postgres/pgauto/data 
export PGPORT=5432
export PGUSER=postgres
export DATABASE=postgres

source .bash_profile

四、pg_auto_failover服务部署

4.1、安装pg_auto_failover

说明：切换到pg_auto_failover-main，执行安装操作。

[postgres@Node1 install]$ cd pg_auto_failover-main
[postgres@Node1 pg_auto_failover-main]$ make && make install

[postgres@Node2 install]$ cd pg_auto_failover-main
[postgres@Node2 pg_auto_failover-main]$ make && make install

[postgres@Node3 install]$ cd pg_auto_failover-main
[postgres@Node3 pg_auto_failover-main]$ make && make install

4.2、检查版本

检查版本

pg_autoctl --version

Node1：

[postgres@Node1 ~]$
[postgres@Node1 ~]$ pg_autoctl --version
pg_autoctl version
pg_autoctl extension version 2.1
compiled with PostgreSQL 14.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
compatible with Postgres 11, 12, 13, 14, 15, and 16
[postgres@Node1 ~]$

Node2：

[postgres@Node2 ~]$
[postgres@Node2 ~]$ pg_autoctl --version
pg_autoctl version
pg_autoctl extension version 2.1
compiled with PostgreSQL 14.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
compatible with Postgres 11, 12, 13, 14, 15, and 16
[postgres@Node2 ~]$

Node3：

[postgres@Node3 ~]$
[postgres@Node3 ~]$ pg_autoctl --version
pg_autoctl version
pg_autoctl extension version 2.1
compiled with PostgreSQL 14.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
compatible with Postgres 11, 12, 13, 14, 15, and 16
[postgres@Node3 ~]$

4.2、配置Monitor

说明：在Node3上部署Monitor服务

[postgres@Node3 ~]$ pg_autoctl create monitor --pgdata /postgres/pgauto/data --pgport 5432 --hostname node3 --auth trust  --ssl-self-signed
23:13:10 127626 INFO  Using default --ssl-mode "require"
23:13:10 127626 INFO  Using --ssl-self-signed: pg_autoctl will create self-signed certificates, allowing for encrypted network traffic
23:13:10 127626 WARN  Self-signed certificates provide protection against eavesdropping; this setup does NOT protect against Man-In-The-Middle attacks nor Impersonation attacks.
23:13:10 127626 WARN  See https://www.postgresql.org/docs/current/libpq-ssl.html for details
23:13:10 127626 INFO  Initialising a PostgreSQL cluster at "/postgres/pgauto/data"
23:13:10 127626 INFO  /postgres/server/bin/pg_ctl initdb -s -D /postgres/pgauto/data --option '--auth=trust'
23:13:11 127626 INFO   /bin/openssl req -new -x509 -days 365 -nodes -text -out /postgres/pgauto/data/server.crt -keyout /postgres/pgauto/data/server.key -subj "/CN=node3"
23:13:11 127626 INFO  Started pg_autoctl postgres service with pid 127665
23:13:11 127665 INFO   /postgres/server/bin/pg_autoctl do service postgres --pgdata /postgres/pgauto/data -v
23:13:11 127626 INFO  Started pg_autoctl monitor-init service with pid 127666
23:13:11 127671 INFO   /postgres/server/bin/postgres -D /postgres/pgauto/data -p 5432 -h *
23:13:11 127665 INFO  Postgres is now serving PGDATA "/postgres/pgauto/data" on port 5432 with pid 127671
23:13:11 127666 WARN  NOTICE:  installing required extension "btree_gist"
23:13:11 127666 INFO  Granting connection privileges on 192.168.1.0/24
23:13:11 127666 WARN  Skipping HBA edits (per --skip-pg-hba) for rule: hostssl "pg_auto_failover" "autoctl_node" 192.168.1.0/24 trust
23:13:11 127666 INFO  Your pg_auto_failover monitor instance is now ready on port 5432.
23:13:11 127666 INFO  Monitor has been successfully initialized.
23:13:11 127626 WARN  pg_autoctl service monitor-init exited with exit status 0
23:13:11 127665 INFO  Postgres controller service received signal SIGTERM, terminating
23:13:11 127665 INFO  Stopping pg_autoctl postgres service
23:13:11 127665 INFO  /postgres/server/bin/pg_ctl --pgdata /postgres/pgauto/data --wait stop --mode fast
23:13:11 127626 INFO  Stop pg_autoctl
[postgres@Node3 ~]$

4.3、启动Monito

[postgres@Node3 ~]$ pg_autoctl create monitor --pgdata /postgres/pgauto/data --pgport 5432 --hostname node3 --auth trust  --ssl-self-signed --run &
[1] 129496
[postgres@Node3 ~]$ 23:15:12 129496 INFO  Using default --ssl-mode "require"
23:15:12 129496 INFO  Using --ssl-self-signed: pg_autoctl will create self-signed certificates, allowing for encrypted network traffic
23:15:12 129496 WARN  Self-signed certificates provide protection against eavesdropping; this setup does NOT protect against Man-In-The-Middle attacks nor Impersonation attacks.
23:15:12 129496 WARN  See https://www.postgresql.org/docs/current/libpq-ssl.html for details
23:15:12 129496 INFO   /bin/openssl req -new -x509 -days 365 -nodes -text -out /postgres/pgauto/data/server.crt -keyout /postgres/pgauto/data/server.key -subj "/CN=node3"
23:15:12 129496 INFO  Started pg_autoctl postgres service with pid 129505
23:15:12 129496 INFO  Started pg_autoctl listener service with pid 129506
23:15:12 129505 INFO   /postgres/server/bin/pg_autoctl do service postgres --pgdata /postgres/pgauto/data -v
23:15:12 129513 INFO   /postgres/server/bin/postgres -D /postgres/pgauto/data -p 5432 -h *
23:15:12 129505 INFO  Postgres is now serving PGDATA "/postgres/pgauto/data" on port 5432 with pid 129513
23:15:12 129506 INFO  The user "autoctl" already exists, skipping.
23:15:12 129506 INFO  The database "pg_auto_failover" already exists, skipping.
23:15:12 129506 WARN  NOTICE:  extension "pgautofailover" already exists, skipping
23:15:12 129506 INFO  Granting connection privileges on 192.168.1.0/24
23:15:12 129506 WARN  Skipping HBA edits (per --skip-pg-hba) for rule: hostssl "pg_auto_failover" "autoctl_node" 192.168.1.0/24 trust
23:15:12 129506 INFO  Your pg_auto_failover monitor instance is now ready on port 5432.
23:15:12 129506 INFO  Monitor has been successfully initialized.
23:15:12 129506 INFO   /postgres/server/bin/pg_autoctl do service listener --pgdata /postgres/pgauto/data -v
23:15:12 129506 INFO  Managing the monitor at postgres://autoctl_node@node3:5432/pg_auto_failover?sslmode=require
23:15:12 129506 INFO  Reloaded the new configuration from "/home/postgres/.config/pg_autoctl/postgres/pgauto/data/pg_autoctl.cfg"
23:15:12 129506 INFO  Reloading Postgres configuration and HBA rules
23:15:13 129506 INFO  The version of extension "pgautofailover" is "2.1" on the monitor
23:15:13 129506 INFO  Contacting the monitor to LISTEN to its events.

4.4、创建PrimaryNode

说明：在Node1主机上运行pg_autoctl create postgres 命令创建PrimaryNode

[postgres@Node1 ~]$ pg_autoctl create postgres --hostname node1 --auth trust --ssl-self-signed --monitor 'postgres://autoctl_node@node3:5432/pg_auto_failover?sslmode=require'  --run &
[1] 19470
[postgres@Node1 ~]$ 23:18:13 19470 INFO  Using default --ssl-mode "require"
23:18:13 19470 INFO  Using --ssl-self-signed: pg_autoctl will create self-signed certificates, allowing for encrypted network traffic
23:18:13 19470 WARN  Self-signed certificates provide protection against eavesdropping; this setup does NOT protect against Man-In-The-Middle attacks nor Impersonation attacks.
23:18:13 19470 WARN  See https://www.postgresql.org/docs/current/libpq-ssl.html for details
23:18:13 19470 INFO  Started pg_autoctl postgres service with pid 19472
23:18:13 19472 INFO   /postgres/server/bin/pg_autoctl do service postgres --pgdata /postgres/pgauto/data -v
23:18:13 19470 INFO  Started pg_autoctl node-active service with pid 19473
23:18:13 19473 INFO  Registered node 1 "node_1" (node1:5432) in formation "default", group 0, state "single"
23:18:13 19473 INFO  Writing keeper state file at "/home/postgres/.local/share/pg_autoctl/postgres/pgauto/data/pg_autoctl.state"
23:18:13 19473 INFO  Writing keeper init state file at "/home/postgres/.local/share/pg_autoctl/postgres/pgauto/data/pg_autoctl.init"
23:18:13 19473 INFO  Successfully registered as "single" to the monitor.
23:18:13 19473 INFO  FSM transition from "init" to "single": Start as a single node
23:18:13 19473 INFO  Initialising postgres as a primary
23:18:13 19473 INFO  Initialising a PostgreSQL cluster at "/postgres/pgauto/data"
23:18:13 19473 INFO  /postgres/server/bin/pg_ctl initdb -s -D /postgres/pgauto/data --option '--auth=trust'
23:18:40 19473 WARN  Failed to resolve hostname "node1" to an IP address that resolves back to the hostname on a reverse DNS lookup.
23:18:40 19473 WARN  Postgres might deny connection attempts from "node1", even with the new HBA rules.
23:18:40 19473 WARN  Hint: correct setup of HBA with host names requires proper reverse DNS setup. You might want to use IP addresses.
23:18:40 19473 WARN  Using IP address "192.168.1.101" in HBA file instead of hostname "node1"
23:18:40 19473 INFO   /bin/openssl req -new -x509 -days 365 -nodes -text -out /postgres/pgauto/data/server.crt -keyout /postgres/pgauto/data/server.key -subj "/CN=node1"
23:18:40 19494 INFO   /postgres/server/bin/postgres -D /postgres/pgauto/data -p 5432 -h *
23:18:40 19473 INFO  The user "postgres" already exists, skipping.
23:18:40 19473 INFO  CREATE USER postgres
23:18:40 19473 INFO  CREATE DATABASE postgres;
23:18:40 19473 INFO  The database "postgres" already exists, skipping.
23:18:40 19473 INFO  CREATE EXTENSION pg_stat_statements;
23:18:40 19472 INFO  Postgres is now serving PGDATA "/postgres/pgauto/data" on port 5432 with pid 19494
23:18:40 19473 INFO  Disabling synchronous replication
23:18:40 19473 INFO  Reloading Postgres configuration and HBA rules
23:18:40 19473 INFO   /bin/openssl req -new -x509 -days 365 -nodes -text -out /postgres/pgauto/data/server.crt -keyout /postgres/pgauto/data/server.key -subj "/CN=node1"
23:18:40 19473 INFO  Contents of "/postgres/pgauto/data/postgresql-auto-failover.conf" have changed, overwriting
23:18:40 19473 WARN  Failed to resolve hostname "node3" to an IP address that resolves back to the hostname on a reverse DNS lookup.
23:18:40 19473 WARN  Postgres might deny connection attempts from "node3", even with the new HBA rules.
23:18:40 19473 WARN  Hint: correct setup of HBA with host names requires proper reverse DNS setup. You might want to use IP addresses.
23:18:40 19473 WARN  Using IP address "192.168.1.103" in HBA file instead of hostname "node3"
23:18:40 19473 INFO  Reloading Postgres configuration and HBA rules
23:18:40 19473 INFO  Transition complete: current state is now "single"
23:18:40 19473 INFO  keeper has been successfully initialized.
23:18:40 19473 INFO   /postgres/server/bin/pg_autoctl do service node-active --pgdata /postgres/pgauto/data -v
23:18:40 19473 INFO  Reloaded the new configuration from "/home/postgres/.config/pg_autoctl/postgres/pgauto/data/pg_autoctl.cfg"
23:18:40 19473 INFO  Reloading Postgres configuration and HBA rules
23:18:40 19473 INFO  pg_autoctl service is running, current state is "single"
23:18:40 19473 INFO  New state for this node (node 1, "node_1") (node1:5432): single ➜ single
23:18:50 19473 INFO  New state for node 2 "node_2" (node2:5432): wait_standby ➜ wait_standby
23:18:50 19473 INFO  Fetched current list of 1 other nodes from the monitor to update HBA rules, including 1 changes.
23:18:50 19473 WARN  Failed to resolve hostname "node2" to an IP address that resolves back to the hostname on a reverse DNS lookup.
23:18:50 19473 WARN  Postgres might deny connection attempts from "node2", even with the new HBA rules.
23:18:50 19473 WARN  Hint: correct setup of HBA with host names requires proper reverse DNS setup. You might want to use IP addresses.
23:18:50 19473 WARN  Using IP address "192.168.1.102" in HBA file instead of hostname "node2"
23:18:50 19473 INFO  Ensuring HBA rules for node 2 "node_2" (192.168.1.102:5432)
23:18:50 19473 INFO  Adding HBA rule: hostssl replication "pgautofailover_replicator" 192.168.1.102/32 trust
23:18:50 19473 INFO  Adding HBA rule: hostssl "postgres" "pgautofailover_replicator" 192.168.1.102/32 trust
23:18:50 19473 INFO  Writing new HBA rules in "/postgres/pgauto/data/pg_hba.conf"
23:18:50 19473 INFO  Reloading Postgres configuration and HBA rules
23:18:50 19473 INFO  Monitor assigned new state "wait_primary"
23:18:50 19473 INFO  Creating replication slot "pgautofailover_standby_2"
23:18:50 19473 INFO  FSM transition from "single" to "wait_primary": A new secondary was added
23:18:50 19473 INFO  Transition complete: current state is now "wait_primary"
23:18:50 19473 INFO  New state for this node (node 1, "node_1") (node1:5432): single ➜ wait_primary
23:18:50 19473 INFO  New state for this node (node 1, "node_1") (node1:5432): wait_primary ➜ wait_primary
23:18:50 19473 INFO  New state for node 2 "node_2" (node2:5432): wait_standby ➜ catchingup
23:18:50 19473 INFO  New state for node 2 "node_2" (node2:5432): wait_standby ➜ catchingup
23:18:50 19473 INFO  New state for node 2 "node_2" (node2:5432): catchingup ➜ catchingup
23:18:52 19473 INFO  New state for node 2 "node_2" (node2:5432): catchingup ➜ secondary
23:18:52 19473 INFO  New state for node 2 "node_2" (node2:5432): secondary ➜ secondary
23:18:52 19473 INFO  Monitor assigned new state "primary"
23:18:52 19473 INFO  FSM transition from "wait_primary" to "primary": A healthy secondary appeared
23:18:52 19473 INFO  Setting synchronous_standby_names to 'ANY 1 (pgautofailover_standby_2)'
23:18:52 19473 INFO  Reloading Postgres configuration and HBA rules
23:18:52 19473 INFO  Waiting until standby node has caught-up to LSN 0/3000000
23:18:52 19473 INFO  Standby reached LSN 0/3000000, thus advanced past LSN 0/3000000
23:18:52 19473 INFO  Transition complete: current state is now "primary"
23:18:52 19473 INFO  New state for this node (node 1, "node_1") (node1:5432): wait_primary ➜ primary
23:18:52 19473 INFO  New state for node 2 "node_2" (node2:5432): secondary ➜ secondary
23:18:52 19473 INFO  New state for this node (node 1, "node_1") (node1:5432): primary ➜ primary

[postgres@Node1 ~]$

4.5、创建Secondary节点

说明：在Node2主机上运行pg_autoctl create postgres 命令创建Secondary



[postgres@Node2 ~]$ pg_autoctl create postgres --hostname node2 --auth trust --ssl-self-signed --monitor 'postgres://autoctl_node@node3:5432/pg_auto_failover?sslmode=require'  --run &
[1] 20382
[postgres@Node2 ~]$ 23:19:10 20382 INFO  Using default --ssl-mode "require"
23:19:10 20382 INFO  Using --ssl-self-signed: pg_autoctl will create self-signed certificates, allowing for encrypted network traffic
23:19:10 20382 WARN  Self-signed certificates provide protection against eavesdropping; this setup does NOT protect against Man-In-The-Middle attacks nor Impersonation attacks.
23:19:10 20382 WARN  See https://www.postgresql.org/docs/current/libpq-ssl.html for details
23:19:10 20382 INFO  Started pg_autoctl postgres service with pid 20384
23:19:10 20384 INFO   /postgres/server/bin/pg_autoctl do service postgres --pgdata /postgres/pgauto/data -v
23:19:10 20382 INFO  Started pg_autoctl node-active service with pid 20385
23:19:10 20385 INFO  Registered node 2 "node_2" (node2:5432) in formation "default", group 0, state "wait_standby"
23:19:10 20385 INFO  Writing keeper state file at "/home/postgres/.local/share/pg_autoctl/postgres/pgauto/data/pg_autoctl.state"
23:19:10 20385 INFO  Writing keeper init state file at "/home/postgres/.local/share/pg_autoctl/postgres/pgauto/data/pg_autoctl.init"
23:19:10 20385 INFO  Successfully registered as "wait_standby" to the monitor.
23:19:10 20385 INFO  FSM transition from "init" to "wait_standby": Start following a primary
23:19:10 20385 INFO  Transition complete: current state is now "wait_standby"
23:19:10 20385 INFO  New state for node 1 "node_1" (node1:5432): single ➜ wait_primary
23:19:10 20385 INFO  New state for node 1 "node_1" (node1:5432): wait_primary ➜ wait_primary
23:19:10 20385 INFO  FSM transition from "wait_standby" to "catchingup": The primary is now ready to accept a standby
23:19:10 20385 INFO  Initialising PostgreSQL as a hot standby
23:19:10 20385 INFO   /postgres/server/bin/pg_basebackup -w -d 'application_name=pgautofailover_standby_2 host=node1 port=5432 user=pgautofailover_replicator sslmode=require' --pgdata /postgres/pgauto/backup/node_2 -U pgautofailover_replicator --verbose --progress --max-rate 100M --wal-method=stream --slot pgautofailover_standby_2
23:19:10 20385 INFO  pg_basebackup: initiating base backup, waiting for checkpoint to complete
23:19:20 20385 INFO  pg_basebackup: checkpoint completed
23:19:20 20385 INFO  pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
23:19:20 20385 INFO  pg_basebackup: starting background WAL receiver
23:19:20 20385 INFO     66/23610 kB (0%), 0/1 tablespace (...pgauto/backup/node_2/global/2967)
23:19:20 20385 INFO  23620/23620 kB (100%), 0/1 tablespace (.../backup/node_2/global/pg_control)
23:19:20 20385 INFO  23620/23620 kB (100%), 1/1 tablespace
23:19:20 20385 INFO  pg_basebackup: write-ahead log end point: 0/2000100
23:19:20 20385 INFO  pg_basebackup: waiting for background process to finish streaming ...
23:19:20 20385 INFO  pg_basebackup: syncing data to disk ...
23:19:21 20385 INFO  pg_basebackup: renaming backup_manifest.tmp to backup_manifest
23:19:21 20385 INFO  pg_basebackup: base backup completed
23:19:21 20385 INFO  Creating the standby signal file at "/postgres/pgauto/data/standby.signal", and replication setup at "/postgres/pgauto/data/postgresql-auto-failover-standby.conf"
23:19:21 20385 INFO   /bin/openssl req -new -x509 -days 365 -nodes -text -out /postgres/pgauto/data/server.crt -keyout /postgres/pgauto/data/server.key -subj "/CN=node2"
23:19:21 20393 INFO   /postgres/server/bin/postgres -D /postgres/pgauto/data -p 5432 -h *
23:19:21 20384 INFO  Postgres is now serving PGDATA "/postgres/pgauto/data" on port 5432 with pid 20393
23:19:21 20385 INFO  PostgreSQL started on port 5432
23:19:21 20385 INFO  Fetched current list of 1 other nodes from the monitor to update HBA rules, including 1 changes.
23:19:21 20385 WARN  Failed to resolve hostname "node1" to an IP address that resolves back to the hostname on a reverse DNS lookup.
23:19:21 20385 WARN  Postgres might deny connection attempts from "node1", even with the new HBA rules.
23:19:21 20385 WARN  Hint: correct setup of HBA with host names requires proper reverse DNS setup. You might want to use IP addresses.
23:19:21 20385 WARN  Using IP address "192.168.1.101" in HBA file instead of hostname "node1"
23:19:21 20385 INFO  Ensuring HBA rules for node 1 "node_1" (192.168.1.101:5432)
23:19:21 20385 INFO  Adding HBA rule: hostssl replication "pgautofailover_replicator" 192.168.1.101/32 trust
23:19:21 20385 INFO  Adding HBA rule: hostssl "postgres" "pgautofailover_replicator" 192.168.1.101/32 trust
23:19:21 20385 INFO  Writing new HBA rules in "/postgres/pgauto/data/pg_hba.conf"
23:19:21 20385 INFO  Reloading Postgres configuration and HBA rules
23:19:21 20385 INFO  Transition complete: current state is now "catchingup"
23:19:21 20385 INFO  keeper has been successfully initialized.
23:19:21 20385 INFO   /postgres/server/bin/pg_autoctl do service node-active --pgdata /postgres/pgauto/data -v
23:19:21 20385 INFO  Reloaded the new configuration from "/home/postgres/.config/pg_autoctl/postgres/pgauto/data/pg_autoctl.cfg"
23:19:21 20385 INFO  Reloading Postgres configuration and HBA rules
23:19:21 20385 INFO  pg_autoctl service is running, current state is "catchingup"
23:19:25 20385 INFO  Fetched current list of 1 other nodes from the monitor to update HBA rules, including 1 changes.
23:19:25 20385 WARN  Failed to resolve hostname "node1" to an IP address that resolves back to the hostname on a reverse DNS lookup.
23:19:25 20385 WARN  Postgres might deny connection attempts from "node1", even with the new HBA rules.
23:19:25 20385 WARN  Hint: correct setup of HBA with host names requires proper reverse DNS setup. You might want to use IP addresses.
23:19:25 20385 WARN  Using IP address "192.168.1.101" in HBA file instead of hostname "node1"
23:19:25 20385 INFO  Ensuring HBA rules for node 1 "node_1" (192.168.1.101:5432)
23:19:25 20385 INFO  Reloading Postgres configuration and HBA rules
23:19:27 20385 INFO  Monitor assigned new state "secondary"
23:19:27 20385 INFO  FSM transition from "catchingup" to "secondary": Convinced the monitor that I'm up and running, and eligible for promotion again
23:19:27 20385 INFO  Reached timeline 1, same as upstream node 1 "node_1" (node1:5432)
23:19:27 20385 INFO  Creating replication slot "pgautofailover_standby_1"
23:19:27 20385 INFO  Transition complete: current state is now "secondary"
23:19:27 20385 INFO  New state for this node (node 2, "node_2") (node2:5432): secondary ➜ secondary
23:19:27 20385 INFO  New state for node 1 "node_1" (node1:5432): primary ➜ primary

[postgres@Node2 ~]$

4.6、查看pg_autoctl运行状态

[postgres@Node1 ~]$ pg_autoctl show state
  Name |  Node |  Host:Port |       TLI: LSN |   Connection |      Reported State |      Assigned State
-------+-------+------------+----------------+--------------+---------------------+--------------------
node_1 |     1 | node1:5432 |   1: 0/3063C70 |   read-write |             primary |             primary
node_2 |     2 | node2:5432 |   1: 0/3063C70 |    read-only |           secondary |           secondary

[postgres@Node1 ~]$

4.7、查看pg_autoctl连接的URL


[postgres@Node1 ~]$ pg_autoctl show uri
        Type |    Name | Connection String
-------------+---------+-------------------------------
     monitor | monitor | postgres://autoctl_node@node3:5432/pg_auto_failover?sslmode=require
   formation | default | postgres://node2:5432,node1:5432/postgres?target_session_attrs=read-write&sslmode=require

[postgres@Node1 ~]$

五、故障演练

5.1、主降备，备升主

停止Node1：

[postgres@Node1 ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@Node1 ~]$ 23:20:40 19473 WARN  Failed to fetch current replication properties from standby node: no standby connected in pg_stat_replication.
23:20:40 19473 WARN  HINT: check pg_autoctl and Postgres logs on standby nodes
23:20:40 19473 WARN  Failed to update the keeper's state from the local PostgreSQL instance.
23:20:40 39650 INFO   /postgres/server/bin/postgres -D /postgres/pgauto/data -p 5432 -h *
23:20:40 19472 WARN  PostgreSQL was not running, restarted with pid 39650
23:20:40 19473 WARN  PostgreSQL was not running, restarted with pid 39650
23:20:42 19473 INFO  New state for node 2 "node_2" (node2:5432): secondary ➜ prepare_promotion
23:20:42 19473 INFO  New state for this node (node 1, "node_1") (node1:5432): primary ➜ draining
23:20:42 19473 WARN  Failed to fetch current replication properties from standby node: no standby connected in pg_stat_replication.
23:20:42 19473 WARN  HINT: check pg_autoctl and Postgres logs on standby nodes
23:20:42 19473 WARN  Failed to update the keeper's state from the local PostgreSQL instance.
23:20:42 19473 INFO  Monitor assigned new state "draining"
23:20:42 19473 INFO  FSM transition from "primary" to "draining": A failover occurred, stopping writes
23:20:42 19472 INFO  Stopping pg_autoctl postgres service
23:20:42 19472 INFO  /postgres/server/bin/pg_ctl --pgdata /postgres/pgauto/data --wait stop --mode fast
23:20:42 19473 INFO  Transition complete: current state is now "draining"
23:20:42 19473 INFO  Updated the keeper's state from the local PostgreSQL instance, which is not running
23:20:42 19473 INFO  Monitor assigned new state "demote_timeout"
23:20:42 19473 INFO  FSM transition from "draining" to "demote_timeout": Secondary confirms it’s receiving no more writes
23:20:42 19473 INFO  Transition complete: current state is now "demote_timeout"
23:20:42 19473 INFO  New state for node 2 "node_2" (node2:5432): prepare_promotion ➜ prepare_promotion
23:20:42 19473 INFO  New state for node 2 "node_2" (node2:5432): prepare_promotion ➜ stop_replication
23:20:42 19473 INFO  New state for this node (node 1, "node_1") (node1:5432): primary ➜ demote_timeout
23:20:42 19473 INFO  New state for this node (node 1, "node_1") (node1:5432): draining ➜ demote_timeout
23:20:42 19473 INFO  New state for this node (node 1, "node_1") (node1:5432): demote_timeout ➜ demote_timeout
23:20:43 19473 INFO  New state for node 2 "node_2" (node2:5432): stop_replication ➜ stop_replication
23:20:43 19473 INFO  New state for node 2 "node_2" (node2:5432): stop_replication ➜ wait_primary
23:20:43 19473 INFO  New state for this node (node 1, "node_1") (node1:5432): demote_timeout ➜ demoted
23:20:43 19473 INFO  Monitor assigned new state "demoted"
23:20:43 19473 INFO  FSM transition from "demote_timeout" to "demoted": Demote timeout expired
23:20:43 19473 INFO  Transition complete: current state is now "demoted"
23:20:43 19473 INFO  Monitor assigned new state "catchingup"
23:20:43 19473 INFO  FSM transition from "demoted" to "catchingup": A new primary is available. First, try to rewind. If that fails, do a pg_basebackup.
23:20:43 19473 INFO  Rewinding PostgreSQL to follow new primary node 2 "node_2" (node2:5432)
23:20:43 19473 INFO   /postgres/server/bin/pg_rewind --target-pgdata /postgres/pgauto/data --source-server 'application_name=pgautofailover_standby_1 host=node2 port=5432 user=pgautofailover_replicator dbname=postgres sslmode=require' --progress
23:20:43 19473 INFO  pg_rewind: connected to server
23:20:43 19473 INFO  pg_rewind: servers diverged at WAL location 0/3063CE8 on timeline 1
23:20:43 19473 INFO  pg_rewind: rewinding from last common checkpoint at 0/3063C70 on timeline 1
23:20:43 19473 INFO  pg_rewind: reading source file list
23:20:43 19473 INFO  pg_rewind: reading target file list
23:20:43 19473 INFO  pg_rewind: reading WAL in target
23:20:43 19473 INFO  pg_rewind: need to copy 51 MB (total source directory size is 71 MB)
23:20:43 19473 INFO      0/52943 kB (0%) copied
23:20:45 19473 INFO   1912/52943 kB (3%) copied
23:20:45 19473 INFO  52943/52943 kB (100%) copied
23:20:45 19473 INFO  pg_rewind: creating backup label and updating control file
23:20:45 19473 INFO  pg_rewind: syncing target data directory
23:20:46 19473 INFO  pg_rewind: Done!
23:20:46 19473 INFO  Creating the standby signal file at "/postgres/pgauto/data/standby.signal", and replication setup at "/postgres/pgauto/data/postgresql-auto-failover-standby.conf"
23:20:46 19473 INFO  Contents of "/postgres/pgauto/data/postgresql-auto-failover-standby.conf" have changed, overwriting
23:20:46 39669 INFO   /postgres/server/bin/postgres -D /postgres/pgauto/data -p 5432 -h *
23:20:46 19473 INFO  Dropping replication slots (to reset their xmin)
23:20:46 19472 WARN  PostgreSQL was not running, restarted with pid 39669
23:20:46 19473 INFO  Transition complete: current state is now "catchingup"
23:20:46 19473 INFO  New state for this node (node 1, "node_1") (node1:5432): demoted ➜ demoted
23:20:46 19473 INFO  New state for node 2 "node_2" (node2:5432): wait_primary ➜ wait_primary
23:20:46 19473 INFO  New state for this node (node 1, "node_1") (node1:5432): demoted ➜ catchingup
23:20:46 19473 INFO  New state for this node (node 1, "node_1") (node1:5432): catchingup ➜ catchingup
23:20:47 19473 INFO  Monitor assigned new state "secondary"
23:20:47 19473 INFO  FSM transition from "catchingup" to "secondary": Convinced the monitor that I'm up and running, and eligible for promotion again
23:20:47 19473 INFO  Reached timeline 2, same as upstream node 2 "node_2" (node2:5432)
23:20:47 19473 INFO  Creating replication slot "pgautofailover_standby_2"
23:20:47 19473 INFO  Transition complete: current state is now "secondary"
23:20:47 19473 INFO  New state for node 2 "node_2" (node2:5432): primary ➜ primary

[postgres@Node1 ~]$

再次查询

说明：此处显示Node1变为备节点，Node2变为主节点

[postgres@Node2 ~]$
[postgres@Node2 ~]$ pg_autoctl show state
  Name |  Node |  Host:Port |       TLI: LSN |   Connection |      Reported State |      Assigned State
-------+-------+------------+----------------+--------------+---------------------+--------------------
node_1 |     1 | node1:5432 |   2: 0/3063F60 |    read-only |           secondary |           secondary
node_2 |     2 | node2:5432 |   2: 0/3063F60 |   read-write |             primary |             primary

[postgres@Node2 ~]$

5.2、手动切换主、备节点

说明：恢复Node1为主节点，Node2为备节点。

[postgres@Node1 ~]$ pg_autoctl perform switchover
23:22:43 40077 INFO  Targetting group 0 in formation "default"
23:22:43 19473 INFO  New state for node 2 "node_2" (node2:5432): primary ➜ draining
23:22:43 19473 INFO  New state for this node (node 1, "node_1") (node1:5432): secondary ➜ prepare_promotion
23:22:43 40077 INFO  Listening monitor notifications about state changes in formation "default" and group 0
23:22:43 40077 INFO  Following table displays times when notifications are received
    Time |   Name |  Node |  Host:Port |       Current State |      Assigned State
---------+--------+-------+------------+---------------------+--------------------
23:22:43 19473 INFO  Monitor assigned new state "prepare_promotion"
23:22:43 19473 INFO  FSM transition from "secondary" to "prepare_promotion": Stop traffic to primary, wait for it to finish draining.
23:22:43 19473 INFO  Transition complete: current state is now "prepare_promotion"
23:22:43 | node_2 |     2 | node2:5432 |             primary |            draining
23:22:43 | node_1 |     1 | node1:5432 |           secondary |   prepare_promotion
23:22:43 | node_1 |     1 | node1:5432 |   prepare_promotion |   prepare_promotion
23:22:43 | node_1 |     1 | node1:5432 |   prepare_promotion |    stop_replication
23:22:43 | node_2 |     2 | node2:5432 |             primary |      demote_timeout
23:22:43 19473 INFO  Monitor assigned new state "stop_replication"
23:22:43 19473 INFO  FSM transition from "prepare_promotion" to "stop_replication": Prevent against split-brain situations.
23:22:43 19473 INFO  Prevent writes to the promoted standby while the primary is not demoted yet, by making the service incompatible with target_session_attrs = read-write
23:22:43 19473 INFO  Setting default_transaction_read_only to on
23:22:43 19473 INFO  Reloading Postgres configuration and HBA rules
23:22:43 19473 INFO  Promoting postgres
23:22:43 19473 INFO  Waiting for postgres to promote
23:22:44 | node_2 |     2 | node2:5432 |            draining |      demote_timeout
23:22:44 | node_2 |     2 | node2:5432 |      demote_timeout |      demote_timeout
23:22:44 19473 INFO  Cleaning-up Postgres replication settings
23:22:44 19473 INFO  Disabling synchronous replication
23:22:44 19473 INFO  Reloading Postgres configuration and HBA rules
23:22:45 19473 INFO  Transition complete: current state is now "stop_replication"
23:22:45 | node_1 |     1 | node1:5432 |    stop_replication |    stop_replication
23:22:45 | node_1 |     1 | node1:5432 |    stop_replication |        wait_primary
23:22:45 | node_2 |     2 | node2:5432 |      demote_timeout |             demoted
23:22:45 19473 INFO  Monitor assigned new state "wait_primary"
23:22:45 19473 INFO  FSM transition from "stop_replication" to "wait_primary": Confirmed promotion with the monitor
23:22:45 19473 INFO  Setting default_transaction_read_only to off
23:22:45 19473 INFO  Reloading Postgres configuration and HBA rules
23:22:45 19473 INFO  Fetched current list of 1 other nodes from the monitor to update HBA rules, including 1 changes.
23:22:45 19473 WARN  Failed to resolve hostname "node2" to an IP address that resolves back to the hostname on a reverse DNS lookup.
23:22:45 19473 WARN  Postgres might deny connection attempts from "node2", even with the new HBA rules.
23:22:45 19473 WARN  Hint: correct setup of HBA with host names requires proper reverse DNS setup. You might want to use IP addresses.
23:22:45 19473 WARN  Using IP address "192.168.1.102" in HBA file instead of hostname "node2"
23:22:45 19473 INFO  Ensuring HBA rules for node 2 "node_2" (192.168.1.102:5432)
23:22:45 19473 INFO  Reloading Postgres configuration and HBA rules
23:22:45 19473 INFO  Transition complete: current state is now "wait_primary"
23:22:45 | node_2 |     2 | node2:5432 |             demoted |             demoted
23:22:45 | node_1 |     1 | node1:5432 |        wait_primary |        wait_primary
23:22:45 19473 INFO  New state for this node (node 1, "node_1") (node1:5432): prepare_promotion ➜ prepare_promotion
23:22:45 19473 INFO  New state for this node (node 1, "node_1") (node1:5432): prepare_promotion ➜ stop_replication
23:22:45 19473 INFO  New state for node 2 "node_2" (node2:5432): primary ➜ demote_timeout
23:22:45 19473 INFO  New state for node 2 "node_2" (node2:5432): draining ➜ demote_timeout
23:22:45 19473 INFO  New state for node 2 "node_2" (node2:5432): demote_timeout ➜ demote_timeout
23:22:45 19473 INFO  New state for this node (node 1, "node_1") (node1:5432): stop_replication ➜ stop_replication
23:22:45 19473 INFO  New state for this node (node 1, "node_1") (node1:5432): stop_replication ➜ wait_primary
23:22:45 19473 INFO  New state for node 2 "node_2" (node2:5432): demote_timeout ➜ demoted
23:22:45 19473 INFO  New state for node 2 "node_2" (node2:5432): demoted ➜ demoted
23:22:45 19473 INFO  New state for this node (node 1, "node_1") (node1:5432): wait_primary ➜ wait_primary
23:22:45 | node_2 |     2 | node2:5432 |             demoted |          catchingup
23:22:45 19473 INFO  New state for node 2 "node_2" (node2:5432): demoted ➜ catchingup
23:22:45 | node_2 |     2 | node2:5432 |          catchingup |          catchingup
23:22:45 19473 INFO  New state for node 2 "node_2" (node2:5432): catchingup ➜ catchingup
23:22:46 | node_2 |     2 | node2:5432 |          catchingup |           secondary
23:22:46 | node_2 |     2 | node2:5432 |           secondary |           secondary
23:22:46 19473 INFO  New state for node 2 "node_2" (node2:5432): secondary ➜ secondary
23:22:46 | node_1 |     1 | node1:5432 |        wait_primary |             primary
23:22:46 19473 INFO  Monitor assigned new state "primary"
23:22:46 19473 INFO  FSM transition from "wait_primary" to "primary": A healthy secondary appeared
23:22:46 19473 INFO  Setting synchronous_standby_names to 'ANY 1 (pgautofailover_standby_2)'
23:22:46 19473 INFO  Reloading Postgres configuration and HBA rules
23:22:46 19473 INFO  Waiting until standby node has caught-up to LSN 0/30641F0
23:22:46 19473 INFO  Standby reached LSN 0/30642A0, thus advanced past LSN 0/30641F0
23:22:46 19473 INFO  Transition complete: current state is now "primary"
23:22:46 | node_1 |     1 | node1:5432 |             primary |             primary
[postgres@Node1 ~]$ 23:22:47 19473 INFO  New state for this node (node 1, "node_1") (node1:5432): wait_primary ➜ primary
23:22:47 19473 INFO  New state for this node (node 1, "node_1") (node1:5432): primary ➜ primary

[postgres@Node1 ~]$

再次查询

[postgres@Node2 ~]$ pg_autoctl show state
  Name |  Node |  Host:Port |       TLI: LSN |   Connection |      Reported State |      Assigned State
-------+-------+------------+----------------+--------------+---------------------+--------------------
node_1 |     1 | node1:5432 |   3: 0/30642A0 |   read-write |             primary |             primary
node_2 |     2 | node2:5432 |   3: 0/30642A0 |    read-only |           secondary |           secondary

[postgres@Node2 ~]$

六、问题

6.1、编译安装pg_auto_failover出错

原因：未修改环境变量。

错误代码如下：

[postgres@Node1 pg_auto_failover-main]$ make && make install
fatal: Not a git repository (or any of the parent directories): .git
fatal: Not a git repository (or any of the parent directories): .git
make -C src/monitor/ all
make[1]: execvp: pg_config: Permission denied
make[1]: Entering directory `/install/pg_auto_failover-main/src/monitor'
make[1]: execvp: pg_config: Permission denied
make[1]: *** No rule to make target `all'.  Stop.
make[1]: Leaving directory `/install/pg_auto_failover-main/src/monitor'
make: *** [monitor] Error 2
[postgres@Node1 pg_auto_failover-main]$

解决方案：

vi  ~/.bash_profile
export PGHOME=/postgres/server
export PATH=$PGHOME/bin:$PATH
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
#说明：此处的PGDATA应改为pgauto/data目录进行安装。
export PGDATA=/postgres/pgauto/data
export PGPORT=5432
export PGUSER=postgres
export DATABASE=postgres

source .bash_profile

6.2、执行pg_autoctl show uri提示路径错误

原因：该用户无权执行安装目录

错误代码如下：

[postgres@Node2 ~]$ pg_autoctl show uri
23:25:20 35343 ERROR Failed to create state directory "/run/user/0/pg_autoctl": Permission denied
23:25:20 35343 ERROR Failed to build pg_autoctl pid file pathname, see above.
23:25:20 35343 FATAL Failed to set pid filename from PGDATA "/postgres/pgauto/data", see above for details.
23:25:20 35343 ERROR Failed to create state directory "/run/user/0/pg_autoctl": Permission denied
23:25:20 35343 ERROR Failed to build pg_autoctl pid file pathname, see above.
23:25:20 35343 FATAL Failed to set pid filename from PGDATA "/postgres/pgauto/data", see above for details.
[postgres@Node2 ~]$

解决方案：

sudo chmod -R 777 /run/user/0
sudo chown -R postgres:postgres /run/user/0

七、总结

综上，pg_auto_failover是一个强大的PostgreSQL高可用性解决方案，它不仅能够提供快速的故障转移能力，还能够确保数据的一致性和系统的稳定性。无论用户是初学者还是高级用户，都能够从pg_auto_failover中获益，特别是在需要高可用性和快速恢复服务的生产环境中更是如此。

原创文章，作者：kirin，如若转载，请注明出处：https://blog.ytso.com/tech/bigdata/317968.html

PostgreSQL+pg_auto_failover HA高可用保姆级集群部署手册

一、项目介绍

1.1、pg_auto_failover概述

1.2、功能列表

1.3、多节点架构

1.4、官网资料地址

二、环境初始化

2.1、环境规划

2.2、检查操作系统

2.3、统一主机名称

2.4、关闭SELINUX

2.5、关闭防火墙

2.6、安装插件

2.7、编辑hosts

2.8、用户limits设置

2.9、调整内核

2.10、时间同步

2.11、创建用户

2.12、所有节点配置sudo

2.13、配置各节点互信

三、PostgreSQL服务部署

3.1、目录规划

3.2、部署PostgreSQL（三台主机Node1、Node2、Node3都要部署）

3.3、由Node1分发文件到Node2、Node3

3.4、解压PostgreSQL安装包（三台主机Node1、Node2、Node3都要解压）

3.5、部署PostgreSQL（三台主机Node1、Node2、Node3都要部署）

3.6、设置软链接（三台主机Node1、Node2、Node3都要设置）

3.7、切换到pg源码contrib目录，执行安装（三台主机Node1、Node2、Node3都要编译安装）

3.8、切换到pg源码contrib/btree_gist目录，执行安装（三台主机Node1、Node2、Node3都要编译安装）

3.9、切换到pg源码contrib/pg_stat_statements目录，执行安装（三台主机Node1、Node2、Node3都要编译安装）

3.10、设置环境变量（三台主机Node1、Node2、Node3都要设置）

四、pg_auto_failover服务部署

4.1、安装pg_auto_failover

4.2、检查版本

4.2、配置Monitor

4.3、启动Monito

4.4、创建PrimaryNode

4.5、创建Secondary节点

4.6、查看pg_autoctl运行状态

4.7、查看pg_autoctl连接的URL

五、故障演练

5.1、主降备，备升主

5.2、手动切换主、备节点

六、问题

6.1、编译安装pg_auto_failover出错

原因：未修改环境变量。

解决方案：

6.2、执行pg_autoctl show uri提示路径错误

原因：该用户无权执行安装目录

解决方案：

七、总结

相关推荐

发表回复