pg_pool使用
简介
Pgpool-II 是一个位于 PostgreSQL服务器和 PostgreSQL数据库客户端之间的中间件，Pgpool-II提供了连接池（Connection Pooling）、复制（Replication）、负载均衡（Load Balancing）、缓存（In Memory Query Cache）、看门狗（Watchdog）、超出限制链接（Limiting Exceeding Connections）等功能，可以基于这些特性来搭建PostgreSQL高可用集群。
pg_pool安装
首先前往https://www.pgpool.net/mediawiki/index.php/Downloads，下载对应的源码包,并解压。
进入解压的根目录,安装。
1
2
3
4apt install postgresql-server-dev-9.5 # 安装插件前需要安装 pg-dev
./configure --prefix=/opt/pgpool
make
make install–prefix 为安装后的目录前缀，也就是根目录
若出现
configure 错误
checking for PQexecPrepared in -lpq… no
configure: error: libpq is not installed or libpq is old
是由于找不到 pg lib 库,可添加参数-with-pgsql=pgsql lib路径。
安装后如果找不到命令,要么进入 /opt/pgpool/bin 执行。
推荐引入环境变量, /etc/profile1
2export PGPOOL=/opt/pgpool
export PATH=$PATH:$PGPOOL/bin最好将目录所有者也修改
1
chown -R postgres.postgres /opt/pgpool
进入解压后的目录，安装相关函数。
比如:/home/d/pgpool-II-3.7.12/src/sql。
分别进入pgpool_adm，pgpool-recovery，pgpool-regclass文件夹，进行 make & make install。
检查: 进入/usr/share/postgresql/9.5/extension目录，查看是否有相关函数文件。配置
根据安装路径不同，配置文件的路径也不一样，比如例子中配置文件路径在/opt/pgpool/etc。
有时候也可能不再安装路径，而在/usr/local/etc。要区分到底使用的是哪一个路径的配置。
可通过直接执行 pgpool 根据获取安装路径。
复制一份 sample 用于修改。1
2
3cp pcp.conf.sample pcp.conf
cp pgpool.conf.sample pgpool.conf
cp pool_hba.conf.sample pool_hba.conf4.1 pcp.conf
执行 pg_md5 用户的密码,比如1
2postgres@ubuntu:/opt/pgpool/etc$ pg_md5 d
8277e0910d750195b448797616e091ad编辑 pcp.conf, 在末尾输入 USERID:MD5PASSWD。
eg:1
postgres:8277e0910d750195b448797616e091ad
4.2 pool_hba.conf
任何地址连接任何用户的全部使用 md5 验证1
2local all all md5
host all all 0.0.0.0/0 md54.3 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
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141# - pgpool Connection Settings -
listen_addresses = '*'
port = 9999
# - Backend Connection Settings -
backend_hostname0 = 'db1'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/postgresql/9.5/main'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = 'db2'
backend_port1 = 5433
backend_weight1 = 1
backend_data_directory1 = '/var/lib/postgresql/9.5/main'
backend_flag1 = 'ALLOW_TO_FAILOVER'
# - Authentication -
enable_pool_hba = on
pool_passwd = 'pool_passwd'
authentication_timeout = 60
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
pid_file_name = '/opt/pgpool/pgpool.pid'
logdir = '/var/log/pgpool'
# 以上这两个目录均要设置所有者为执行命令的用户,此例子中是 postgres
#------------------------------------------------------------------------------
# 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 = 'postgres'
sr_check_password = 'd'
sr_check_database = 'postgres'
delay_threshold = 0
#------------------------------------------------------------------------------
# HEALTH CHECK GLOBAL PARAMETERS
#------------------------------------------------------------------------------
health_check_period = 10
health_check_timeout = 20
health_check_user = 'postgres'
health_check_password = 'd'
health_check_database = 'postgres'
health_check_max_retries = 0
health_check_retry_delay = 1
connect_timeout = 10000
#------------------------------------------------------------------------------
# FAILOVER AND FAILBACK
#------------------------------------------------------------------------------
failover_command = '/opt/pgpool/failover_stream.sh %H %R'
#------------------------------------------------------------------------------
# WATCHDOG 看门狗
#------------------------------------------------------------------------------
# - Enabling -
use_watchdog = on
# Activates watchdog
# (change requires restart)
# -Connection to up stream servers -
trusted_servers = ''
# trusted server list which are used
# to confirm network connection
# (hostA,hostB,hostC,...)
# (change requires restart)
ping_path = '/bin'
# ping command path
# (change requires restart)
# - Watchdog communication Settings -
wd_hostname = 'db1' # 此看门狗IP，即本机
wd_port = 9000
wd_priority = 1
wd_authkey = ''
wd_ipc_socket_dir = '/tmp'
# - Virtual IP control Setting -
delegate_IP = '192.168.56.122' # 浮动IP
if_cmd_path = '/sbin' # if命令执行的路径
if_up_cmd = 'ip addr add $_IP_$/24 dev ens33 label ens33:0'
if_down_cmd = 'ip addr del $_IP_$/24 dev ens33'
# shutdown delegate IP command
# (change requires restart)
arping_path = '/usr/sbin'
# arping command path
# (change requires restart)
arping_cmd = 'arping -U $_IP_$ -w 1 -I ens33'
# arping command
# (change requires restart)
# 需要给命令 chmod u+s ,以赋予执行时的 ROOT 权限。
# -- heartbeat mode --
wd_heartbeat_port = 9694
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
# 可填写多个终端
heartbeat_destination0 = 'db2' # 终端0 IP
heartbeat_destination_port0 = 9694 # 终端0 端口
heartbeat_device0 = 'ens33'
# -- query mode --
wd_life_point = 3
wd_lifecheck_query = 'SELECT 1'
wd_lifecheck_dbname = 'postgres'
wd_lifecheck_user = 'postgres'
wd_lifecheck_password = 'd'
# - Other pgpool Connection Settings -
# 其他 pool 连接
other_pgpool_hostname0 = 'db2'
other_pgpool_port0 = 5432
other_wd_port0 = 9000配置 pool_passwd
1
pg_md5 -p -m -u postgres pool_passwd
日志目录创建以及所有者更改
1
2root@ubuntu:/var/log# mkdir pgpool
root@ubuntu:/var/log# chown postgres.postgres pgpool创建 故障转移脚本
vim /opt/pgpool/failover_stream.sh1
2
3
4
5
6
7
8
9
10
11
12#! /bin/sh
# Failover command for streaming replication.
# Arguments: $1: new master hostname.
new_master=$1
new_data=$2
trigger_command="$PGHOME/bin/pg_ctl promote -D $2"
# Prompte standby database.
/usr/bin/ssh -T $new_master $trigger_command
exit 0;配置 ssh 密钥
1
2
3ssh-keygen -t rsa
cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
chmod 600 ~/.ssh/authorized_keys最好都手动连接一下,免得要你写 yes。
修改 hosts
vim /etc/hosts
后面添加1
2
3192.168.56.139 db1
192.168.56.138 db2
192.168.56.177 vipip 相关命令赋予权限
1
2
3chmod u+s /sbin/ip
apt install arping
chmod u+s /usr/sbin/arping不同服务器上配置文件大致相同, 主要是 pgpool.conf 修改一下部分:
1
2
3wd_hostname = '' 修改为本机IP
heartbeat_destination0 = '' # 修改为其他 pool 服务器IP
other_pgpool_hostname0 = '' # 修改为其他pool 服务器IP启动
1
pgpool -n -D
启动后，可以看到多了一个IP，也就是浮动IP。也就代表成功了
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15postgres@ubuntu:~/9.5/main$ ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:ca:40:8f brd ff:ff:ff:ff:ff:ff
inet 192.168.56.139/24 brd 192.168.56.255 scope global ens33
valid_lft forever preferred_lft forever
inet 192.168.56.122/24 scope global secondary ens33:0
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:feca:408f/64 scope link
valid_lft forever preferred_lft forever
测试
测试数据库挂掉
启动
主
1 | postgres@ubuntu:~$ pgpool -n -D |
副
1 | postgres@ubuntu:~/9.5/main$ pgpool -D . start |
连接数据库
1 | postgres@ubuntu:~/9.5/main$ psql -U postgres -h vip -p 9999 |
关闭主数据库
1 | postgres@ubuntu:~/9.5/main$ pg_ctl -D . stop |
检查结果
1 | postgres@ubuntu:~/9.5/main$ psql -U postgres -h vip -p 9999 |
以上结果显示，db1（旧主）已经 down, 且 role 由 primary 变成 standby。db2 role 由 standby 变成 primary。也就是切换成功了。
恢复(以下操作均在 旧master 上面执行)
先倒带
1 | pg_rewind --target-pgdata=/var/lib/postgresql/9.5/main --source-server='host=192.168.56.138 port=5432 user=postgres password=d dbname=postgres' -P |
重命名recovery.done => recovery.conf，并配置。值得注意的是 host 要填写新的 master 的IP。
1 | postgres@ubuntu:~/9.5/main$ mv recovery.done recovery.conf |
启动
1 | postgres@ubuntu:~/9.5/main$ pg_ctl -D . start |
附加到pgpool
1 | pcp_attach_node -d -U postgres -h vip -p 9898 -n 0 |
查看结果
1 | postgres@ubuntu:~/9.5/main$ psql -U postgres -h vip -p 9999 |
以上结果显示 db1 已经启动。
在新master 上执行
1 | postgres@ubuntu:~$ psql |
显示新的备份服务器已经关联到新的master服务器。
完美！
测试 pgpool 挂掉
主启动,副启动, 除了pgpool.conf 有些许不同，其他基本一样。
1 | postgres@ubuntu:~$ pgpool -n -D |
停止主 pgpool, 还是不知道为什么停止很久还是无法停止。
我的临时解决方式是以 pgpool -n 方式启动, 然后 ctrl+c 将它停止。
直接 kill 会导致很多问题，有很多其他的任务无法被关闭。只能重启。
1 | pgpool -m fast stop |
master server ip
1 | postgres@ubuntu:~/9.5/main$ ip addr |
副服务器IP
1 | root@ubuntu:/var/log# ip addr |
可以看到服务器 IP 已经浮动到副服务器上。
恢复
恢复很简单，只要重新启动 pgpool 即可。
1 | pgpool -n -D |
如下结果，即代表连接成功，否则状态可能为 SHUTDOWN。
1 | postgres@ubuntu:/opt/pgpool/bin$ pcp_watchdog_info |
测试 pgpool 以及 postgresql 数据库
简单粗暴,直接 shutdown master 服务器。
1 | shutdown now |
检查备用服务器IP
1 | postgres@ubuntu:~/9.5/main$ ip addr |
数据库状态
1 | postgres@ubuntu:~/9.5/main$ psql -U postgres -h vip -p 9999 |
正常工作。
恢复
开机, 其实就是以上两个恢复操作都来一遍。
恢复数据库，主要就是四个步骤:
- 倒带(pg_rewind);
- 编辑 recovery.conf;
- 启动(pg_ctl);
- 附加到 pgpool (pcp_attach_node)。
恢复 pgpool 就更简单了，只有启动。(pgpool)
不再赘述。
检查 pgpool。检查数据库1
2
3
4
5
6postgres@ubuntu:/opt/pgpool/bin$ pcp_watchdog_info
Password:
2 YES db2:9999 Linux ubuntu db2
db2:9999 Linux ubuntu db2 9999 9000 4 MASTER
db1:9999 Linux ubuntu db1 9999 9000 7 STANDBY成功恢复！1
2
3
4
5
6
7
8
9
10
11postgres@ubuntu:/opt/pgpool/bin$ psql -U postgres -h vip -p 9999
Password for user postgres:
psql (9.5.19)
Type "help" for help.
postgres=# show pool_nodes;
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------
0 | db1 | 5432 | up | 0.500000 | standby | 0 | false | 0
1 | db2 | 5432 | up | 0.500000 | primary | 1 | true | 0
(2 rows)
附录:
配置文件介绍
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
76
77
78
79listen_addresses: 监听来自哪里的请求
port: pgpool 的主程序端口
backend_hostname0:后端数据库连接0的hostname
backend_port0:后端数据库0连接的port
backend_weight0:后端数据库0连接的权重
backend_data_directory0:后端数据库0的data 目录
backend_flag0:控制后端的行为,可ALLOW_TO_FAILOVER(允许故障转移)，DISALLOW_TO_FAILOVE(不允许故障转移)，ALWAYS_MASTER（永远为主）
#0可以依次递增
enable_pool_hba: 是否开启pool_hba,他会前往目录寻找pool_hba.conf 文件。类似pg的hba配置，用于限制用户连接以及权限
log_destination：log放在哪,有syslog和stderr。
还有记录什么的参数。
pid_file_name：pid 文件名。喜爱那个对于pgpool.conf的路径或者绝对路径
logdir: 记录pgpool状态的文件。
master_slave_mode： off or on 开启或者关闭主从模式
master_slave_sub_mode: 主从子模式,分别为stream,slony,logical 默认stream
sr_check_period： 流复制检查周期,默认0禁用
sr_check_user: 流复制检查所用用户，即使金庸也要填写。
sr_check_password： 流复制检查用户的密码
sr_check_database： 流复制检查的数据库
delay_threshold：未将查询调度给备用节点之前的阈值，单位为字节。不太理解
健康检查全局参数
health_check_period： 健康检查周期，0禁用
health_check_timeout: 健康检查超时时间。
health_check_user： 健康检查用户
health_check_password： 健康检查密码
health_check_database： 健康检查数据库
health_check_max_retries： 放弃前健康检查最大重试失败次数
health_check_retry_delay： 失败后间隔多少秒重试
connect_timeout: 放弃连接前的超时时间。0表示无超市时间。单位为ms。仅用健康检查，不用于连接backend。
failover_command：故障转义命令。 部分参数如下:
# Executes this command at failover
# Special values:
# %d = node id
# %h = host name
# %p = port number
# %D = database cluster path
# %m = new master node id
# %H = hostname of the new master node
# %M = old master node id
# %P = old primary node id
# %r = new master port number
# %R = new master database cluster path
# %% = '%' character
use_watchdog: 是否开启看门狗
wd_hostname： 此看门狗的hostname
wd_port: 看门口的port
delegate_IP: 虚拟IP
if_cmd_path = '/sbin' if_up/down_cmd的目录
if_up_cmd = 'ip addr add $_IP_$/24 dev eth0 label eth0:0' 启动delegate_IP的命令
if_down_cmd = 'ip addr del $_IP_$/24 dev eth0' 关闭delegate_IP的命令
arping_path = '/usr/sbin' arping 的目录
arping_cmd = 'arping -U $_IP_$ -w 1 -I eth0' arping 的命令
需要 chmod u+s XXX,让它可以被其他角色执行。
心跳模式
wd_heartbeat_port:接收心跳信号的端口
wd_heartbeat_keepalive： 间隔多久发送心跳信号，单位秒
wd_heartbeat_deadtime： 心跳信号停滞时间间隔
heartbeat_destination0: 目标0的 hostname或者ip，用于发送心跳信号
heartbeat_destination_port0: 目标0的端口。
heartbeat_device0： NIC设备名称,比如(eth0)，猜测是网卡名字。用于发送心跳信号，仅在非空时有效。且 pgpool 具有root权限。
查询模式
wd_life_point: 生命检查重试次数
wd_lifecheck_query: 从看门狗到pgpool的生命检查查询
wd_lifecheck_dbname： 生命检查连接的数据库
wd_lifecheck_user: 监听pgpool生命检查用户
wd_lifecheck_password： 生命检查密码
其他pgpool的连接设置
other_pgpool_hostname0: pool0 的 host
other_pgpool_port0: pool0 的 port
other_wd_port0: pool0 的 看门狗 port异常
MD5 authentication is not supported in replication and master-slave modes.
首先需要将 pgpool.conf 设置 enable_pool_hba = on。 然后参要一下表格。
简单的解决方法就是将所有 pool_hba.conf 以及 pg_hba.conf 的登陆认证均设置为MD5。
| pg_hba.conf | pool_hba.conf | pool_passwd | result |
|---|---|---|---|
| md5 | md5 | yes | md5 auth |
| md5 | md5 | no | “MD5” authentication with pgpool failed for user “XX” |
| md5 | trust | yes/no | MD5 authentication is unsupported in replication, master-slave and parallel mode |
| trust | md5 | yes | no auth |
| trust | md5 | no | “MD5” authentication with pgpool failed for user “XX” |
| trust | trust | yes/no | no auth |
- 即使postgresql 启动了，status 却仍然显示 down。
重现步骤: 配置好 backend1，运行pgpool，然后停止此 postgresql 数据库。
这时候即使重新启动了postgresql 数据库, pgpool 还是会一直设置 backend1 为 down。
倘若只有一个 backend，就会导致无法连接 pgpool。
找到一个设置状态的地方, 也就是logdir 配置的路径。找了好久好久，才发现可以在 logdir 配置的目录设置 backend 状态。
但是目前发现只能手动设置解决此问题，还未找到更好的方式。
找到了更好的方法：- 启动时添加 -D 参数,清除状态启动。
- 使用 pcp_attach_node 再次附加上去。
疑问
- pgpool stop 停止很久但是无法关闭。
在具有浮点IP的机器上关闭 pgpool 很久还是没有关闭。发现有一个原因是因为连接没有关闭，但是有时候连接关闭了，还添加了 -m fast 参数还是无法关闭。还未找到原因。
经过配置发现 开启 watchdog 会导致此问题。use_watchdog = off 则不会
提示如下。初步猜测是 watchdog 关闭出现了问题。目前只发现可以 ctrl+c 能干净的终止。1
2
3
4
5
6
7
8
9
10
112020-02-10 01:25:38: pid 28280: LOG: Watchdog is shutting down
2020-02-10 01:25:38: pid 28280: LOG: waiting for escalation process to exit before starting de-escalation
2020-02-10 01:25:39: pid 28280: LOG: waiting for escalation process to exit before starting de-escalation
2020-02-10 01:25:40: pid 28280: LOG: waiting for escalation process to exit before starting de-escalation
2020-02-10 01:25:41: pid 28280: LOG: waiting for escalation process to exit before starting de-escalation
2020-02-10 01:25:42: pid 28280: LOG: waiting for escalation process to exit before starting de-escalation
2020-02-10 01:25:43: pid 28280: LOG: escalation process does not exited in time
2020-02-10 01:25:43: pid 28280: DETAIL: starting the de-escalation anyway
2020-02-10 01:25:43: pid 40639: LOG: watchdog: de-escalation started
2020-02-10 01:25:43: pid 40639: LOG: successfully released the delegate IP:"192.168.56.122"
2020-02-10 01:25:43: pid 40639: DETAIL: 'if_down_cmd' returned with success
命令简介
- pgpool
-C memqcache_method 为 memcached 时，清除查询缓存
-n 不以守护模式运行
-m :
-X: 打开断言检查，调试帮助fast: 快速关闭 smart: 关闭查询之后再关闭 immediate： 和fast类似
-d, –debug 开启debug调试 - pcp_attach_node
-U, –username=NAME username for PCP authentication
-h, –host=HOSTNAME pgpool-II host
-p, –port=PORT PCP port number
-w, –no-password never prompt for password
-W, –password 强制密码认证
-n, –node-id=NODEID 节点ID
-d, –debug 开启 debug 信息
-v, –verbose 输出详细的信息 - pg_ctl
-D data目录，如果没有指明，默认环境变量 PGDATA。
-s 只打印 error
-t 超时时间
-V 版本信息
-w 等待直到程序完成
-W 不等待,也就是 -w 相反
-c 允许 postgres 生成核心文件
-l log 文件名
-m :
fast: 直接退出
smart: 等待连接断开后之后再关闭
immediate 立即关闭，无需完全关闭，导致重启后恢复。 - pg_rewind
-D, –target-pgdata=DIRECTORY 目标data目录，本地
–source-pgdata=DIRECTORY 源data目录，远程
–source-server=CONNSTR 连接字符串,eg:’host=192.168.56.138 port=5432 user=postgres password=d dbname=postgres’
-n, –dry-run 不修改任何东西的启动
-P, –progress 写进程信息
–debug 写大量debug信息