基于Pgpool-II的PostgreSQL集群

安装Pgpool-II

Pgpool-II 是一个位于 PostgreSQL服务器PostgreSQL数据库客户端之间的中间件,Pgpool-II提供了连接池(Connection Pooling)、复制(Replication)、负载均衡(Load Balancing)、缓存(In Memory Query Cache)、看门狗(Watchdog)、超出限制链接(Limiting Exceeding Connections)等功能,可以基于这些特性来搭建PostgreSQL高可用集群。

yum安装

官方参考地址
Yum Repository
Installation from RPM

  1. 安装CentOS7的yum源。
1
$ yum install http://www.pgpool.net/yum/rpms/4.0/redhat/rhel-7-x86_64/pgpool-II-release-4.0-1.noarch.rpm
  1. 安装支持PostgreSQL10版本的pgpool-II
1
2
3
4
$ yum install pgpool-II-pg10
$ yum install pgpool-II-pg10-debuginfo
$ yum install pgpool-II-pg10-devel
$ yum install pgpool-II-pg10-extensions
  1. 服务开机启动
1
$ systemctl enable pgpool.service
  1. 启动/停止服务
1
2
$ systemctl start pgpool.service 
$ systemctl stop pgpool.service

配置Pgpool-II

参考文章

pgpool-II的安装及使用

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

实例准备
role ip PostgreSQL Pgpool-II port
master 192.168.1.3 10.9 4.0.5 5432
slave 192.168.1.2 10.9 4.0.5 5432
vip 192.168.1.100

watchdog端口9000pcp端口9898psql服务连接端口9999

架构图
60D1BAA7.png

配置系统环境

配置程序运行的用户和组为 postgres

1
2
3
4
5
6
7
8
9
10
# 创建或修改目录权限
$ chown -R postgres.postgres /etc/pgpool-II
$ mkdir /var/run/pgpool/
$ chown postgres.postgres /var/run/pgpool/

# 修改pgpool运行用户和组为postgres
$ vi /usr/lib/systemd/system/pgpool.service

User=postgres
Group=postgres
配置认证方式

配置pool_hba.conf,要么都是trust,要么都是md5验证方式,这里采用了md5验证方式如下设置

1
2
3
4
5
6
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
host all all 0.0.0.0/0 md5

配置pg_hba.conf,认证方式保持一致

1
2
3
4
5
6
7
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 0.0.0.0/0 md5
# IPv6 local connections:
host all all ::1/128 md5

非必要可选步骤,为了集群可扩展性,可以将复制的认证条件放宽

1
2
3
4
5
6
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all md5
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5
host replication all 192.168.1.0/24 md5
配置pcp

pcp.conf 配置用于pgpool自己登陆管理使用的,一些操作pgpool的工具会要求提供密码等,配置如下

配置用户名密码
1
2
3
4
5
6
$ pg_md5 postgres
e8a48653851e28c69d0506508fb27fc5

$ vi pcp.conf
# USERID:MD5PASSWD
postgres:e8a48653851e28c69d0506508fb27fc5
添加pg数据库用户密码

在pgpool中添加pg数据库的用户名和密码,数据库登录用户是postgres,这里输入登录密码

1
2
3
4
5
6
$ pg_md5 -p -m -u postgres pool_passwd
password:

# 输入密码后,会生成一个pool_passwd文件
$ cat pool_passwd
postgres:md53175bce1d3201d16594cebf9d7eb3f9d
配置系统命令权限
1
2
3
4
5
$ chmod +s /sbin/ifconfig
$ chmod +s /sbin/ip
$ chmod +s /sbin/ifup
$ chmod +s /bin/ping
$ chmod +s /sbin/arping
配置 pgpool.conf
primary
1
$ cp pgpool.conf.sample-stream pgpool.conf

编辑内容如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
# - pgpool Connection Settings -
listen_addresses = '*'
port = 9999

# - pgpool Communication Manager Connection Settings -
pcp_listen_addresses = '*'
pcp_port = 9898

# - Backend Connection Settings -
backend_hostname0 = '192.168.1.3'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/10/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = '192.168.1.2'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/10/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

# - Authentication -
enable_pool_hba = on
pool_passwd = 'pool_passwd'
authentication_timeout = 60

# FILE LOCATIONS
pid_file_name = '/var/run/pgpool/pgpool.pid'

# CONNECTION POOLING
connection_cache = on

# REPLICATION MODE
replication_mode = off

# LOAD BALANCING MODE
load_balance_mode = on

# MASTER/SLAVE MODE
master_slave_mode = on
master_slave_sub_mode = 'stream'

# - Streaming -
sr_check_period = 5
sr_check_user = 'repuser'
sr_check_password = 'repuser'
sr_check_database = 'postgres'

# HEALTH CHECK
health_check_period = 10
health_check_timeout = 10
health_check_user = 'postgres'
health_check_password = 'postgres'
health_check_database = 'postgres'

# FAILOVER AND FAILBACK
failover_command = '/var/lib/pgsql/10/failover_stream.sh %H'

# WATCHDOG
use_watchdog = on
wd_hostname = '192.168.1.3'
wd_port = 9000
delegate_IP = '192.168.1.100'
if_cmd_path = '/sbin'
if_up_cmd = 'ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0'
if_down_cmd = 'ifconfig eth0:0 down'

# -- heartbeat mode --
heartbeat_destination0 = '192.168.1.2'
heartbeat_device0 = 'eth0'

# - Other pgpool Connection Settings -
other_pgpool_hostname0 = '192.168.1.2'
other_pgpool_port0 = 9999
other_wd_port0 = 9000
standby

编辑内容如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
# - pgpool Connection Settings -
listen_addresses = '*'
port = 9999

# - pgpool Communication Manager Connection Settings -
pcp_listen_addresses = '*'
pcp_port = 9898

# - Backend Connection Settings -
backend_hostname0 = '192.168.1.3'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/10/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = '192.168.1.2'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/10/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

# - Authentication -
enable_pool_hba = on
pool_passwd = 'pool_passwd'
authentication_timeout = 60

# FILE LOCATIONS
pid_file_name = '/var/run/pgpool/pgpool.pid'

# CONNECTION POOLING
connection_cache = on

# REPLICATION MODE
replication_mode = off

# LOAD BALANCING MODE
load_balance_mode = on

# MASTER/SLAVE MODE
master_slave_mode = on
master_slave_sub_mode = 'stream'

# - Streaming -
sr_check_period = 5
sr_check_user = 'repuser'
sr_check_password = 'repuser'
sr_check_database = 'postgres'

# HEALTH CHECK
health_check_period = 10
health_check_timeout = 10
health_check_user = 'postgres'
health_check_password = 'postgres'
health_check_database = 'postgres'

# FAILOVER AND FAILBACK
failover_command = '/var/lib/pgsql/10/failover_stream.sh %H'

# WATCHDOG
use_watchdog = on
wd_hostname = '192.168.1.2'
wd_port = 9000
delegate_IP = '192.168.1.100'
if_cmd_path = '/sbin'
if_up_cmd = 'ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0'
if_down_cmd = 'ifconfig eth0:0 down'

# -- heartbeat mode --
heartbeat_destination0 = '192.168.1.3'
heartbeat_device0 = 'eth0'

# - Other pgpool Connection Settings -
other_pgpool_hostname0 = '192.168.1.3'
other_pgpool_port0 = 9999
other_wd_port0 = 9000
failover_stream.sh

配置failover_stream.sh脚本,内容如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
$ pwd
/var/lib/pgsql/10
$ touch failover_stream.sh
$ chmod u+x failover_stream.sh
$ cat failover_stream.sh
#! /bin/sh
# Failover command for streaming replication.
# Arguments: $1: new master hostname.

new_master=$1
trigger_command="/usr/bin/pg_ctl promote -D /var/lib/pgsql/10/data"

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

exit 0;
设置主机互信

配置ssh秘钥,分别在master、slave上生成ssh密钥对,并设置主机互信。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$ ssh-keygen -t rsa -b 1024
$ cd ~/.ssh
$ pwd
/var/lib/pgsql/.ssh
$ touch authorized_keys
$ chmod 600 authorized_keys

# 将master公钥id_rsa.pub内容拷贝到salve认证文件authorized_keys,同理将slave公钥id_rsa.pub内容拷贝到master认证文件authorized_keys

# 测试主机互信
# salve
$ ssh postgres@192.168.1.3
# master
$ ssh postgres@192.168.1.2

如果这个脚本的执行目标是本地,并且ssh本地登陆没有设置免秘钥,那么这个脚本会一直卡在输入密码的阶段,这时候主备自动切换过程就阻塞了。如果 pgpoolpostgresql在同一台机器部署,需要添加本地登陆免秘钥。

1
2
3
4
5
6
$ pwd
/var/lib/pgsql
$ cd .ssh/
$ ls
authorized_keys id_rsa id_rsa.pub
$ cat id_rsa.pub >> authorized_keys
启动集群

分别启动masterslavePostgreSQL服务

1
$ systemctl start postgresql-10.service

分别启动各节点Pgpool-II服务

1
$ systemctl start pgpool.service
集群状态

vip登录集群,查看状态

1
2
3
4
5
6
7
8
9
10
11
$ psql -p 9999 -h 192.168.1.100 -U postgres
Password for user postgres:
psql (10.9)
Type "help" for help.

postgres=# show pool_nodes;
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | last_status_change
---------+---------------+------+--------+-----------+---------+------------+-------------------+-------------------+---------------------
0 | 192.168.1.3 | 5432 | up | 0.500000 | primary | 5 | true | 0 | 2019-08-08 16:11:26
1 | 192.168.1.2 | 5432 | up | 0.500000 | standby | 3 | false | 0 | 2019-08-08 16:13:14
(2 rows)

至此,基于 Pgpool-II 中间件的 PostgreSQL 集群搭建完成。

PCP命令
  • 获取节点数

    1
    2
    3
    4
    5
    6
    7
    8
    # 获取192.168.1.3可见节点数
    $ pcp_node_count -h 192.168.1.3 -p 9898 -U postgres
    Password:
    2
    # 获取192.168.1.2可见节点数
    $ pcp_node_count -h 192.168.1.2 -p 9898 -U postgres
    Password:
    2
  • 获取节点信息

    1
    2
    3
    4
    5
    6
    7
    8
    9
    # 查看节点0信息
    $ pcp_node_info -h 192.168.1.3 -p 9898 -U postgres 0
    Password:
    192.168.1.3 5432 2 0.500000 up primary 0 2019-08-08 16:11:26
    # 查看节点1信息
    $ pcp_node_info -h 192.168.1.3 -p 9898 -U postgres 1
    Password:
    192.168.1.2 5432 2 0.500000 up standby 0 2019-08-08 16:13:14
    $
  • pgpool-II中脱离一个节点
    该命令将节点slavepgpool-II中脱离。一般如果需要维护某个数据库节点、或不希望pgpool-II将连接分发到该节点时,需要将该节点从pgpool-II中用该命令脱离。

    1
    $ pcp_detach_node -h 192.168.1.100 -p 9898 -U postgres -n 1
  • pgpool-II关联一个节点
    该命令将节点slave关联到pgpool-II中。当维护结束,或新添加一个节点后,可以将节点添加到pgpool-II
    另外,如果该节点由于主机或数据库故障导致检测到数据库为启动时,即使后期服务器重新修复、数据库手工启动,也需要执行attach操作。同时需要注意从两个节点上观察是否节点都已经attach

    1
    $ pcp_attach_node -h 192.168.1.100 -p 9898 -U postgres -n 1
----------------本文结束 感谢阅读----------------