搭建pgpool-Ⅱ
搭建PostgreSQL的 基于 pgpool-Ⅱ 主备异步流复制的高可用方案
- 环境
服务器 | 角色 |
10.10.56.18:5432 | master |
10.10.56.19:5432 | slave |
10.10.56.87:5432 | VIP |
10.10.56.18:9999 | pgpool-Ⅱ Master |
10.10.56.19:9999 | pgpool-Ⅱ SLave |
- 介绍
PgPool-Ⅱ 是一个位于PostgreSQL 服务器和客户端的中间件,可以提供以下功能:
连接池、复制、负载均衡、限制超过限度的连接、并行查询等
pgpool-Ⅱ 的后端数据库支持不同小版本PG数据库,不支持大版本
pgpool-Ⅱ 负载均衡是基于会话级别的,不是语句级别的(即一个session开始到结束都是在同一个节点)
18、19 服务器分别安装Pgpool-Ⅱ
- 下载
pgpool-Ⅱ
,最好于PostgreSQL版本对应,此处为
[pgpool-Ⅱ下载地址:] http://www.pgpool.net/mediawiki/images/pgpool-II-3.7.3.tar.gz - 解压
CLW-DB3:/home/postgres/ha/pgpool # ls
pgpool-II-3.7.3.tar.gz
CLW-DB3:/home/postgres/ha/pgpool # tar xf pgpool-II-3.7.3.tar.gz
CLW-DB3:/home/postgres/ha/pgpool # cd pgpool-II-3.7.3/
- 编译检查到指定目录
/opt/pgpool-3
CLW-DB3:/home/postgres/ha/pgpool/pgpool-II-3.7.3 # ./configure --prefix=/opt/pgpool-3
...
checking that generated files are newer than configure... done
configure: creating ./config.status
config.status: creating src/sql/pgpool_adm/Makefile
config.status: creating Makefile
config.status: creating doc/Makefile
config.status: creating doc/src/Makefile
config.status: creating doc/src/sgml/Makefile
config.status: creating doc.ja/Makefile
config.status: creating doc.ja/src/Makefile
config.status: creating doc.ja/src/sgml/Makefile
config.status: creating src/Makefile
config.status: creating src/include/Makefile
config.status: creating src/parser/Makefile
config.status: creating src/libs/Makefile
config.status: creating src/libs/pcp/Makefile
config.status: creating src/tools/Makefile
config.status: creating src/tools/pgmd5/Makefile
config.status: creating src/tools/pcp/Makefile
config.status: creating src/watchdog/Makefile
config.status: creating src/include/config.h
config.status: executing libtool commands
CLW-DB3:/home/postgres/ha/pgpool/pgpool-II-3.7.3 #
看到如上信息表示编译检查完成
- make 编译
CLW-DB3:/home/postgres/ha/pgpool/pgpool-II-3.7.3 # make
...
make[3]: Leaving directory '/home/postgres/ha/pgpool/pgpool-II-3.7.3/src/tools/pgmd5'
make[3]: Entering directory '/home/postgres/ha/pgpool/pgpool-II-3.7.3/src/tools'
make[3]: Nothing to be done for 'all-am'.
make[3]: Leaving directory '/home/postgres/ha/pgpool/pgpool-II-3.7.3/src/tools'
make[2]: Leaving directory '/home/postgres/ha/pgpool/pgpool-II-3.7.3/src/tools'
Making all in include
make[2]: Entering directory '/home/postgres/ha/pgpool/pgpool-II-3.7.3/src/include'
make all-am
make[3]: Entering directory '/home/postgres/ha/pgpool/pgpool-II-3.7.3/src/include'
make[3]: Leaving directory '/home/postgres/ha/pgpool/pgpool-II-3.7.3/src/include'
make[2]: Leaving directory '/home/postgres/ha/pgpool/pgpool-II-3.7.3/src/include'
make[1]: Leaving directory '/home/postgres/ha/pgpool/pgpool-II-3.7.3/src'
make[1]: Entering directory '/home/postgres/ha/pgpool/pgpool-II-3.7.3'
make[1]: Nothing to be done for 'all-am'.
make[1]: Leaving directory '/home/postgres/ha/pgpool/pgpool-II-3.7.3'
CLW-DB3:/home/postgres/ha/pgpool/pgpool-II-3.7.3 #
看到如上表示编译完成
- make install 安装
CLW-DB3:/home/postgres/ha/pgpool/pgpool-II-3.7.3 # make install
...
make[4]: Nothing to be done for 'install-data-am'.
make[4]: Leaving directory '/home/postgres/ha/pgpool/pgpool-II-3.7.3/src/tools'
make[3]: Leaving directory '/home/postgres/ha/pgpool/pgpool-II-3.7.3/src/tools'
make[2]: Leaving directory '/home/postgres/ha/pgpool/pgpool-II-3.7.3/src/tools'
Making install in include
make[2]: Entering directory '/home/postgres/ha/pgpool/pgpool-II-3.7.3/src/include'
make[3]: Entering directory '/home/postgres/ha/pgpool/pgpool-II-3.7.3/src/include'
make[3]: Nothing to be done for 'install-exec-am'.
make[3]: Nothing to be done for 'install-data-am'.
make[3]: Leaving directory '/home/postgres/ha/pgpool/pgpool-II-3.7.3/src/include'
make[2]: Leaving directory '/home/postgres/ha/pgpool/pgpool-II-3.7.3/src/include'
make[1]: Leaving directory '/home/postgres/ha/pgpool/pgpool-II-3.7.3/src'
make[1]: Entering directory '/home/postgres/ha/pgpool/pgpool-II-3.7.3'
make[2]: Entering directory '/home/postgres/ha/pgpool/pgpool-II-3.7.3'
make[2]: Nothing to be done for 'install-exec-am'.
make[2]: Nothing to be done for 'install-data-am'.
make[2]: Leaving directory '/home/postgres/ha/pgpool/pgpool-II-3.7.3'
make[1]: Leaving directory '/home/postgres/ha/pgpool/pgpool-II-3.7.3'
- 到指定编译的目录查看
CLW-DB3:/home/postgres/ha/pgpool/pgpool-II-3.7.3 # cd /opt/pgpool-3/
CLW-DB3:/opt/pgpool-3 # ls
bin etc include lib share
CLW-DB3:/opt/pgpool-3 #
看到如上目录表示安装成功
- 配置环境变量 (方便使用命令),添加如下信息
postgres@CLW-DB3:/opt/pgpool-3/etc> vim /etc/profile
export PATH=/opt/pgpool-3/bin:$PATH
请使用 root
权限的账户安装,否则 VIP 无法自动切换。
安装扩展函数
-
18 master
安装pgpool_regclass
,PG8.0之后的内部使用
CLW-DB3:/home/postgres/ha/pgpool/pgpool-II-3.7.3/src/sql # cd /home/postgres/ha/pgpool/pgpool-II-3.7.3/src/sql
CLW-DB3:/home/postgres/ha/pgpool/pgpool-II-3.7.3/src/sql # ls
Makefile insert_lock.sql pgpool-recovery pgpool-regclass pgpool_adm
CLW-DB3:/home/postgres/ha/pgpool/pgpool-II-3.7.3/src/sql #
CLW-DB3:/home/postgres/ha/pgpool/pgpool-II-3.7.3/src/sql # make
CLW-DB3:/home/postgres/ha/pgpool/pgpool-II-3.7.3/src/sql # make install
postgres@CLW-DB3:~> cd ha/pgpool/pgpool-II-3.7.3/src/sql/pgpool-regclass/
postgres@CLW-DB3:~/ha/pgpool/pgpool-II-3.7.3/src/sql/pgpool-regclass> ls
Makefile pgpool-regclass.c pgpool-regclass.o pgpool-regclass.sql uninstall_pgpool-regclass.sql
pgpool_regclass--1.0.sql pgpool_regclass.control pgpool-regclass.so pgpool-regclass.sql.in
postgres@CLW-DB3:~/ha/pgpool/pgpool-II-3.7.3/src/sql/pgpool-regclass> psql -p 5433 -f pgpool-regclass.sql template1
CREATE FUNCTION
postgres@CLW-DB3:~/ha/pgpool/pgpool-II-3.7.3/src/sql/pgpool-regclass>
-
18 master
建立insert_lock
表
postgres@CLW-DB3:~/ha/pgpool/pgpool-II-3.7.3/src/sql> ls
insert_lock.sql Makefile pgpool_adm pgpool-recovery pgpool-regclass
postgres@CLW-DB3:~/ha/pgpool/pgpool-II-3.7.3/src/sql> psql -p 5433 -f insert_lock.sql template1
DROP TABLE
psql:insert_lock.sql:5: ERROR: schema "pgpool_catalog" already exists
CREATE TABLE
INSERT 0 1
GRANT
GRANT
GRANT
GRANT
postgres@CLW-DB3:~/ha/pgpool/pgpool-II-3.7.3/src/sql>
-
18 master
安装 C 语言函数
CLW-DB3:/home/postgres/ha/pgpool/pgpool-II-3.7.3/src/sql/pgpool-regclass # cd ../pgpool-recovery/
CLW-DB3:/home/postgres/ha/pgpool/pgpool-II-3.7.3/src/sql/pgpool-recovery # ls
Makefile pgpool-recovery.o pgpool-recovery.sql pgpool_recovery--1.1.sql uninstall_pgpool-recovery.sql
pgpool-recovery.c pgpool-recovery.so pgpool-recovery.sql.in pgpool_recovery.control
CLW-DB3:/home/postgres/ha/pgpool/pgpool-II-3.7.3/src/sql/pgpool-recovery # make install
/usr/bin/mkdir -p '/opt/pgsql-10/share/extension'
/usr/bin/mkdir -p '/opt/pgsql-10/share/extension'
/usr/bin/mkdir -p '/opt/pgsql-10/lib'
/usr/bin/install -c -m 644 .//pgpool_recovery.control '/opt/pgsql-10/share/extension/'
/usr/bin/install -c -m 644 .//pgpool_recovery--1.1.sql pgpool-recovery.sql '/opt/pgsql-10/share/extension/'
/usr/bin/install -c -m 755 pgpool-recovery.so '/opt/pgsql-10/lib/'
CLW-DB3:/home/postgres/ha/pgpool/pgpool-II-3.7.3/src/sql/pgpool-recovery # su - postgres
postgres@CLW-DB3:~> cd ha/pgpool/pgpool-II-3.7.3/src/sql/pgpool-recovery
postgres@CLW-DB3:~/ha/pgpool/pgpool-II-3.7.3/src/sql/pgpool-recovery> ls
Makefile pgpool-recovery.c pgpool-recovery.o pgpool-recovery.sql uninstall_pgpool-recovery.sql
pgpool_recovery--1.1.sql pgpool_recovery.control pgpool-recovery.so pgpool-recovery.sql.in
postgres@CLW-DB3:~/ha/pgpool/pgpool-II-3.7.3/src/sql/pgpool-recovery> psql -p 5433 -f pgpool-recovery.sql template1
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
postgres@CLW-DB3:~/ha/pgpool/pgpool-II-3.7.3/src/sql/pgpool-recovery>
- 在
18 master
PostgreSQL 中创建扩展
postgres=# create extension pgpool_regclass;
CREATE EXTENSION
postgres=# CREATE EXTENSION pgpool_recovery;
CREATE EXTENSION
postgres=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+---------------------+------------------+----------------------------------------------------------------------------------+--------
public | pgpool_pgctl | boolean | action text, stop_mode text | normal
public | pgpool_recovery | boolean | script_name text, remote_host text, remote_data_directory text | normal
public | pgpool_recovery | boolean | script_name text, remote_host text, remote_data_directory text, remote_port text | normal
public | pgpool_remote_start | boolean | remote_host text, remote_data_directory text | normal
public | pgpool_switch_xlog | text | arcive_dir text | normal
(5 rows)
postgres=#
注:上述扩展函数和插件在主库执行,因为PG数据库配置的为 主备流复制
,所以备库也会自动安装该扩展和函数
配置pgpool-Ⅱ 配置文件
-
18、19服务器
分别复制
默认pgpool 配置模板
文件
CLW-DB3:/home/postgres/ha/pgpool/pgpool-II-3.7.3/src/sql/pgpool-recovery # cd /opt/pgpool-3/etc/
CLW-DB3:/opt/pgpool-3/etc # ls
pcp.conf.sample pgpool.conf.sample-logical pgpool.conf.sample-replication pool_hba.conf.sample
pgpool.conf.sample pgpool.conf.sample-master-slave pgpool.conf.sample-stream
CLW-DB3:/opt/pgpool-3/etc # cp pgpool.conf.sample pgpool.conf
CLW-DB3:/opt/pgpool-3/etc # cp pcp.conf.sample pcp.conf
- 配置 18服务器
pgpool.conf
文件,该文件为pgpool-Ⅱ 的主要配置文件,用于配置具体参数
CLW-DB3:/opt/pgpool-3/etc # vim pgpool.conf
listen_addresses = '*' # rtm 用于pgpool监听地址,控制哪些地址可以通过pgpool 连接,`*`表示接受所有连接
port = 9999 # rtm pgpool 监听的端口
pcp_listen_addresses = '*' # rtm
pcp_port = 9898 # rtm
backend_hostname0 = '10.10.56.18' # rtm 配置后端postgreSQL 数据库地址,此处为 18master
backend_port0 = 5432 # rtm 后端postgreSQL 数据库端口
backend_weight0 = 1 # rtm 权重,用于负载均衡
backend_data_directory0 = '/pgdata/ha/masterdata' # rtm 后端postgreSQL 数据库实例目录
backend_flag0 = 'ALLOW_TO_FAILOVER' # rtm 允许故障自动切换
backend_hostname1 = '10.10.56.19' # rtm 此处为 19 PostgreSQL slave数据库地址
backend_port1 = 5432 # rtm
backend_weight1 = 1 # rtm
backend_data_directory1 = '/pgdata/ha/slavedata' # rtm
backend_flag1 = 'ALLOW_TO_FAILOVER' # rtm
enable_pool_hba = on # rtm 开启pgpool认证,需要通过 `pool_passwd` 文件对连接到数据库的用户进行md5认证
pool_passwd = 'pool_passwd' # rtm 认证文件
log_destination = 'stderr,syslog' # rtm 日志级别,标注错误输出和系统日志级别
log_line_prefix = '%t: pid %p: ' # rtm 日志输出格式
log_connections = on # rtm 开启日志
log_hostname = on # rtm 打印主机名称
#log_statement = all # rtm 取消注释则打印sql 语句
#log_per_node_statement = on # rtm 取消注释则开启打印sql负载均衡日志,记录sql负载到每个节点的执行情况
#client_min_messages = log # rtm 日志
#log_min_messages = info # rtm # 日志级别
pid_file_name = '/opt/pgpool-3/run/pgpool/pgpool.pid' # rtm pgpool的运行目录,若不存在则先创建
logdir = '/opt/pgpool-3/log/pgpool' # rtm 指定日志输出的目录
replication_mode = off # rtm 关闭pgpool的复制模式
load_balance_mode = on # rtm 开启负载均衡
master_slave_mode = on # rtm 开启主从模式
master_slave_sub_mode = 'stream' # rtm 设置主从为流复制模式
sr_check_period = 10 # rtm 流复制的延迟检测的时间间隔
sr_check_user = 'pgcheck' # rtm 流复制的检查用户,该用户需要在pg数据库中存在,且拥有查询权限
sr_check_password = '123456' # rtm
sr_check_database = 'postgres' # rtm 流复制检查的数据库名称
delay_threshold = 10000000 # rtm 设置允许主备流复制最大延迟字节数,单位为kb
health_check_period = 10 # rtm pg数据库检查检查间隔时间
health_check_timeout = 20 # rtm
health_check_user = 'pgcheck' # rtm 健康检查用户,需pg数据库中存在
health_check_password = '123456' # rtm
health_check_database = 'postgres' # rtm 健康检查的数据库名称
health_check_max_retries = 3 # rtm 健康检查最大重试次数
health_check_retry_delay = 3 # rtm 重试次数间隔
failover_command = '/opt/pgpool-3/script/failover.sh' # rtm 故障切换脚本
fail_over_on_backend_error = off # rtm 如果设置了health_check_max_retries次数,则关闭该参数
use_watchdog = on # rtm 开启看门狗,用于监控pgpool 集群健康状态
wd_hostname = '10.10.56.18' # rtm 本地看门狗地址
wd_port = 9000 # rtm
wd_priority = 1 # rtm 看门狗优先级,用于pgpool 集群中master选举
delegate_IP = '10.10.56.87' # rtm VIP 地址
if_up_cmd = 'ip addr add $_IP_$/24 dev eth0 label eth0:0' # rtm 配置虚拟IP到本地网卡
if_down_cmd = 'ip addr del $_IP_$/24 dev eth0' # rtm
wd_lifecheck_method = 'heartbeat # rtm' 看门狗健康检测方法
wd_heartbeat_port = 9694 # rtm 看门狗心跳端口,用于pgpool 集群健康状态通信
wd_heartbeat_keepalive = 2 # rtm 看门狗心跳检测间隔
wd_heartbeat_deadtime = 30 # rtm
heartbeat_destination0 = '10.10.56.19' # rtm 配置需要监测健康心跳的IP地址,非本地地址,即互相监控,配置对端的IP地址
heartbeat_destination_port0 = 9694 # rtm 监听的端口
heartbeat_device0 = 'eth0' # rtm 监听的网卡名称
wd_life_point = 3 # rtm 生命检测失败后重试次数
wd_lifecheck_query = 'SELECT 1' # rtm 用于检查 pgpool-II 的查询语句。默认为“SELECT 1”。
wd_lifecheck_dbname = 'postgres' # rtm 检查健康状态的数据库名称
wd_lifecheck_user = 'pgcheck' # rtm 检查数据库的用户,该用户需要在Postgres数据库存在,且有查询权限
wd_lifecheck_password = '123456' # rtm 看门狗健康检查用户密码
other_pgpool_hostname0 = '10.10.56.19' # rtm 指定被监控的 pgpool-II 服务器的主机名
other_pgpool_port0 = 9999 # rtm 指定被监控的 pgpool-II 服务器的端口号
other_wd_port0 = 9000 # rtm 指定 pgpool-II 服务器上的需要被监控的看门狗的端口号
- 18服务器创建pgpool 的
pid
目录,为pgpool的启动进程文件目录
CLW-DB3:/opt/pgpool-3/etc # mkdir -p /opt/pgpool-3/run/pgpool/
- 配置 19服务器的
pgpool.conf
文件
listen_addresses = '*' # rtm
port = 9999 # rtm
pcp_listen_addresses = '*' # rtm
pcp_port = 9898 # rtm
backend_hostname0 = '10.10.56.18' # rtm
backend_port0 = 5432 # rtm
backend_weight0 = 1 # rtm
backend_data_directory0 = '/pgdata/ha/masterdata' # rtm
backend_flag0 = 'ALLOW_TO_FAILOVER' # rtm
backend_hostname1 = '10.10.56.19' # rtm
backend_port1 = 5432 # rtm
backend_weight1 = 1 # rtm
backend_data_directory1 = '/pgdata/ha/slavedata' # rtm
backend_flag1 = 'ALLOW_TO_FAILOVER' # rtm
enable_pool_hba = on # rtm
pool_passwd = 'pool_passwd' # rtm
log_destination = 'stderr,syslog' # rtm
log_line_prefix = '%t: pid %p: ' # rtm # printf-style string to output at beginning of each log line.
log_connections = on # rtm
log_hostname = on # rtm
#log_statement = all # rtm
#log_per_node_statement = on # rtm
#client_min_messages = log # rtm # values in order of decreasing detail:
#log_min_messages = info # rtm # values in order of decreasing detail:
pid_file_name = '/opt/pgpool-3/run/pgpool/pgpool.pid' # rtm
logdir = '/opt/pgpool-3/log/pgpool' # rtm
replication_mode = off # rtm
load_balance_mode = on # rtm
master_slave_mode = on # rtm
master_slave_sub_mode = 'stream' # rtm
sr_check_period = 10 # rtm
sr_check_user = 'pgcheck' # rtm
sr_check_password = '123456' # rtm
sr_check_database = 'postgres' # rtm
delay_threshold = 10000000 # rtm
health_check_period = 10 # rtm
health_check_timeout = 20 # rtm
health_check_user = 'pgcheck' # rtm
health_check_password = '123456' # rtm
health_check_database = 'postgres' # rtm
health_check_max_retries = 3 # rtm
health_check_retry_delay = 3 # rtm
failover_command = '/opt/pgpool-3/script/failover.sh' # rtm
fail_over_on_backend_error = off # rtm
use_watchdog = on # rtm
wd_hostname = '10.10.56.19' # rtm
wd_port = 9000 # rtm
wd_priority = 1 # rtm
delegate_IP = '10.10.56.87' # rtm
if_up_cmd = 'ip addr add $_IP_$/24 dev eth0 label eth0:0' # rtm
if_down_cmd = 'ip addr del $_IP_$/24 dev eth0' # rtm
wd_heartbeat_port = 9694 # rtm
wd_heartbeat_keepalive = 2 # rtm
wd_heartbeat_deadtime = 30 # rtm
heartbeat_destination0 = '10.10.56.18' # rtm
heartbeat_destination_port0 = 9694 # rtm
heartbeat_device0 = 'eth0' # rtm
wd_life_point = 3 # rtm
wd_lifecheck_query = 'SELECT 1' # rtm
wd_lifecheck_dbname = 'postgres' # rtm
wd_lifecheck_user = 'pgcheck' # rtm
wd_lifecheck_password = '123456' # rtm
other_pgpool_hostname0 = '10.10.56.18' # rtm
other_pgpool_port0 = 9999 # rtm
other_wd_port0 = 9000 # rtm
- 19服务器创建pgpool 的
pid
目录,为pgpool的启动进程文件目录
CLW-DB4:/opt/pgpool-3/etc # mkdir -p /opt/pgpool-3/run/pgpool/
- 分别在
18、19
服务器,配置该pcp.conf
文件,通过pg_md5
工具可生成对应的 md5密码
CLW-DB3:/opt/pgpool-3/etc # pg_md5 123456
e10adc3949ba59abbe56e057f20f883e
CLW-DB3:/opt/pgpool-3/etc # tail -2 pcp.conf
# USERID:MD5PASSWD
postgres:e10adc3949ba59abbe56e057f20f883e
CLW-DB3:/opt/pgpool-3/etc #
- 分别在
18、19
服务器, 配置pool_passwd
文件,用户:postgres
密码:123456
CLW-DB3:/opt/pgpool-3/etc # pg_md5 -m -p -u postgres pool_passwd
password:
CLW-DB3:/opt/pgpool-3/etc # cat pool_passwd
postgres:md5a3556571e93b0d20722ba62be61e8c2d
pgcheck:md5afe8b63242d4aeae80679e2545bb4ffd
CLW-DB3:/opt/pgpool-3/etc #
此文件默认不存在,可通过 pg_md5
自动生成该文件,该文件存放用户名和MD5密码,该文件用户控制认证哪些用户可以通过密码验证访问pgpool-Ⅱ。
注意:上述 pcp.conf
和 pool_passwd
文件中用户名和密码直接不能包含空格,默认会把空格当成字符。
- 以上方式使用明文密码,不安全,也可通过以下方式来获取MD5密码
postgres=# SELECT rolpassword FROM pg_authid WHERE rolname='postgres';
rolpassword
-------------------------------------
md5a3556571e93b0d20722ba62be61e8c2d
(1 row)
postgres=#
之后将 psotgres
用户名,和密码:md5a3556571e93b0d20722ba62be61e8c2d
写入 pool_passwd
文件,并且用 冒号
相隔,且中间 不能包含空格
- 分别18、19启动
pgpool
,默认为守护进程启动,不会打印日志,若需显示打印信息,则如下启动
postgres@CLW-DB3:/opt/pgpool-3/etc> pgpool -n -d > pgpool.log 2>&1 &
[1] 14606
-d 模式Debug下log
-n 是不使用后台模式
在 后台启动pgpool
,并将标准错误输出和标准输出 到指定 pgpool.log
日志文件,可指定绝对路径
- 停止
pgpool
postgres@CLW-DB3:~> pgpool -m fast stop
2018-06-19 14:25:59: pid 7258: LOG: stop request sent to pgpool. waiting for termination...
.done.
postgres@CLW-DB3:~>
- 重新加载
pgpool
postgres@CLW-DB3:/opt/pgpool-3/etc> pgpool reload
- 搭建主备异步流复制略,请参考之前postgresql 异步流复制搭建指导
- 连接
pgpool
错误一
postgres@CLW-DB3:~> psql -h 127.0.0.1 -p 9999 postgres postgres -w
psql: ERROR: MD5 authentication is unsupported in replication and master-slave modes.
HINT: check pg_hba.conf
postgres@CLW-DB3:~>
- 参数介绍
-h 指定pgpoo-Ⅱ 服务器地址或者VIP -p 指定pgpool-Ⅱ 端口
第一个`postgres` 为数据库名称 第二个 `postgres` 为用户名称
-w 表示不输入密码,该方式需要在postgres的home目录下配置 .pgpass 文件,且权限为600
该错误为 pgpool-Ⅱ 本地默认认证方式为trust,只需要把 pool_hba.conf
对应的 trust
改为 md5
postgres@CLW-DB3:/opt/pgpool-3/etc> ls
pcp.conf pgpool.conf pgpool.conf.sample-logical pgpool.conf.sample-replication pool_hba.conf pool_passwd
pcp.conf.sample pgpool.conf.sample pgpool.conf.sample-master-slave pgpool.conf.sample-stream pool_hba.conf.sample
postgres@CLW-DB3:/opt/pgpool-3/etc> vim pool_hba.conf
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 md5 # 此处trust 修改为 md5
- 修改后重启
pgpool
,或者reload
postgres@CLW-DB3:/opt/pgpool-3/etc> pgpool -m fast stop
2018-06-19 15:05:47: pid 14562: LOG: stop request sent to pgpool. waiting for termination...
.done.
postgres@CLW-DB3:/opt/pgpool-3/etc> pgpool -n -d > pgpool.log 2>&1 &
[1] 14606
postgres@CLW-DB3:/opt/pgpool-3/etc> ls
- 连接 pgpool 错误2
postgres@CLW-DB3:/opt/pgpool-3/etc> psql -h 10.10.56.18 -p 9999 pgpool repl
psql: FATAL: md5 authentication failed
DETAIL: pool_passwd file does not contain an entry for "repl"
出现该问题是因为在 pool_passwd
文件中没有配置该用户 repl
- 连接 pgpool,-h 为 pgpool 服务器安装的地址,或者为配置的VIP
postgres@CLW-DB3:/opt/pgpool-3/etc> psql -h 127.0.0.1 -p 9999 postgres postgres
Password for user postgres:
psql (10.3)
Type "help" for help.
postgres=#
- 查询节后端数据库节点状态,若没有该函数,则说明没有在postgresql 数据库安装pgpool的扩展函数和插件
postgres=# show pool_nodes;
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay
---------+-------------+------+--------+-----------+---------+------------+-------------------+-------------------
0 | 10.10.56.19 | 5433 | up | 0.500000 | primary | 5 | true | 0
1 | 10.10.56.19 | 5434 | up | 0.500000 | standby | 0 | false | 0
(2 rows)
postgres=#
上述 status
中 up
表示后端Postgres数据库正在运行 , role
为数据库对应的角色, lb_weight
为权重 load_balance_node
是否开启负载均衡 ,replication_delay
复制延迟
- 显示
pgpool
的线程池的进程信息
postgres=# show pool_processes;
pool_pid | start_time | database | username | create_time | pool_counter
----------+---------------------+----------+----------+---------------------+--------------
14607 | 2018-06-19 15:06:04 | | | |
14608 | 2018-06-19 15:06:04 | | | |
14609 | 2018-06-19 15:06:04 | | | |
14610 | 2018-06-19 15:06:04 | | | |
14611 | 2018-06-19 15:06:04 | | | |
14612 | 2018-06-19 15:06:04 | | | |
14613 | 2018-06-19 15:06:04 | | | |
14614 | 2018-06-19 15:06:04 | | | |
14615 | 2018-06-19 15:06:04 | | | |
14616 | 2018-06-19 15:06:04 | | | |
14617 | 2018-06-19 15:06:04 | | | |
14618 | 2018-06-19 15:06:04 | | | |
14619 | 2018-06-19 15:06:04 | | | |
14620 | 2018-06-19 15:06:04 | | | |
16808 | 2018-06-19 15:18:32 | | | |
14622 | 2018-06-19 15:06:04 | | | |
16778 | 2018-06-19 15:18:23 | | | |
14624 | 2018-06-19 15:06:04 | | | |
14625 | 2018-06-19 15:06:04 | | | |
14626 | 2018-06-19 15:06:04 | | | |
14627 | 2018-06-19 15:06:04 | | | |
14628 | 2018-06-19 15:06:04 | | | |
14629 | 2018-06-19 15:06:04 | | | |
14630 | 2018-06-19 15:06:04 | | | |
14631 | 2018-06-19 15:06:04 | | | |
14632 | 2018-06-19 15:06:04 | | | |
14633 | 2018-06-19 15:06:04 | | | |
14634 | 2018-06-19 15:06:04 | postgres | postgres | 2018-06-19 15:35:14 | 1
14635 | 2018-06-19 15:06:04 | | | |
14636 | 2018-06-19 15:06:04 | | | |
14637 | 2018-06-19 15:06:04 | | | |
14638 | 2018-06-19 15:06:04 | | | |
(32 rows)
- 查看
pgpool
配置信息
postgres=# show pool_status;
item | value | description
--------------------------------------+---------------------------------------------+---------------------------------------------------------------------------------
listen_addresses | * | host name(s) or IP address(es) to listen on
port | 9999 | pgpool accepting port number
socket_dir | /tmp | pgpool socket directory
pcp_listen_addresses | * | host name(s) or IP address(es) for pcp process to listen on
pcp_port | 9898 | PCP port # to bind
pcp_socket_dir | /tmp | PCP socket directory
enable_pool_hba | 1 | if true, use pool_hba.conf for client authentication
pool_passwd | pool_passwd | file name of pool_passwd for md5 authentication
authentication_timeout | 60 | maximum time in seconds to complete client authentication
ssl | 0 | SSL support
ssl_key | | path to the SSL private key file
ssl_cert | | path to the SSL public certificate file
ssl_ca_cert | | path to a single PEM format file
ssl_ca_cert_dir | | directory containing CA root certificate(s)
num_init_children | 32 | # of children initially pre-forked
listen_backlog_multiplier | 2 | determines the size of the queue for pending connections
serialize_accept | 0 | whether to serialize accept() call
max_pool | 4 | max # of connection pool per child
child_life_time | 300 | if idle for this seconds, child exits
child_max_connections | 0 | if max_connections received, chile exits
connection_life_time | 0 | if idle for this seconds, connection closes
client_idle_limit | 0 | if idle for this seconds, child connection closes
log_destination | stderr | logging destination
log_line_prefix | %t: pid %p: | printf-style string to output at beginning of each log line
log_error_verbosity | 1 | controls how much detail about error should be emitted
client_min_messages | 18 | controls which message should be sent to client
log_min_messages | 14 | controls which message should be emitted to server log
log_connections | 0 | if true, print incoming connections to the log
log_hostname | 0 | if true, resolve hostname for ps and log print
log_statement | 0 | if non 0, logs all SQL statements
log_per_node_statement | 0 | if non 0, logs all SQL statements on each node
log_standby_delay | 3 | how to log standby delay
syslog_facility | LOCAL0 | syslog local faclity
syslog_ident | pgpool | syslog program ident string
pid_file_name | /opt/pgpool-3/run/pgpool/pgpool.pid | path to pid file
logdir | /var/log/pgpool | PgPool status file logging directory
connection_cache | 1 | if true, cache connection pool
reset_query_list | ABORT; DISCARD ALL | queries issued at the end of session
- 查看
pgpool
连接池中的各个连接信息
postgres=# show pool_pools;
pool_pid | start_time | pool_id | backend_id | database | username | create_time | majorversion | minorversion | pool_counter | pool_backendpi
d | pool_connected
----------+---------------------+---------+------------+----------+----------+---------------------+--------------+--------------+--------------+---------------
--+----------------
14607 | 2018-06-19 15:06:04 | 0 | 0 | | | | 0 | 0 | 0 | 0
| 0
14607 | 2018-06-19 15:06:04 | 0 | 1 | | | | 0 | 0 | 0 | 0
| 0
14607 | 2018-06-19 15:06:04 | 1 | 0 | | | | 0 | 0 | 0 | 0
| 0
14607 | 2018-06-19 15:06:04 | 1 | 1 | | | | 0 | 0 | 0 | 0
| 0
14607 | 2018-06-19 15:06:04 | 2 | 0 | | | | 0 | 0 | 0 | 0
| 0
14607 | 2018-06-19 15:06:04 | 2 | 1 | | | | 0 | 0 | 0 | 0
| 0
14607 | 2018-06-19 15:06:04 | 3 | 0 | | | | 0 | 0 | 0 | 0 | 0
14607 | 2018-06-19 15:06:04 | 3 | 1 | | | | 0 | 0 | 0 | 0 | 0
14608 | 2018-06-19 15:06:04 | 0 | 0 | | | | 0 | 0 | 0 | 0 | 0
14608 | 2018-06-19 15:06:04 | 0 | 1 | | | | 0 | 0 | 0 | 0 | 0
14608 | 2018-06-19 15:06:04 | 1 | 0 | | | | 0 | 0 | 0 | 0 | 0
14608 | 2018-06-19 15:06:04 | 1 | 1 | | | | 0 | 0 | 0 | 0 | 0
14608 | 2018-06-19 15:06:04 | 2 | 0 | | | | 0 | 0 | 0 | 0 | 0
14608 | 2018-06-19 15:06:04 | 2 | 1 | | | | 0 | 0 | 0 | 0
- 添加
任意注释
来取消负载均衡
CLW-DB3:/opt/pgpool-3/etc # psql -h 127.0.0.1 -p 9999 postgres postgres
Password for user postgres:
psql (10.3)
Type "help" for help.
postgres=# \c pgpool
You are now connected to database "pgpool" as user "postgres".
pgpool=# /*REPLICATION*/ update sr_delay set sr_date = now () where id = 1;
UPDATE 1
pgpool=# select * from sr_delay ;
id | sr_date
----+---------------------------
1 | 2018-06-19 17:31:47.52918
(1 row)
- 使用虚拟IP 时 pgpool 用户需要root权限,若使用非root用户则需要下述操作,root用户则无需该操作
- 问题1
arping[/usr/sbin/arping] doesn't have setuid bit
CLW-DB4:/home/postgres/pgpool/pgpool-II-3.7.3/src/sql/pgpool-regclass # cd /sbin/
CLW-DB4:/sbin # chmod +s ifup
CLW-DB4:/sbin # chmod +s ifconfig
CLW-DB4:/sbin # chmod +s arping
CLW-DB4:/sbin #
- 问题2
could not open pid file as /opt/pgpool-3/run/pgpool/pgpool.pid. reason: No such file or directory
,该目录用于存放pgpool启动进程的 PID 文件
postgres@CLW-DB4:/opt/pgpool-3/run> mkdir -p /opt/pgpool-3/run/pgpool/
pcp 介绍
- 背景
pcp 是用来管理 pgpool 的linux命令 ,所有参数 pgpool-Ⅱ 3.5之后
都发生了变化,通过pcp.conf 来管理认证连接,管理哪些用户可以通过
pcp 连接管理pgpool-Ⅱ
- pcp 命令参数说明
-h 为pgpool服务器安装地址,或者VIP 地址
-d 表示为debug 模式
-U 为pcp 用户,该用户为 `pcp.conf` 配置文件配置的用户,与数据库用户无关,推荐全部使用统一用户,便于管理
-v 表示输出详细信息
- 通过
pcp
查看pgpool的配置信息
CLW-DB3:~ # pcp_pool_status -h 127.0.0.1 -p 9898 -U postgres -v -d
...
Name [128]: backend_hostname1
Value: 10.10.56.19
Description: backend #1 hostname
Name [129]: backend_port1
Value: 5432
Description: backend #1 port number
Name [130]: backend_weight1
Value: 0.500000
Description: weight of backend #1
Name [131]: backend_data_directory1
Value: /pgdata/ha/slavedata
Description: data directory for backend #1
Name [132]: backend_status1
Value: down
Description: status of backend #1
Name [133]: standby_delay1
Value: 0
Description: standby delay of backend #1
Name [134]: backend_flag1
Value: ALLOW_TO_FAILOVER
Description: backend #1 flag
Name [135]: other_pgpool_hostname0
Value: 10.10.56.19
Description: pgpool #0 hostname
Name [136]: other_pgpool_port0
Value: 9999
Description: pgpool #0 port number
Name [137]: other_pgpool_wd_port0
Value: 9000
Description: pgpool #0 watchdog port number
Name [138]: heartbeat_device0
Value: eth0
Description: name of NIC device #0 for sending hearbeat
Name [139]: heartbeat_destination0
Value: 10.10.56.19
Description: destination host for sending heartbeat using NIC device 0
Name [140]: heartbeat_destination_port0
Value: 9694
Description: destination port for sending heartbeat using NIC device 0
DEBUG: send: tos="X", len=4
- 查看
pgpool
集群状态
CLW-DB4:/opt/pgpool-3/bin # pcp_watchdog_info -h 127.0.0.1 -p 9898 -v -U postgres
Password:
Watchdog Cluster Information
Total Nodes : 2
Remote Nodes : 1
Quorum state : QUORUM EXIST
Alive Remote Nodes : 1
VIP up on local node : YES
Master Node Name : 10.10.56.19:9999 Linux CLW-DB4
Master Host Name : 10.10.56.19
Watchdog Node Information
Node Name : 10.10.56.19:9999 Linux CLW-DB4
Host Name : 10.10.56.19
Delegate IP : 10.10.56.87
Pgpool port : 9999
Watchdog port : 9000
Node priority : 1
Status : 4
Status Name : MASTER
Node Name : 10.10.56.18:9999 Linux CLW-DB3
Host Name : 10.10.56.18
Delegate IP : 10.10.56.87
Pgpool port : 9999
Watchdog port : 9000
Node priority : 1
Status : 7
Status Name : STANDBY
CLW-DB4:/opt/pgpool-3/bin #
- 参数说明
Total Nodes : 节点数量
Quorum state:集群选举状态,当pgpool 服务器数量大于3时,通过优先级来进行选举
VIP up on local node : VIP 是否在当前节点
Status :当前节点状态
Status Name : 当前节点名称
- 查看
pgpool-Ⅱ
节点数量
CLW-DB3:/opt/pgpool-3/bin # pcp_node_count -h 127.0.0.1 -p 9898 -U postgres -dv
Password:
DEBUG: recv: tos="m", len=8
DEBUG: recv: tos="r", len=21
DEBUG: send: tos="L", len=4
DEBUG: recv: tos="l", len=22
Node Count
____________
2
DEBUG: send: tos="X", len=4
CLW-DB3:/opt/pgpool-3/bin #
- 添加一个节点、或者删除一个节点
pcp_attach_node
和pcp_detach_node
,
CLW-DB3:/opt/pgpool-3/bin # pcp_attach_node --help
pcp_attach_node - attach a node from pgpool-II
Usage:
pcp_attach_node [OPTION...] [node-id]
Options:
-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 force password prompt (should happen automatically)
-n, --node-id=NODEID ID of a backend node
-d, --debug enable debug message (optional)
-v, --verbose output verbose messages
-?, --help print this help
- 查看
pgpool
节点状态
postgres@CLW-DB3:/opt/pgpool-3/bin> pcp_node_info -h 127.0.0.1 -p 9898 -U postgres -dv -n 1
Password:
DEBUG: recv: tos="m", len=8
DEBUG: recv: tos="r", len=21
DEBUG: send: tos="I", len=6
DEBUG: recv: tos="i", len=59
Hostname : 10.10.56.19
Port : 5432
Status : 3
Weight : 0.500000
Status Name: down
Role : standby
DEBUG: send: tos="X", len=4
postgres@CLW-DB3:/opt/pgpool-3/bin> pcp_node_info -h 127.0.0.1 -p 9898 -U postgres -dv -n 0
Password:
DEBUG: recv: tos="m", len=8
DEBUG: recv: tos="r", len=21
DEBUG: send: tos="I", len=6
DEBUG: recv: tos="i", len=59
Hostname : 10.10.56.18
Port : 5432
Status : 1
Weight : 0.500000
Status Name: waiting
Role : primary
DEBUG: send: tos="X", len=4
postgres@CLW-DB3:/opt/pgpool-3/bin>
- 参数说明
status : 为pgpool的状态 ,共有0、1、2、3
0 表示初始化,pcp永远不会显示该状态
1 表示 up 在运行状态,但是还没有用户连接
2 表示 up 在运行状态,用户连接使用中
3 表示 down 该节点停止服务
Weight 表示权重
Status Name: 参数状态
up 表示启用用户连接 与 1 对应
waiting 表示启用等待用户连接 与 2 对应
down 表示停止服务器状态 与 3 对应
- 查看更多相关命令
CLW-DB3:/opt/pgpool-3/bin # ls
pcp_attach_node pcp_node_count pcp_pool_status pcp_proc_info pcp_recovery_node pcp_watchdog_info pgpool watchdog_setup
pcp_detach_node pcp_node_info pcp_proc_count pcp_promote_node pcp_stop_pgpool pg_md5 pgpool_setup
CLW-DB3:/opt/pgpool-3/bin #
- 添加 pgpool 节点,其中参数
-n 1
表示需要添加的pgpool 机器对应的节点id为1
postgres@CLW-DB3:/opt/pgpool-3/bin> pcp_attach_node -h 127.0.0.1 -p 9898 -v -n 1
Password:
pcp_attach_node -- Command Successful
- 删除一个 pgpool 节点 ,参数
n 1 表示该pgpool节点id 为 1
postgres@CLW-DB3:/opt/pgpool-3/bin> pcp_detach_node n 1
pcp_detach_node: Warning: extra command-line argument "1" ignored
Password:
pcp_detach_node -- Command Successful
- 查看
sql语句
负载均衡
CLW-DB4:~ # psql -h 127.0.0.1 -p 9999 -U postgres
Password for user postgres:
psql (10.3)
Type "help" for help.
postgres=# \c pgpool
pgpool=# select * from sr_delay;
LOG: statement: select * from sr_delay;
LOG: DB node id: 1 backend pid: 24866 statement: select * from sr_delay;
id | sr_date
----+----------------------------
1 | 2018-06-21 14:57:28.152245
上述可知 该查询语句分配在 DB node 1
,即备库,负载是基于会话(session)级别的,即第一次分配在哪个
库,再结束该会话前会一直再该库
- 查看更新
update语句
负载情况
pgpool=# update sr_delay set sr_date = now() where id = 1 returning *;
LOG: statement: update sr_delay set sr_date = now() where id = 1 returning *;
LOG: DB node id: 0 backend pid: 8445 statement: update sr_delay set sr_date = now() where id = 1 returning *;
id | sr_date
----+----------------------------
1 | 2018-06-21 14:57:28.152245
(1 row)
UPDATE 1
上述可知,该更新语句负载分配到 DB node id: 0
主节点上
- 查看
VIP
CLW-DB4:~ # 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: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
link/ether fa:16:3e:19:a7:a8 brd ff:ff:ff:ff:ff:ff
inet 10.10.56.19/24 brd 10.10.56.255 scope global eth0
valid_lft forever preferred_lft forever
inet 10.10.56.87/24 scope global secondary eth0:0
valid_lft forever preferred_lft forever
inet6 fe80::f816:3eff:fe19:a7a8/64 scope link
valid_lft forever preferred_lft forever
CLW-DB4:~ #
发现 VIP 绑定在master pgpool 服务器上
- 配置
pgpool.conf
自动切换脚本failover.sh
,设置权限755
failover_command = '/opt/pgpool-3/script/failover.sh 1 5432 10.10.56.19 /pgdata/ha/slavedata'
- 创建上述配置目录和脚本
CLW-DB3:/opt/pgpool-3 # ls
bin etc include lib log pgpool pgpool.log run share
CLW-DB3:/opt/pgpool-3 # mkdir script
CLW-DB3:/opt/pgpool-3 # ls
bin etc include lib log pgpool pgpool.log run script share
CLW-DB3:/opt/pgpool-3 # cd script/
CLW-DB3:/opt/pgpool-3/script # vim failover.sh
CLW-DB3:/opt/pgpool-3/script # ls
failover.sh
CLW-DB3:/opt/pgpool-3/script # chmod 755 failover.sh
CLW-DB3:/opt/pgpool-3/script # ll
total 0
-rwxr-xr-x 1 root root 0 Jun 21 16:50 failover.sh
- 配置
failover.sh
脚本内容如下
#!/bin/bash
export PGPORT=5432
export PGUSER=postgres
export PG_OS_USER=postgres
export PGDBNAME=pgpool
export PGPATH=/opt/pgsql-10/bin
export PATH=$PATH:$PGPATH
export PGMIP=10.10.56.19
export PGDATA=/pgdata/ha/slavedata
log=/opt/pgpool-3/log/pgpool/failover.log
set PGPASSWORD=123456
# 主备数据库同步时延,单位为秒
SQL1='select pg_is_in_recovery from pg_is_in_recovery();'
db_role=`echo $SQL1 | psql -At -p $PGPORT -U $PGUSER -d $PGDBNAME`
SWITCH_COMMAND='pg_ctl promote -D /pgdata/ha/slavedata'
# 如果为主库,则不切换
if [ $db_role == t ];then
echo -e `date +"%F %T"` "Attention:The current database is statndby,ready to switch master database!" >> $log
su - $PG_OS_USER -c "$SWITCH_COMMAND"
echo -e `date +"%F %T"` "success:The current standby database successed to switched the primary PG database !" >> $log
exit 0
fi
- 在19 服务器postgreSQL 备库创建
trigger_file
文件
CLW-DB4:cd /pgdata/ha/slavedata
CLW-DB4: /pgdata/ha/slavedata # touch trigger_file
CLW-DB4: /pgdata/ha/slavedata # chmod 755 trigger_file
CLW-DB4:/pgdata/ha/slavedata # ll trigger_file
-rwxr-xr-x 1 postgres postgres 0 Jun 22 15:06 trigger_file
CLW-DB4:/pgdata/ha/slavedata #
测试主库宕机,自动切换
- 查看正常运行时,数据库状态
CLW-DB4:psql -h 10.10.56.87 -p 9999 postgres postgres
postgres=# show pool_nodes;
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay
---------+-------------+------+--------+-----------+---------+------------+-------------------+-------------------
0 | 10.10.56.18 | 5432 | up | 0.500000 | primary | 0 | true | 0
1 | 10.10.56.19 | 5432 | up | 0.500000 | standby | 0 | false | 0
(2 rows)
- 上述参数介绍
node_id : 对应 pgpool-Ⅱ 服务器节点的 id
hostname : postsgreSQL数据库 服务器的 IP 地址
port : postgresql 数据库端口
lb_weight : 权重,用于控制sql语句的负载 ,此处配置的为1:1 及 平均分配
role :postgresSQL 数据库的角色
load_balance_node:是否开启负载均衡
上述可知 18 为 primary
,19 为 standby
,PostgreSQL 数据库服务 status
都为 up
即都正常运行
-
停止18服务器
主数据库的服务,模拟数据库宕机
postgres@CLW-DB3:/pgdata/ha/masterdata> pg_ctl -D /pgdata/ha/masterdata/ stop
waiting for server to shut down.... done
server stopped
postgres@CLW-DB3:/pgdata/ha/masterdata>
- 查看此时数据库
节点状态
CLW-DB4:/opt/pgpool-3 # psql -h 10.10.56.87 -p 9999 postgres postgres
postgres=# show pool_nodes;
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay
---------+-------------+------+--------+-----------+---------+------------+-------------------+-------------------
0 | 10.10.56.18 | 5432 | down | 0.500000 | standby | 0 | false | 0
1 | 10.10.56.19 | 5432 | up | 0.500000 | primary | 0 | true | 0
(2 rows)
发现此时 18服务器
数据库已经 down
即 服务停止了,且 19服务器
数据库由 standby
切换为 primary
- 测试变更数据
pgpool=# select * from sr_delay ;
id | sr_date
----+----------------------------
1 | 2018-06-21 17:08:45.842635
(1 row)
pgpool=# update sr_delay set sr_date = now() where id = 1 returning *;
id | sr_date
----+----------------------------
1 | 2018-06-22 16:32:04.294857
(1 row)
UPDATE 1
pgpool=#
发现数据库可以正常提供服务,即 pgpool自动切换
成功!