postgresql基于pgpool-Ⅱ高可用方案

 基于pgpool-Ⅱ高可用方案

pgpool-Ⅱ是一款介于数据库服务器和客户端之间的中间件。

7.9.1 pgpool-Ⅱ特性

• 连接池：pgpool 提供连接池功能，降低建立连接带来的开销，同时增加系统的吞吐量
• 负载均衡：当数据库运行在复制模式或主备模式下，SELECT语句运行在集群中任何一个节点都能返回一致的结果，pgpool能将查询语句分发到集群的各个数据库中，从而提升系统的吞吐量，负载均衡适用于只读场景。
• 高可用：当集群中的主库不可用时，pgpool 能够探测到并且激活备库，实现故障转移
• 复制：pgpool 可以管理多个 PostgreSQL 数据库，这是 pgpool 内置的复制特性，也可以使用外部复制方式，例如 PostgreSQL 的流复制等
• 限制超过限度的连接：PostgreSQL支持限制当前最大的连接数，拒绝新连接。pgpool-Ⅱ也支持限制最大连接数，它将超过限制的连接放入队列而不是立即返回一个错误。
• 并行查询：使用并行查询时，数据可以分割到多台服务器上同时执行，以减少总体的执行时间。但并行查询功能不完善。通常使用Postgres-XC|PL/Proxy方案实现PG数据水平拆分

7.9.2 架构

​ pgpool-Ⅱ完全实现PostgreSQL的连接协议，客户端连接到pgpool-Ⅱ上，就于连接到数据库上完全一样。pgpool-Ⅱ的复制是同步的，即如果客户端发送一个DML语句，将并发地在后端所有数据库上执行，保证所有数据库的一致性。

pgpool-Ⅱ是一个多进程的架构

• pcp进程：pcp是一个管理工具，向pgpool-Ⅱ发送管理命令
• pgpool-Ⅱ父进程：负责检查各个底层数据库的健康状态
• pgpool-Ⅱ子进程：负责接收用户发送过来的SQL请求，然后根据规则发送到底层数据库上
• worker进程：负责检查底层数据库之间的复制延迟

7.9.3 pgpool-Ⅱ 的运行模式

pgpool-Ⅱ有连接池、复制、负载均衡等功能，使用这些功能需要配置在不同的工作模式下。

• 原始模式：只实现故障切换功能，当配置多个后端数据库情形，第一个后端数据库故障时切换到第二个后端数据库，依次类推。这种模式pgpool不负责后端数据库数据同步，数据库的数据同步由用户负责，对应配置文件为$prefix/etc/pgpool.conf.sample，这种模式不支持负载均衡。
• 连接池模式：实现连接池的功能和原始模式的故障切换功能。
• 内置复制模式：这种模式下pgpool负责后端数据库数据同步，pgpool节点上的写操作需等待所有后端数据库将数据写入后才向客户端返回成功，是强同步复制方式，配置文件为$prefix/etc/pgpoo1.conf.sampie-repIication，这种模式实现负载均衡的功能。
• 主备模式：使用第三方工具(如：Slony，流复制)完成pgpool的后端数据库的数据同步复制，中间件层使用pgpool-Ⅱ，pgpool提供高可用和连接池的功能。配置文件为$prefix/etc/pgpool.conf.sample-master-slave，这种模式支持负载均衡。
  • 配合流复制的主备模式：使用PostgreSQL流复制方式，PostgreSQL流复制负责pgpool后端数据库数据同步，对应的配置文件为$prefix/etc/pgpool.conf.sample-stream，这种模式支持负载均衡。pgpool+pg复制实现高可用解决方案
  • 配合Slony的主备模式：
• 并行模式：实现查询的并行执行。并行模式不能与主备模式同时使用。

复制模式与主备模式对比优缺点

复制模式优点

• 数据是同步的，数据强一致性
• 自动Failover
• 读可以负载均衡
• 可以在线恢复，不需要停止pgpool就可以在线修复或增加后端数据库节点
• 容易配置

复制模式缺点

• 写性能不会很好，有30%的写性能下降
• 不支持部分查询。一些随机函数、序列号，直接在不同的后端数据库上执行SQL将产生不同的结果集，在复制模式下，不能使用此类函数和序列号

主备模式优点

• 写性能较好，只有10%~20%的性能下降
• 自动Failover
• 可以做读的负载均衡

主备模式的缺点

• 复制是异步
• 配置复杂

pgpool-Ⅱ3.0之后，支持配合使用流复制+Standby的主备模式，它实现读写分离。这种方式的好处：

• 智能的读写查询分发：pgpool可以区分只读查询和更新查询，对于只读查询，会将请求负载均衡到各个节点上，写查询只发送到主库上
• 智能的负载均衡：pgpool能检测备库和主库的延迟。如果备库延迟超过设定值，读查询将之发送到主库上
• 增加Standby Server时不需要停止pgpool-Ⅱ

7.9.4 安装部署pgpool

rpm包安装方式

# yum 包安装(https://www.pgpool.net/mediawiki/index.php/Yum_Repository)
yum -y install http://www.pgpool.net/yum/rpms/4.1/redhat/rhel-7-x86_64/pgpool-Ⅱ-release-4.1-1.noarch.rpm

# 安装适配指定pg版本pgpool软件
yum -y install pgpool-Ⅱ-pg11

# 移除pgpool
yum -y erase pgpool-Ⅱ-pg11

源码包编译安装

# https://www.pgpool.net/mediawiki/index.php/Downloads
wget https://www.pgpool.net/mediawiki/images/pgpool-Ⅱ-3.6.20.tar.gz 
su - root
mkdir -p /ups/app/postgresql/pgpool
chown postgres:postgres /ups/app/postgresql/pgpool

su - postgres
tar -xf pgpool-Ⅱ-4.1.0.tar.gz
cd pgpool-Ⅱ-4.1.0

./configure --prefix=/ups/app/postgresql/pgpool --with-pgsql=/ups/app/postgresql/pgsql-11
make && make install

# 

配置自启动服务

vi /usr/lib/systemd/system/pgpool.service

[Unit]
Description=pgpool-Ⅱ
After=syslog.target network.target

[Service]

User=postgres
Group=postgres

EnvironmentFile=-/etc/sysconfig/pgpool

ExecStart=/ups/app/postgresql/pgpool/bin/pgpool -f /ups/app/postgresql/pgpool/etc/pgpool.conf $OPTS
ExecStop=/ups/app/postgresql/pgpool/bin/pgpool -f /ups/app/postgresql/pgpool/etc/pgpool.conf $STOP_OPTS stop
ExecReload=/ups/app/pgpool/bin/pgpool -f /ups/app/postgresql/pgpool/etc/pgpool.conf reload

[Install]
WantedBy=multi-user.target

编辑/etc/sysconfig/pgpool文件

cat >> /etc/sysconfig/pgpool <<EOF
# Options for pgpool

# -n: don't run in daemon mode. does not detatch control tty
# -d: debug mode. lots of debug information will be printed

#OPTS=" -d -n"
OPTS=" -n"

STOP_OPTS=" -m fast"
EOF

安装扩展函数

# 安装pgpool_adm函数
cd /ups/soft/pgpool-Ⅱ-4.1.0/src/sql/pgpool_adm/
make && make install
# 安装C语言函数-pgpool_recovery
cd /ups/soft/pgpool-Ⅱ-4.1.0/src/sql/pgpool-recovery
make && make install
psql -f /ups/soft/pgpool-Ⅱ-4.1.0/src/sql/pgpool-recovery/pgpool-recovery.sql template1

# pgpool_regclass函数被pgpool-II内部使用，用于解决不同schema中处理同名非临时表
cd /ups/soft/pgpool-Ⅱ-4.1.0/src/sql/pgpool-regclass
make && make install
# 加载到template1 模板库
psql -f /ups/soft/pgpool-Ⅱ-4.1.0/src/sql/pgpool-regclass/pgpool-regclass.sql template1

# 或 使用统一命令安装扩展函数
cd /ups/soft/pgpool-Ⅱ-4.1.0/src/sql
make && make install

# 建立pgpool_catalog.insert_lock表 (master)，用于解决复制中互斥的问题。在复制模式中，pgpool-Ⅱ需要锁定目标表，而表锁与VACUUM冲突，插入操作可能会出现长时间等待
psql -f /ups/soft/pgpool-Ⅱ-4.1.0/src/sql/insert_lock.sql template1

安装扩展插件

-- psql
-- 主节点安装插件
create EXTENSION pgpool_regclass;
CREATE EXTENSION pgpool_recovery;
df

通过PCP接口管理

pgpool 提供一个用于管理 pgpool 的系统层命令行工具。PCP 命令使用的用户属于 pgpool 层面，和 PostgreSQL 数据库中的用户没有关系，但pcp 命令 的用户名和 MD5 加密的密码必须首先在 pcp .conf 文件中定义。

# 配置用户加密密码
pg_md5 pgpool

# 编辑pcp.conf文件
# 格式： USERID:MD5PASSWD
cp /ups/app/postgresql/pgpool/etc/pcp.conf.sample /ups/app/postgresql/pgpool/etc/pcp.conf

vi /ups/app/postgresql/pgpool/etc/pcp.conf
postgres:md53175bce1d3201d16594cebf9d7eb3f9d
sync:md52803003449203cdbe05c56deb21cfa86
pgpool:ba777e4c2f15c11ea8ac3be7e0440aa0

使用

# pcp_node_info 命令查看节点信息
pcp_node_info --verbose -h 192.168.10.199 -U pgpool 0

7.9.5 简单示例环境搭建

复制和负载均衡环境

服务器信息

配置文件

# 1. 
export PGPOOL_HOME=/ups/app/postgresql/pgpool
cd ${PGPOOL_HOME}
cp ${PGPOOL_HOME}/etc/pgpool.conf.sample ${PGPOOL_HOME}/etc/pgpool.conf
cp etc/pcp.conf.sample etc/pcp.conf

# 2. 配置pcp.conf
# 2.1 计算密码的md5值
${PGPOOL_HOME}/bin/pg_md5 --help
Usage:

  /ups/app/postgresql/pgpool/bin/pg_md5 [OPTIONS]
  /ups/app/postgresql/pgpool/bin/pg_md5 <PASSWORD>

  --prompt, -p         Prompt password using standard input.
  --md5auth, -m        Produce md5 authentication password.
  --username, -u USER  When producing a md5 authentication password,
                       create the pool_passwd entry for USER.
  --config-file, -f CONFIG-FILE  Specify pgpool.conf.
  --help, -h           This help menu.

vi etc/pcp.conf
postgres:md53175bce1d3201d16594cebf9d7eb3f9d
sync:md52803003449203cdbe05c56deb21cfa86
pgpool:ba777e4c2f15c11ea8ac3be7e0440aa0


# 3. 配置pgool.conf
listen_addresses = '*'
port = 9999

replication_mode = on
load_balance_mode = on

backend_hostname0 = '192.168.10.181'
backend_port0 = 1921
backend_weight0 = 1
backend_data_directory0 = '/ups/data/pgdata/11/pg_root'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 's1'
backend_hostname1 = '192.168.10.182'
backend_port1 = 1921
backend_weight1 = 1
backend_data_directory1 = '/ups/data/pgdata/11/pg_root'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 's1'
failover_on_backend_error = on

启动pgpool

${PGPOOL_HOME}/bin/pgpool -f ${PGPOOL_HOME}/etc/pgpool.conf

验证

-- 连接到pgpool操作数据
psql -p 9999 -h 192.168.10.200 -U dev devdb
CREATE TABLE test01(id int, note text);
INSERT INTO test01 VALUES(1,'1');


-- 连接到后端数据库查看
psql -p 1521 -h 192.168.10.181 -U dev devdb
d test01
SELECT id, note FROM test01;

psql -p 1521 -h 192.168.10.182 -U dev devdb
d test01
SELECT id, note FROM test01;

流复制的主备模式

服务器信息

主从数据库间使用异步流复制

配置参数文件

# 1. pgpool.conf
vi etc/pgpool.conf
listen_addresses = '*'
port = 9999

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

backend_hostname0 = '192.168.10.181'
backend_port0 = 1921
backend_weight0 = 1
backend_data_directory0 = '/ups/data/pgdata/11/pg_root'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 's1'
backend_hostname1 = '192.168.10.182'
backend_port1 = 1921
backend_weight1 = 1
backend_data_directory1 = '/ups/data/pgdata/11/pg_root'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 's1'
failover_on_backend_error = on

启动pgpool

${PGPOOL_HOME}/bin/pgpool -f ${PGPOOL_HOME}/etc/pgpool.conf

检查

使用show命令检查pgpool信息

• pool_status: 查看配置信息
• pool_nodes: 查看后端数据库节点信息
• pool_processes: 查看进程信息
• pool_pools: 显示连接池的各个连接信息
• pool_version: 显示版本

-- psql -p 9999 -h 192.168.10.200 -U dev devdb
show pool_status;
show pool_nodes;