最完善的PostgreSQL集群搭建

前言

在前几章的文章当中，已经有过了PostgreSQL的集群搭建了，那么为什么要重新再写这篇文章呢？

因为前面的Pgpool集群搭建，是直接从Pgpool搭建的，而不是从最开始的PostgreSQL集群开始搭建的，可能导致搭建出来的PostgreSQL集群不可用，或者说备机一直处于宕机状态等等问题，所以才有这篇文章的出现。

这篇文章是基于PostgreSQL14.9搭建的，且用的是源码安装方式进行搭建的，里面未涉及到PostgreSQL的自带的插件安装，如果要进行插件的安装，需要进入到源码包里面的contrib目录中，执行**make && make install **进行安装，如果只需要其中部分插件，则需要进入对应的插件目录，进行独立的编译安装。

在这里，给大家推荐一套PostgreSQL基于Pgpool-II搭建集群的视频，非常推荐，小编也是跟着这套视频逐步完善自己的PostgreSQL集群的。

生产环境安装、配置、管理PostgreSQL 14.5数据库集群_哔哩哔哩_bilibili

可以结合这套视频以及小编的笔记对着搭建，小编的环境是基于centos7.9搭建的。

1、准备

1.1、环境

主机名IP备注node1192.168.98.155主节点node2192.168.98.156备节点sync[同步复制]node3192.168.98.157备节点async[异步复制]WAL192.168.98.158WAL流复制使用的IP(standby primary_conninfo)VIP192.168.98.159VIP节点对外提供服务

1.2、创建postgres用户

# 创建postgres的家目录
mkdir -p /usr/pgsql-14
# 指定家目录创建以及登录方式
useradd -d /usr/pgsql-14 -s /bin/bash postgres
# 更改目录属主
chown -R postgres:postgres /usr/pgsql-14
# 修改密码
passwd postgres
# 验证
su - postgres
pwd

1.3、配置hosts文件

vim /etc/hosts

192.168.98.155 node1
192.168.98.156 node2
192.168.98.157 node3
192.168.98.158 vip
# 测试
ping -c1 node1 &&
ping -c1 node2 &&
ping -c1 node3

1.4、配置免密登录

ssh-keygen
ssh-copy-id node1
ssh-copy-id node2
ssh-copy-id node3

1.5、创建数据目录

mkdir -p /app/pgsql/14/data
chown -R postgres:postgres /app/pgsql/

1.6、下载postgresql14.9的源码包

postgresql14.9下载

1.7、解压并安装

# postgresql14.9
tar zxf postgresql-14.9.tar.gz
cd postgresql-14.9/
# 参数详解
# --prefix=/usr/pgsql-14   指定安装目录
# --with-systemd  支持systemd服务通知(只有使用systemd启动pg时才有效)
# --with-icu   支持使用icu排序规则功能
# --with-llvm  JIT编译
# --with-lz4   允许使用LZ4来压缩表数据
# --with-ssl=openssl  SSL(加密)连接的支持
# --with-uuid=ossp  支持使用ossp构建uuid
# --with-libxml  SQL/XML支持
# --with-libxslt 支持XML和XSL转换，使用此选项必须指定--with-libxml
# --with-system-tzdata=/usr/share/zoneinfo PostgreSQL包括自己的时区数据库，这是日期和时间操作所需要的，这个时区数据库实际上与许多操作系统(如FreeBSD  Linux  Solaris)提供的IANA时区数据库兼容，因此再次安装它是多余的，使用此选项时，将使用DIRECTORY中系统提供的时区数据库，而不是PostgreSQL源代码分发中包含的时区数据库，必须将目录指定为绝对路径。注意：安装过程中不会自动检测不匹配或错误的时区数据，如果使用此选项，建议回归测试，以验证指向的时区数据是否与PostgreSQL正确使用
# --with-segsize=4  设置段大小(默认是1  单位为GB)，大型表被划分为多个操作系统文件，每个文件的大小等于段大小，这避免了许多平台存在的文件大小限制问题，默认的段大小1GB在所有支持的平台上都是安全的，如果所用操作系统具有“大文件”支持(现在大多数系统都支持)，则可以使用更大的段大小，这有助于减少处理非常大的表时消耗的文件描述符数，建议值为2的幂，注意：不同段大小的PG是不兼容的。
# --with-blocksize=8  设置块大小(默认为8，单位为KB)，这是表中的存储I/O单位，默认值8KB，适用于大多数情况，如果阵列中的磁盘全是SSD，设置为4KB可能会有更好的性能，建议值为2的幂，注意：不同块大小的PG是不兼容的，这个参数与执行计划相关(Buffers: shared hit=805 read=64 dirtied=4)
# --with-wal-blocksize  WAL块大小(默认为8，单位为KB)，wal-segsize指定WAL段的大小(默认为16MB)，wal-segsize/wal-blocksize为每个WAL段中WAL块的数量，建议值为2的幂，注意：不同WAL块大小的PG是不兼容的，--with-wal-segsize大小可以在initdb时指定，也可以通过pg_resetwal修改
./configure --prefix=/usr/pgsql-14 --with-systemd
make && make install

# -------------------------------
yum install -y  systemd-devel readline-devel zlib-devel

1.8、配置环境变量

vim /etc/profile

# 软件安装目录
export PGHOME=/usr/pgsql-14/
# PG数据目录
export PGDATA=/app/pgsql/14/data/
export PATH=$PGHOME/bin:$PATH

# 更新环境配置
source /etc/profile

# 验证是否安装成功
psql -V  ||  psql --version
psql (PostgreSQL) 14.9

1.9 [附加]添加虚拟IP[命令式]

1、虚拟IP给备用服务器(primary_conninfo)流复制连接master使用

当master故障切换时，虚拟IP也必须从原来的master上down掉，然后再在新的master上up

1、设置虚拟IP

ifconfig eth0:0 192.168.98.158 netmask 255.255.255.0 up

2、删除虚拟IP

ifonfig eth0:0 down

3、永久设置虚拟IP

vim /etc/network/interfaces

auto eth0:0
iface eth0:0 inet static
name Ethernet alias LAN card
address 192.168.98.158
netmask 255.255.255.0
broadcast 192.168.98.2

1.10、[附加]添加虚拟IP[脚本式][node1 | node2]

~/virtual_ip只在node1和node2创建和运行
当node1[master]故障时，优先提升同步standby为master，同时尽快修复故障机器并加入集群做同步standby
node3[异步]功能仅是分担查询负载

vim ~/virtual_ip

#!/bin/bash
VIRTUAL_IP=$(/usr/sbin/ifconfig | grep 192.168.98.158)
if [ ! -n "${VIRTUAL_IP}" ];then
    echo 'virtual ip is null'
else
    /usr/sbin/ifconfig eth0:1 down
fi
/usr/sbin/ifconfig eth0:1 192.168.98.158 netmask 255.255.255.0 up

chmod 700 ~/virtual_ip

1.11、添加postgres可以使用sudo的权限

由于后面使用时，需要用到管理员权限，所以则将postgres添加到管理组

1、编辑sudoers文件添加管理组
vim /etc/sudoers
## Allows people in group wheel to run all commands
%wheel ALL=(ALL)       ALL
2、将postgres添加到sudo权限

usermode postgres -G whell

1.12、安装sshpass

安装sshpass是为了方便后面使用脚本时，不用手动输入密码

yum install -y sshpass

2、node1

2.1、初始化postgresql

su - postgres
# 查看初始化的参数
initdb --help
# 参数详解
# -D  [必填参数]指定数据库集簇应该存放的目录
# -E  数据库的编码
# --locale  设置默认区域
# -A  指定在pg_hba.conf中使用的默认认证方法
# -W  强制指定必须输入数据库超级用户(postgres)密码
# -U  数据库超级用户的用户名，通常为postgres
# --wal-segsize  设置WAL段尺寸，以MB字节为单位，默认的尺寸为16MB字节
# 初始化
initdb -D /app/pgsql/14/data/ --encoding=UTF-8 --locale=zh_CN.UTF-8 --waldir=/app/pgsql/14/wal -A scram-sha-256 -W -U postgres --wal-segsize=16

2.2、配置pg_hba.conf

vim $PGDATA/pg_hba.conf  || vim /app/pgsql/14/data/pg_hba.conf

# 允许本网段的机器无需密码相互访问(包括流复制)，对外提供服务的数据暂时不设置，其它内部全部注释
host     replication     rep             192.168.98.0/24         trust
host     all             all             127.0.0.1/32            trust
host     all             all             192.168.98.0/24         trust

2.3、postgresql.conf配置详解

----------CONNECTIONS AND AUTHENTICATION-------------------------

listren_addresses = '*' 监听所有IP

port = 5432 端口

max_connections = 100 所有连接数据，实际连接数据要-superuser_reserved_connections

superuser_reserved_connections = 2 流复制连接数据，设置备机数量

unix_socket_directories = '/tmp' 默认的unix socket文件存放

unix_socket_permissions = 0700 默认的访问权限是0777

client_connection_check_interval=1h 价差客户端连接状态的时间间隔

authentication_timeout = 5s 客户端认证的最长时间，以秒为单位

password_encryption = scram-sha-256 密码使用sha256验证

----------RESOURCE USAGE(except WAL)-------------------------

shared_buffers = 4GB 共享内存.物理内存-(操作系统+应用程序使用内存)

huge_pages = on 启用大页

temp_buffers = 8MB 临时缓冲区，仅用于当前会话的临时表，如果不使用临时表则可以调小，否则尽可能大

max_prepared_transactions = 96 预备事务最大数量，保持和max_connections一致，备机的此参数>=主机

work_mem = 8MB 工作内存，例如排序或哈希表

maintenance_work_mem = 256MB 维护操作要使用的最大内存，例如：VACUUM, CREATE INDEX, ALTER TABLE, ADD FOREIGN KEY 等会使用

autovacuum_work_mem = 64MB -1：使用maintenance_work_mem

logical_decoding_work_mem = 64MB 逻辑解码要使用的最大内存量

max_stack_depth = 7680kB 执行堆栈(递归)的最大安全深度，建议改成7680kB

shared_memory_type = mmap 共享内存类型，保持默认

dynamic_shared_memory_type = posix 动态共享内存类型，保持默认

max_files_per_process = 65535 运行同时打开文件的最大数量(fs.file-max*0.5)

effective_cache_size = 4GB 设置规划器对一个单一查询可用的有效磁盘缓冲区尺寸的假设，建议为物理内存的一半或更多

effective_io_concurrency = 1 期望可以同时执行的并发磁盘I/O操作数，对于RAID-0或RAID-1来说，这个值应该是驱动器总的数量，而对于RAID-5来说，这个值应该是除了做奇偶校验(parity)驱动器之外所有的数据驱动器

max_worker_processes = 8 可以使用多少个worker进程

max_parallel_workers_per_gather = 2 每个Gather node最多运行启用多少个work process

max_parallel_maintenance_workers = 2 单个应用程序启动的并行工作线程的最大数目

max_parallel_workers = 8 运行并行操作的最大工作线程数

----------WRITE-AHEAD LOG-------------------------

wal_level = replica 使用流复制

fsync = on 强制把数据同步更新到磁盘，不能off，否则数据可能丢失，但可大幅提升写入性能

synchronous_commit = on 在数据库服务器向客户端返回“成功”指示之前必须完成的WAL处理，只允许为on

wal_sync_method = fsync 每次提交时强制将WAL同步更新到磁盘

full_page_writes = on 全页写，不能off，否则数据可能丢失

wal_log_hints = on 当这个参数为on时，PostgreSQL服务器一个检查点后页面被第一次修改期间把该磁盘页面的整个内存都写入WAL，及时对所谓的提示位做非关键修改也会这样做

#wal_compression = on 压缩WAL

wal_buffers = 262143 尚未写入磁盘的WAL数据的共享内存大小

#检查点

checkpoint_timeout = 30min 自动WAL检查点之间的最长时间

checkpoint_completion_target = 0.9 指定检查点完成的目标，即检查点之间总时间的一小部分

max_wal_size = 32GB 允许WAL在自动检查点期间增长的最大大小，这是一个软限制，在特殊情况下，如重负载，故障或高设置，WAL尺寸可能会超过，增加此参数可能会增加崩溃恢复所需的时间

min_wal_size = 8GB 只要WAL磁盘使用率保持在此设置下，旧的WAL文件就会被回收以供将来在检查点使用，而不是被删除，这可用于确保保留足够的WAL空间来处理WAL使用中的峰值，例如：在运行大型批处理作业时

#WAL归档

#如果需要恢复年代久远的数据，将WAL归档文件复制到WAL主机上

archive_mode = on

#要先启动WAL主机，并创建目录archive，目录archive的所有者为postgres，权限为700

#postgres@wal-archive必须设置免密登录

archive_command = 'scp %p postgres@wal-archive:/archive/%f'

#归档恢复，因为在集群的服务器上，不需要恢复，所以不配置

#restore_command = 'scp postgres@wal-archive:/archive/%f %p'

#archive_cleanup_command = 'pg_archivecleanup postgres@wal-archive:/archive %r'

#recovery_end_command

#恢复，后面使用时在设置

#recovery_target

#recovery_target_name

#recovery_target_time

#recovery_target_xid

#recovery_target_lsn

recovery_target_timeline = 'latest'

----------REPLICATION 用时再设置-------------------------

#指定来自后备服务器或流式基础备份客户端的并发连接的最大数量(即同时运行WAL 发送进程的最大数)

max_wal_senders = 10

#备库保留的WAL大小

wal_keep_size = 2GB

#关键词FIRST加上num_sync指定一种基于优先的同步复制，并且会让事务提交等待，直到它们的WAL记录被复制到基于优先级选择的num_sync台同步后背上为止，例如，设置FIRST 3 (s1, s2, s3, s4)将导致每次提交都等待来自三台较高优先级的后备机的答复，这三台后备机将从后备服务器s1, s2, s3, s4中选出，在该列表中出现较早的后备服务器将被给予较高的优先级，并且被考虑为同步后备，列表中出现的其他后备服务器表示潜在的同步后备，如果当前的任何同步后备因为某种原因断开连接，它将立刻被下一个最高优先级的后备服务器替代，关键词FIRST是可选的。

#关键词ANY加上num_sync指定一种基于规定数量的同步复制，并且会让事务提交等待，直到它们的WAL记录被复制到所列出后备服务器中的至少num_sync台上为止，例如：设置ANY 3 (s1, s2, s3, s4)将导致每次提交会在收到s1, s2, s3, s4中任意三台后备服务器的回答后立刻继续下去

#同步复制要求在数据写入standby数据库后，事务的commit才返回，所以standby库出现问题时，会导致主库被hang住，解决这个问题的办法是至少两个standby服务器，这两个standby数据库只要有一个正常的，就不会让主库hang住，所以在实际应用中，同步流复制，总是有1个主库和2个以上的standby库，standby01名称不能喝主机名相同，此处设置的standby01, standby02 就是在standby数据库中配置连接参数application_name

#synchronous_standby_names = 'FIRST 1 (standby02)'

#指定在恢复期间，是否能够连接并运行查询，默认值是on，这个参数只能在服务器启动时设置，它只在归档恢复期间或后备机模式下才有效

hot_standby = on

----------REPORTING AND LOGGING-------------------------

#如果有条件，log最好挂载单独的存储

log_destination = 'csvlog'

logging_collector = on

log_directory = 'log'

#'postgresql-%Y-%m-%d_%H%M%S.log' 默认保存所有日志

#'postgresql-%d.log' 最多保存一个月的日志，每天一个文件，需要配合Log_truncate_on_ratation = on

log_filename = 'postgresql-%d.log' #'postgresql-%Y-%m-%d_%H%M%S.log'

log_file_mode = 0600

log_truncate_on_rotation = on

#记录检查点日志

log_checkpoints = on

#记录autovacuum日志

log_autovacuum_min_duration = 0

----------AUTOVACUUM-------------------------

autovacuum = on

autovacuum_max_workers = 3

autovacuum_naptime = 1h

autovacuum_vacuum_threshold = 10000

autovacuum_vacuum_insert_threshold = 10000

autovacuum_analyze_threshold = 20000

autovacuum_vacuum_scale_factor = 0

autovacuum_vacuum_insert_scale_factor = 0

autovacuum_analyze_scale_factor = 0

2.4、配置为系统服务

1、配置为自启动服务

vim /etc/systemd/system/postgresql-14.service

cat > /etc/systemd/system/postgresql-14.service << "EOF"
[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
Wants=network.target
After=network.target

[Service]
Type=forking
User=postgres
Group=postgres
PIDFile=/app/pgsql/14/data/postmaster.pid
ExecStart=/usr/pgsql-14/bin/pg_ctl start -D /app/pgsql/14/data
ExecStop=/usr/pgsql-14/bin/pg_ctl stop -D /app/pgsql/14/data -m fast
ExecReload=/usr/pgsql-14/bin/pg_ctl reload -D /app/pgsql/14/data

[Install]
WantedBy=multi-user.target
EOF

2、重新加载配置文件

systemctl daemon-reload

3、启动 | 重新启动 | 停止 postgresql

systemctl start | restart | stop postgreslq-14.service

4、加入开机自启动

systemctl enable postgresql-14.service

2.5、测试登录

# 切换到postgres账号
su - postgres
# 登录
psql -h localhost -U postgres -d postgres

2.6、数据库相关

1、创建表空间

--create tablespace fast_temp Location '/app/pgsql/14/temp';

2、回收postgres数据库的public权限

revoke all on database postgres from public;

3、创建流复制用户rep

create user rep replication login connection limit 4 encrypted password '123456';

4、修改连接数

-- alter user rep connection limit 4;

5、创建数据test用户

create user test with password '123456' nocreatedb;

6、创建表空间

--create tablespace test owner test location '/pdata/test' with(effective_io_concurrency = 1);

7、创建索引表空间

--create tablespace idxtest owner test location '/pfast/idxtest' with(effective_io_concurrency = 1);

8、创建数据库

create database test 
    --with owner = test                    -- 自定用户test为次库的所有者
        template template0
        encoding 'UTF8'
        LC_COLLATE 'zh_CN.UTF-8'
        LC_CTYPE 'zh_CN.UTF-8'
        --tablespace = test                -- 使用创建的数据目录，和pg系统数据目录分开
        connection limit = -1;

9、记录数据库相关的OID[important]

-- 获取用户OID
select oid from pg_authid where rolname = 'test';
-- 获取数据库OID
select oid from pg_database where datname = 'test';
-- 获取表空间OID
select oid,spcname from pg_tablespace;

10、切换数据库

\c test;

11、创建\删除\查看扩展

drop extension if exists pgcrypto;   -- 加密扩展
create extension pgcrypto;

12、从test数据库回收public的所有权限

revoke all on database test from public;

13、回收schema的public权限 | \dn 查看模式

revoke all on schema public from public;

14、将schema的所有权限授予用户test

grant all on schema public to test;

15、创建schema时的操作

drop schema if exists history;
create schema history;
revoke all on schema history from public;
grant all on schema history to test;

16、使用test用户创建测试表

su - postgres
psql -h localhost -U test -d test

create table public.test as
    select id,((random()*(1023-1)+1)::bigint) from generate_series(1,10000) as id;

select * from test;

17、修改pg_hba.conf允许本网段的机器无需密码访问

vim pg_hba.conf

host     replication     rep             192.168.98.0/24         trust
#host     all             all             127.0.0.1/32            trust
host     postgres        postgres        127.0.0.1/32            trust
host     postgres        postgres        192.168.98.0/24         trust
#host     all             all             192.168.98.0/24         trust
host     test            test            192.168.98.0/24         trust
host     test            test            0.0.0.0/0               scram-sha-256
# 重启
systemctl restart postgresql-14.service | pg_ctl -D ${PGDATA} reload

# 查看数据库状态
systemctl status postgresql-14.service  | pg_controldata -D ${PGDATA}

2.7、备份测试

node2

1、基础备份
su - postgres

pg_basebackup \
--pgdata=/pgbaseback \
--wal-method=stream \
--format=t \
--verbos --progress \
-h 192.168.98.155 -p 5432 -U rep
2、删除原数据，然后将备份的数据还原
#systemctl stop postgresql-14.service
ssh root@node1 'systemctl stop postgresql-14.service'  | ssh postgres@node1 "/usr/pgsql-14/bin/pg_ctl -D ${PGDATA} stop"
#su - postgres
#cd /app/pgsql/14/data
#rm -rf base/* && rm -rf pg_wal/*
ssh postgres@node1 "rm -rf /app/pgsql/14/data/* && rm -rf /app/pgsql/14/wal/*"
3、创建data和wal目录
mkdir data && mkdir wal
4、解压数据
tar -xf base.tar -C data
tar -xf pg_wal.tar -C wal
5、恢复数据
scp -r data/ postgres@node1:/app/pgsql/14/
scp -r wal/ postgres@node1:/app/pgsql/14/
node1

1、查看文件是否都恢复了
ls -l /app/pgsql/14/data/base
ls -l /app/pgsql/14/data/
ls -l /app/pgsql/14/wa/
2、备注：如果pg_wal没有恢复为软连接，需要重新指定
rm -rf /app/pgsql/14/data/pg_wal
ln -s /app/pgsql/14/wal /app/pgsql/14/data/pg_wal
3、重新启动postgresql
systemctl restart postgresql-14.service | pg_ctl -D ${PGDATA} reload
4、如果启动不成功，则查看日志，根据日志错误修改即可
1、日志显示：--这个不属于error和invalid，所以基本上不用管，只用管第二个错误即可

If you are restoring from a backup, touch "/app/pgsql/14/data/recovery.signal" and add required recovery options. If you are not restoring from a backup, try removing the file "/app/pgsql/14/data/backup_label".

解决：将backup_label移动到其他地方即可
mv backup_label  /backup/
2、日志提示：

LOG: invalid primary checkpoint record

解决：需要查看wal里面的二进制日志文件是否已经恢复过来了，这个问题大部分都是日志文件恢复错误导致的

解决1：删除wal里面的日志文件尝试能不能启动成功
rm -rf /app/pgsql/14/wal/*
解决2：从备份的机器上恢复日志文件过来
scp -r wal/ postgres@node1:/app/pgsql/14/

2.8、修改流复制主机为虚拟IP

vim postgresql.auto.conf

primary_conninfo = 'host=192.168.98.158 port=5432 user=rep application_name=standby02'

2.9、数据库启动 | 停止

这种启动方式仅在master上面启动，不能所有机器都用该种方式启动

sudo ./virtual_ip &&
systemctl restart postgresql-14.service

sudo ifconfig eth0:0 down &&
systemctl stop postgresql-14.service

3、node2

3.1、创建相应目录

1、创建二进制日志目录
mkdir -p /app/pgsql/14/wal

3.2、备份node1的数据

1、备份
su - postgres

pg_basebackup \
    --pgdata=${PGDATA} \
    --wal-method=stream \
    --waldir=/app/pgsql/14/wal \
    --format=p \
    --verbose \
    --progress \
    --write-recovery-conf \
    -h 192.168.98.155 -p 5432 -U rep
注释：由于指定了--write-recovery-conf选项，备份后在PGDATA多了一个名称为standby.signal的文件，它是一个没有任何内容的文件，启动服务器时PGDATA中存在该文件，则服务器将进入待机模式

也可以不使用--write-recovery-conf选项，pg_basebackup完成后，手动创建

--format=p 表示不压缩
su - postgres
touch ${PGDATA}/standby.signal
注：当将数据同步到备机后，需要将synchronous_standby_names给注释掉
vim ${PGDATA}/postgresql.conf

#synchronous_standby_names='FIRST 1 (standby02)'
2、设置data目录的权限值
chmod 700 /app/pgsql/14/data
3、修改postgresql.auto.conf
1、如果使用了--write-recovery-conf选项，则修改postgresql.auto.conf vim ${PGDATA}/postgresql.auto.conf 2、否则修改postgresql.conf vim ${PGDATA}/postgresql.conf

3、注：application_name是主机postgresql.conf->synchronous_standby_names中设置的名称
primary_conninfo = 'host=node1 port=5432 user=rep application_name=standby02'

3.3、将同步复制开启[node1]

如果是异步复制，则这一步不能设置即可

如果是同步和异步同时存在，那么则需要执行这一步，并且在开启"synchronous_standby_names"的时候，括号里面只包含同步复制的备机数据库的应用名称即可

ssh postgres@node1

vim ${PGDATA}/postgresql.conf

# 开启同步复制(sync)
# 在该配置里面如果没有设置的备机数据库应用名，则备份的时候默认是异步复制(async)
synchronous_standby_names = 'FIRST 1 (standby02)'

# 重启数据库
systemctl restart postgresql-14.service

3.4、检查状态并启动数据库

1、查看数据库状态

su - postgres 
pg_controldata -D ${PGDATA}
#最后一次checkpoint的wal：
#Latest checkpoint's REDO location:    0/8000060
#最后一次checkpoint时的时间线：
#Latest checkpoint's TimeLineID:       1
#如果Latest checkpoint's REDO WAL file和Latest checkpoint's TimeLineID与主服务器不一致，还需要使用pg_rewind同步预写式日志

# 该命令需要在执行命令所在服务器的pg数据库停止额时候执行
pg_rewind --target-pgdata=${PGDATA} --source-server='host=node1 port=5432 user=postgres dbname=postgres'

pg_ctl -D ${PGDATA} start

3.5、配置postgresql为系统服务

1、配置为自启动服务

vim /etc/systemd/system/postgresql-14.service

[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
Wants=network.target
After=network.target

[Service]
Type=forking
User=postgres
Group=postgres
PIDFile=/app/pgsql/14/data/postmaster.pid
ExecStart=/usr/pgsql-14/bin/pg_ctl start -D /app/pgsql/14/data
ExecStop=/usr/pgsql-14/bin/pg_ctl stop -D /app/pgsql/14/data -m fast
ExecReload=/usr/pgsql-14/bin/pg_ctl reload -D /app/pgsql/14/data

[Install]
WantedBy=multi-user.target

2、重新加载配置文件

systemctl daemon-reload

3、启动 | 重新启动 | 停止 postgresql

systemctl start | restart | stop postgreslq-14.service

4、加入开机自启动

systemctl enable postgresql-14.service

3.6、查询流复制的情况[node1]

su - postgres
psql -h localhost -U postgres -d postgres

select * from pg_stat_replication;
select usename,application_name,client_addr,sync_state from pg_stat_replication;

postgres=# select usename,application_name,client_addr,sync_state from pg_stat_replication;
 usename | application_name |  client_addr   | sync_state 
---------+------------------+----------------+------------
 rep     | standby02        | 192.168.98.156 | sync
(1 row)

3.7、修改流复制主机为虚拟IP

vim postgresql.auto.conf

primary_conninfo = 'host=192.168.98.158 port=5432 user=rep application_name=standby02'

3.8、数据库启动 | 重启

这种启动方式仅在master上面启动，不能所有机器都用该种方式启动

sudo ./virtual_ip &&
systemctl restart postgresql-14.service

sudo ifconfig eth0:0 down &&
systemctl stop postgresql-14.service

4、node3

4.1、创建相应目录

1、创建二进制日志目录
mkdir -p /app/pgsql/14/wal

4.2、备份node1的数据

1、备份
su - postgres

pg_basebackup \
    --pgdata=${PGDATA} \
    --wal-method=stream \
    --waldir=/app/pgsql/14/wal \
    --format=p \
    --verbose \
    --progress \
    --write-recovery-conf \
    -h 192.168.98.155 -p 5432 -U rep
注释：由于指定了--write-recovery-conf选项，备份后在PGDATA多了一个名称为standby.signal的文件，它是一个没有任何内容的文件，启动服务器时PGDATA中存在该文件，则服务器将进入待机模式

也可以不使用--write-recovery-conf选项，pg_basebackup完成后，手动创建

--format=p 表示不压缩
su - postgres
touch ${PGDATA}/standby.signal
注：当将数据同步到备机后，需要将synchronous_standby_names给注释掉
vim ${PGDATA}/postgresql.conf

#synchronous_standby_names='FIRST 1 (standby02)'
2、设置data目录的权限值
chmod 700 /app/pgsql/14/data
3、修改postgresql.auto.conf
1、如果使用了--write-recovery-conf选项，则修改postgresql.auto.conf vim ${PGDATA}/postgresql.auto.conf 2、否则修改postgresql.conf vim ${PGDATA}/postgresql.conf

3、注：application_name是主机postgresql.conf->synchronous_standby_names中设置的名称
primary_conninfo = 'host=node1 port=5432 user=rep application_name=standby03'

4.3、检查状态并启动数据库

1、查看数据库状态

su - postgres 
pg_controldata -D ${PGDATA}
# Database cluster state:    in productio
#最后一次checkpoint的wal：
#Latest checkpoint's REDO location:    0/8000060
#最后一次checkpoint时的时间线：
#Latest checkpoint's TimeLineID:       1
#如果Latest checkpoint's REDO WAL file和Latest checkpoint's TimeLineID与主服务器不一致，还需要使用pg_rewind同步预写式日志
pg_rewind --target-pgdata=${PGDATA} --source-server='host=node1 port=5432 user=postgres dbname=postgres'

pg_ctl -D ${PGDATA} start

4.4、配置postgresql为系统服务

1、配置为自启动服务

vim /etc/systemd/system/postgresql-14.service

[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
Wants=network.target
After=network.target

[Service]
Type=forking
User=postgres
Group=postgres
PIDFile=/app/pgsql/14/data/postmaster.pid
ExecStart=/usr/pgsql-14/bin/pg_ctl start -D /app/pgsql/14/data
ExecStop=/usr/pgsql-14/bin/pg_ctl stop -D /app/pgsql/14/data -m fast
ExecReload=/usr/pgsql-14/bin/pg_ctl reload -D /app/pgsql/14/data

[Install]
WantedBy=multi-user.target

2、重新加载配置文件

systemctl daemon-reload

3、启动 | 重新启动 | 停止 postgresql

systemctl start | restart | stop postgreslq-14.service

4、加入开机自启动

systemctl enable postgresql-14.service

4.5、查询流复制的情况[node1]

su - postgres
psql -h localhost -U postgres -d postgres

select * from pg_stat_replication;
select usename,application_name,client_addr,sync_state from pg_stat_replication;

postgres=# select usename,application_name,client_addr,sync_state from pg_stat_replication;
 usename | application_name |  client_addr   | sync_state 
---------+------------------+----------------+------------
 rep     | standby03        | 192.168.98.157 | async
 rep     | standby02        | 192.168.98.156 | sync
(2 rows)

4.6、修改流复制主机为虚拟IP

vim postgresql.auto.conf

primary_conninfo = 'host=192.168.98.158 port=5432 user=rep application_name=standby02'

5、故障模拟

5.1、创建测试表[node1]

1、创建test01表[node1]

-- 查看有哪些表
\d
create table test01 as
    select id,((random()*(1023-1)+1)::bigint) from generate_series(now(),now()+'1 hour','1 minute') as id;

select * from test01 limit 10;

2、登录node2查看并删除

psql -h node2 -U test -d test -c "select * from test01 limit 10";
psql -h node2 -U test -d test -c "delete from test01";

3、登录node3查看并删除

psql -h node3 -U test -d test -c "select * from test01 limit 10";
psql -h node3 -U test -d test -c "delete from test01";

5.2、停止数据库[node1]

# 停掉网卡和数据库
sudo /usr/sbin/ifconfig eth0:1 down && systemctl stop postgresql-14.service

5.3、将node2升级为master[node2]

故障转移运行pg_ctl promote，或者调用pg_promote函数，或者创建一个触发器文件，其文件名和路径由promote_tigger_file设置指定，如果使用pg_ctl promote或调用pg_promote函数进行故障转移，promote_trigger_file就不是必要的。

1、修改postgresql.conf配置
su - postgres
vim ${PGDATA}/postgresql.conf
不在使用同步流复制主机，注释synchronous_standby_names，node2提升为主机时，如果不修改synchronous_standby_names，则任何写操作都会被hang住(挂起)
#synchronous_standby_names='FIRST 1 (standby02)'
如果仍然需要同步复制，则将同步复制主机改为standby03
synchronous_standby_names='FIRST 1 (standby03)'
如果node1已经修复并重新加入了集群，则将synchronous_standby_names修改为node1
synchronous_standby_names='FIRST 1 (standby01)'
2、重新加载数据库
systemctl reload postgresql-14.service
3、查看synchronous_standby_names参数
psql -h node2 -U postgres -d postgres -c 'show synchronous_standby_names'
4、提升为master
#第一种方式 使用pg_ctl promote
sudo ~/virtual_ip && pg_ctl promote -D ${PGDATA} -s
#第二种方式 使用pg_promote函数完成故障转移
sudo sh -f ~/virtual_ip && psql -h node2 -U postgres -d postgres -c "select pg_promote();"
#第三种方式 配置promote_trigger_file完成故障转换
# promote_trigger_file因为需要在postgresql.conf中配置，因此目前一般不使用此方式
#假设postgresql.conf中promote_trigger_file='promote.signal'，提升为主机只需要在PGDATA目录中创建一个promote.signal文件即可
su - postgres
sudo sh -f ~/virtual_ip && touch ${PGDATA}/promote.signal
4、查看是否升级为master
pg_controldata ${PGDATA}

5.4、查看流复制状态

psql -h node2 -U postgres -d postgres -c "select usename,application_name,client_addr,sync_state from pg_stat_replication;"

6、故障恢复

6.1、查看状态[node1 | node2]

当查看状态后，如果Latest checkpoint's REDO WAL file和Latest checkpoint's TimeLineID与主服务器不一致，还需要进行同步数据

pg_controldata -D ${PGDATA}

6.2、同步数据[node1]

同步数据使用pg_rewind和pg_basebackup都可以

注：下面两种方式均在PG服务停止时执行

1、第一种方式使用pg_rewind
pg_rewind --target-pgdata=${PGDATA} --source-server='host=192.168.98.158 port=5432 user=postgres dbname=postgres'
2、第二种方式使用pg_basebackup同步数据
rm -rf /app/pgsql/14/data/* /app/pgsql/14/wal/*

pg_basebackup \
    --pgdata=${PGDATA} \
    --wal-method=stream \
    --waldir=/app/pgsql/14/wal \
    --format=p \
    --verbose \
    --progress \
    --write-recovery-conf \
    -h 192.168.98.158 -p 5432 -U rep

6.3、将synchronous_standby_names打开[node1]

vim postgresql.conf

synchronous_standby_names = 'FIRST 1 (standby02)'

6.4、修改primary_conninfo[node1]

vim postgresql.auto.conf

primary_conninfo = 'host=192.168.98.158 port=5432 user=rep application_name=standby01'

6.5、将synchronous_standby_names打开[node2]

vim postgresql.conf

synchronous_standby_names = 'FIRST 1 (standby01)'
# 重新加载PG服务
systemctl reload postgresql-14.service

# 查看synchronous_standby_names值
[postgres@node2 data]$ psql -h node2 -U postgres -d postgres -c "show synchronous_standby_names;"
 synchronous_standby_names 
---------------------------
 FIRST 1 (standby01)
(1 row)

6.6、node1加入集群[node1]

1、在备用模式下，启动node1，在数据目录建立名为standby.signal文件
touch ${PGDATA}/standby.signal && \
chmod 600 ${PGDATA}/standby.signal && \
ls -l ${PGDATA}/standby.signal
2、启动PG
systemctl restart postgresql-14.service
3、查看集群[流复制]情况
psql -h 192.168.98.158 -U postgres -d postgres -c "select usename,application_name,client_addr,sync_state from pg_stat_replication;"
4、查看数据同步状况
psql -h node1 -U test -d test -c "\d"

6.7、将node1升级为主库

1、停掉虚拟网卡 | 创建standby.signal文件 | 停止数据库服务[node2]
sudo ifconfig eth0:0 down && \
systemctl stop postgresql-14.service && \
ifconfig

vim postgresql.conf
#synchronous_standby_names = 'FIRST 1 (standby01)'
2、启动虚拟IP以及重新设置为master[node1]
# 启动虚拟IP
sudo ~/virtual_ip
# 设置为master
pg_ctl promote -D ${PGDATA} -s

# 启动同步复制
vim postgresql.conf

synchronous_standby_names = 'FIRST 1 (standby02)'

# 重新加载配置文件
systemctl reload postgresql-14.service
3、查看数据库状态[node1][node2]
pg_controldata -D ${PGDATA}
4、如果Latest checkpoint's REDO WAL file和Latest checkpoint's TimeLineID与主服务器不一致，还需要使用pg_rewind同步预写式日志[node2]
pg_rewind --target-pgdata=${PGDATA} --source-server='host=192.168.98.158 port=5432 user=postgres dbname=postgres'
5、如果第四步进行了，则需要修改配置[node2]
vim postgresql.conf
#synchronous_standby_names = 'FIRST 1 (standby01)'

vim postgresql.auto.conf
primary_conninfo = 'host=192.168.98.158 port=5432 user=rep application_name=standby02'
6、创建备机的standby.signal文件[node2]
touch ${PGDATA}/standby.signal 
chmod 600 ${PGDATA}/standby.signal 
7、启动数据库[node2]
systemctl start postgresql-14.service
8、查看集群状态[node1]
psql -h 192.168.98.158 -U postgres -d postgres -c "select usename,application_name,client_addr,sync_state from pg_stat_replication;"

7、原始的自动故障切换和负载均衡

使用最简单的方式(越简单越可靠) 实现自动故障切换和负载均衡

7.1、故障检测

pg_isready

返回值：

0：accepting connections 接收连接

1：rejecting connections 拒绝连接

2：no response 无响应

实现原理：

假设现在node1为master，node2为sync salve

分别在node1和node2(或其他sync slave)上创建pg_fail_switch.sh

待集群全部启动以后，在node2(如有多台sync slave，如果判断在优先级最高的sync slave)上运行pg_fail_switch.sh
 su - postgres

 vim ~/pg_fail_switch.sh
注意hosts

pg_fail_switch.sh在node1上的hosts名称为node2

pg_fail_switch.sh在node2上的hosts名称为node1

同时要注意修改网卡名称
#!/bin/bash
password="123456"
pg_isready -q -h node2 -p 5432 -U postgres -d postgres
OP_MODE=$?
while [ ${OP_MODE} -eq 0 ];do
    sleep 30s  #sleep 支持睡眠(秒s  分m  时h)
     pg_isready -q -h node2 -p 5432 -U postgres -d postgres
     OP_MODE=$?


done
# 卸载node1上的流复制虚拟IP
sshpass -p 123456 ssh root@node2 "/usr/sbin/ifconfig eth0:0 down"
# pg_isready 已经返回非0值了，因此不需要停止数据库了，如果为了更可靠可以停止数据库，但是停止时间可能比较长
#sshpass -p 123456 ssh root@node1 "systemctl stop postgresql-14.service"

#sudo可以免密码 
expect -c "
    spawn su - root
    expect {
        \"*(password|Password|passwd):\" {send -- \"${password}\r\";exp_continue}
        \"*密码：\" {send -- \"${password}\r\"}
    }
    expect \"root@*\" {send -- \"ifconfig eth0:0 192.168.98.158 netmask 255.255.255.0 up\r\"}
    expect eof
"
pg_ctl promote -D ${PGDATA} -s

8、误操作数据恢复模拟[node1]

8.1、创建表测试表

create table sms_codes(
    objectid bigint not null,
    mobile varchar(16) not null,
    code varchar(64) not null,
    gen timestamptz not null,
    constraint pk_sms_codes_objectid primary key(objectid) with (fillfactor=80) 
) with (
    fillfactor=100,
    autovacuum_enabled=false,
    toast.autovacuum_enabled=false,
    autovacuum_analyze_threshold=600
);

8.2、生成测试数据

--写入一年的发送的短信验证码，每10s发送一条，全年数据量为365*24*60(60/10)=3153600

--测试间隔：20*60*(60/10)*31=267840
select
    (row_number() over())
    ,((random()*(9999999999-1000000000)+1000000000)::bigint)
    ,((random()*(999999-100000)+100000)::bigint)
    ,x
from generate_series(
     make_timestamptz(2024,1,1,0,0,0)
    ,make_timestamptz(2024,1,1,0,0,59.999)
    ,make_interval(secs => 10::float8)
) as x;

-- 压测数据 24*60*(60/10)*365 = 3153600
-- 如果数据较多，可以多打开几个psql终端同时插入
\timing on
do $$
    declare
        v_start timestamptz;
        v_end timestamptz;
        v_interval interval;
    begin
        v_start := timestamp with time zone '2022-01-01 00:00:00.000+00';
        v_end := timestamp with time zone '2022-12-31 23:59:59.999+00';
        v_interval = make_interval(secs => 10::float8);
        
        insert into sms_codes(objectid,mobile,code,gen)
            select
                (row_number() over()) as objectid
                ,format('1%s',((random()*(9999999999-100000000)+1000000000)::bigint))
                ,((random()*(999999-100000)+100000)::bigint)
                ,x
            from generate_series(v_start,v_end,v_interval) as x;
        end;
    $$;
    
    select count(*) from sms_codes;

9、pgpool-II

9.1、下载pgpool-II

pgpool-II4.3.3

需求相关

wget https://www.pgpool.net/mediawiki/images/pgpool-II-4.3.3.tar.gz

9.2、编译安装[node1 | node2 | node3]

yum install -y arping 
sudo rm -rf /usr/pgpool-II && \
sudo mkdir -p /usr/pgpool-II && \
sudo chown -R postgres:postgres /usr/pgpool-II/

tar -xf pgpool-II-4.3.3.tar.gz && \
cd pgpool-II-4.3.3
./configure --help
./configure --prefix=/usr/pgpool-II --with-pgsql=/usr/pgsql-14
make && make install

# 在线恢复建议手动操作，防止脑裂
# cd src/sql/pgpool-recovery
#make USE_PGXS=1 PG_CONFIG=/usr/pgsql-14/bin/pg_config
#make USE_PGXS=1 PG_CONFIG=/usr/pgsql-14/bin/pg_config install

9.3、配置环境变量

sudo vim /etc/profile

export PGPOOL=/usr/pgpool-II
export PATH=$PGPOOL/lib:$PGPOOL/bin:$PATH

source /etc/profile

9.4、pgpool-II4.3.3的参数详解

#------------------------------------------------------------------------------
# BACKEND CLUSTERING MODE       集群采用的模式(共6种)
# Choose one of: 'streaming_replication', 'native_replication',
#    'logical_replication', 'slony', 'raw' or 'snapshot_isolation'
# (change requires restart)
#------------------------------------------------------------------------------

backend_clustering_mode = 'streaming_replication'

#------------------------------------------------------------------------------
# CONNECTIONS           PostgreSQL节点配置信息
# https://www.pgpool.net/docs/latest/en/html/runtime-config-connection.html
#------------------------------------------------------------------------------

# - pgpool Connection Settings -
listen_addresses = '0.0.0.0'
port = 9999
socket_dir = '/tmp'

#当此参数设置为1或更大时,当前连接数超过(num_init_children-reserved_connections)将不接受来自客户端的连接,并显示错误消息“抱歉，客户端已太多”.
#如果此参数设置为0,则不会拒绝来自客户端的连接,但是侦听队列的长度可能很长,并可能导致系统不稳定.默认值为0.
reserved_connections=1

# - pgpool Communication Manager Connection Settings pgpool通信管理器连接设置-
# 

pcp_listen_addresses = '0.0.0.0'
pcp_port = 9898
pcp_socket_dir = '/tmp'
#指定连接队列的长度(实际上是listen的“backlog”参数)为,backlog = listen_backlog_multiplier * num_init_children。
listen_backlog_multiplier = 1
#设置为on时,Pgpool-II将在传入的客户端连接上启用序列化。如果没有序列化,操作系统内核会唤醒所有Pgpool-II子进程以执行accept,但是只有其中的一个进程实际上获得了传入连接,因为所有子进程同时唤醒，会发生大量的上下文切换，会影响性能.
#可以通过序列化accept来解决性能问题,因为只有一个Pgpool-II进程被唤醒并传入连接执行accept.但序列化有其自身的开销,设置为on时建议将num_init_children值设置大一点.对于少量num_init_children,可能会由于序列化开销而降低性能.
#注意:启用child_life_time后,serialize_accept不起作用.如果您打算打开serialize_accept,请确保将child_life_time设置为0.如果您担心Pgpool-II进程内存泄漏或任何潜在问题,则可以使用child_max_connections,这纯粹是一种实现限制,将来可能会被删除
#serialize_accept = off

# - Backend Connection Settings PostgreSQL连接设置(最多128台)-
# https://www.pgpool.net/docs/latest/en/html/runtime-config-backend-settings.html

#有几台PostgreSQL NODE就设置几台(包含主备)
backend_hostname0 = 'node1'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/app/pgsql/14/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'         #允许故障转移
backend_application_name0 = 'standby01'

backend_hostname1 = 'node2'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/app/pgsql/14/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'standby02'

backend_hostname2 = 'node3'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/app/pgsql/14/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'
backend_application_name2 = 'standby03'

# - Authentication -
enable_pool_hba = on                       #是否启用pgpool_hba
pool_passwd = 'pool_passwd'                 #pool_passwd密码              
authentication_timeout = 5s                 #完成客户端认证的最长时间，以秒计  
allow_clear_text_frontend_auth = off        #如果PostgreSQL需要md5或SCRAM身份验证才能进行某些用户的身份验证,但该用户的密码不存在于“pool_passwd”文件中,则启用allow_clear_text_frontend_auth将允许Pgpool-II对前端客户端使用明文密码身份验证,以从客户端获取纯文本形式的密码并将其用于后端身份验证。

# - SSL Connections -
#不使用

#------------------------------------------------------------------------------
# POOLS     pgpool进程池
# https://www.pgpool.net/docs/latest/en/html/runtime-config-connection.html
#------------------------------------------------------------------------------

# - Concurrent session and pool size 并发会话和池大小-

#Pgpool-II启动的子进程数量.分为两种情况:
#   reserved_connections>0时,当前连接数超过(num_init_children-reserved_connections)将不接受来自客户端的连接,并显示错误消息“抱歉，客户端已太多”.
#   reserved_connections=0时,来自客户端的连接将写入队列等待连接,队列大小为listen_backlog_multiplier*num_init_children
#
#   max_pool、num_init_children、max_connections superuser_reserved_connections必须满足以下公式：
#       max_pool*num_init_children <= (max_connections - superuser_reserved_connections) (no query canceling needed)
#       max_pool*num_init_children*2 <= (max_connections - superuser_reserved_connections) (query canceling needed)

num_init_children = 32   

#每个Pgpool-II子进程中缓存连接的最大数量.
#如果传入连接连接到具有相同用户名,相同的PostgreSQL连接参和相同的数据库时,Pgpool-II将重用缓存的连接.
#如果条件不满足Pgpool-II将创建与PostgreSQL的新连接.
#如果缓存的连接数超过max_pool,则将丢弃最旧的连接并将该槽用于新连接。
#默认值为4.请注意从Pgpool-II进程到后端的连接数可能达到num_init_children * max_pool
max_pool = 2

# - Life time 与PostgreSQL连接池的生成周期-

#指定Pgpool-II子进程(如果该进程保持空闲状态)的的生命周期(以秒为单位).当Pgpool-II由于child_life_time而终止时,新的子进程会立即生成.child_life_time是防止Pgpool-II子进程中的内存泄漏和其他意外错误的措施。默认值为 300(5 分钟),设置为0将禁用该功能。
#注意： child_life_time不适用于尚未接受任何连接的进程。
#注意：启用child_life_time后，serialize_accept将失效。
child_life_time = 10min

#默认值为0,表示关闭该功能.Pgpool-II子进程为child_max_connections客户端连接提供服务后终止子进程,并将立即生成一个新的子进程来代替它.child_max_connections在非常繁忙的服务器上很有用,因为child_life_time和connection_life_time永远不会被触发.
#child_max_connections = 0

#指定终止与PostgreSQL后端的连接的时间(以秒为单位).默认值为0,表示连接不会断开。
#connection_life_time = 0

#指定客户端自上次查询以来保持空闲状态时断开客户端连接的时间(以秒为单位).防止Pgpool-II子级被懒惰的客户端占用或客户端与Pgpool-II之间的TCP/IP连接中断非常有用.默认值为0,将关闭该功能。
#client_idle_limit = 0

#------------------------------------------------------------------------------
# LOGS  日志
# https://www.pgpool.net/docs/latest/en/html/runtime-config-logging.html
#------------------------------------------------------------------------------

# - Where to log -

log_destination = 'stderr'                  #枚举[syslog,stderr],logging_collector为on时需要设置为stderr,syslog写入至/var/log/pgpool.log.

# - What to log -
#log_line_prefix = '%m: %a pid %p: '
#log_connections = off                       #设置为 on,日志记录所有客户端连接时的信息
#log_disconnections = off                    #设置为 on,日志记录所有客户端断开连接时的信息
#log_hostname = off                          #设置为 on,在ps命令结果中输出主机名而不是IP地址,以及连接日志(当log_connections=on时)
#log_statement = off                         #设置为 on,日志记录所有SQL语句
#log_per_node_statement = off                #与log_statement类似,不同之处在于它分别打印每个数据库节点的日志,确保复制或负载均衡正常工作
#log_client_messages = off                   #设置为 on,日志记录所有将客户端消息
#log_standby_delay = 'if_over_threshold'     #枚举[if_over_threshold,,none],日志记录备机延迟,值范围

# - Syslog specific 只有log_destination=syslog才有效-

#syslog_facility = 'LOCAL0'
                                   # Syslog local facility. Default to LOCAL0
#syslog_ident = 'pgpool'
                                   # Syslog program identification string
                                   # Default to 'pgpool'

# - Debug -

#log_error_verbosity = default               #控制为记录的每条消息发出的详细信息量,值为terse、default、verbose
#client_min_messages = notice                #控制发送到客户端的最低消息级别
#log_min_messages = warning                  #默认值为“警告”，控制向日志发出的最低消息级别

# This is used when logging to stderr:
logging_collector = on                      #logging_collector参数只有log_destination = 'stderr'才有效.启用一个后台进程收集标准stderr消息并将其重定向到日志文件

# -- Only used if logging_collector is on 只有logging_collector = on才有效---
log_directory = '/app/pgsql/14/pgpool/logs'        #日志文件的目录
log_filename = 'pgpool-%Y-%m-%d.log'        #设置所创建日志文件的文件名
#log_file_mode = 0600                        #日志文件的权限
#log_truncate_on_rotation = off              #是否截断日志文件,需要和log_filename配合使用

#log_rotation_age = 1d                      #使用单个日志文件的最长时间,之后将创建新的日志文件.如果指定此值时不带单位,则以分钟为单位.默认值为 24 小时
#log_rotation_size = 0                      #单个日志文件的最大大小.将这几千字节发送到日志文件后,将创建一个新的日志文件.设置为零可禁用基于大小创建新日志文件。

#------------------------------------------------------------------------------
# FILE LOCATIONS    文件位置调整至ssd磁盘
# https://www.pgpool.net/docs/latest/en/html/runtime-misc.html
#------------------------------------------------------------------------------

pid_file_name = '/app/pgsql/14/pgpool/pgpool.pid'  #pgpool
logdir = '/app/pgsql/14/pgpool'                           #pgpoo状态文件目录

#------------------------------------------------------------------------------
# CONNECTION POOLING        pgpool连接池功能
# https://www.pgpool.net/docs/latest/en/html/runtime-config-connection-pooling.html
#------------------------------------------------------------------------------

connection_cache = on                       #设置为on时,将缓存到后端的连接.即使启用了connection_cache也不会连接template0、template1、 postgres、regression这几个数据库.

#https://www.postgresql.org/docs/14/sql-abort.html
#reset_query_list = 'ABORT; DISCARD ALL'     #默认值.在退出用户会话时要发送的用于重置后端连接的SQL命令.可以通过用 “;” 分隔每个命令来指定多个命令。
#reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'
#   ABORT:中止当前事务
#   RESET ALL:把所有运行时参数的值恢复到默认值
#   SET SESSION AUTHORIZATION DEFAULT:设置当前会话的会话用户标识符和当前用户标识符为默认
#   DISCARD ALL:抛弃所有会话状态

#------------------------------------------------------------------------------
# REPLICATION MODE  复制模式
# https://www.pgpool.net/docs/42/en/html/runtime-config-running-mode.html
#------------------------------------------------------------------------------

#replicate_select = off             #当设置为on时,Pgpool-II将启用“选择查询复制”模式,即SELECT查询被发送到所有后备节点
#insert_lock = off                  #当设置为on时,Pgpool-II将在PostgreSQL上自动锁定该表,然后再为此发出INSERT语句
#lobj_lock_table = ''               #指定用于大对象复制的表名.如果指定Pgpool-II将锁定由lobj_lock_table指定的表,并生成一个大对象ID,可通过pg_largeobject查看,然后调用lo_create创建大对象,此过程可保证Pgpool-II将在复制模式下的所有数据库节点中获取相同的大型对象ID。

# - Degenerate handling -
#当设置为off时,并且所有节点都不以相同的数据包类型回复发送到所有PostgreSQL后端节点的查询时,则回复与大多数节点不同的后端节点将被Pgpool-II退化。
#如果replication_stop_on_mismatch设置为关闭,并且发生了类似的情况,则Pgpool-II仅终止当前用户会话,而不会退化后端节点
#replication_stop_on_mismatch = off

#如果设置为on,并且所有节点都不会以相同数量的受影响元组回复INSERT/UPDATE/DELETE查询,则回复与大多数不同的后端节点将被 Pgpool-II退化.
#如果failover_if_affected_tuples_mismatch设置为关闭,并且发生类似的情况,则Pgpool-II仅终止当前用户会话,而不会退化后端节点
#failover_if_affected_tuples_mismatch = off
                                   # On disagreement with the number of affected
                                   # tuples in UPDATE/DELETE queries, then
                                   # degenerate the node which is most likely
                                   # "minority".
                                   # If off, just abort the transaction to
                                   # keep the consistency

#------------------------------------------------------------------------------
# LOAD BALANCING MODE   负载均衡模式
# https://www.pgpool.net/docs/latest/en/html/runtime-config-load-balancing.html
#------------------------------------------------------------------------------

load_balance_mode = on                  #启用SELECT查询负载均衡模式
ignore_leading_white_space = on         #忽略负载均衡中SQL查询开头的空格

#如查你的函数为可读写的,必须将函数添加至write_function_list列表
read_only_function_list = ''            #只读pg函数列表,用英文的,号分隔,有条件带架构名称(test,public.test2),函数名支持正则表达式
write_function_list = ''                #只写pg函数列表,用英文的,号分隔,有条件带架构名称(test,public.test2),函数名支持正则表

#primary_routing_query_pattern_list = '' #指定应发送到主节点的以分号分隔的SQL列表.正则表达式特殊字符'、;、*、(、)、|、+、.、\、?、^、$、{、{、}需要使用\来转义

 #指定“数据库名称:节点名称或节点id(负载均衡概率)"列表,以指定的负载均衡按概率将SELECT查询发送到特定postgresql备机.负载均衡概率指定一个介于0和1之间的值.默认值为 1.0
#   示例:database_redirect_preference_list = 'postgres:primary,mydb[01]:1(0.3),mydb2:standby'
#       将postgres数据库上的所有SELECT路由到主机
#       将连接至mydb0或mydb1数据库上的30% SELECT路由到1号备机,其他70%的SELECT将发送到其它备机。
#       将连接至mydb2数据库上的所有SELECT路由到备机
#database_redirect_preference_list = '' 

#app_name_redirect_preference_list = ''  #指定应用程序名称:节点名称或节点id(负载均衡概率)”对的列表,使用方法同上。                                   
#allow_sql_comments = off                #设置为on时,pgpool-II在确定查询上是否可以进行负载均衡或查询缓存时,将忽略SQL中的注释.当此参数设置为off时,查询上的SQL注释可以有效地阻止对查询进行负载均衡或缓存

#指定出现写入SQL后的负载平衡行为.此参数在流式复制模式下特别有用,将写入SQL发送到主服务器时,异步备机存在时间滞后问题,因此如果客户端在写入SQL后立即读取刚才写入的行,则客户端可能无法看到该行的最新值,此时客户端应始终从主服务器读取数据,但是这会导致禁用了负载平衡从而导致性能降低.
#   off:则即使出现写入后立即SELECT,也会对SELECT进行负载平衡,但客户端可能会看到较旧的数据.但是对于PostgreSQL synchronous_commit = “remote_apply”时或同步复制非常有用,因为在此类环境中没有复制延迟.
#   transaction:写入查询且显式事务时,在事务结束之前,后续select不会进行负载平衡.请注意,不在显式事务中的select不受该参数的影响.在大多数情况下,此设置可提供最佳平衡.这是Pgpool-II 3.7或更早版本中的默认和相同行为.
#   trans_transaction:写入查询且显式事务时,在会话结束之前后续select不会在事务和后续显式事务中进行负载平衡.对于较旧的应用程序更安全,但事务性能较低。请注意,不在显式事务中的select不受该参数的影响。
#   always: 在会话结束之前,后续select不会进行负载平衡,无论它们是否在显式事务中.这提供了与非群集感知应用程序最高兼容性和最低的性能
#   dml_adaptive:Pgpool-II将跟踪显式事务中WRITE语句中引用的每个表,并且如果它们正在读取的表以前在同一事务中修改过,则不会对后续READ查询进行负载平衡.表上的依赖函数、触发器和视图可以使用dml_adaptive_object_relationship_list
#disable_load_balance_on_write = 'off'   #设置为off可以采用insert into t(c) values(1) returning c这种方法获取当前写入的行数据

#防止依赖对象的负载平衡,您可以指定对象名称后跟冒号(:)，,分隔的依赖对象名称列表。
#   table_1上安装了触发器,该触发器每个INSERT都执行table_2插入,您需要确保在插入到table_1后,读取table_2不得在同一事务中获得负载平衡。对于此配置，您可以设置
#dml_adaptive_object_relationship_list= ''

#设置为on时将为每个select确定负载平衡节点.
#设置为off时负载平衡节点在会话开始时确定,并且在会话结束之前不会更改。
#例如:在使用连接池的应用程序中与postgresql始终保持打开的连接状态,此会话可能保留很长时间,因此负载平衡节点在会话结束之前不会更改.在此类应用程序中,启用statement_level_load_balance时,可以决定每个查询的负载平衡节点,而不是每个会话的负载均衡节点
#statement_level_load_balance = off

#------------------------------------------------------------------------------
# STREAMING REPLICATION MODE    流复制模式
# https://www.pgpool.net/docs/latest/en/html/runtime-streaming-replication-check.html
#------------------------------------------------------------------------------

# - Streaming -

sr_check_period = 10                #检查流式复制延迟的时间间隔(以秒为单位)
sr_check_user = 'rep'               #指定要执行流式复制检查的PostgreSQL用户名
sr_check_password = ''              #指定sr_check_user用户的密码.如果用户不需要密码则不填写
sr_check_database = 'test'          #指定要执行流式复制延迟检查的数据库

#指定备用服务器上相对于主服务器的复制延迟的最大容差级别(以WAL字节为单位),如果延迟超过配置的级别,Pgpool-II将停止将SELECT查询发送到备用服务器,并开始将所有内容路由到主服务器(即使启用了load_balance_mode),直到备用服务器赶上主服务器.将此参数设置为0将禁用延迟检查.此延迟阈值检查每隔sr_check_period执行一次.
#delay_threshold = 0 

#仅当delay_threshold设置为大于0时此参数才有效.
#设置为on时如果负载平衡节点的延迟大于delay_threshold,Pgpool-II不会向主节点发送读取查询,而是发送给backend_weight>0且延迟最小的备用节点.如果所有备用节点的延迟都大于delay_threshold时,且主节点配置为负载平衡节点,则Pgpool-II将发送到主节点。默认值为关闭。
#prefer_lower_delay_standby = off

# - Special commands -

#follow_primary_command = ''        #用于通过调用pcp_recovery_node命令从新的主数据库恢复备用数据库。
#------------------------------------------------------------------------------
# HEALTH CHECK GLOBAL PARAMETERS            PostgreSQL数据库健康检查-全局配置
# https://www.pgpool.net/docs/latest/en/html/runtime-config-health-check.html
#------------------------------------------------------------------------------

health_check_period = 20                    #PostgreSQL健康检查时间间隔(以秒为单位).默认值为0,表示禁用健康检查
health_check_timeout = 10                   #PostgreSQL健康检查时连接至PostgreSQL的超时时间
health_check_user = 'test'                  #PostgreSQL健康检查时连接至PostgreSQL的用户名                                    
health_check_password = '123456'               #PostgreSQL健康检查时连接至PostgreSQL的密码
health_check_database = 'test'              #PostgreSQL健康检查时连接至PostgreSQL的数据库
#health_check_max_retries = 0                #PostgreSQL健康检查失败时放弃并启动故障转移之前要执行的最大重试次数
#health_check_retry_delay = 1                #PostgreSQL健康检查失败后在重新尝试之前的休眠时间(以秒为单位),health_check_max_retries>0时才有效,否则不使用此参数.如果为0则健康检查失败后立即重试(无延迟)
#connect_timeout = 10000                     #指定在放弃使用系统调用连接到后端之前的时间量(以毫秒为单位).默认值为10000毫秒(10秒),0表示无超时connect.
#------------------------------------------------------------------------------
# HEALTH CHECK PER NODE PARAMETERS (OPTIONAL) 健康检查-为每个节点单独配置
# https://www.pgpool.net/docs/latest/en/html/runtime-config-health-check.html
#------------------------------------------------------------------------------
#health_check_period0 = 0
#health_check_timeout0 = 20
#health_check_user0 = 'nobody'
#health_check_password0 = ''
#health_check_database0 = ''
#health_check_max_retries0 = 0
#health_check_retry_delay0 = 1
#connect_timeout0 = 10000

#------------------------------------------------------------------------------
# FAILOVER AND FAILBACK             故障切换和故障恢复
# https://www.pgpool.net/docs/latest/en/html/runtime-config-failover.html
#------------------------------------------------------------------------------

#故障转移意味着自动分离Pgpool-II无法访问的PostgreSQL节点.无论配置参数如何设置都会进行故障转移,因此称为自动故障转移过程.Pgpool-II使用以下方法确认PostgreSQL节点的不可访问性:
#   定期运行健康检查并尝试从Pgpool-II连接到PostgreSQL节点以确认其运行状况.如果连接失败则可能是Pgpool-II和PostgreSQL节点之间的网络连接有问题或者PostgreSQL节点无法正常工作.Pgpool-II不会区分每种情况,只是决定如果运行健康检查失败则表示此PostgreSQL节点不可用
#   连接到PostgreSQL节点时发生错误,或者在与其通信时发生网络级错误.在这种情况下,如果failover_on_backend_error关闭,Pgpool-II将断开与客户端的会话
#   在客户端已经连接到Pgpool-II并且PostgreSQL处于关闭状态的情况下(请注意:如果没有客户端连接到Pgpool-II,则关闭PostgreSQL不会触发故障转移)
#如果配置了failover_command并发生了故障转移则执行failover_command.failover_command应由用户提供.从4.1开始,故障转移命令的示例脚本为failover.sh.sample.你可以failover.sh.sample基础按需修改
#故障转移命令的主要作用是从现有备用服务器中选择新的主服务器,并将其升级为主服务器,并给管理员发送邮件通知管理员发生了故障转移
#虽然发生故障时可能会发生故障转移,但也可以手动触发故障转移,这称为切换,可以使用pcp_detach_node命令触发切换
#在默认设置中,通过故障转移或切换分离的PostgreSQL节点永远不会自动返回到以前的状态(附加状态).PostgreSQL节点修复好后使用-D选项重新启动Pgpool-II或运行pcp_attach_node使其再次进入附加状态.建议在执行此操作之前确认SHOW POOL NODES的replication_state状态为“streaming”.该状态表示备用服务器已通过流式复制正确连接到主服务器,并且两个数据库处于同步状态
#从4.1开始,可以使用新的参数auto_failback自动执行上述操作

#   特殊字符    描述
#01       %d        已分离节点的数据库节点ID
#02       %h        分离节点的主机名
#03       %p        分离节点的端口号
#04       %D        分离节点的数据库群集目录
#05       %m        新的主节点 ID
#06       %H        新主节点的主机名
#07       %M        旧的主节点标识
#08       %P        旧的主节点ID
#09       %r        新主节点的端口号
#10       %R        新主节点的数据库集群目录
#11       %N        旧主节点的主机名（Pgpool-II 4.1 或更高版本）
#12       %S        旧主节点的端口号（Pgpool-II 4.1 或更高版本）
#13       %%        “%”字符,代表%自身
failover_command = '/usr/pgpool-II/etc/failover.sh %d %h  %p %D %m %M %H %P %r %R %N %S'

#指定在PostgreSQL节点附加到Pgpool-II时要运行的命令.在执行命令之前Pgpool-II将以下特殊字符替换为后端特定信息.
#   特殊字符    描述
#01   %d            附加节点的数据库节点ID
#02   %h            附加节点的主机名
#03   %p            附加节点的端口号
#04   %D            附加节点的数据库群集目录
#05   %m            新的主节点 ID
#06   %H            新主节点的主机名
#07   %M            旧的主节点标识
#08   %P            旧的主节点ID
#09   %r            新主节点的端口号
#10   %R            新主节点的数据库集群目录
#11   %N            旧主节点的主机名（Pgpool-II 4.1 或更高版本）
#12   %S            旧主节点的端口号（Pgpool-II 4.1 或更高版本）
#13   %%            “%”字符,代表%自身
#failback_command = ''

#failover_on_backend_error = on             #设置为on时Pgpool-II会将PostgreSQL节点读取/写入错误视为后端节点故障,并在断开当前会话后触发该节点上的故障转移.当此设置为off时Pgpool-II仅报告错误并在发生此类错误时断开会话

#failover_on_backend_shutdown = on         #设置为on时Pgpool-II通过检查当前连接PostgreSQL节点会话上的特定错误代码57P01(admin_shutdown)和57P02(crash_shutdown)来检测后端关闭事件.如果它检测到这些错误代码,则会在该节点上触发故障转移,当此设置为off时Pgpool-II仅报告错误并在发生此类错误时断开会话.默认值为关闭。

#detach_false_primary = off                 #如果设置为on则分离假的主节点.默认值为off.此参数仅在流式复制模式下有效,并且对于PostgreSQL 9.6或更高版本有效.更多内容主参看文档

#search_primary_node_timeout = 5min         #指定发生故障转移方案时搜索主节点的最长时间(以秒为单位).超过此时间Pgpool-II将放弃查找主节点.默认值为300.将此参数设置为0意味着永远尝试查找主节点

#------------------------------------------------------------------------------
# ONLINE RECOVERY   在线恢复,建议使用手动恢复,防止脑裂
# https://www.pgpool.net/docs/latest/en/html/runtime-online-recovery.html
#------------------------------------------------------------------------------

#recovery_user = 'nobody'
                                   # Online recovery user
#recovery_password = ''
                                   # Online recovery password
                                   # Leaving it empty will make Pgpool-II to first look for the
                                   # Password in pool_passwd file before using the empty password

#recovery_1st_stage_command = ''
                                   # Executes a command in first stage
#recovery_2nd_stage_command = ''
                                   # Executes a command in second stage
#recovery_timeout = 90
                                   # Timeout in seconds to wait for the
                                   # recovering node's postmaster to start up
                                   # 0 means no wait
#client_idle_limit_in_recovery = 0
                                   # Client is disconnected after being idle
                                   # for that many seconds in the second stage
                                   # of online recovery
                                   # 0 means no disconnection
                                   # -1 means immediate disconnection

#auto_failback = off
                                   # Dettached backend node reattach automatically
                                   # if replication_state is 'streaming'.
#auto_failback_interval = 1min
                                   # Min interval of executing auto_failback in
                                   # seconds.

#------------------------------------------------------------------------------
# WATCHDOG      看门狗
#https://www.pgpool.net/docs/latest/en/html/tutorial-watchdog-intro.html#TUTORIAL-WATCHDOG-COORDINATING-NODES
#注意:为确保仲裁机制正常工作,Pgpool-II节点的数量必须为奇数且大于或等于3。
#
#https://www.pgpool.net/docs/latest/en/html/runtime-watchdog-config.html
#Pgpool-II 4.1或更早版本因为需要指定自己的pgpool节点信息和目标pgpool节点信息,因此每个pgpool节点的设置是不同的.从Pgpool-II 4.2开始所有主机上的所有配置参数都是相同的
#------------------------------------------------------------------------------

# - Enabling -

use_watchdog = on                           #是否启用看门狗

# -Connection to up stream servers -

#指定需要检查的服务器的列表(以英文的,号分隔服务器).注意列表中的每个服务器都需要响应ping.如果无法ping通的服务器看门狗会将其视为故障服务器.请注意,您不应将PostgreSQL服务器分配给此参数.
#"您不应将PostgreSQL服务器分配给此参数"这句话的意思是pgpool和postgresql部署在不同机器上的情况
#在本案例中pgpool只要master和sync slave上运行,async slave不运行pgpool,因此只要看门狗只要设置两台即可
#   async slave是在Backend Connection Settings上设置的,可以通过负载均衡可以访问,它的故障由健康检查负责,它和看门狗是不冲突的
#   pgpool在pgser01上trusted_servers设置为pgser02,同时也要修改hostname0
#   pgpool在pgser02上trusted_servers设置为pgser01,同时也要修改hostname0
trusted_servers = 'node1,node2'

#ping_path = '/bin'                          #ping命令所在目录,测试目录是否正确/bin/ping pgser02.

# - Watchdog communication Settings 看门狗通信设置-
hostname0 = 'node1'                       #指定Pgpool-II服务器的主机名或IP地址
wd_port0 = 9000                             #看门狗服务端口号
pgpool_port0 = 9999                         #pgpool端口号

hostname1 = 'node2'                       #指定Pgpool-II服务器的主机名或IP地址
wd_port1 = 9000                             #看门狗服务端口号
pgpool_port1 = 9999 

#此参数可用于在选择领导者看门狗节点的选举中提升本地看门狗节点的优先级.当集群在旧的领导者看门狗节点发生故障时,集群将选择新的领导者节点,具有较高wd_priority值的节点将被选为领导者看门狗节点.wd_priority在群集启动时也有效,当一些看门狗节点同时启动时,选择wd_priority值较高的节点作为领导节点.因此我们应该按照wd_priority优先级的顺序启动看门狗节点,以防止意外节点被选为领导者
#wd_priority在 Pgpool-II V3.5 之前的版本中不可用。
#在本案例中pgpool只要master和sync slave上运行,因此此参数不受影响
#wd_priority = 1

#wd_authkey = ''                            #指定用于所有监视程序通信的身份验证密钥,空值表示禁用监视程序身份验证。
wd_ipc_socket_dir = '/tmp'           #创建接受Pgpool-II看门狗IPC连接的UNIX域套接字的目录

# - Virtual IP control Setting 虚拟ip-

delegate_IP = '192.168.98.159'        #连接的Pgpool-II的虚拟IP地址(VIP)
#if_cmd_path = '/sbin'               #指定Pgpool-II将用于切换虚拟IP的命令的路径
#加载和卸载虚拟ip,注意修改网卡名称
if_up_cmd =   '/usr/bin/sudo /sbin/ip addr add 192.168.98.158/24 dev eth0 label ens32:0 && /usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev ens32 label eth0:1'
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del 192.168.98.158/24 dev eth0 && /usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev eth0'
#arping_path = '/usr/sbin'
#arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I ens32:1' #指定用于在虚拟IP交换之后发送ARP请求的命令

# - Behaivor on escalation Setting 升级和降级-

#设置为on时当pgpool-II升级到master状态时将清除共享内存中的所有查询缓存.这可以防止新的master Pgpool-II使用与旧master不一致的查询缓存
#这只有在memqcache_method是“shmem”时才有效
clear_memqcache_on_escalation = off

#wd_escalation_command = ''                 #看门狗在升级到领导看门狗的节点上执行此命令.如果在节点上配置了虚拟IP,则在启动虚拟IP之前执行此命令
#wd_de_escalation_command = ''              #当master看门狗节点变更为slave看门狗节点时,master看门狗会在该节点上执行此命令.

# - Watchdog consensus settings for failover 看门狗故障转换-

#该参数为on时Pgpool-II在切换为slave或故障转移时将考虑仲裁.如果看门狗节点的数量(即Pgpool-II节点的数量)可以占看门狗节点总数的大多数,我们可以说“仲裁存在”.例如:假设看门狗节点数为5,如果活动节点数大于或等于3则存在仲裁.另一方面如果活动节点数为2或更低则仲裁不存在,因为它永远不会占多数。
#如果仲裁存在Pgpool-II可以更好地进行故障检测,因为即使看门狗节点错误地检测到后端节点的故障,也会被其他master看门狗节点拒绝.Pgpool-II在failover_require_consensus处于打开状态时以这种方式工作(默认值),但您可以对其进行更改,以便在检测到故障时立即进行故障转移.检测到错误的故障的Pgpool-II节点将隔离后端节点.
#在本案例中pgpool只有master和sync slave上运行,因此此参数为off
failover_when_quorum_exists = off

#该参数为on时Pgpool-II在切换为slave或故障转移将进行故障转移投票(如果监视程序仲裁存在)
#例如在三节点群集中执行故障转移,至少有两个节点要求在特定后端节点上执行故障转移
#如果此参数处于关闭状态,则即使没有共识,也会触发故障转移
failover_require_consensus = off

#此参数与failover_require_consensus结合使用.启用后单个Pgpool-II节点在故障转移时可以投多个投票。
#allow_multiple_failover_requests_from_node = off

#此参数配置Pgpool-II如何进行多个投标规则计算,以达成故障转移的共识
#enable_consensus_with_half_votes = off

# - Watchdog cluster membership settings for quorum computation -

#wd_remove_shutdown_nodes = off         #SHUTDOWN节点时会立即标记为非成员并从集群中删除,如果SHUTDOWN的节点再次启动，它将自动添加到群集中。
#wd_lost_node_removal_timeout = 0s      #迷失的看门狗节点达到X秒后(以秒为单位)仍无响应,将标记为非成员并从集群中删除.当LOST节点重新连接到群集时,将还原其集群成员身份
#wd_no_show_node_removal_timeout = 0s   #如果节点在集群初始化时未显示且x秒后(以秒为单位)仍无响应,则将其标记为非成员.非成员节点在启动并连接到集群后立即成为集群成员

# - Lifecheck Setting 看门狗生命周期检查-

# -- common --

#以英文逗号分隔的网络设备名称列表,由监视程序进程监视网络链路状态.如果列表中的所有网络接口都变为非活动状态(禁用或电缆拔出),则看门狗将将其视为完全网络故障Pgpool-II节点将自杀.指定“”(空)列表将禁用网络接口监视.将其设置为“any”将启用对除环回之外的所有可用网络接口的监视。
#wd_monitoring_interfaces_list在 Pgpool-II V3.5 之前的版本中不可用。
#wd_monitoring_interfaces_list = ''

wd_lifecheck_method = 'heartbeat'           #指定生命周期检查的方法,心跳,查询,外部
wd_interval = 10                            #指定Pgpool-II生命周期检查间隔（以秒为单位)
# -- heartbeat mode 心跳模式--
#在本案例中pgpool只要master和sync slave上运行,async slave不运行pgpool,因此只要看门狗只要设置两台即可
#   pgpool在pgser01上heartbeat_hostname0设置为pgser02,注意修改网卡的名称
#   pgpool在pgser02上heartbeat_hostname0设置为pgser01,注意修改网卡的名称
heartbeat_hostname0 = 'node2'
heartbeat_port0 = 9694
heartbeat_device0 = 'eth0'

wd_heartbeat_keepalive = 2                 #指定发送检测信号之间的间隔时间(以秒为单位)
wd_heartbeat_deadtime = 30                 #指定将远程节点标记为失败/失效节点(如果在该时间内未收到检测信号)之前的时间(以秒为单位)
# -- query mode --

#wd_life_point = 3
                                    # lifecheck retry times
                                    # (change requires restart)
#wd_lifecheck_query = 'SELECT 1'
                                    # lifecheck query to pgpool from watchdog
                                    # (change requires restart)
#wd_lifecheck_dbname = 'template1'
                                    # Database name connected for lifecheck
                                    # (change requires restart)
#wd_lifecheck_user = 'nobody'
                                    # watchdog user monitoring pgpools in lifecheck
                                    # (change requires restart)
#wd_lifecheck_password = ''
                                    # Password for watchdog user in lifecheck
                                    # Leaving it empty will make Pgpool-II to first look for the
                                    # Password in pool_passwd file before using the empty password
                                    # (change requires restart)

#------------------------------------------------------------------------------
# OTHERS    其它配置
#https://www.pgpool.net/docs/latest/en/html/runtime-misc.html
#------------------------------------------------------------------------------
#relcache_expire = 0               #relation缓存的生存期(以秒为单位).0:表示relation缓存永不过期(默认值).relation缓存用于针对PostgreSQL system catalog查询结果.relation缓存包括表结构在内的各种信息并检查表类型(例如:检查引用的表是否为临时表)等.relation缓存保留在pgpool子过程内存空间中,其生存期与子进程的生存期相同.如果启用了enable_shared_relcache,缓存也保留在共享内存中,以便在子进程之间共享。如果使用ALTER TABLE或类似的命令修改了表,则relation缓存与实际不再一致,因此使用relcache_expire控制relation缓存的生存期.

 #指定relation缓存条目的数量,默认值为 256.您可以通过公式"使用表 * 10"估计所需的relation缓存数量
# 如果 Pgpool-II 日志中经常出现以下消息"pool_search_relcache: cache replacement happened"，则可能需要增加relcache_size以获得更好的性能。
#relcache_size = 256

#check_temp_table = catalog                 #枚举[catalog,trace,none],设置为catalog或trace时将在SELECT语句中启用临时表检查

#check_unlogged_table = on                  #设置为on，则在SELECT语句中启用unlogged table检查

enable_shared_relcache = off                #是否启用relcache共享

#relcache_query_target = primary            #枚举[primary,load_balance_node],relcache查询目标,默认发送至master节点
#------------------------------------------------------------------------------
# IN MEMORY QUERY MEMORY CACHE      #pgpool共享内存缓存
#https://www.pgpool.net/docs/latest/en/html/runtime-in-memory-query-cache.html     
#因为内存资源紧张,查询不缓存至pgpool.实际也建议不缓存
#------------------------------------------------------------------------------
#是否启用pgpool共享内存缓存.注意:如果enable_shared_relcache设置为on,即使将memory_cache_enabled参数设置为off,也会使用pgpool缓存
memory_cache_enabled = off 

#memqcache_method = 'shmem'                 #枚举[shmem,memcached],指定pgpool共享内存缓存的存储类型
#memqcache_memcached_host = 'localhost'     #设置pgpool共享内存缓存所在的位置(设置主机名或IP地址)
#memqcache_memcached_port = 11211           #设置pgpool共享内存缓存所在的位置的端口号
#memqcache_total_size = 64MB                #设置pgpool共享内存缓存大小

#指定pgpool共享内存缓存条目数,用于定义共享内存管理空间的大小
#管理空间大小可以按以下方式计算:memqcache_max_num_cache * 48字节.太小的数字会导致注册缓存时出错,另一方面,数量过多会浪费空间
#memqcache_max_num_cache = 1000000

#指定pgpool共享内存的生存期(以秒为单位).默认值为0,表示pgpool共享内存永不过期,缓存在更新表之前保持有效
#memqcache_expire和memqcache_auto_cache_invalidation彼此正交。
#memqcache_expire = 0

#设置为on时将自动删除与更新的表相关的缓存.关闭时不会删除缓存
#memqcache_auto_cache_invalidation = on

#指定要缓存的SELECT查询结果的最大大小(以字节为单位).查询结果数据大于此值的结果将不会由Pgpool-II缓存.
#当由于大小限制而拒绝缓存数据时,将显示以下消息"LOG:   pid 13756: pool_add_temp_query_cache: data size exceeds memqcache_maxcache. current:4095 requested:111 memq_maxcache:4096"
#对于pgpool共享内存存储类型为shmem,缓存memqcache_maxcache必须设置为低于memqcache_cache_block_size.
#对于pgpool共享内存存储类型为memcached,它必须小于slab的大小
#memqcache_maxcache = 400kB

#memqcache_cache_block_size = 1MB           #指定pgpool共享内存缓存块大小.如果memqcache_method =“shmem”,则为必填项
#memqcache_oiddir = '/var/log/pgpool/oiddir'    #指定用于记录SELECT时使用的表的oid的完整路径(临时工作目录)

#cache_safe_memqcache_table_list = ''       #以英文逗号分隔的表名列表,其SELECT结果应由Pgpool-II缓存.此参数仅适用于访问未记录表的VIEW和SELECT.除非由cache_unsafe_memqcache_table_list指定
#cache_unsafe_memqcache_table_list = ''     #以英文逗号分隔的表名列表,其SELECT结果Pgpool-II不会缓存.

9.5、pgpool-II的基础知识

9.5.1、端口号

端口用途备注9999接受客户端连接暴露在外网的端口9898PCP命令使用内网使用ping看门狗通过ping检查各个节点网络状态内网使用arping探测虚拟IP的存活状态[活跃探测功能][更新邻近主机的ARP缓存]内网使用9000看门狗互相通信使用的端口内网使用9694看门狗声明周期检查心跳模式使用的端口内网使用11211启用内存缓存且内存缓存方法为memcached使用的内存缓存服务端口号内网使用

9.5.2、pgpool主进程

负责检查各个底层数据库的健康状态

同时启动以下7个进程：

1、启动PgpoolLogger进程

记录pgpool日志

2、启动watchdog进程

pgpool.conf中WATCHDOG的节点。注意：watchdog中PostgreSQL节点是单独配置的，和pgpool配置连接多少台PostgreSQL节点无关

节点直接的ping检查

watchdog之间相互通信检查

pgpool子进程通信检查

PostgreSQL数据库连接健康检查(在指定的数据库上执行select 1)

更新邻近主机的ARP缓存(arping不能再本机执行)

仲裁功能：发生故障时所有存活的pgpool节点仲裁出新的pgpool Master主机(注意2台pgpool无仲裁功能，pgpool直接提升另外一台主机为pgpool master主机)

watchdog会检测出以下三种状态

1、pgpool故障PostgeSQL节点无故障

pgpool Master crash或关闭pgpool Master

pgpool根据剩余的pgpool slave 仲裁出一台新的pgpool Master，然后根据仲裁结果将pgpool slave提升为pgpool Master

PostgreSQL集群状态无变化

2、pgpool无故障PostgreSQL节点故障

pgpool集群状态无变化

2.1、PostgreSQL Master节点故障

如果是PostgreSQL Master节点故障，根据pgpool仲裁结果提升PostgreSQL（在当前pgpool Master主机运行failover_command命令提升PostgreSQL Master）.pgpool集群将故障的PostgreSQL节点状态标记为down，且不在连接故障主机，同时需要注意的是：Watchdog communication Settings-看门狗通信至少要配置2个PostgreSQL节点

2.2、PostgreSQL slave节点故障

如果是PostgreSQL slave节点故障，pgpool集群将故障的PostgreSQL节点状态标记为down，且不在连接故障主机

3、pgpool故障(含关闭)PostgreSQL节点故障

所有存活的pgpool节点仲裁出新的pgpool Master主机，然后再新的pgpool Master主机运行，failover_command命令提升PostgreSQL Master

pgpool集群状态改变

PostgreSQL集群状态改变

3、启动lifecheck进程

watchdog生命周期检查进程(watchdog Lifecheck Setting中的配置)

4、启动POOLS子进程池(子进程数量为：num_init_children)

POOLS子进程池接受客户端连接

pgpool子进程数量由num_init_children(pgpool.conf)决定

每个子进程会产生max_pool(pgpool.conf)个数据库的连接，因此实际连接数为num_init_children*max_pool

子进程定期检查(child_life_time)与数据库的连接是否正常，不正常则尝试重新连接或重新连接至其它数据库服务器.

5、启动pcp进程

PCP是通过网络操作pgpool-II的unix命令

6、启动worker process进程 PostgreSQL流复制进程

检查PostgreSQL WAL复制延迟(pgpool.conf->STREAMING REPLICATION MODE)

7、启动health check process进程

检查PostgreSQL存活状态进程，例如：配置了4个PostgreSQL节点(pgpool.conf->backend_hostnamex),那么health check process进程数量是4，每个health check process检查PostgreSQL节点

9.6、pgpool-II相关配置[node1 | node2]

9.6.1、pgpool.conf相关配置

# 创建日志目录
mkdir -p /app/pgsql/pgpool/logs

cd /usr/pgpool-II/etc
cp pgpool.conf.sample pgpool.conf
vim pgpool.conf

backend_clustering_mode = 'streaming_replication

listen_addresses = '*'
port = 9999
reserved_connections = 1

pcp_listen_addresses = '*'
pcp_port = 9898
listen_backlog_multiplier = 1

backend_hostname0 = 'node1'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = 'app/pgsql/14/data
backend_flag0 = 'ALLOW_TO_FAILOVER'
#backend_application_name0 = 'standby01'

backend_hostname1 = 'node2'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/app/pgsql/14/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
#backend_application_name1 = 'standby02'

backend_hostname2 = 'node3'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/app/pgsql/14/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'
#backend_application_name2 = 'standby03'

enable_pool_hba = on
pool_passwd = 'pool_passwd'
authentication_timeout = 5s

num_init_children = 32
max_pool = 2
child_life_time = 10min

log_destination = 'stderr'
logging_collector = on
log_directory = '/app/pgsql/pgpool/logs'
log_filename = 'pgpool-%Y-%m-%d.log'
log_file_mode = 0600
log_truncate_on_rotation = on

pid_file_name = '/app/pgsql/pgpool/pgpool.pid'
logdir = '/app/pgsql/pgpool

connection_cache = on
reset_query_list = 'ABORT; DISCARD ALL'

load_balance_mode = on
ignore_leading_white_space = on
read_only_function_list = ''
write_function_list = ''

# 配置节点负载均衡  主库用于写，备库用于读，各站50%
database_redirect_preference_list = 'test:0(0),test:1(0.5),test:2(0.5)'

sr_check_period = 30
sr_check_user = 'postgres'
sr_check_password = ''
sr_check_database = 'postgres'

health_check_period = 20
health_check_timeout = 10
health_check_user = 'test'
health_check_password = ''
health_check_database = 'test'

failover_command = '/usr/pgpool-II/etc/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'

use_watchdog = on
trusted_servers = 'node1,node2'
ping_path = '/bin'
hostname0 = 'node1'
wd_port0 = 9000
pgpool_port0 = 9999

hostname1 = 'node2'
wd_port1 = 9000
pgpool_port1 = 9999

delegate_IP = '192.168.98.159'
if_cmd_path = '/sbin'
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev eth0 label eth0:0'
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev eth0'

arping_path = '/usr/sbin'
arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I eth0'

clear_memqcache_on_escalation = off
wd_escalation_command = '/usr/pgpool-II/etc/delegate_check.sh'

failover_when_quorum_exists = off
failover_require_consensus = off

enable_consensus_with_half_votes = on

wd_monitoring_interfaces_list = 'any'
wd_lifecheck_method = 'heartbeat'
wd_interval = 10
heartbeat_hostname0 = 'node1'
heartbeat_port0 = 9694
heartbeat_device0 = 'eth0'

heartbeat_hostname1 = 'node2'
heartbeat_port1 = 9694
heartbeat_device1 = 'eth0'

wd_life_point = 3
wd_lifecheck_query = 'SELECT 1'
wd_lifecheck_dbname = 'test'
wd_lifecheck_user = 'test'
wd_lifecheck_password = ''

enable_shared_relcache = off

memory_cache_enabled = off

ipadd.sh

#!/bin/bash
#IP=192.168.98.159
WAL_IP=192.168.98.158
password=123456
ping -c1 -W1 $1 >/dev/null
if [ $? -ne 0 ]; then
    expect -c "
      set timeout 1
      spawn su - root
      expect {
        \"*(password|Password|passwd):\" {send -- \"${password}\r\";exp_continue}
        \"*密码：\" {send -- \"${password}\r\"}
      }
      expect \"root@*\" {send -- \"ip addr add $1/24 dev eth0 label eth0:0 &&  exit\r\"}
      expect eof
    "
fi

#ping -c1 -W1 $WAL_IP >/dev/null
#if [ $? -ne 0 ]; then
#        expect -c "
#      set timeout 1
#          spawn su - root
#          expect {
#                \"*(password|Password|passwd):\" {send -- \"${password}\r\";exp_continue}
#                \"*密码：\" {send -- \"${password}\r\"}
#          }
#          expect \"root@*\" {send -- \"ip addr add $WAL_IP/24 dev eth0 label eth0:1 &&  exit\r\"}
#          expect eof
#        "
#fi
exit 0

ipdel.sh

#!/bin/bash
#IP=192.168.98.159
WAL_IP=192.168.98.158
password=123456
ping -c1 $1 >/dev/null
if [ $? -eq 0 ]; then
    expect -c "
      set timeout 1
      spawn su - root
      expect {
        \"*(password|Password|passwd):\" {send -- \"${password}\r\";exp_continue}
        \"*密码：\" {send -- \"${password}\r\"}
      }
      expect \"root@*\" {send -- \"ip addr del $1/24 dev eth0  && exit\r\"}
      expect eof
    "
fi

#ping -c1 $WAL_IP >/dev/null
#if [ $? -eq 0 ]; then
#        expect -c "
#      set timeout 1
#          spawn su - root
#          expect {
#                \"*(password|Password|passwd):\" {send -- \"${password}\r\";exp_continue}
#                \"*密码：\" {send -- \"${password}\r\"}
#          }
#          expect \"root@*\" {send -- \"ip addr del $WAL_IP/24 dev eth0 &&  exit\r\"}
#          expect eof
#        "
#fi
exit 0

9.6.2、PCP相关配置

pcp.conf是PCP命令使用的密码

截止目前只支持md5命令

cp pcp.conf.sample pcp.conf

vim pcp.conf

生成密码 | 写入到pcp.conf里面

pg_md5 123456   

e10adc3949ba59abbe56e057f20f883e

vim pcp.conf

postgres:e10adc3949ba59abbe56e057f20f883e

cat /usr/pgpool-II/etc/pcp.conf

配置免密登录

vim ~/.pcppass

node1:9898:postgres:e10adc3949ba59abbe56e057f20f883e

9.6.3、创建pgpool.conf所需的配置文件

cp failover.sh.sample failover.sh
cp pool_hba.conf.sample pool_hba.conf

9.6.4、虚拟IP检查脚本delegate_check.sh[node1 | node2]

虚拟IP检查脚本主要是防止pgpool crash时虚拟IP未删除情况

# 在node1 | node2上面分别测试root ssh连接
sshpass -p 123456 ssh root@node1 "exit" && \
sshpass -p 123456 ssh root@node2 "exit"

ping -c1 192.168.98.158 && echo $?
vim /usr/pgpool-II/etc/delegate_check.sh

node1 delegate_check.sh

ping -c1 192.168.98.159>/dev/null
if [ $? -eq 0 ]; then
    sshpass -p 123456 ssh root@node2 "/usr/sbin/ip addr del 192.168.98.159/24 dev eth0 >/dev/null"

fi
exit 0

node2 delegate_check.sh

ping -c1 192.168.98.159>/dev/null
if [ $? -eq 0 ]; then
    sshpass -p 123456 ssh root@node1 "/usr/sbin/ip addr del 192.168.98.159/24 dev eth0 >/dev/null"

fi
exit 0

node1 | node2 更改delegate_check.sh的权限并测试一下

chmod 700 delegate_check.sh
ll
sh delegate_check.sh && echo $?

9.6.5、配置failover.sh

cp failover.sh.sample failover.sh
chmod 700 failover.sh

#!/bin/bash
# This script is run by failover_command.

set -o xtrace

# Special values:
# 1)  %d = failed node id
# 2)  %h = failed node hostname
# 3)  %p = failed node port number
# 4)  %D = failed node database cluster path
# 5)  %m = new main node id
# 6)  %H = new main node hostname
# 7)  %M = old main node id
# 8)  %P = old primary node id
# 9)  %r = new main port number
# 10) %R = new main database cluster path
# 11) %N = old primary node hostname
# 12) %S = old primary node port number
# 13) %% = '%' character

FAILED_NODE_ID="$1"
FAILED_NODE_HOST="$2"
FAILED_NODE_PORT="$3"
FAILED_NODE_PGDATA="$4"
NEW_MAIN_NODE_ID="$5"
NEW_MAIN_NODE_HOST="$6"
OLD_MAIN_NODE_ID="$7"
OLD_PRIMARY_NODE_ID="$8"
NEW_MAIN_NODE_PORT="$9"
NEW_MAIN_NODE_PGDATA="${10}"
OLD_PRIMARY_NODE_HOST="${11}"
OLD_PRIMARY_NODE_PORT="${12}"

PGHOME=/usr/pgsql-14
#REPL_SLOT_NAME=${FAILED_NODE_HOST//[-.]/_}
POSTGRESQL_STARTUP_USER=postgres
#SSH_KEY_FILE=id_rsa_pgpool
#SSH_OPTIONS="-o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null -i ~/.ssh/${SSH_KEY_FILE}"

echo failover.sh: start: failed_node_id=$FAILED_NODE_ID failed_host=$FAILED_NODE_HOST \
    old_primary_node_id=$OLD_PRIMARY_NODE_ID new_main_node_id=$NEW_MAIN_NODE_ID new_main_host=$NEW_MAIN_NODE_HOST

## If there's no main node anymore, skip failover.
if [ $NEW_MAIN_NODE_ID -lt 0 ]; then
    echo failover.sh: All nodes are down. Skipping failover.
    exit 0
fi

if [ ${NEW_MAIN_NODE_ID} -eq ${FAILED_NODE_ID} ]; then
##如果新的pgpool节点和故障pgpool节点相同，pgpool无故障，PostgreSQL故障
    echo "pgpool OK,PostgreSQL fail."
else
##否则pgpool节点和PostgreSQL主节点同时故障
    echo "pgpool fail,PostgreSQL fail"
fi

##检查流复制IP是否删除，添加删除IP时注意修改网卡名称
##如果pgpool正常关闭则虚拟IP也会被删除
##如果pgpool crash此时未删除虚拟IP
##上述两种情况再wd_escalation_command = '/usrl/pgpool-II/etc/delegate_check.sh'中检查虚拟IP
ping -c1 192.168.98.158 >/dev/null
if [ $? -eq 0 ]; then
    sshpass -p 123456 ssh root@${FAILED_NODE_HOST} "/usr/sbin/ip addr del 192.168.98.158/24 dev eth0"

fi
sshpass -p 123456 ssh root@${NEW_MAIN_NODE_HOST} "/usr/sbin/ip addr add 192.168.98.158/24 dev eth0 label eth0:1"

## Promote Standby node.
echo failover.sh: primary node is down, promote new_main_node_id=$NEW_MAIN_NODE_ID on ${NEW_MAIN_NODE_HOST}.

##ssh -T ${SSH_OPTIONS} ${POSTGRESQL_STARTUP_USER}@${NEW_MAIN_NODE_HOST} ${PGHOME}/bin/pg_ctl -D ${NEW_MAIN_NODE_PGDATA} -w promote
## 因为本机postgres用户通过ssh登录本机未设置ssh互信，因此需要输入密码
## 也可以配置本机postgres用户ssh登录本机ssh互信
sshpass -p 123456 ssh ${POSTGRESQL_STARTUP_USER}@${NEW_MAIN_NODE_HOST} ${PGHOME}/bin/pg_ctl -D ${NEW_MAIN_NODE_PGDATA} -w promote
if [ $? -ne 0 ]; then
    echo ERROR: failover.sh: end: failover failed
    exit 1
fi

echo failover.sh: end: new_main_node_id=$NEW_MAIN_NODE_ID on ${NEW_MAIN_NODE_HOST} is promoted to a primary
## 这里应该发送短信和邮件通知DBA发生故障了，需要尽快恢复故障节点
exit 0

9.6.6、生成pool_passwd参数所需文件

自Pgpool-II4.0起支持scam-sha-256、证书和明文密码

pool_passwd存储的是登录PostgreSQL用户名和密码，所以要和PostgreSQL一致

rm -rf ~/.pgpoolkey && \
rm -rf /usr/pgpool-II/etc/pool_passwd

echo '123456' > ~/.pgpoolkey && \
chmod 600 ~/.pgpoolkey

cat ~/.pgpoolkey

## 使用AES加密码
#-u参数必须保持和数据库中的用户名一致，执行后，输入的密码也必须保持和数据库中-u用户的密码一致
#/usr/pgpool-II/bin/pg_enc -m -k ~/.pgpoolkey -u postgres -p
#/usr/pgpool-II/bin/pg_enc -m -k ~/.pgpoolkey -u test -p
## 使用md5加密
pg_md5 -u postgres -m -p
pg_md5 -u test -m -p

cat /usr/pgpool-II/etc/pool_passwd

9.6.7、配置pool_hba.conf

cp pool_hba.conf.sample pool_hba.conf
vim pool_hba.conf

#host     test        test        0.0.0.0/0             scram-sha-256
host     all         all         127.0.0.1/32          trust
host     all         all         192.168.98.0/24       trust
host     test        test        0.0.0.0/0             scram-sha-256

9.6.8、pgpool_node_id

Pgpool-II 4.1或更早版本，因为需要指定自己的Pgpool节点信息和目标Pgpool节点信息，因此每个Pgpool节点的设置是不同的

从Pgpool-II 4.2开始，所有主机上所有的配置参数都是相同的，如果启用了监视程序功能，为了区分哪个主机，需要创建pgpool_node_id文件。pgpool_node_id文件并指定pgpool(看门狗)节点号来标识Pgpool(看门狗)主机

必须手动创建pgpool_node_id文件，否则会报异常：

FATAL: Pgpool node id file /usr/pgpool-II/etc/pgpool_node_id does not exist

#创建pgpool_node_id node1
vim /usr/pgpool-II/etc/pgpool_node_id

0

#创建pgpool_node_id node2
vim /usr/pgpool-II/etc/pgpool_node_id

1

9.7、启动 | 停止 Pgpool

#AES加密启动
pgpool -k ~/.pgpoolkey

# MD5加密启动
su - root
pgpool 
# 重新加载配置参数
pgpool reload

# 查看日志
tail -f /app/pgsql/pgpool/logs/pgpool-2024-03-01.log 

pe -ef | grep pgpool

#停止Pgpool
pgpool -m fast stop

# 查看IP 
ip a

9.8、查看Pgpool状态

Pgpool的状态

psql -h 192.168.98.159 -p 9999 -U test -d test -c "show pool_nodes;"

##-----------------
 node_id | hostname | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change  
---------+----------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | node1    | 5432 | up     | up        | 0.333333  | primary | primary | 0          | false             | 0                 |                   |                        | 2024-03-01 13:06:47
 1       | node2    | 5432 | up     | up        | 0.333333  | standby | standby | 0          | true              | 0                 |                   |                        | 2024-03-01 13:06:47
 2       | node3    | 5432 | up     | up        | 0.333333  | standby | standby | 0          | false             | 0                 |                   |                        | 2024-03-01 13:06:47

pcp_node_info -h 192.168.110.159 -p 9898 -U postgres -a
-v  详细信息

看门狗的状态

# 尽量在Master上面执行
pcp_watchdog_info -h 192.168.98.159 -p 9898 -U postgres
-v  详细信息

##-------------------
node1:9999 Linux node1 node1 9999 9000 4 LEADER 0 MEMBER
node2:9999 Linux node2 node2 9999 9000 7 STANDBY 0 MEMBER

流复制状态

psql -h localhost -U postgres -d postgres -c "select usename, application_name, client_addr, sync_state from pg_stat_replication;"

9.9、验证负载均衡

vim ~/load_balance.sh

#!/bin/bash
for (( i=0;i<=100;i=`expr $i+1`))
do
   psql -h 192.168.98.159 -p 9999 -U test -d test -c "select count(*) from test;" > /dev/null

done
exit 0

在开始测试之前必须设置免密码登录或者设置pg_hba.conf互相信任

chmod 700 ~/load_balance.sh

./load_balance.sh

psql -h 192.168.98.159 -p 9999 -U test -d test -c "show POOL_NODES;"

9.10、查看PostgreSQL的连接数

psql -h localhost -p 5432 -U test -d test -c "select count(*) from pg_stat_activity;"

9.11、向pgpool写入数据

psql -h 192.168.98.159 -p 9999 -U test -d test -c "
create table test05 as 
select id,((random()*(1023-1)+1)::bigint) from generate_series(now(),now()+' 1 hour', '1 minute') as id;"

psql -h 192.168.98.159 -p 9999 -U test -d test -c "select count(*) from test05;" 
psql -h 192.168.98.159 -p 9999 -U test -d test -c "show pool_nodes;"

9.12、问题解决

1、在使用psql -h 192.168.98.159 -p 9999 -U test -d test -c "show pool_nodes;"发现如果status是down，则使用下面命令加入集群

pcp_attach_node -h localhost -p 9898 -U postgres -n 1

2、如果打印Pgpool日志，发现一直循环出现find_primary_node: primary node is 1，则执行如下命令

/usr/pgpool-II/etc/failover.sh 0 node1 5432 /app/pgsql/14/data 1 node2 0 0 5432 /app/pgsql/14/data node2 5432

3、当主库挂了后[包含Pgpool挂了和主库挂了 | 包含Pgpool没挂和主库挂了]，需要在挂了的数据库里面首先同步一次未挂且已经升级为主库的数据库，并且，修改PostgreSQL的同步复制设置以及primary_info信息以及添加standby.signal文件

# 查看时间线和wal文件所在(node1 | node2)
pg_controldata

# 同步主库数据(宕机的数据库)
pg_rewind --target-pgdata=${PGDATA} --source-server="host=192.168.98.158 port=5432 dbname=postgres"

vim ${PGDATA}/postgresql.conf
#synchronous_standby_names = 'FIRST 1 (standby01)'
vim ${PGDATA}/postgresql.auto.conf
primary_conninfo = 'host=192.168.110.230 port=5432 user=rep application_name=standby02'

# 创建从机的文件
touch standby.signal && \
chmod 600 standby.signal && \
ll standby.signal

# 主库 开启同步复制

vim ${PGDATA}/postgresql.conf
synchronous_standby_names = 'FIRST 1 (standby02)' 

# 重新加载配置文件
systemctl reload postgresql-14.service

标签： linux postgresql 数据库

本文转载自: https://blog.csdn.net/qq_41525527/article/details/138859168
版权归原作者 DDad. 所有，如有侵权，请联系我们删除。

最完善的PostgreSQL集群搭建

前言

1、准备

1.1、环境

1.2、创建postgres用户

1.3、配置hosts文件

1.4、配置免密登录

1.5、创建数据目录

1.6、下载postgresql14.9的源码包

1.7、解压并安装

1.8、配置环境变量

1.9 [附加]添加虚拟IP[命令式]

1.10、[附加]添加虚拟IP[脚本式][node1 | node2]

1.11、添加postgres可以使用sudo的权限

1.12、安装sshpass

2、node1

2.1、初始化postgresql

2.2、配置pg_hba.conf

2.3、postgresql.conf配置详解

2.4、配置为系统服务

2.5、测试登录

2.6、数据库相关

2.7、备份测试

node2

node1

2.8、修改流复制主机为虚拟IP

2.9、数据库 启动 | 停止

3、node2

3.1、创建相应目录

3.2、备份node1的数据

3.3、将同步复制开启[node1]

3.4、检查状态并启动数据库

3.5、配置postgresql为系统服务

3.6、查询流复制的情况[node1]

3.7、修改流复制主机为虚拟IP

3.8、数据库 启动 | 重启

4、node3

4.1、创建相应目录

4.2、备份node1的数据

4.3、检查状态并启动数据库

4.4、配置postgresql为系统服务

4.5、查询流复制的情况[node1]

4.6、修改流复制主机为虚拟IP

5、故障模拟

5.1、创建测试表[node1]

5.2、停止数据库[node1]

5.3、将node2升级为master[node2]

5.4、查看流复制状态

6、故障恢复

6.1、查看状态[node1 | node2]

6.2、同步数据[node1]

6.3、将synchronous_standby_names打开[node1]

6.4、修改primary_conninfo[node1]

6.5、将synchronous_standby_names打开[node2]

6.6、node1加入集群[node1]

6.7、将node1升级为主库

7、原始的自动故障切换和负载均衡

7.1、故障检测

8、误操作数据恢复模拟[node1]

8.1、创建表测试表

8.2、生成测试数据

9、pgpool-II

9.1、下载pgpool-II

9.2、编译安装[node1 | node2 | node3]

9.3、配置环境变量

9.4、pgpool-II4.3.3的参数详解

9.5、pgpool-II的基础知识

9.6、pgpool-II相关配置[node1 | node2]

9.6.1、pgpool.conf相关配置

9.6.2、PCP相关配置

9.6.3、创建pgpool.conf所需的配置文件

9.6.4、虚拟IP检查脚本delegate_check.sh[node1 | node2]

9.6.5、配置failover.sh

9.6.6、生成pool_passwd参数所需文件

9.6.7、配置pool_hba.conf

9.6.8、pgpool_node_id

9.7、启动 | 停止 Pgpool

9.8、查看Pgpool状态

9.9、验证负载均衡

9.10、查看PostgreSQL的连接数

2.9、数据库启动 | 停止

3.8、数据库启动 | 重启