pgpool-II读写分离

原创

xcagy 2023-01-10 06:41:22 博主文章分类：PGSQL ©著作权

文章标签 postgresql 数据库 sql 编辑文件 文章分类 运维

©著作权归作者所有：来自51CTO博客作者xcagy的原创作品，请联系作者获取转载授权，否则将追究法律责任

IP 角色端口 192.168.0.31 master 5432 pgpool-II 9999 192.168.0.32 slave 5432

一、基础环境配置

host设置修改名称，如果在下面的配置中无法使用主机名称，则使用IP地址

hostnamectl set-hostname master
hostnamectl set-hostname slave
192.168.0.31 master
192.168.0.32 slave

安装

PostgreSQL

# 添加源
rpm -Uvh https://yum.postgresql.org/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
# 下载
yum install postgresql10-server postgresql10
# 安装
/usr/pgsql-10/bin/postgresql-10-setup initdb

# 启动
systemctl enable postgresql-10.service
systemctl start postgresql-10.service

# 验证
su - postgres -c "psql"
# 出现以下信息则成功
psql (10.0)
Type "help" for help.
postgres=#

# 创建密码
postgres=# ALTER USER postgres WITH PASSWORD 'postgres';

# 查看路径（/var/lib/pgsql/10/data）
postgres=# show data_directory;

# 编辑文件 (vi /var/lib/pgsql/10/data/pg_hba.conf)
# host all all 127.0.0.1/32 ident 修改为允许所有网络登录，并使用md5方式进行认证：
# host all all 0.0.0.0/0 md5

# 编辑文件 (vi /var/lib/pgsql/10/data/postgresql.conf)
listen_addresses = '*' # 表示开放外网访问

# 打开防火墙，或者禁用防火墙
sudo firewall-cmd --add-service=postgresql --permanent
sudo firewall-cmd --reload
# 重启
systemctl restart postgresql-10.service

pgpool-II

# 安装
yum install https://www.pgpool.net/yum/rpms/4.2/redhat/rhel-7-x86_64/pgpool-II-pg10-4.2.4-1pgdg.rhel7.x86_64.rpm
yum install https://www.pgpool.net/yum/rpms/4.2/redhat/rhel-7-x86_64/pgpool-II-pg10-debuginfo-4.2.4-1pgdg.rhel7.x86_64.rpm
# 可选
yum install pgpool-II-https://www.pgpool.net/yum/rpms/4.2/redhat/rhel-7-x86_64/pgpool-II-pg10-devel-4.2.4-1pgdg.rhel7.x86_64.rpm
yum install pgpool-II-https://www.pgpool.net/yum/rpms/4.2/redhat/rhel-7-x86_64/pgpool-II-pg10-extensions-4.2.4-1pgdg.rhel7.x86_64.rpm

#启动
systemctl enable pgpool.service
systemctl start pgpool.service

二、流复制

Master

# 创建用于复制的用户
su - postgres
psql
postgres=# CREATE ROLE pgrepuser REPLICATION LOGIN PASSWORD 'pgreppass';
# 编辑文件 (vi /var/lib/pgsql/10/data/pg_hba.conf)
# host replication pgrepuser 0.0.0.0/0 md5

# 编辑文件 (vi /var/lib/pgsql/10/data/postgresql.conf),修改配置(根据实际情况填写)
wal_level = hot_standby
archive_mode = on
max_wal_sender = 4
wal_keep_segments = 10

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

# 停止服务
systemctl stop postgresql-10.service

su - postgres
# 使用 pg_basebackup 生成备库
#1. 清空 $PGDATA 目录
rm -rf /var/lib/pgsql/10/data

# pg_basebackup 命令生成备库
pg_basebackup -D /var/lib/pgsql/10/data -Fp -Xs -v -P -h master -U pgrepuser

# 编辑文件 (vi /var/lib/pgsql/10/data/postgresql.conf)
hot_standby = on

# 新建文件 (vi /var/lib/pgsql/10/data/recovery.conf)
#注意PGSQL-12的版本似乎不支持这个文件，直接改到postgresql.conf
standby_mode = 'on'
primary_conninfo = 'host=master port=5432 user=pgrepuser password=pgreppass'
trigger_file = 'failover.now'
recovery_target_timeline = 'latest'

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

#验证：在master新增数据slave节点可以看到数据。

三、读写分离

pgpool配置

cd /etc/pgpool-II
cp -pv pgpool.conf.sample-stream pgpool.conf

# 修改 vi pgpool.conf
listen_addresses = '*'# 外网访问
# 0为主库
backend_hostname0 = 'master
backend_port0 = 5432
backend_weight0 = 0 # 分配比例
backend_data_directory0 = '/var/lib/pgsql/10/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = 'slave'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/10/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
#hba认证
enable_pool_hba = on
# 执行log
log_statement = on
log_per_node_statement = on
# 流复制
sr_check_user = 'replicator' # 流复制账号
sr_check_password = '123456'  # 流复制密码
# 函数默认分发到从节点，过滤如下
black_function_list = 'currval,lastval,nextval,setval,funcw_.*'

# 修改 vi pool_hba.conf
host    all         all         0.0.0.0/0          md5

# 修改 vi pcp.conf
pcp:e10adc3949ba59abbe56e057f20f883e # 密码为123456

# 生成pool_passwd
pg_md5  123456

# 与 postgresql 用户密码一致
pg_md5 -m -u postgres postgres

# 启动pgpool
# systemctl restart pgpool.service 
pgpool -n -d > /etc/pgpool-II/pgpool.log 2>&1 &

# 连接
su - postgres
psql postgres -h master -p 9999 -U postgres

# 节点信息
postgres=# show pool_nodes;
node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay 
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------
0       | master   | 5432 | up     | 0.000000  | primary | 28         | false             | 0
1       | slave    | 5432 | up     | 1.000000  | standby | 6          | true              | 0

# 查看日志
tail -f /etc/pgpool-II/pgpool.log 

# 下面是测试情况：
# select 1;
2017-10-30 06:38:25: pid 3637: LOG:  DB node id: 1 backend pid: 3658 statement: select * from test where id = 1;

# update test set name = 'test' where id = 2;
DB node id: 0 backend pid: 8032 statement: update test set name = 'test' where id = 2;
#/*REPLICATION*/select 1; # 强制master节点执行
DB node id: 0 backend pid: 8032 statement:  /*REPLICATION*/select 1;
# DB node id,0表示主节点执行，1表示从节点

四、错误解决

端口占用

2017-10-30 01:50:21: pid 3790: FATAL:  failed to bind a socket: "/tmp/.s.PGSQL.9998"
2017-10-30 01:50:21: pid 3790: DETAIL:  bind socket failed with error: "Address already in use"

# 非正常结束导致的，删除以下目录即可
rm -f /tmp/.s.PGSQL.9999
rm -f /tmp/.s.PGSQL.9898

五、后续优化

宕机主从切换

# 修改 vi pgpool.conf
follow_master_command = '/etc/pgpool-II/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;

pgpool集群

配置虚拟ip(delegate_IP)，使用WATCHDOG监控，服务A宕机时，服务B自动接管虚拟IP对外提供服务。