說明：本系列文章僅用于共享我的學習成果，未經過生產系統考驗，對于知識點和一些組件的使用會不定時更新，僅供參考，如有錯誤的地方，歡迎留言共同學習。

本高可用系列測試不說理論知識，如有需要自行百度，因生產環境大多數是內網環境，無法連接互聯網，為模擬生產環境安裝，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           # 備庫