PGPool-II-12+PG-12流复制实现HA主备切换

postgresql-12数据库安装+流复制+主备切换

主库安装
yum install -y postgresql12-*
vim /usr/lib/systemd/system/postgresql-12.service

修改PGDATA目录为自定义目录，可复制一个作为备库程序，并修改成备库的PGDATA目录
导入环境变量
export PATH=/usr/pgsql-12/bin:$PATH
创建自定义的PGDATA目录并修改权限用于初始化数据库
mkdir /home/pg12/data
chmod 700 /home/pg12/data
/usr/pgsql-12/bin/postgresql-12-setup initdb [若启动自定义的pg程序时需要指定service name]

一般安装好后psql等命令应该可以执行，如果 psql：找不到命令 可以如下操作
vim ~/.bashrc
添加一行
export PATH=/usr/pgsql-12/bin:$PATH
再重新连接服务器后即可

vim PGDATA/postgresql.conf
listen_addresses = '*' 
port = 自定义
wal_level = replica
max_wal_senders = 10

vim PGDATA/pg_hba.conf
添加
host    all                  all             0.0.0.0/0               md5
host    replication     all             0.0.0.0/0               md5

systemctl enable postgresql-12
systemctl start postgresql-12

su postgres
psql -p 自定义端口
\l 查看是不是正确的数据库
修改数据库用户postgres密码
ALTER USER postgres WITH PASSWORD 'postgres';   
--必须以分号结束，成功执行后会出现ALTER ROLE
\q 退出

复制备库
若是同机器上的备库只需要创建备库的PGDATA目录即可
若是不同机器的备库则重复上面的安装步骤到PGDATA目录创建并修改权限完成即可

su postgres
pg_basebackup -D /home/pg12/data（备库PGDATA目录） -X stream -R -Fp -v -P -h 主库ip（若是本机则localhost） -p 5432（主库端口）
需要输入密码，即上面修改的postgres密码

复制完成后进入备库PGDATA目录，查看是否有standby.signal（有这个文件则启动时会作为备库）

vim postgresql.auto.conf 
查看这个文件里的host和port信息是否是主库的，否则需要修改

vim postgresql.conf
因为是复制的所以只需要修改
port = 自定义（同机需要改，不同机看有没有冲突端口）
hot_standby = on（备库需要打开，允许在备库执行查询sql）

完成后启动备库
回到root用户
systemctl start postgresql-12.service（同机的则启动上面复制的新程序）

查看是主库还是备库
pg_controldata /home/pg12/data（要看的库的PGDATA目录）

Database cluster state:               in production           （主库）
Database cluster state:               in archive recovery （备库）

数据库簇状态:                         在运行中              （主库）
数据库簇状态:                         正在归档恢复       （备库）

进入主库
su postgres
psql -p 5432（主库端口）
select client_addr, sync_state from pg_stat_replication;
如下则表示正在异步流复制

 client_addr  | sync_state 
--------------+------------
 10.10.10.2 | async
 ::1          | async

主备切换

停止主库
systemctl stop postgresql-12
切换备库为主库
su postgres
psql -p 5434（备库端口）
select pg_promote();
如下则表示成功
 pg_promote 
------------
 t

但此时在新的主库查询是没有其他备库在流复制的
select client_addr, sync_state from pg_stat_replication;
 client_addr | sync_state 
-------------+------------
(0 行记录)

将其他备库的流复制切换到新的主库

到其他备库上执行以下操作
vim postgresql.auto.conf 修改host和port信息为新主库
需要有standby.signal文件，没有可以touch standby.signal
重启备库

再回到新的主库
select client_addr, sync_state from pg_stat_replication;
 client_addr  | sync_state 
--------------+------------
 10.10.10.2 | async
(1 行记录)

其他备库切换期间对新主库的写入操作也会复制到备库

将原主库启动作为新的备库
su postgres
vim postgresql.auto.conf 修改host和port信息为新主库
touch standby.signal
重启原主库

再回到新的主库
select client_addr, sync_state, state from pg_stat_replication;
 client_addr  | sync_state |   state   
--------------+------------+-----------
 10.10.10.2 | async      | streaming
 ::1          | async      | streaming

也可以清空原主库的PGDATA目录，重复pg_basebackup操作
异步流复制，原主库宕机很久后再恢复，可能和新主库的时间线不同，pg_rewind暂时没研究，所以还是用pg_basebackup重来

=========================================================================

PostgreSQL数据默认会创建一个linux用户postgres，可以修改linux系统的postgres用户的密码
1.删除PostgreSQL用户密码
# sudo passwd -d postgres
passwd: password expiry information changed.
//passwd -d 是清空指定用户密码的意思
2.设置PostgreSQL用户密码
#sudo -u postgres passwd
输入新的 UNIX 密码：
重新输入新的 UNIX 密码：
passwd：已成功更新密码

=========================================================================

PGPool-II-12+PG-12流复制实现HA主备切换

规划
pgpool 10.10.10.1 主
pg        10.10.10.1 主
pgpool 10.10.10.2 备
pg        10.10.10.2 备
vip        10.10.10.3

分别在两台机器上
vim /etc/hosts
10.10.10.1 master
10.10.10.2 slave
10.10.10.3 vip

在master,slave机器上都生成ssh如下：
su postgres
ssh-keygen -t rsa 一路回车
cat ~/.ssh/id_rsa.pub 查看生成的公钥
把生成的公钥追加到 ~/.ssh/authorized_keys
chmod 600 ~/.ssh/authorized_keys

分别将
master的公钥追加到slave的authorized_keys，
slave的公钥追加到master的authorized_keys。
两边互相ssh验证

ssh postgres@slave

ssh postgres@master

安装pgpool
yum install -y pgpool-II-12

pgpool配置目录: /etc/pgpool-II-12

su postgres
cp pool_hba.conf.sample pool_hba.conf
vim pool_hba.conf
将trust都改成md5，并添加一行
host    all             all             0.0.0.0/0               md5

cp pcp.conf.sample pcp.conf
pg_md5 postgres 会生成密文密码
vim pcp.conf
添加一行postgres:密文密码
pg_md5 -p -m -u postgres pool_passwd
输入密码postgres回车
会生成pool_passwd文件

回到root
chmod u+s /usr/sbin

vim /etc/sudoers
添加两行
postgres ALL=NOPASSWD: /sbin/ip
postgres ALL=NOPASSWD: /usr/sbin/arping

查看本机网卡，配置后面的delegate_IP需要
ifconfig找到本机ip的网卡，这个em1后面要用
em1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 10.10.10.1  netmask 255.255.255.0  broadcast 10.10.10.255

cp pgpool.conf.sample pgpool.conf
vim pgpool.conf
需要修改的配置如下（备机上的pgpool.conf配置类似，把其中的对端换一下）：

=========================================================================

listen_addresses = '*'
port = 9999
pcp_listen_addresses = '*'
pcp_port = 9898

backend_hostname0 = 'master'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/home/postgres/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = 'slave'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/home/postgres/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

enable_pool_hba = on
pool_passwd = 'pool_passwd'

replication_mode = off
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'

sr_check_period = 10
sr_check_user = 'postgres'
sr_check_password = 'postgres'
sr_check_database = 'postgres'

health_check_period = 10 
health_check_timeout = 20
health_check_user = 'postgres'
health_check_password = 'postgres' #数据库密码
health_check_database = 'postgres'
#必须设置，否则primary数据库down了，pgpool不知道，不能及时切换。从库流复制还在连接数据，报连接失败。
#只有下次使用pgpool登录时，发现连接不上，然后报错，这时候，才知道挂了，pgpool进行切换。

failover_command = '/etc/pgpool-II-12/failover_stream.sh %H '

use_watchdog = on
wd_hostname = 'master'
wd_port = 9000

delegate_IP = '10.10.10.3'        # 找一个没有用到的ip地址
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev em1 label em1:0'        # eth1根据现场机器改掉，下面一样
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev em1'
arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I em1'

enable_consensus_with_half_votes = on    # 一定要打开这个开关才能使单台pgpool获取vip，或者两台时down一台另一台自动切换vip

wd_heartbeat_port = 9694
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
heartbeat_destination0 = 'slave'
heartbeat_destination_port0 = 9694
heartbeat_device0 = 'em1'            # em1根据现场机器改掉

other_pgpool_hostname0 = 'slave' #对端
other_pgpool_port0 = 9999
other_wd_port0 = 9000

=========================================================================

编写脚本
vim failover_stream.sh
内容如下：
=========================================================================

#! /bin/sh 
# Failover command for streaming replication. 
# Arguments: $1: new master hostname. 

new_master=$1
trigger_command="/usr/pgsql-12/bin/pg_ctl promote -D /home/pg12/data"

# Prompte standby database. 
/usr/bin/ssh -T $new_master $trigger_command

exit 0;

=========================================================================

修改配置文件和脚本权限
chown -R postgres.postgres /etc/pgpool-II-12
chmod 777 /etc/pgpool-II-12/failover_stream.sh

创建日志目录
mkdir /var/log/pgpool
chown -R postgres.postgres /var/log/pgpool

在postgres用户下启动pgpool，否则会在执行failover.sh时提示输入密码而失败
需要debug日志时可以加参数 -d
su postgres
pgpool -n -D > /var/log/pgpool/pgpool.log 2>&1 &
停止命令
pgpool -m fast stop

主备机pgpool都启动后，查看状态
主机上 ip addr 可以看到em1网卡下有vip（ifconfig看不到）

su postgres
psql -h vip -p 9999 （此时可以将vip作为对外统一的数据库ip，端口9999）
输入密码后查询
show pool_nodes;

 node_id |   hostname   | port | status | lb_weight |  role   | select_cnt | load_balance_node 
---------+--------------+------+--------+-----------+---------+------------+-------------------
 0       | 10.10.10.1 | 5434 | up     | 0.500000  | primary | 0          | true              
 1       | 10.10.10.2 | 5432 | up     | 0.500000  | standby | 0          | false             
(2 行记录)

pgpool的HA，stop任何一台pgpool，仍然可以用vip+9999端口访问数据库，重新启动后即可恢复
pg的HA

停止主库，此时备库会自动变成主库
 node_id |   hostname   | port | status | lb_weight |  role   | select_cnt | load_balance_node 
---------+--------------+------+--------+-----------+---------+------------+-------------------
 0       | 10.10.10.1 | 5434 | down   | 0.500000  | standby | 0          | false             
 1       | 10.10.10.2 | 5432 | up     | 0.500000  | primary | 0          | true              
(2 行记录)

主库使用上面方法手动进行恢复成备库后启动，需要手动加入pgpool集群
pcp_attach_node -d -U postgres -h 10.10.10.3(vip) -p 9898 -n 0（node_id）
输入pcp管理密码postgres
加入后节点状态一直是waiting，需要停止pgpool主节点，再删除pgpool_status后重启pgpool
rm /var/log/pgpool-II-12/pgpool_status （这个目录是自动创建的，不是前面自定义的日志目录）

 node_id |   hostname   | port | status | lb_weight |  role   | select_cnt | load_balance_node  
---------+--------------+------+--------+-----------+---------+------------+-------------------
 0       | 10.10.10.1 | 5434 | up     | 0.500000  | standby | 5          | true              
 1       | 10.10.10.2 | 5432 | up     | 0.500000  | primary | 132        | false             
(2 行记录)