某一个 postgresql 数据库挂掉 (多台数据库启动后其中一台作为主机,其余作为备机构成一个数据库集群);
1. 如果是主机primary,集群检测到挂掉会通过配置的策略重新选一个备机standby切换为主机primary, 整个集群仍旧保证可用, 当原主机恢复服务后, 重新作为一个新备机standby,同步完数据后加入集群
2. 如果是备机standby,对整个集群无可见影响, 当备机恢复服务后,从主库同步完数据后,恢复正常状态加入集群;
某一台机器上的pgpool-ii 程序挂掉;
1. 监测每个pgpool-ii进程的状态, 监测到挂掉之后,及时"切换"虚拟ip所在的主机以保证可用性(有些人叫IP漂移);
2. 整个集群始终对外提供一个唯一的,可用的虚拟IP 来提供访问;
3. 监测每个主机postgresql数据库的状态, 以即使切换数据库的主备角色;
某一台主机直接宕机;
1. 当pgpool-ii监测主机挂掉之后, 需要进行数据库角色的切换和ip的切换两个操作(如果需要)

二、配置方案

基于两台装有postgresql数据库的服务器，通过每台机器上的pgpool-ii程序来维护一个高可用体系, 从而保证能始终提供一个可用的IP地址,用于外界数据操作或者访问.

发行版	ip	补充说明
Cent OS7	10.242.111.204	安装postgresql 12.1 + pgpool-ii 4.1并进行配置
Cent OS7	10.242.111.205	安装postgresql 12.1 + pgpool-ii 4.1并进行配置
Cent OS7	10.242.111.206	安装postgresql 12.1 + pgpool-ii 4.1并进行配置
/	10.242.111.203	virtual ip, 通过一个虚拟的IP统一对外提供访问

3(n)台主机均安装有postgresql 12 版本的数据库和pgpool-ii 4.1 版本的中间件;

3(n)个数据库之间可以做到数据同步以(通过流复制来实现, 但同一时刻主机primary只有一台,其余作为备机standby)及身份切换;

pgpool-ii 是一个介于postgresql 服务器和postgresql数据库之间的中间件, 提供了链接池(Connection Pooling),看门狗(WatchDog),复制,负载均衡,缓存等功能(具体的可以查看官方文档);

通过pgpool-ii 维护的虚拟ip, 向外界提供一个始终可用的访问地址, 屏蔽掉具体的主机数据库地址概念;

通过pgpool-ii 程序来自动处理宕机后相关方案(后面有讲)

数据库down之后需要通过pcp_attach_node将节点加入集群

流复制数据同步: 通过postgresql数据库配置来实现

虚拟ip自动切换: 通过pgpool-ii 配置实现

数据库主备角色切换: 通过pgpool-ii 监测机 + 执行 postgresql 中的promote命令来实现

三、postgresql-12安装(3台机器均安装)

（一）yum 在线安装

#设置rpm源

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

#安装(这里是版本为12的postgresql)

sudo yum install -y postgresql12-server

systemctl stop firewalld

systemctl disable firewalld

（二）rpm离线安装

# ftp下载rpm离线包

curl -O https://yum.postgresql.org/12/redhat/rhel-7-x86_64/postgresql12-12.3-1PGDG.rhel7.x86_64.rpm

curl -O https://yum.postgresql.org/12/redhat/rhel-7-x86_64/postgresql12-contrib-12.3-1PGDG.rhel7.x86_64.rpm

curl -O https://yum.postgresql.org/12/redhat/rhel-7-x86_64/postgresql12-libs-12.3-1PGDG.rhel7.x86_64.rpm

curl -O https://yum.postgresql.org/12/redhat/rhel-7-x86_64/postgresql12-server-12.3-1PGDG.rhel7.x86_64.rpm

# 上传文件到服务器之后, 执行安装命令

rpm -ivh postgresql*.rpm

执行完安装之后(查看状态可跳过, 直接进行数据库初始化):

会帮我们创建一个postgresql-12服务, 此时未进行数据库初始化, 还无法访问.
会帮我们创建一个postgres/postgres 的用户，密码相同.

此时使用systemctl status postgresql-12 查看服务状态:

● postgresql-12.service - PostgreSQL 12 database server

Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service; disabled; vendor preset: disabled)

Active: inactive (dead)

Docs: https://www.postgresql.org/docs/12/static/

我们可以找到默认配置文件地址: /usr/lib/systemd/system/postgresql-12.service

如果cat命令查看配置文件, 我们可以得到一些基础信息:
数据库数据目录: Environment=PGDATA=/var/lib/pgsql/12/data/
postgresql安装目录: PGHOME=/usr/pgsql-12/

数据库初始化

sudo /usr/pgsql-12/bin/postgresql-12-setup initdb

#设置开机自启

systemctl enable postgresql-12 && systemctl restart postgresql-12

**本机测试访问 **

su - postgres

-bash-4.2$ psql

postgres=# (这里就可以开始写查询语句了)

**配置远程访问 **
修改数据目录下配置文件: pg_hba.conf

vim /var/lib/pgsql/12/data/pg_hba.conf# 在文件中添加:

host all all 0.0.0.0/0 md5

修改数据目录下配置文件: postgresql.conf

vim /var/lib/pgsql/12/data/postgresql.conf# 在文件中修改(此配置仅用于远程访问, 流复制后续还有额外配置):

listen_addresses = '*'

port = 5432

max_connections = 100

修改完成重启服务, 使用远程命令或者远程客户端测试访问即可

-- 使用远程命令访问

psql -h 10.242.111.204 -p 5432 -U postgres-- 登陆成功可以执行操作-- 如修改密码 (引号中为新密码)

postgres=# alter role postgres with password 'postgres';

（三）postgresql-12 流复制配置

1.流复制原理简述

流复制大约是从pg9版本之后使用, 流复制其原理为：备库不断的从主库同步相应的数据，并在备库apply每个WAL record，这里的流复制每次传输单位是WAL日志的record。(关于预写式日志WAL,是一种事务日志的实现)

图中可以看到流复制中日志提交的大致流程为:

1. 事务commit后，日志在主库写入wal日志，还需要根据配置的日志同步级别，等待从库反馈的接收结果。
2. 主库通过日志传输进程将日志块传给从库，从库接收进程收到日志开始回放，最终保证主从数据一致性。
流复制同步级别
通过在postgresql.conf配置synchronous_commit参数来设置同步级别

synchronous_commit = off # synchronization level; # off, local, remote_write, or on

remote_apply：事务commit或rollback时，等待其redo在primary、以及同步standby(s)已持久化，并且其redo在同步standby*(s)已apply。

on：事务commit或rollback时，等待其redo在primary、以及同步standby(s)已持久化。

remote_write：事务commit或rollback时，等待其redo在primary已持久化; 其redo在同步standby(s)已调用write接口(写到 OS, 但是还没有调用持久化接口如fsync)。

local：事务commit或rollback时，等待其redo在primary已持久化;

off：事务commit或rollback时，等待其redo在primary已写入wal buffer，不需要等待其持久化;

配置时需要注意的点:

postgresql-12版本不再支持通过recovery.conf的方式进行主备切换，如果数据目录中存在recovery.conf，则数据库无法启动;
新增 recovery.signal 标识文件，表示数据库处于 recovery 模式;
新增加 standby.signal 标识文件，表示数据库处于 standby 模式(这个需要重点关注一下);
以前版本中 standby_mode 参数不再支持;
recovery.conf文件取消, 合并到了postgresql.conf文件中;
配置中war_level存储级别, postgresql-9.6以后有改变:

等级	说明
minimal	不能通过基础备份和wal日志恢复数据库
replica	9.6新增,将之前版本的 archive 和 hot_standby合并, 该级别支持wal归档和复制
logical	在replica级别的基础上添加了支持逻辑解码所需的信息

2．流复制配置过程

（1）主库、从库都要配置

创建一个账户repuser 用户, 提供给备库远程访问, 用来获取流:

su - postgres

-bash-4.2$ psql

postgres=# create role repuser login replication encrypted password 'repuser';

CREATE ROLE

postgres=# \q

修改数据目录下配置文件: pg_hba.conf

vim /var/lib/pgsql/12/data/pg_hba.conf# 在文件中添加:

host replication repuser 0.0.0.0/0 md5

修改数据目录下配置文件: postgresql.conf

vim /var/lib/pgsql/12/data/postgresql.conf# 在文件中修改(此配置仅用于远程访问, 流复制后续还有额外配置):

listen_addresses = '*'

port = 5432

max_connections = 100 # 最大连接数，据说从机需要大于或等于该值

# 控制是否等待wal日志buffer写入磁盘再返回用户事物状态信息。同步流复制模式需要打开。

synchronous_commit = on# *=all，意思是所有slave都被允许以同步方式连接到master，但同一时间只能有一台slave是同步模式。# 另外可以指定slave，将值设置为slave的application_name即可。

synchronous_standby_names = '*'

wal_level = replica

max_wal_senders = 2 #最多有2个流复制连接

wal_keep_segments = 16

wal_sender_timeout = 60s #流复制超时时间

修改完成重启postgresql-12服务

systemctl restart postgresql-12

（2）从库(所有从库服务器都要操作)

关闭备库postgresql数据库

systemctl stop postgresql-12

权限配置

如果备库机器上没有 PGDATA(/var/lib/pgsql/12/data)目录(恢复出故障数据，目录消失同样操作)

mkdir /var/lib/pgsql/12/data

chmod 0700 data

chown postgres.postgres data

把主库整个备份到从库

其实后续的pgpool的主库挂了, 从库升级主库之后, 主库恢复为从库的过程就是: 备份data目录,然后重复这里的第2,3步骤

# 切换到postgres用户，否则备份的文件属主为root, 导致启动出错

su – postgres

pg_basebackup -h 10.242.111.204 -p 5432 -U repuser -Fp -Xs -Pv -R -D /var/lib/pgsql/12/data# -h 的ip是当前的主库, -U 就是前面个船舰的用来复制的那个用户

额外需要注意的是:

如果主库的pg_hba.conf中配置的策略为trust, 这里不需要口令, 如果为md5模式,需要输你创建用户时的那个密码;

这里 -R 参数一定要加, 拷贝完在$PGDATA目录下生成standby.signal标志文件(用于表示此库为备库);

使用命令同步完之后, 在data目录下会自动生成postgresql.auto.conf文件中, 优先级是大于postgresql.conf的;

启动备库postgresql-12 服务

systemctl restart postgresql-12

3.流复制验证

使用命令或者远程客户端工具登入postgresql主(primary)数据库


# 切换垂直显示
 
postgres=# \x
 
Expanded display is on.# 查询备机连接
 
postgres=# select * from pg_stat_replication;
 
    -[ RECORD 1 ]----+------------------------------
 
    pid              | 27132
 
    usesysid         | 16384
 
    usename          | repuser
 
    application_name | walreceiver
 
    client_addr      | 10.242.111.205
 
    client_hostname  |
 
    client_port      | 34726
 
    backend_start    | 2020-06-19 16:23:01.309172+08
 
    backend_xmin     |
 
    state            | streaming
 
    sent_lsn         | 0/11000148
 
    write_lsn        | 0/11000148
 
    flush_lsn        | 0/11000148
 
    replay_lsn       | 0/11000148
 
    write_lag        |
 
    flush_lag        |
 
    replay_lag       |
 
    sync_priority    | 1
 
    sync_state       | sync
 
    reply_time       | 2020-06-19 18:39:43.346062+08# 可以发现207的那台备机已经连接上, 并且使用的是 sync 同步模式(async 为异步)# 此时可以在主库上创建数据库/表/添加数据, 然后去备库上查询 以显示效果, 如:
 
postgres=# create database test_204;
 
postgres=# \c test_204
 
postgres=# create table test_table(name text);
 
postgres=# insert into test_table(name) values ('china');

# 补充基础查询命令# \list 列出可用数据库# \db_name 选择数据库# \dt 查询关系表

至此, 流复制(数据同步)策略完成.

postgresql 主从切换(primary->standby)

主数据库是读写的，备数据库是只读的。
使用/usr/pgsql-12/bin/pg_controldata /var/lib/pgsql/12/data/ 可以查看数据库运行状态
- Database cluster state: in production (主库为运行中)
- Database cluster state: in archive recovery (从库为正在归档回复)
当主库primary 出现故障, 我们需要将从库standby提升为主库primary;
- pg12版本以前的切换方式有两种:
  - pg_ctl 方式: 在备库主机执行 pg_ctl promote shell 脚本
  - 触发器文件方式: 备库配置 recovery.conf 文件的 trigger_file 参数，之后在备库主机上创建触发器文件
- pg12开始新增了一个pg_promote()函数, 支持知用promote指令进行切换
- pg_promote(wait boolean DEFAULT true, wait_seconds integer DEFAULT 60)
  - wait: 表示是否等待备库的 promotion 完成或者 wait_seconds 秒之后返回成功，默认值为 true
  - wait_seconds: 等待时间，单位秒，默认 6
切换举例


# 关闭主库primary
 
systemctl stop postgresql-12# 从库上执行切换# 第一种
 
/usr/pgsql-12/bin/pg_ctl promote -D /var/lib/pgsql/12/data# 第三种
 
su postgres
 
psql
 
postgres=# select pg_promote(true,60);# 此时从库会提升为主库接管集群中的读写操作

pgpool-ii 中默认的 failover_command 切换数据库主备角色脚本, 是通过pg_ctl promote 来实现的

四、pgpool-ii4.1.2集群配置

概述:

在postgresql12 安装完成之后, 我们需要安装pool-ii中间件, 来完成 HA 自动切换, ip 自动切换的工作。当某一个数据库挂掉之后, 检测其生命状态, 执行相应策略(主库挂掉, 切换某个备库为主库等)；当某一个pgpool节点不可用, 其他节点收到信息进行IP转移(访问入口接管)。

（一）服务器SSH免密设置

为后续使用 hostname 和脚本中 SSH 免登录:

#临时关闭selinux

setenforce 0

#永久关闭selinux

修改 /etc/selinux/config 文件中的 SELINUX=enforcing 修改为 SELINUX=disabled

注意：需要关闭selinux，否则免登陆设置后还是要求输入密码


# 主机/备机操作
 
su – postgres
-bash-4.2$ ssh-keygen
# 一直enter

首先修改postgres用户密码，以root用户操作：

passwd postgres

输入密码如postgres后即可修改。

公钥拷贝到其他服务器（每台服务器执行相同指令）


-bash-4.2$ ssh-copy-id -i .ssh/id_rsa.pub 10.242.111.204
 
-bash-4.2$ ssh-copy-id -i .ssh/id_rsa.pub 10.242.111.205
 
-bash-4.2$ ssh-copy-id -i .ssh/id_rsa.pub 10.242.111.206

注意：

提示输入密码，请输入第二步修改的密码，如postgres
如提示未找到/var/lib/pgsql/.ssh目录，在root用户创建目录:

创建目录、赋予权限

mkdir /var/lib/pgsql/.ssh

chown postgres /var/lib/pgsql/.ssh

注意本服务器也需要执行ssh免登陆，因切换主节点时候，需要使用ip登录

（二）pgpool-ii 安装(三台机器均安装)

1.yum 在线安装

#设置rpm源

curl -O https://www.pgpool.net/yum/rpms/4.1/redhat/rhel-7-x86_64/pgpool-II-release-4.1-2.noarch.rpm

rpm -ivh pgpool-II-release-4.1-2.noarch.rpm

#安装(关于对应的 postgresql 版本,体现在文件名中的 pgXX 这里)

yum -y install pgpool-II-pg12

yum -y install pgpool-II-pg12-debuginfo

yum -y install pgpool-II-pg12-devel

yum -y install pgpool-II-pg12-extensions

2.rpm离线安装

# ftp下载rpm离线包

curl -O https://www.pgpool.net/yum/rpms/4.1/redhat/rhel-7-x86_64/pgpool-II-pg12-4.1.2-1pgdg.rhel7.x86_64.rpm

curl -O https://www.pgpool.net/yum/rpms/4.1/redhat/rhel-7-x86_64/pgpool-II-pg12-debuginfo-4.1.2-1pgdg.rhel7.x86_64.rpm

curl -O https://www.pgpool.net/yum/rpms/4.1/redhat/rhel-7-x86_64/pgpool-II-pg12-devel-4.1.2-1pgdg.rhel7.x86_64.rpm

curl -O https://www.pgpool.net/yum/rpms/4.1/redhat/rhel-7-x86_64/pgpool-II-pg12-extensions-4.1.2-1pgdg.rhel7.x86_64.rpm

# 上传文件到服务器之后, 执行安装命令

rpm -ivh pgpool*.rpm

补充: 源码编译安装参照官方指定文档：Installation of Pgpool-II

3.授权

因为pgpool-ii 配置中会以 postgres 用户执行一些系统权限命令, 需要使用设置普通用户授权:

使用服务器root账号配置

chmod u+x /usr/sbin/ip

chmod u+s /usr/sbin/arping

chmod u+s /sbin/ip

chown postgres.postgres /var/run/pgpool

mkdir -p /var/log/pgpool/

touch /var/log/pgpool/pgpool_status

chown -R postgres.postgres /var/log/pgpool/

（三）pgpool-ii 配置

1.pool_hba.conf

pool_hba.conf 是配置用户链接时的验证策略, 和postgresql的pg_hba.conf保持一致，要么都是trust，要么都是md5验证方式，这里采用了md5验证方式如下设置:


[root@localhost ~] su - postgres
 
-bash-4.2$ cd /etc/pgpool-II/
 
-bash-4.2$ vim pool_hba.conf#编辑内容如下(这里和postgressql设置一样, trust/md5保持一致)# IPv4 local connections:
 
host    all         all         0.0.0.0/0          md5
 
host    all         all         0/0                 md5

2.pcp.conf

这个文件是pgpool管理器自己的用户名和密码，用于管理集群的.


-bash-4.2$ pg_md5 postgres
 
e8a48653851e28c69d0506508fb27fc5
 
-bash-4.2$ vim pcp.conf#编辑内容如下:# USERID:MD5PASSWD
 
postgres: e8a48653851e28c69d0506508fb27fc5#在pgpool中添加pg数据库的用户名和密码
 
[postgres@etc~]$ pg_md5 -p -m -u postgres pool_passwd#数据库登录用户是postgres,这里输入登录密码，不能出错#输入密码后，在/etc/pgpoll-II目录下会生成一个pool_passwd文件

3.pgpool.conf

这个文件中会配置我们pgpool-ii 节点的关键参数, pgpool-ii 自带提供几种不同模式的配置文件:

# 执行完前面步骤 /etc/pgpool-II/目录下的文件, 我们需要用到的

failover.sh (数据库故障切换脚本)

pcp.conf (用户访问验证策略trust/md5)

pgpool.conf (pgpool-ii 主配置文件)

pool_hba.conf (集群节点密码管理)

pool_passwd (数据库密码管理文件)

recovery_1st_stage.sample (在线故障恢复的脚本示例, 放到postgresql数据目录/var/lib/pgsql/12/data 下)

主库pgpool-ii 节点(10.242.111.204-master)

#常用基础配置

pid_file_name = '/var/run/pgpool/pgpool.pid'# pid 文件位置，如果不配置有默认的

logdir = '/var/run/pgpool' # status 文件存储位置# 通用

listen_addresses = '*'

port = 9999

pcp_listen_addresses = '*'

pcp_port = 9898

# 后台数据库链接信息配置

backend_hostname0 = '10.242.111.204' # 第一台数据库信息

backend_port0 = 5432

backend_weight0 = 1 # 这个权重和后面负载比例相关

backend_data_directory0 = '/var/lib/pgsql/12/data'

backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = '10.242.111.205' # 第二台数据库信息

backend_port1 = 5432

backend_weight1 = 1

backend_data_directory1 = '/var/lib/pgsql/12/data'

backend_flag1 = 'ALLOW_TO_FAILOVER'

backend_hostname2 = '10.242.111.206' # 第三台数据库信息

backend_port2 = 5432

backend_weight2 = 1

backend_data_directory2 = '/var/lib/pgsql/12/data'

backend_flag2 = 'ALLOW_TO_FAILOVER'

# 流复制相关配置

replication_mode = off # pgpool-ii 中复制制模式关闭

load_balance_mode = on # 负载均衡打开

master_slave_mode = on # 主从打开

master_slave_sub_mode = 'stream' # 主从之间模式为流传输stream

sr_check_period = 5 # 流复制检查相关配置

sr_check_user = 'repuser'

sr_check_password = 'repuser'

sr_check_database = 'postgres'

数据库故障转移(故障后处理)

# 数据库运行状况检查，以便Pgpool-II执行故障转移: 数据库的主备切换

health_check_period = 10 # Health check period, Disabled (0) by default

health_check_timeout = 20 # 健康检查的超时时间,0 永不超时

health_check_user = 'postgres' # 健康检查的用户

health_check_password = 'postgres' # 健康检查的用户密码

health_check_database = 'postgres' # 健康检查的数据库

# 故障后处理, 为了当postgresql数据库挂掉之后执行相应的策略# 这个脚本时放在pgpool的目录下, 确切的说是由pgpool执行脚本来维护集群中数据库的状态

failover_command = '/etc/pgpool-II/failover_stream.sh %d %P %H %R' # follow_master_command = '' # 2台服务器不配置# 如果使用3台PostgreSQL服务器，则需要指定follow_master_command在主节点故障转移上的故障转移后运行。如果有两个PostgreSQL服务器，则无需follow_master_command设置。

具体配置脚本详见pgpool.conf文件

配置文件中传入的相应参数请参照: config-failover-params

watchdog(看门狗)配置(用于检测pgpool-ii 节点状态, 为后续pgpool故障处理提供依据)

use_watchdog = on # 激活看门狗配置

wd_hostname = 'master' # 当前主机(也可使用IP)

wd_port = 9000 # 工作端口

# 虚拟IP指定

delegate_IP = '10.242.111.203'

if_cmd_path = '/sbin' # 如果if_up_cmd, if_down_cmd 以/开头, 忽略此配置# 命令中的`ens160` 请根据自己机器上ip addr 实际的网卡名称进行修改# 当前节点启动指定虚拟IP的命令

if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev ifcfg-ens03 label ifcfg-ens03:0'# 当前节点指定关闭虚拟IP的命令

if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev ifcfg-ens03'

# watchdog 健康检查

wd_heartbeat_port = 9694 # 健康检查端口

wd_heartbeat_keepalive = 2

wd_heartbeat_deadtime = 30# 其他机器地址配置(多台请增加配置)

heartbeat_destination0 = '10.242.111.205'

heartbeat_destination_port0 = 9694

heartbeat_device0 = 'ifcfg-ens03'

heartbeat_destination0 = '10.242.111.206'

heartbeat_destination_port0 = 9694

heartbeat_device0 = 'ifcfg-ens03'

# 其他pgpgool节点链接信息(多台请增加配置)

other_pgpool_hostname0 = '10.242.111.205' # 其他节点地址

other_pgpool_port0 = 9999

other_wd_port0 = 9000 # 其他节点watchdof 端口

# watchdog 发生故障后, 处理的相关配置(宕机, pgpool进程终止)# 当某个节点故障后,

other_pgpool_hostname1 = '10.242.111.206' # 其他节点地址

other_pgpool_port1 = 9999

other_wd_port1 = 9000

failover_when_quorum_exists = on

failover_require_consensus = on

allow_multiple_failover_requests_from_node = on

enable_consensus_with_half_votes = on

关于watchdog本身(pgpool-ii)发生故障后相关的处理策略, 请务必阅读官方文档: CONFIG-WATCHDOG-FAILOVER-BEHAVIOR
watchdog本身(pgpool-ii节点)本身故障后, 如果配置打开, 其他节点会执行仲裁, 如仲裁从节点中那一个成为主节点, 那一台阶段虚拟IP等, 这个仲裁本身有投票机制, 和无视仲裁结果等配置;
如果不配置, 主pgpool-i 节点关闭后, 可能不会转移虚拟IP, 出现集群暂时不可访问;
注意网卡配置，需要根据ip addr命令结果的配置

关于在线恢复(master 恢复后自动变为备库)

# 此配置将在多个pgpool-ii 节点时无效

recovery_user = 'postgres'

recovery_password = 'postgres'

recovery_1st_stage_command = 'recovery_1st_stage' # 这个脚本时放在postgresql数据目录下的

如果有多个pgpool-ii 节点共同维护集群状态, 此配置将不可用, 需要手动恢复同步数据>加入集群

备库pgpool-ii 节点(10.242.111.205和10.242.111.206-slave)

# 将主库的配置文件拷贝过来

在备份节点服务器上的/etc/pgpool-II/目录下操作以下命令：

scp 10.242.111.205:/etc/pgpool-II/\{pool_hba.conf,pcp.conf,pool_passwd,pgpool.conf,failover.sh, follow_matser.sh\} .

#备份节点服务器迁移recovery_1st_stage文件

cp recovery_1st_stage.sample recovery_1st_stage

mv recovery_1st_stage /var/lib/pgsql/12/data

# 修改以下几项即可

wd_hostname = '10.242.111.205' # 当前机器ip

heartbeat_destination0 = '10.242.111.204' # 其他pg库机器（如10.242.111.204）

heartbeat_destination1 = '10.242.111.206' # 其他pg库机器（如10.242.111.206）

other_pgpool_hostname0 = '10.242.111.204' # 其他pgpool节点机器

other_pgpool_hostname1 = '10.242.111.206' # 其他pgpool节点机器

（四）验证

启动命令

su - postgres# 启动命令(日志位置可在命令中指定)

pgpool -n -d -D > /var/log/pgpool/pgpool.log 2>&1 & # 有debug日志

pgpool -n -D > /var/log/pgpool/pgpool.log 2>&1 & # 无debug日志

# 终止命令

pgpool -m fast stop

配置相关脚本权限

chown postgres.postgres /etc/pgpool-II

chmod +x /etc/pgpool-II/follow_matser.sh

Chmod +x /etc/pgpool-II/failover.sh

启动完成之后, 可登陆虚拟IP, 进行集群状态查询

psql -h vip -p9999 -Upostgres -d postgres#或

psql -h 10.242.111.203 -p9999 -Upostgres -d postgres

查询结果可见:


[root@slave ~]# psql -h 10.242.111.203 -p9999 -Upostgres -d postgres
 
Password for user postgres:
 
psql (12.3)
 
Type "help" for help.
 
 
 
postgres=# show pool_nodes;
 
 node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
 
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 
 0       | 10.242.111.204   | 5432 | up     | 0.500000  | primary | 0          | true              | 0                 |                   |                        | 2020-06-22 17:48:51
 
 1       | 10.242.111.205    | 5432 | up     | 0.500000  | standby | 0          | false             | 0                 |                   |                        | 2020-06-22 17:48:51
 
 2       | 10.242.111.206    | 5432 | up     | 0.500000  | standby | 0          | false             | 0                 |                   |                        | 2020-06-22 17:48:51
 
 
 
(3 rows)

lb_weight和前面pool.conf配置中backend_weight0 = 1的比例有关;
role 为postgresql 数据库的主备状态;
up为数据库已加入集群管理;
此时可以通过虚拟IP链接, 执行新增/修改/删除等操作来测试集群正常工作状态的数据同步;

（五）宕机验证

（1）pgpool-ii 节点宕机

处理关键步骤:

如果时正常关闭: 释放虚拟IP > 节点正常关闭 > 其他节点检测到时区链接 > 仲裁选出新的"主节点" > 接管虚拟IP > 正常提供服务
如果异常关闭: 其他节点检测到时区链接且IP不可用> 仲裁选出新的"主节点" > 接管虚拟IP > 正常提供服务

# 在master节点上停止pgpool服务

-bash-4.2$ pgpool -m fast stop

# 原从节点切换完之后，访问集群# 可查看/var/log/pgpool/pgpool.log 日志, 可看到被关闭的释放虚拟IP, 其他节点接管虚拟IP

-bash-4.2$ psql -h 10.242.111.203 -p 9999

# 提示输入密码：

Type "help" for help.

postgres=# show pool_nodes;

（2）postgresql 数据库宕机


 # 模拟数据库宕机
 
 systemctl stop postgresql-12
 
 
 
 postgres=# show pool_nodes;
 
 node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay
 
 ---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------
 
 0       | 10.242.111.204   | 5432 | down   | 0.500000  | standby | 0          | false             | 0
 
 1       | 10.242.111.205   | 5432 | up     | 0.500000  | primary | 0          | true     
 2       | 10.242.111.206   | 5432 | up     | 0.500000  | standby | 0          | true           | 0
 
 (3 rows)

#slave已经被切换成primary，且master节点状态是down

master的数据库挂(primary)掉了，同时变成了standby，pgpool执行数据库故障切换脚本failover.sh,slave正在作为新的主数据库(primary)提供服务
这里当master 重启之后, 需要恢复同步数据, 重新加入集群

节点重新加入集群步骤：


1. 恢复同步数据
 
# 2. 清除当前"备库"上的data目录
 
mkdir /var/lib/pgsql/12/data
 
chmod 0700 data
 
chown postgres.postgres data
 
# 3. 执行复制命令
 
su – postgres
 
pg_basebackup -h 目标机器IP -p 5432 -U repuser -Fp -Xs -Pv -R -D /var/lib/pgsql/12/data
 
# 4. 启动服务(切换到root用户)
 
systemctl restart postgresql-12
 
# 5. 重新将postgresql数据库加入集群管理(su - postgres)# -n 节点序号, postgresql 数据库在集群中的节点序号
 
pcp_attach_node -d -U postgres -h vip -p 9898 -n 0
 
# 6. 此时
 
node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay
 
 ---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------
 
 0       | 10.242.111.204   | 5432 | up    | 0.500000  | standby | 0          | false             | 0
 
 1       | 10.242.111.205    | 5432 | up    | 0.500000  | primary | 0          | true              | 0
 
 2       | 10.242.111.206   | 5432 | up    | 0.500000  | standby | 0          | false             | 0
 
 
 
 (3 rows)

如果只有一个pgpool-ii 节点, 可以使用pgpool.conf中配置的recovery_1st_stage_command在线恢复来操作. 多个pgpool节点此配置不支持.

（3）服务器宕机

主机直接宕机, 意味着当前机器时区所有链接, 其他pgpool-ii 节点检测不到原"主pgpool-ii"节点存活
首先, 选举出一个pgpool-ii 节点承担责任
接管维护虚拟IP的责任, 启动指定的虚拟ip
此时检测到原主数据库primary不可链接, 执行故障转移脚本, 将当前备库转换为主库为集群提供存储服务;

五、总结

数据库postgresql 层面的故障转移执行切换脚本, 本质上执行的还是 pg_ctl promote命令;
pgpool-ii 层面故障, 后续的检测/切换虚拟IP, 是不影响下层postgresql数据库运行状态的;
在主机直接挂掉的情况下, 上面两个步骤是都要执行的;
数据库宕机恢复的本质依然是同步流复制pg_basebackup命令;
即使是pgpool-ii 提供的在线恢复,本质上也是几个步骤的脚本:
清空备机节点数据目录 > 使用pg_basebackup命令同步数据 > 启动数据库服务 > 将数据库重新加入集群
如pgpool-ii故障切换出现问题，请查看/var/log/pgpool/pgpool.log日志和/var/log/messages日志，结合一起综合分析异常信息

注意：本文参考https://www.cnblogs.com/applerosa/p/13160566.html（参考原文不能直接使用，会出现各种环境问题）。在此基础上优化，可以直接使用到正式环境。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/你好赵伟/article/detail/173413

postgresql 12.1 + pgpool-ii 4.1构建容灾高可用集群(三节点以上，支持数据同步流复制/主备自动切换)_pgpool-ii搭建集群

一、高可用方案效果

二、配置方案

三、postgresql-12安装(3台机器均安装)

（一）yum 在线安装

（二）rpm离线安装

（三）postgresql-12 流复制配置

四、pgpool-ii4.1.2集群配置

（一）服务器SSH免密设置

（二）pgpool-ii 安装(三台机器均安装)

​​​​​​​1.yum 在线安装

​​​​​​​2.rpm离线安装

​​​​​​​3.授权

（三）pgpool-ii 配置

​​​​​​​1.pool_hba.conf

2.pcp.conf

3.pgpool.conf

​​​​​​​（四）验证

​​​​​​​（五）宕机验证

（1）pgpool-ii 节点宕机

​​​​​​​（2）postgresql 数据库宕机

（3）服务器宕机

五、总结

1.yum 在线安装

2.rpm离线安装

3.授权

1.pool_hba.conf

（四）验证

（五）宕机验证

（2）postgresql 数据库宕机