说明：本系列文章仅用于共享我的学习成果，未经过生产系统考验，对于知识点和一些组件的使用会不定时更新，仅供参考，如有错误的地方，欢迎留言共同学习。

本高可用系列测试不说理论知识，如有需要自行百度，因生产环境大多数是内网环境，无法连接互联网，为模拟生产环境安装，PostgreSQL高可用测试均采用离线部署。

所需软件包均以打包上传百度网盘，如有需要自行下载：https://pan.baidu.com/s/1Tb7GPMvj4kfKEIh8iyvdbA 提取码：n9w2 文件名：PostgreSQL_HA.tar.gz

第一章： 介绍测试环境
第二章： PostgreSQL + replication 部署
第三章： Etcd 部署和管理
第四章： Patroni 部署和管理
第五章： HAProxy + Keepalived 部署和管理
第六章： 高可用模拟故障测试用例
第七章： Prometheus + Grafana 监控部署
第八章： 高可用管理

第二章： PostgreSQL + replication 部署

1. 所有节点关闭防火墙、NetworkManager和SELINUX，如果需要启用防火墙，建议待配置完成后再启用，放开第一章提到的端口

systemctl stop firewalld.service
systemctl disable firewalld.service
systemctl stop NetworkManager.service
systemctl disable NetworkManager.service
sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config 
setenforce 0

2. 所有节点修改主机名

hostnamectl set-hostname pgtest1

3. 所有节点修改主机时间，确保节点间时间和时区同步，有条件的同步时间服务器

timedatectl
timedatectl list-timezones
timedatectl set-timezone Asia/Shanghai
date -s "20211001 00:00:00"
ntpdate time.windows.com && hwclock -w

4. 所有节点安装 PostgreSQL 所需要的软件包

# 挂载操作系统镜像包
mkdir /media/cdrom
mount /dev/cdrom /media/cdrom
# 配置YUM
mkdir /etc/yum.repos.d/bak
mv /etc/yum.repos.d/*.repo /etc/yum.repos.d/bak
cat >> /etc/yum.repos.d/enmo.repo << EOF
[Server]
name=Server
baseurl=file:///media/cdrom
enabled=yes
gpgcheck=0
EOF
# 执行yum安装
yum -y install wget flex libselinux-devel readline-devel zlib zlib-devel openssl-devel pam-devel libxml2-devel libxslt-devel python python-devel tcl-devel systemd-devel pcre-devel gcc gcc-c++ make tree psmisc
yum -y groupinstall "Development Tools"

5. 所有节点配置/etc/hosts解析文件

cat >> /etc/hosts << EOF
192.168.58.10   pgtest1
192.168.58.11   pgtest2
192.168.58.12   pgtest3
EOF

6. 所有节点创建用户和组

groupadd -g 5432 postgres
useradd -u 5432 -g postgres postgres
echo postgres | passwd --stdin postgres

7. 所有节点创建目录

mkdir -p /enmo/soft           # 存放软件包
mkdir -p /enmo/app/pg13/13.3  # PG_HOME
mkdir /enmo/pgdata            # PG_DATA
mkdir /enmo/pgwal             # 存放wal文件
mkdir /enmo/pgarch            # 存放wal归档文件
mkdir /enmo/app/pglog         # 存放PostgreSQL的软件日志文件
chown -R postgres:postgres /enmo
chmod 0700 /enmo/pgdata /enmo/pgwal /enmo/pgarch
# 创建目录软连接，方便日后数据库软件升级
ln -s /enmo/app/pg13 /enmo/app/pgsql

8. 所有节点配置系统内核参数

cat >> /etc/sysctl.conf << EOF
#for postgres db 13.3
kernel.shmall = 966327       # expr `free |grep Mem|awk '{print $2 *1024}'` / `getconf PAGE_SIZE`
kernel.shmmax = 3958075392   # free |grep Mem|awk '{print $2 *1024}'
kernel.shmmni = 4096
kernel.sem = 50100 64128000 50100 1280
fs.file-max = 76724200
net.ipv4.ip_local_port_range = 9000 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_max_syn_backlog = 4096
net.core.netdev_max_backlog = 10000
vm.overcommit_memory = 0
fs.aio-max-nr = 40960000
net.ipv4.tcp_timestamps = 0
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
EOF

9. 所有节点配置资源限制

cat >> /etc/security/limits.conf << EOF
#for postgres db 13.3
* soft    nofile  1048576
* hard    nofile  1048576
* soft    nproc   unlimited
* hard    nproc   unlimited
* soft    core    unlimited
* hard    core    unlimited
* soft    memlock unlimited
* hard    memlock unlimited
EOF

cat >> /etc/pam.d/login << EOF
#for postgres db 13.3
session    required     pam_limits.so
EOF

10. 所有节点配置环境变量

cat >> /etc/profile << EOF
#for postgres db 13.3
export LANG=en_US.utf8
export PGHOME=/enmo/app/pgsql/13.3/
export PGUSER=postgres
export PGPORT=5432
export PGDATA=/enmo/pgdata
export PATH=\$PGHOME/bin:\$PATH:\$HOME/bin
export LD_LIBRARY_PATH=\$PGHOME/lib:\$LD_LIBRARY_PATH
EOF

source /etc/profile   # 使环境变量生效

11. 所有节点解压PostgreSQL安装包，进行源码编译安装

PostgreSQL的官方网站下载：https://www.postgresql.org/

# 解压安装包
cd /enmo/soft
[root@pgtest1 soft]# tar -xvf PostgreSQL_HA.tar.gz
[root@pgtest1 soft]# tar -xzvf postgresql-13.3.tar.gz
# 编译安装
cd /enmo/soft/postgresql-13.3
./configure --prefix=/enmo/app/pgsql/13.3     # --with-pgport=6000
make -j 8 && make install
# 安装工具集
cd /enmo/soft/postgresql-13.3/contrib
make -j 8 && make install
# 查询版本，确认安装成功
postgres --version       # postgres (PostgreSQL) 13.3

12. 主节点初始化数据库

su - postgres
$ initdb --pgdata=/enmo/pgdata --waldir=/enmo/pgwal --encoding=UTF8 --allow-group-access --data-checksums --username=postgres --pwprompt --wal-segsize=32
# pg11起，initdb设置WAL段的大小  --wal-segsize=32

13. 主节点配置数据库参数

[postgres@pgpool01 ~]$ vi $PGDATA/postgresql.auto.conf
log_destination='stderr'
logging_collector=on
log_directory = '/enmo/app/pglog'
log_filename='postgresql-%Y-%m-%d.log'
log_duration=on
log_error_verbosity = default
log_line_prefix = '%m'
log_statement = 'all'
log_file_mode=0600
log_truncate_on_rotation=on
log_rotation_age=1d
log_rotation_size=0
log_checkpoints=on
log_lock_waits=on
log_min_duration_statement=500ms
log_min_messages=warning
idle_in_transaction_session_timeout=300000
autovacuum = 'on'
autovacuum_max_workers = 3
full_page_writes = 'on'
log_autovacuum_min_duration = -1
seq_page_cost = 1

# https://pgtune.leopard.in.ua/#/
superuser_reserved_connections = 10
shared_buffers = 1GB
effective_cache_size = 3GB
maintenance_work_mem = 256MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 1048kB
min_wal_size = 2GB
max_wal_size = 8GB
max_parallel_workers_per_gather = 1
max_parallel_workers = 2
max_parallel_maintenance_workers = 1

# 以下参数在 patroni 中设置，后面配置 patroni 时，可以将以下参数删除
max_connections = 3000
superuser_reserved_connections = 100
max_locks_per_transaction = 64
max_worker_processes = 8
max_prepared_transactions = 0
wal_level = 'logical'
wal_log_hints = 'on'
track_commit_timestamp = 'off'
max_wal_senders = 10
max_replication_slots = 10
wal_keep_size = '4096MB'  # wal_keep_size = wal_keep_segments * wal_segment_size = 128 * 32MB 
hot_standby = 'on'
listen_addresses = '*'
port = 5432
cluster_name = 'pg_cluster'
archive_mode = 'on'
archive_command = 'cp %p /enmo/pgarch/%f'

14. 主节点配置PostgreSQL的访问策略文件

vi $PGDATA/pg_hba.conf
#修改为如下：
host    all             all             192.168.58.0/24           md5

15. 主节点创建PostgreSQL服务，启动数据库

[root@pgtest1 ~]# vi /usr/lib/systemd/system/postgres-13.service 
[Unit]
Description=PostgreSQL 13 database server
After=syslog.target network.target

[Service]
Type=forking
TimeoutSec=120
User=postgres
Environment="PGHOME=/enmo/app/pgsql/13.3"
Environment="PGDATA=/enmo/pgdata"
Environment="PGPORT=5432"
Environment="LD_LIBRARY_PATH=/enmo/app/pgsql/13.3/lib:/usr/local/lib:/usr/local/lib64:/usr/lib64"
ExecStart=/bin/bash -c '${PGHOME}/bin/pg_ctl start -w -D ${PGDATA} -l /enmo/app/pglog/startup.log'
ExecStop=/bin/bash -c '${PGHOME}/bin/pg_ctl stop -m fast -w -D ${PGDATA}'
ExecReload=/bin/bash -c '${PGHOME}/bin/pg_ctl reload -D ${PGDATA}'

[Install]
WantedBy=multi-user.target

[root@pgtest1 ~]# systemctl daemon-reload
[root@pgtest1 ~]# systemctl enable postgres-13.service
[root@pgtest1 ~]# systemctl start postgres-13.service
# 确认启动成功
[root@pgtest1 ~]# netstat -nltp|grep 5432

16. 主节点安装 pg_stat_statements

# 编译安装，在11小节处已安装工具集，此处可不用操作
[root@pgtest1 ~]# cd /enmo/soft/postgresql-13.3/contrib/pg_stat_statements
[root@pgtest1 pg_stat_statements]# make && make install

# 配置参数
[postgres@pgtest1 ~]$ vi $PGDATA/postgresql.auto.conf
# pg_stat_statements
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 1000
pg_stat_statements.track = top
pg_stat_statements.track_utility = true
pg_stat_statements.save = true

# 重启postgres-13服务
[root@pgtest1 ~]# systemctl restart postgres-13.service

# 进入 PostgreSQL 数据库安装扩展插件
[root@pgtest1 ~]# psql
postgres=# create extension pg_stat_statements;

# 确认安装成功
postgres=# select * from pg_stat_statements;

17. 部署 replication 同步两个备库

# 主库上创建用于流复制的用户
postgres=# create user replica WITH REPLICATION ENCRYPTED PASSWORD 'replica';
# 配置主库允许接受流复制的连接
[root@pgtest1 ~]# vi $PGDATA/pg_hba.conf 
host    replication     replica         192.168.58.0/24       md5
[root@pgtest1 ~]# systemctl reload postgres-13.service
# 建议所有节点配置密码文件
[root@pgtest2 ~]# su - postgres
[postgres@pgtest2 ~]$ cat >> ~/.pgpass << EOF
# hostname:port:database:username:password
192.168.58.10:5432:replication:replica:replica
192.168.58.11:5432:replication:replica:replica
192.168.58.12:5432:replication:replica:replica
EOF
[postgres@pgtest1 ~]$ chmod 0600 .pgpass
# 所有备库节点执行pg_basebackup命令初始化数据库
[root@pgtest2 ~]# su - postgres
[postgres@pgtest2 ~]$ pg_basebackup -h 192.168.58.10 -p 5432 -U replica -D $PGDATA -Fp -P -X stream -R -v -l replica_20211016
# 所有备库节点创建PostgreSQL服务postgres-13.service，同主库一样，启动所有备库
[root@pgtest2 ~]# systemctl daemon-reload
[root@pgtest2 ~]# systemctl enable postgres-13.service
[root@pgtest2 ~]# systemctl start postgres-13.service
# 确认启动成功
[root@pgtest2 ~]# netstat -nltp|grep 5432

18. 测试主备同步

# 在主库上查询主备同步状态
postgres=# select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;
  pid  |   state   |  client_addr  | sync_priority | sync_state 
-------+-----------+---------------+---------------+------------
 22100 | streaming | 192.168.58.11 |             0 | async
 22101 | streaming | 192.168.58.12 |             0 | async
# 主库创建测试表，插入数据
postgres=# create table test_1 (id int4,create_time timestamp(0) without time zone);
postgres=# insert into test_1 values (1,now());
# 备库查询测试表，确认是否同步成功
postgres=# select * from test_1;

至此，PostgreSQL + replication 部署完成。

19. 补充：PostgreSQL 主备机的判断

# 1.通过pg_controldata输出：
[root@pgtest1 ~]# pg_controldata
Database cluster state:               in production         # 主库
Database cluster state:               in archive recovery   # 备库

# 2.通过数据字典表pg_stat_replication，主机表中能查到记录，备机表中无记录
postgres=# select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;
  pid  |   state   |  client_addr  | sync_priority | sync_state 
-------+-----------+---------------+---------------+------------
 22100 | streaming | 192.168.58.11 |             0 | async
 22101 | streaming | 192.168.58.12 |             0 | async

# 3.通过wal进程查看，显示 walsender 的是主机，显示 walreceiver 的是备机
[root@pgtest1 ~]# ps -ef |grep wal
postgres  11047  11042  0 00:19 ?        00:00:01 postgres: pg_cluster: walwriter 
postgres  12686  11042  0 17:17 ?        00:00:00 postgres: pg_cluster: walsender replica 192.168.58.12(31644) streaming 0/18000250
postgres  13166  11042  0 17:54 ?        00:00:00 postgres: pg_cluster: walsender replica 192.168.58.11(44964) streaming 0/18000250

[root@pgtest2 ~]# ps -ef |grep wal
postgres  11494  11488  0 17:54 ?        00:00:00 postgres: pg_cluster: walreceiver streaming 0/18000250

# 4. 通过自带函数判断，select pg_is_in_recovery();
postgres=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f           # 主库
 t           # 备库