目录
一、介绍
pgpool-II是位于LightDB服务器和 LightDB 数据库客户端之间的中间件，也是现在比较成熟的读写分离的中间件，它提供以下功能：
- 连接池
Pgpool-II维护与PostgreSQL 服务器的已建立连接，并在出现具有相同属性（即用户名，数据库，协议版本和其他连接参数，如果有）的新连接时重用它们。它减少了连接开销并改善了系统的整体吞吐量。
- 负载均衡
如果复制了数据库（因为以复制模式或主/从模式运行），则在任何服务器上执行SELECT查询都将返回相同的结果。Pgpool-II 利用复制功能来减少每个PostgreSQL服务器上的负载。它通过在可用服务器之间分配SELECT查询来做到这一点，从而提高了系统的整体吞吐量。在理想情况下，读取性能可以与PostgreSQL服务器的数量成比例地提高。在许多用户同时执行许多只读查询的情况下，负载平衡效果最佳。
- 看门狗
跟keepalived一样，对其他pgpool-II节点执行生命检查，以检测Pgpoll-II的故障。如果活动Pgpool-II发生故障，则备用 Pgpool-II可以提升为活动状态，并接管虚拟IP
二、pgpool-II下载
三、pgpool-II 安装
1.源码安装前提
需要有lightdb库环境
GNU make 版本是3.8及以上
#查看版本命令
make --version2.解压编译安装
tar -zxvf pgpool-II-4.4.0.tar.gz -C /data1/lightdb/wuxj
cd /data1/lightdb/wuxj/pgpool-II-4.4.0
#--prefix 安装路径
#-with-pgsql lightdb LTHOME环境路径
./configure --prefix=/data1/lightdb/wuxj/pgpool -with-pgsql=/data1/lightdb/lightdb-em-13.8-22.4-d53528018-el7.x86_64/lightdb-x-for-em/13.8-22.4
make
make install3.添加环境变量
vim ~/.bashrc
#添加如下信息
export PGPOOL_HOME=/data1/lightdb/wuxj/pgpool
export PATH=${PGPOOL_HOME}/bin:${PATH}
#使之生效
source ~/.bashrc4.配置pool_hba.conf
#进入配置目录
cd /data1/lightdb/wuxj/pgpool/etc/
#复制
cp pool_hba.conf.sample pool_hba.conf
增加如下内容
host all all 0.0.0.0/0 md5
5.配置pcp.conf
cp pcp.conf.sample pcp.conf
获取lightdb用户MD5密码
加入lightdb:上面MD5输出
# USERID:MD5PASSWD
lightdb:7550896f8721834867162e708adfe6a66.配置pool_passwd
(master)主节点登陆后执行：select rolname,rolpassword from pg_authid;
[lightdb@lightdb-1 etc]$ ltsql -h10.19.70.49 -p30001 -dpostgres
Password for user lightdb:
ltsql (13.8, server 13.3)
Type "help" for help.
lightdb@postgres=# select rolname,rolpassword from pg_authid;
rolname | rolpassword
---------------------------+-------------------------------------
pg_monitor |
pg_read_all_settings |
pg_read_all_stats |
pg_stat_scan_tables |
pg_read_server_files |
pg_write_server_files |
pg_execute_server_program |
pg_signal_backend |
lightdb | md54f3305909c6724814ede585e873b845c
lt_probackup |
ltcluster | md58b0b5b2bbd4c2a5f6ae630c8e0e3a961
em | md5a7addd43aa6c1b5c21b5a7b7744f9a3d
(12 rows)修改pool_passwd增加lightdb用户的结果
形式为rolname:rolpassword例如：
lightdb:md54f3305909c6724814ede585e873b845c
7.配置pgpool.conf
#内容如下：根据自己的配置进行相应的修改。相关目录需要提前创建。如果相关目录没有权限则需要赋权
chown -R lightdb:lightdb /data1/lightdb/wuxj/pgpool
listen_addresses = '*'
port = 9999
socket_dir = '/data1/lightdb/wuxj/pgpool'
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/data1/lightdb/wuxj/pgpool'
backend_hostname0 = '10.19.70.49' #主库
backend_port0 = 30001
backend_weight0 = 1
backend_data_directory0 = '/home/lightdb/lightdb-22.2-5950/lightdb-x/13.3-22.2/data/defaultCluster' #数据库data位置
backend_flag0 = 'ALLOW_TO_FAILOVER'
#
backend_hostname1 = '10.19.70.51' #从库
backend_port1 = 30001
backend_weight1 = 1
backend_data_directory1 = '/home/lightdb/lightdb-22.2-5950/lightdb-x/13.3-22.2/data/defaultCluster' #数据库data位置
backend_flag1 = 'ALLOW_TO_FAILOVER'
enable_pool_hba = on
pid_file_name = '/data1/lightdb/wuxj/pgpool/pgpool.pid' ##pid文件位置
logdir = '/data1/lightdb/wuxj/pgpool/logs' ##日志位置
replication_mode = off
#开启读写分离
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_period = 5
sr_check_user = 'lightdb' ##主库创建的用户
sr_check_password = 'lightdb123' ##密码
sr_check_database = 'postgres'
#
# # HEALTH CHECK 健康检查
#
health_check_period = 10
health_check_timeout = 20
health_check_user = 'lightdb' ##主库创建的用户
health_check_password = 'lightdb123' ##密码
health_check_database = 'postgres' ##检查的库8.pgpool的启动和关闭
#启动
pgpool -n -D >> /data1/lightdb/wuxj/pgpool/logs/pgpool.log 2>&1 &
#关闭
pgpool -m fast stop
9.查看集群状态
[lightdb@lightdb-1 etc]$ ltsql -p9999 -dpostgres
ltsql (13.8, server 13.3)
Type "help" for help.
lightdb@postgres=# 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 | 10.19.70.49 | 30001 | up | up | 0.500000 | primary | primary | 0 | false | 0 | | | 2022-12-22 15:05:22
1 | 10.19.70.51 | 30001 | up | up | 0.500000 | standby | standby | 0 | true | 0 | | | 2022-12-22 15:05:22
(2 rows)
lightdb@postgres=# 四、验证读写分离
#主节点
10.19.70.49
#备节点
10.19.70.51
新建两张测试表
CREATE table new_test_wuxj(
id int8,
name varchar(100)
);
CREATE table new_test_wuxj_2(
id int8,
name varchar(100)
);通过连接pgpool 对表进行插入和查询
try {
Class.forName("org.postgresql.Driver");
Connection connection = DriverManager.getConnection("jdbc:postgresql://10.20.31.205:9999/postgres", "lightdb", "lightdb123");
String sql = "insert into new_test_wuxj(id,name) values(?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,1);
preparedStatement.setString(2,"nihao");
preparedStatement.execute();
preparedStatement = connection.prepareStatement("select * from new_test_wuxj");
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
String name = resultSet.getString("name");
System.out.println(name);
}
resultSet.close();
preparedStatement.close();
connection.close();
} catch (Exception e) {
e.printStackTrace();
}查看主备日志可以看到写操作在主库，读操作在备库。
开启手动事务
try {
Class.forName("org.postgresql.Driver");
Connection connection = DriverManager.getConnection("jdbc:postgresql://10.20.31.205:9999/postgres", "lightdb", "lightdb123");
String sql = "insert into new_test_wuxj_2(id,name) values(?,?)";
connection.setAutoCommit(false);
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,1);
preparedStatement.setString(2,"读写均在主库");
preparedStatement.execute();
preparedStatement = connection.prepareStatement("select * from new_test_wuxj_2");
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
String name = resultSet.getString("name");
System.out.println(name);
}
connection.commit();
resultSet.close();
preparedStatement.close();
connection.close();
} catch (Exception e) {
e.printStackTrace();
}查看主备日志可以看到读写操作在主库。
五、测试（模拟master宕机）
1.关闭master数据库
查看pgpool ，master关闭后，从节点的连接中断后又成功连接上，发现master已经是standby了，且down机了,slave升为了primary
2.恢复原先主节点
[lightdb@iZvv70ftvto0eaujmkhmu3Z 13.3-22.2]$ lt_ctl -D $LTDATA start
waiting for server to start....2022-12-22 16:51:55.584906T,,,,,postmaster,,00000,2022-12-22 16:51:55 CST,0,1592013, LOG: LightDB autoprewarm: prewarm dbnum=0
2022-12-22 16:51:55.602435T,,,,,postmaster,,00000,2022-12-22 16:51:55 CST,0,1592013, LOG: ltaudit extension initialized
......2022-12-22 16:52:01.757518T,,,,,postmaster,,00000,2022-12-22 16:51:55 CST,0,1592013, LOG: redirecting log output to logging collector process
2022-12-22 16:52:01.757518T,,,,,postmaster,,00000,2022-12-22 16:51:55 CST,0,1592013, HINT: Future log output will appear in directory "log".
............. done
server started查看集群状态master成为了新的standby
6505
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
