postgresql 主备以及pgpool下的ha方案

这篇文章是pg 主备的配置以及 pgpool 的使用

1.pg11 安装

yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install postgresql11
yum install postgresql11-server
/usr/pgsql-11/bin/postgresql-11-setup initdb
systemctl enable postgresql-11
systemctl start postgresql-11

2.stand by

2.1 primary db

创建复制账号

1	CREATE ROLE pgrepuser REPLICATION LOGIN ENCRYPTED PASSWORD 'pgreppass';

postgresql.conf

listen_addresses = '*'
max_connections = 1024
password_encryption = on
wal_level = hot_standby
archive_mode = on
max_wal_sender = 4
wal_keep_segments = 10

pg_hba.conf

1	host replication pgrepuser 0.0.0.0/0 md5

重启 pg11

2.2 standby db 异步

启动容器standby

1	sudo docker run -d --name standby_pg11 -p 15432:5432 -e POSTGRES_PASSWORD=postgres -v /etc/localtime:/etc/localtime -v /home/pgdata/:/var/lib/postgresql/data -e TZ=Asia/Shanghai postgres:11.4

进入内部

1	docker exec -it standby_pg11 bash

获取 primary db 的文件内容
- 切换身份 postgres
- 删掉原有数据
- 执行 pg_basebackup
- 输入密码pgreppass

1
2
3

su postgres  

cd /var/lib/postgresql/data && rm -rf * && /usr/lib/postgresql/11/bin/pg_basebackup -D $PGDATA -Fp -Xstream -R -c fast -v -R -P -h ${primary db host} -U pgrepuser -W

成功后，-R会新生成 recovery.conf

可能出现的问题

如果 primary db 使用中文会报错:

1	invalid value for parameter "lc_messages": "zh_CN.UTF-8"

这是两者版本不一致导致的,删掉 postgres.conf 内的

lc_messages = 'zh_CN.UTF-8'			# locale for system error message
					# strings
lc_monetary = 'zh_CN.UTF-8'			# locale for monetary formatting
lc_numeric = 'zh_CN.UTF-8'			# locale for number formatting
lc_time = 'zh_CN.UTF-8'				# locale for time formatting

然后重启容器，内部执行

1	localedef -f UTF-8 -i zh_CN zh_CN.UTF-8

2.2 standby db 同步配置

a. recovery.conf 修改配置，并重启
1
2
standby_mode = 'on'
primary_conninfo = 'application_name=a ....
其中，a,b,c 代表从库的application_name，逗号分隔，在从库的revovery.conf内配置
修改primarydb
- 1 开启 postgres.conf 参数，指定具体的从库application_name
  1
  synchronous_standby_names='a,b,c'
- 2 加载配置文件
  1
  pg_ctl reload -D ${pg 的data目录}

当从库（standbydb）数量小于1，且配置同步更新时，主库的写操作会终止

2.3 冲突解决

从库

max_standby_streaming_delay = 30s
- 作为切换主备时，值尽可能小
- 作为查询使用时，值尽可能大
hot_standby_feedback=on 多版本冲突解决

2.4 流复制查询

主库执行
- 查询复制 select * from pg_stat_replication

3 pgpool2

官网： https://www.pgpool.net/

这里主要是利用pgpool实现主备切换，负载均衡

设备概览

node	name	desc
192.168.10.212	cent7	pg 服务器A，以及pgpool
192.168.10.233	rh	pg 服务器B，以及pgpool

3.1 pgpool2 安装

可以选择源码编译，或者rpm安装，这里选择的是源码编译

注意关闭selinx

下载源码，解压

mkdir -p /data/source && /data/source
wget http://www.pgpool.net/download.php?f=pgpool-II-4.1.0.tar.gz
tar xsvf d*
rm -f d*

安装编译所需依赖包

yum -y install libmemcached-devel
yum install postgresql-devel

// 这两个可以不安装
yum -y install flex
yum -y install bison

配置pg11 环境变量

vim /etc/profile
export PGHOME=/usr/pgsql-11
export PGDATA=/var/lib/pgsql/11/data
// 保存
source /etc/profile

设置 A B 服务器为postgres账号免密登录

su - postgres
ssh-keygen -t rsa
scp id_rsa.pub postgres@192.168.10.233:/var/lib/pgsql/.ssh/demo
cat demo >> ~/.ssh/authorized_keys
chmod 700 ~/.ssh/authorized_keys

进行编译,把pgpool 生成到 /home/pgpool 目录

cd pgpool2-4_1_0
./configure --prefix=/home/pgpool --with-pgsql=$PGDATA
make && make install
chown -R postgres.postgres /home/pgpool
su - postgres
cd /data/source/pgpool2-4_1_0/src/sql/
make && make install
cd pgpool-recovery
make && make install

修改postgresql.conf 文件

1	pgpool.pg_ctl = '/usr/pgsql-11/bin/pg_ctl'

切换为root用户，赋权

chmod +s /bin/ping
chmod +s /sbin/ifup
chmod +s /sbin/ip
chmod +s /sbin/ifconfig
chmod +s /sbin/arping
chmod u+s /usr/sbin
chmod u+s /sbin/ifconfig

insert_lock，建议开启

1
2
3

su postgres
cd pgpool-II-x.x.x/src/sql
psql -f insert_lock.sql template1

然后把template1 写入 pgpool.conf 配置文件

1 2	insert_lock = on lobj_lock_table = 'template1'

3.2 pgpool 配置

这一步的配置都是在 postgres 用户完成的

#3.1 的步骤是AB服务器通用的
/home/pgpool 里面是pgpool的目录，./etc 内是配置文件

复制模板为配置文件

cd /home/pgpool
cp etc/pgpool.conf.sample-stream etc/pgpool.conf
cp etc/pcp.conf.sample etc/pcp.conf
cp etc/hba.conf.sample etc/hba.conf

其他配置

bin/pg_md5 postgres 把生成的MD5写入pcp.conf:
postgres:e8a48653851e28c69d0506508fb27f2d

etc/hba.conf 与 pg_hba.conf 保持一致

主要配置 pgpool.conf,下面是需要改的，其他的不变

listen_addresses = '*'
# a机器pg的配置
backend_hostname0 = '192.168.10.212'
backend_port0 = 15432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/11/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

# b 机器 pg的配置
backend_hostname1 = '192.168.10.233'
backend_port1 = 15432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/11/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

# 这个目录需要root赋予完全的权限
pid_file_name='/var/run/pgpool/pgpool.pid'

master_slave_sub_mode = 'stream'
sr_check_period=5
sr_check_user = 'postgres'
sr_check_password = 'postgres'

health_check_user = 'postgres'
health_check_password = 'postgres'
health_check_database = 'postgres'

# 这个sh没有，需要自己加在指定路径，并赋权
failover_command = '/home/pgpool/failover_stream.sh %H '

recovery_user = 'postgres'
recovery_password = 'postgres'

use_watchdog = on
# 写本机的配置
wd_hostname = '192.168.10.233'     
# 虚拟ip，需要为一个网段且未被占用
delegate_IP = '192.168.10.100'

# 这里的enp0s20f0u3 是本机的网卡名称
if_cmd_path = '/usr/sbin'
if_up_cmd = 'ip addr add $_IP_$/24 dev enp0s20f0u3 label enp0s20f0u3:0'
if_down_cmd = 'ip addr del $_IP_$/24 dev enp0s20f0u3'

arping_path = '/usr/sbin'
arping_cmd = 'arping -U $_IP_$ -w 1 -I enp0s20f0u3'

# 失败配置，这套配置是不论pgpool节点数量，一旦主节点损坏，立即进行节点转移

failover_when_quorum_exists = off
failover_require_consensus = on
allow_multiple_failover_requests_from_node = off
enable_consensus_with_half_votes = on

# 写对方的配置 device0 是对方的具体的网卡名称
heartbeat_destination0 = '192.168.10.212'
heartbeat_destination_port0 = 9694
heartbeat_device0 = 'enp2s0'

wd_lifecheck_user = 'postgres'
wd_lifecheck_password = 'postgres'

# 写对方的配置
other_pgpool_hostname0 = '192.168.10.212'
other_pgpool_port0 = 9999
other_wd_port0 = 9000

failover_stream.sh

#! /bin/sh
# Failover command for streaming replication.
# Arguments: $1: new master hostname.
new_master=$1
trigger_command="$PGHOME/bin/pg_ctl promote -D $PGDATA"

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

exit 0;

3.3 pgpool的操作

启动

1	bin/pgpool -n -d > /home/pgpool/run.log 2>&1 &

停止

1	bin/pgpool -m fast stop

当节点的pg宕机后，恢复命令

1	bin/pcp_attach_node -p 9898 -h 192.168.10.100 -U postgres -n 0

sql 命令：连接pgopool 的服务（port=9999）执行，可以查看目前的节点状态

1	show pool_nodes;

这套配置可以实现主从的互相切换，当某一个节点出现宕机后，需要进行手动同步节点状态，pgpool才会重新进行负载

3.4 数据恢复

postgresql.conf 需要开启几个配置

1 2	full_page_writes = on wal_log_hints = on

如果宕机的节点为主节点时，需要首先进行数据同步

su postgresql
$PGHOME/bin/pg_rewind  --target-pgdata=$PGDATA -P --source-server='host=192.168.10.100 port=9999 user=postgres dbname=postgres password=postgres'

mv recovery.done recovery.conf
$PGHOME/bin/pg_ctl start -D $PGDATA

bin/pcp_attach_node -p 9898 -h 192.168.10.100 -U postgres -n #{具体的nodeid}

这套配置仅是pg的主从stream模式的高可用配置